dbt Incremental Models: merge vs delete+insert (практический гайд)

Q: Когда incremental, когда table?

- Table (full refresh): 50M строк, источник append-only или с известным update pattern. - Threshold между ними — 10-50M рассмотри dual approach (table в dev, incremental в prod через variable).

Q: Как dev-окружение работает с incremental?

В dev — --full-refresh каждый раз, или limit 1000 через variable:

Q: Что delete+insert vs merge на Postgres — реально разница?

На современном PG (15+) MERGE нативно supported и часто быстрее delete+insert. dbt с PG15 уже default'но использует merge.

Q: Можно ли change incremental strategy без full refresh?

Нет. --full-refresh обязателен при смене strategy.

Когда у тебя витрина на 500M строк, full-refresh каждую ночь — это часы compute и $$$. Incremental models в dbt позволяют считать только новые/изменённые данные.

Но «incremental» — это не одна стратегия. Это четыре: append, merge, delete+insert, insert_overwrite. Каждая для своего случая. Этот гайд — про разницу, подводные камни и как выбрать.

Зачем incremental

Допустим, есть таблица events — 500M строк, +10M в день.

Full-refresh подход (materialized='table'):

SELECT * FROM raw.events  -- читаем все 500M

Каждую ночь = 500M строк процесс. На Snowflake — несколько минут компьюта × $. На Postgres — может зависнуть.

Incremental подход (materialized='incremental'):

{{ config(materialized='incremental', unique_key='event_id') }}

SELECT * FROM raw.events
{% if is_incremental() %}
WHERE event_time > (SELECT MAX(event_time) FROM {{ this }})
{% endif %}

Каждую ночь читаем только 10M новых строк (~50× меньше). Скорость × 50, cost / 50.

Шаг 1: Базовая incremental модель

-- models/marts/fct_events.sql
{{ config(
  materialized='incremental',
  unique_key='event_id'
) }}

SELECT
  event_id,
  user_id,
  event_type,
  event_time,
  created_at,
  updated_at
FROM {{ ref('stg_events') }}

{% if is_incremental() %}
  WHERE event_time > (SELECT COALESCE(MAX(event_time), '1900-01-01') FROM {{ this }})
{% endif %}

Разбор:

materialized='incremental' — dbt сам решает: первый запуск = full-refresh, далее = incremental
is_incremental() macro — true если таблица уже существует
{{ this }} — текущая модель (= dbt_schema.fct_events)
COALESCE(..., '1900-01-01') — на первом incremental run после полного refresh

Что happens at compile-time:

-- При first run:
CREATE TABLE fct_events AS
SELECT ... FROM stg_events;
-- (без WHERE — берёт всё)

-- При subsequent runs:
MERGE INTO fct_events tgt
USING (
  SELECT ... FROM stg_events
  WHERE event_time > (SELECT MAX(event_time) FROM fct_events)
) src
ON tgt.event_id = src.event_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Шаг 2: Incremental strategies

dbt поддерживает 4 стратегии (config incremental_strategy):

append (самый простой)

{{ config(materialized='incremental', incremental_strategy='append') }}

SELECT * FROM source
{% if is_incremental() %}
WHERE event_time > (SELECT MAX(event_time) FROM {{ this }})
{% endif %}

Просто INSERT INTO — никаких updates. Подходит для immutable events (только новые строки, никогда не меняются).

Когда: clickstream events, logs, immutable facts.

Когда НЕ: если та же строка может прийти повторно (idempotency проблема).

merge (default для Snowflake, BigQuery)

{{ config(materialized='incremental', incremental_strategy='merge', unique_key='event_id') }}

Под капотом:

MERGE INTO fct_events tgt USING new_rows src
ON tgt.event_id = src.event_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Когда: SCD Type 1 (только current state), может быть updates существующих строк.

Когда НЕ: late-arriving data за пределами incremental window (модель не «увидит» старые updates).

delete+insert (default для PostgreSQL, Redshift)

{{ config(materialized='incremental', incremental_strategy='delete+insert', unique_key='event_id') }}

Под капотом:

DELETE FROM fct_events WHERE event_id IN (SELECT event_id FROM new_rows);
INSERT INTO fct_events SELECT * FROM new_rows;

Когда: Postgres/Redshift без эффективного MERGE.

Подвох: в transaction может lock таблицу — для production надо ставить session-level lock.

insert_overwrite (Spark, BigQuery)

{{ config(
  materialized='incremental',
  incremental_strategy='insert_overwrite',
  partition_by={'field': 'event_date', 'data_type': 'date'}
) }}

SELECT * FROM source
{% if is_incremental() %}
WHERE event_date IN (SELECT DISTINCT event_date FROM source WHERE event_date >= CURRENT_DATE - 3)
{% endif %}

Удаляет целые партиции и переписывает. Подходит для partitioned tables (BQ, Spark, Iceberg).

Когда: late-arriving data в пределах последних 3-7 дней (re-process partition).

Преимущество: не нужен unique_key — переписываешь всю partition.

Шаг 3: unique_key — главный подвох

{{ config(materialized='incremental', unique_key='event_id') }}

Здесь event_id должен быть гарантированно уникальным. Иначе MERGE даёт duplicates.

Композитный ключ:

{{ config(materialized='incremental', unique_key=['user_id', 'event_date', 'event_type']) }}

Или surrogate key через dbt-utils:

{{ config(materialized='incremental', unique_key='surrogate_id') }}

SELECT
  {{ dbt_utils.generate_surrogate_key(['user_id', 'event_date', 'event_type']) }} AS surrogate_id,
  *
FROM source;

Подвох: если поле в unique_key имеет NULL — Postgres/Snowflake считают NULL != NULL → MERGE не сматчит → duplicates. Гарантируй non-null или используй COALESCE.

Шаг 4: Late-arriving data

Допустим, события могут приходить с задержкой 3 дня (mobile события, offline mode).

Проблема обычного incremental:

WHERE event_time > MAX(event_time) FROM {{ this }}
-- если max = 2024-03-15 14:00, а пришло событие с timestamp 2024-03-12 10:00
-- (опоздавшее на 3 дня) — оно НЕ попадёт в incremental!

Решение 1: lookback window

{% if is_incremental() %}
WHERE event_time > (SELECT MAX(event_time) FROM {{ this }}) - INTERVAL '7 days'
{% endif %}

Берём «последние 7 дней + всё новое». При MERGE на unique_key старые строки обновятся.

Решение 2: insert_overwrite по партициям

Если таблица партиционирована по date, переписываем последние N партиций целиком:

{% if is_incremental() %}
WHERE event_date IN (
  SELECT DISTINCT DATE(event_time)
  FROM {{ ref('stg_events') }}
  WHERE updated_at >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
)
{% endif %}

Решение 3: hard refresh раз в неделю

В Airflow на воскресенье:

dbt run --select fct_events --full-refresh

Late-arriving data корректируется раз в неделю.

Шаг 5: SCD Type 2 через snapshot (а не incremental)

Для historical tracking (запомнить старые значения) — snapshot, не incremental:

{% snapshot users_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key='user_id',
    strategy='timestamp',
    updated_at='updated_at',
  )
}}
SELECT * FROM {{ source('raw', 'users') }}
{% endsnapshot %}

Каждую запуск:

Новые row's → insert
Изменённые → закрываем old row (dbt_valid_to = NOW()), insert new
Удалённые → закрываем old row

В результате — полная история изменений (SCD Type 2).

Шаг 6: CI/CD checks для incremental

Главная боль incremental — silent data corruption: модель «работает», но цифры неверные. Защита:

dbt tests

# models/marts/fct_events.yml
version: 2
models:
  - name: fct_events
    columns:
      - name: event_id
        tests:
          - not_null
          - unique
      - name: event_time
        tests:
          - not_null

В CI после dbt run всегда:

dbt test --select fct_events

Row count check

-- analyses/check_event_count_growth.sql
WITH daily AS (
  SELECT DATE(event_time) AS day, COUNT(*) AS rows
  FROM {{ ref('fct_events') }}
  WHERE event_time >= CURRENT_DATE - 7
  GROUP BY 1
)
SELECT day, rows,
       LAG(rows) OVER (ORDER BY day) AS prev_rows,
       100.0 * (rows - LAG(rows) OVER (ORDER BY day)) / LAG(rows) OVER (ORDER BY day) AS pct_change
FROM daily
ORDER BY day DESC;

Алерт если pct_change < -50% (внезапное падение) или > 200% (внезапный рост).

dbt-elementary для anomaly detection

elementary-data.com — open-source data observability, плагин для dbt. Анализирует динамику row counts, freshness, schema changes.

FAQ

Когда incremental, когда table?

Table (full refresh): < 10M строк, или модель быстро меняется (бизнес-логика).
Incremental: > 50M строк, источник append-only или с известным update pattern.
Threshold между ними — 10-50M рассмотри dual approach (table в dev, incremental в prod через variable).

Как dev-окружение работает с incremental?

В dev — --full-refresh каждый раз, или limit 1000 через variable:

SELECT * FROM source
{% if target.name == 'dev' %}
  LIMIT 100000
{% endif %}

Что delete+insert vs merge на Postgres — реально разница?

На современном PG (15+) MERGE нативно supported и часто быстрее delete+insert. dbt с PG15 уже default'но использует merge.

Можно ли change incremental strategy без full refresh?

Нет. --full-refresh обязателен при смене strategy.

Почему moя incremental модель медленная?

Чаще всего: (1) WHERE event_time > MAX() — sub-query тяжёлый на 500M строк (добавь index/partition по event_time), (2) unique_key non-indexed (создай index), (3) merge на колонке с low cardinality (плохо для anti-join).

Что дальше

🧪 SQL-тренажёр — практика SQL под dbt-модели
🧠 3000+ вопросов с собесов — 50+ про dbt
📚 dbt практический гайд — первые шаги
🧪 dbt Tests: generic + custom + dbt-utils — следующий уровень качества
⚡ Airflow DAG patterns — оркестрация dbt
🚀 ClickHouse гайд — destination для dbt

Источники

docs.getdbt.com/docs/build/incremental-models — официальные доки
discourse.getdbt.com — community с реальными use-cases
elementary-data.com — observability for dbt

Incremental модели — это рычаг масштабирования. Открой SQL-тренажёр и натренируй SQL под dbt-модели, которые будут крутиться на витринах.