Materialized views на собеседовании Data Engineer

Что такое materialized view

View — сохранённый запрос, выполняется при каждом обращении.

Materialized view (MV) — сохранённый запрос с физическим хранением результата. Читается быстро (как обычная таблица), но требует обновления при изменениях источника.

Trade-off: скорость чтения vs свежесть данных vs стоимость поддержки.

На собесе DE спрашивают: «когда вместо таблицы делать MV?», «как обновляется MV в Postgres / ClickHouse?», «чем MV отличается от incremental-таблицы dbt?».

Главная боль без MV — аналитик каждый раз пересчитывает «выручка по дням» из 10ТБ-fact'а. 30 секунд × 100 запросов в день = час compute. С MV — 200 мс на запрос.

MV в Postgres / Greenplum

CREATE MATERIALIZED VIEW orders_daily AS
SELECT
    DATE_TRUNC('day', created_at) AS order_date,
    COUNT(*) AS orders_cnt,
    SUM(amount) AS revenue
FROM orders
GROUP BY 1;

CREATE INDEX ON orders_daily (order_date);

Refresh:

REFRESH MATERIALIZED VIEW orders_daily;             -- блокирует чтение
REFRESH MATERIALIZED VIEW CONCURRENTLY orders_daily; -- без блокировки (требует UNIQUE индекс)

CONCURRENTLY — внутри Postgres делает full rebuild через temp-таблицу, swap. Долго, но прод не лежит.

Минус Postgres MV:

• Нет автоматического incremental refresh. Каждый refresh = полный пересчёт.
• На больших таблицах refresh — часы.

Workaround: периодический REFRESH через cron или Airflow. Расписание зависит от SLA свежести.

MV в ClickHouse

ClickHouse даёт мощный механизм MV — incremental через триггер на INSERT.

CREATE TABLE orders (
    order_id UInt64,
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
) ENGINE = MergeTree ORDER BY created_at;

CREATE MATERIALIZED VIEW orders_daily_mv
ENGINE = SummingMergeTree
ORDER BY order_date
AS
SELECT
    toDate(created_at) AS order_date,
    count() AS orders_cnt,
    sum(amount) AS revenue
FROM orders
GROUP BY 1;

Как работает:

• При INSERT в orders срабатывает триггер
• В MV пишется агрегация только для вставляемой партии
• SummingMergeTree мерджит частичные суммы в фоне → корректный итог

Свойства:

• Incremental: каждая строка обновляет MV автоматически
• Real-time
• Не требует ручного refresh

Грабли CH MV:

• Не работает при backfill через INSERT INTO ... SELECT (не сработает триггер на источник, если данные приходят минуя источник)
• Не догоняет старые данные — только новое
• При DELETE/UPDATE источника MV не пересчитывается автоматически (CH не любит мутации)

REFRESHABLE MV (CH 23.x+): более удобный синтаксис с расписанием:

CREATE MATERIALIZED VIEW orders_daily REFRESH EVERY 5 MINUTE
TO orders_daily_target
AS SELECT ... FROM orders GROUP BY 1;

Refresh-стратегии

Full refresh — пересчитать MV целиком. Простой, но дорогой.

Incremental refresh — обновить только изменившиеся партиции.

-- Postgres + cron + dbt
WITH new_data AS (
    SELECT
        DATE_TRUNC('day', created_at) AS order_date,
        COUNT(*) AS orders_cnt,
        SUM(amount) AS revenue
    FROM orders
    WHERE created_at >= '{{ ds }}'::DATE
      AND created_at <  '{{ ds }}'::DATE + 1
    GROUP BY 1
)
DELETE FROM orders_daily WHERE order_date = '{{ ds }}'::DATE;
INSERT INTO orders_daily SELECT * FROM new_data;

Это incremental dbt — функционально аналог MV с incremental refresh, но управляется явно.

Triggered refresh — обновлять при событии (Postgres triggers, CDC).

Time-based refresh — по расписанию (каждые 5 минут).

Выбор стратегии:

Когда MV vs incremental dbt модель

MV:

• Источник и MV в одной БД
• Хочется автоматизации (CH MV)
• Нет внешнего оркестратора

dbt incremental:

• Multi-step pipeline с Airflow
• Тесты, документация, CI
• Источник и target могут быть разными системами
• Кросс-платформенный (работает на CH, BQ, Snowflake, Postgres)

В современном стеке dbt incremental часто заменяет Postgres MV. ClickHouse MV — особый случай, потому что incremental там встроен.

Частые ошибки

Postgres MV на real-time данных без cron. Без refresh данные устаревают сразу. Нужен явный REFRESH MATERIALIZED VIEW.

REFRESH без CONCURRENTLY в проде. Чтения блокируются на время refresh, продакт жалуется на «отчёт лежит».

CH MV на источник с UPDATE/DELETE. MV не отлавливает мутации. Если в источнике строка может изменяться — использовать ReplacingMergeTree MV.

MV-pile-up. Несколько MV на одну таблицу-источник. Каждый INSERT триггерит все MV — нагрузка кратна.

Не индексировать MV. MV — обычная таблица. Без индексов фильтрация по дате — full scan, преимущество MV теряется.

Backfill минуя источник. В CH backfill через INSERT в target минуя источник — MV не отрабатывает. Backfill писать через source.

Использовать MV вместо нормальной агрегации в ad-hoc. Если запрос делается раз в неделю — MV избыточен. MV — для часто-исполняемых, медленных запросов.

Связанные темы

• dbt на собеседовании DE
• DWH ClickHouse на собесе DE
• Партиционирование в ClickHouse для DE
• Подготовка к собесу Data Engineer
• Greenplum на собесе DE

FAQ

View, materialized view и table — в чём разница?

View — сохранённый запрос без хранения. Materialized view — запрос + физическое хранение результата + обновление. Table — данные хранятся напрямую, без производного запроса.

CH MV или dbt incremental — что лучше?

CH MV — для real-time агрегаций внутри CH без оркестратора. dbt — для tested, documented, multi-step pipelines. На больших командах часто оба: MV для горячих агрегаций, dbt для остального.

Сколько MV на таблицу — нормально?

3-5 — без проблем. 10+ — INSERT начинает тормозить, потому что каждый INSERT триггерит все MV. Мониторить latency и нагрузку.

Можно ли построить MV над JOIN?

В Postgres — да. В CH MV над JOIN осторожно: CH MV срабатывает только на левую таблицу INSERT'а, JOIN с другой таблицей берёт «как есть на момент INSERT». На реальном времени получаются inconsistencies.

Нужны ли индексы на CH MV?

CH MV — это MergeTree под капотом. ORDER BY в DDL играет роль primary key. Индексы как в Postgres — нет. Sparse primary index по ORDER BY — есть.

Это официальная информация?

Нет. Статья основана на документации Postgres, ClickHouse, dbt и общей практике DWH.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.