delete+insert vs merge: deep dive

В прошлом уроке мы видели append — стратегия для immutable данных. Но большинство production-таблиц mutable: order status меняется, customer обновляется, subscription renew’ится. На таких таблицах append даёт дубли (один customer записан 5 раз с разными состояниями), и нужны стратегии с дедупликацией.

В dbt две главные mutable-стратегии: delete+insert и merge. Они решают одну задачу по-разному, и выбор между ними — компромисс. Этот урок — про их внутреннюю механику и когда что выбрать.

delete+insert — универсальная стратегия

delete+insert — это двухступенчатый процесс:

1. DELETE из target всех строк, где unique_key совпадает с дельтой.
2. INSERT дельту в target.

Что под капотом dbt компилирует:

-- На обычном run:
BEGIN;

-- Шаг 1: DELETE matching
DELETE FROM analytics.orders
WHERE order_id IN (
    SELECT order_id FROM <delta_subquery>
);

-- Шаг 2: INSERT delta
INSERT INTO analytics.orders
SELECT * FROM <delta_subquery>;

COMMIT;

Эта стратегия работает на любом warehouse, потому что использует базовые DELETE + INSERT, которые поддерживаются везде. Это её главное преимущество — универсальность.

Пример dbt-модели:

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

select
    order_id,
    customer_id,
    order_total_usd,
    order_status,
    updated_at
from {{ source('app', 'orders') }}

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Здесь:

• unique_key='order_id' — по этой колонке dbt будет делать DELETE matching.
• Фильтр по updated_at оставляет только обновлённые/новые orders в дельте.
• DELETE удаляет старые версии этих orders из target.
• INSERT вставляет свежие версии.

delete+insert на конкретном примере

Допустим, в target таблице analytics.orders есть строка:

order_id=42, status='pending', updated_at='2026-05-01 10:00'

В source raw.orders эта строка обновилась:

order_id=42, status='paid', updated_at='2026-05-19 14:00'

dbt run выполняет:

1. Дельта-фильтр: where updated_at > '2026-05-01 10:00' (current max в target). Эта строка в дельте.
2. DELETE: удаляется старая строка order_id=42 из target.
3. INSERT: вставляется новая строка order_id=42, status='paid'.

Результат — в target одна актуальная строка. Это и есть main use case.

merge — современная стратегия

merge использует SQL команду MERGE INTO, которая делает то же самое в один шаг:

MERGE INTO analytics.orders AS target
USING <delta_subquery> AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET
    customer_id = source.customer_id,
    order_total_usd = source.order_total_usd,
    order_status = source.order_status,
    updated_at = source.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, ...)
VALUES (source.order_id, source.customer_id, ...);

В dbt:

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

-- остальной SQL такой же

delete+insert vs merge: главные различия

Когда выбирать delete+insert

1. Warehouse не поддерживает MERGE. До DuckDB 1.4 — это единственная mutable стратегия.
2. Простота отладки. DELETE+INSERT — две понятные операции, легко логировать и debug’ить. MERGE — одна команда, профайлить сложнее.
3. Очень большая дельта (>10% таблицы). На таком объёме MERGE деградирует, потому что warehouse внутри использует hash join, и hash table становится огромной. DELETE+INSERT иногда быстрее.

Когда выбирать merge

1. Warehouse поддерживает MERGE — Snowflake, BigQuery, DuckDB 1.4+, Redshift (limited), Postgres (как INSERT … ON CONFLICT через dbt adapter).
2. Маленькая дельта (менее 5% таблицы). MERGE оптимизирован для этого сценария.
3. Сложная логика UPDATE. MERGE поддерживает WHEN MATCHED AND condition THEN UPDATE, что даёт conditional update logic. DELETE+INSERT не имеет такой fine-grained control.
4. Колонки, которые НЕ должны обновляться. См. merge_exclude_columns ниже.

merge_exclude_columns

При MERGE по умолчанию обновляются все колонки. Иногда нужно исключить — например, колонка created_at (timestamp создания записи) не должна меняться:

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    merge_exclude_columns=['created_at']
) }}

При MERGE created_at останется как был в target, не перезапишется значением из source. Это важно для:

• created_at — timestamp создания, не должен обновляться при update.
• inserted_at — timestamp вставки строки в target.
• _dbt_loaded_at — служебный timestamp от dbt.

С delete+insert такой опции нет — строка пересоздаётся целиком, теряя historical created_at. Поэтому для таблиц с auditable timestamps merge почти всегда лучше.

merge_update_columns

Альтернатива merge_exclude_columns — явно указать какие колонки обновлять:

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    merge_update_columns=['order_status', 'updated_at']
) }}

Здесь обновляются только два поля: order_status и updated_at. Остальные колонки (customer_id, order_total_usd) при матче не трогаются.

Use case — narrow update: вы знаете, что в источнике меняется только статус и timestamp, остальное стабильно. Это значительно ускоряет MERGE на широких таблицах с 50+ колонками — warehouse обновляет 2 колонки вместо 50.

Не-уникальный unique_key — катастрофа

Самая болезненная ошибка с обеими стратегиями — unique_key не уникален в дельте.

Сценарий: вы поставили unique_key='customer_id' на fact-таблицу orders. Но customer_id в orders не уникален — один customer может иметь много orders. dbt этого не знает.

С delete+insert:

• Один customer пришёл в дельте с двумя orders.
• DELETE: удалит все строки с этим customer_id из target (включая старые orders).
• INSERT: вставит две новые строки.
• Результат: старые orders этого customer потеряны.

С merge:

• ON-clause target.customer_id = source.customer_id дал bы matched 5 строк target на 1 строку source.
• Snowflake/Postgres падает с Cardinality Error: “More than one row matched”.
• DuckDB 1.4+ — поведение зависит от версии (может тоже упасть, может тихо взять последнюю).

Решение — unique_key должен быть реально уникальным. Для orders это order_id, не customer_id. Для composite key (order_id, line_id) — список:

unique_key=['order_id', 'line_id']

Защита: добавьте unique-тест на staging-слое ({{ test_unique(column_name='order_id') }}), чтобы non-unique key падал на этапе тестов, не в production-инциденте.

delete+insert с фильтром в DELETE

Тонкость, которая многих застаёт врасплох — стандартная dbt-генерация DELETE сканирует всю target таблицу:

-- Сгенерированный dbt SQL:
DELETE FROM analytics.orders
WHERE order_id IN (SELECT order_id FROM <delta>);

На таблице 1B строк это full scan. Даже с indexed order_id warehouse читает много данных, чтобы понять, что IN оптимизировать.

Это решается через incremental_predicates — следующий урок. Спойлер: можно добавить WHERE updated_at > 'yesterday' к DELETE, чтобы он сканировал только последние сутки. На 1B строк это разница в 100x.

DuckDB-специфика

• delete+insert работает на любой версии DuckDB.
• merge — DuckDB 1.4+ обязательно. На 1.3 и старее (или dbt-duckdb до 1.10.0) — config error при попытке использовать merge.
• На MotherDuck merge работает. FK constraints не работают.
• merge_update_columns и merge_exclude_columns поддерживаются на DuckDB 1.4+.
• Транзакционная безопасность DuckDB: DELETE+INSERT в одной транзакции. Если процесс упал между ними — target откатывается к pre-DELETE состоянию.

Performance benchmark

Реальные числа на production-проекте 2025 (Snowflake medium warehouse):

Видно три тренда:

1. append всегда быстрейший (когда применим).
2. merge на 30-50% быстрее delete+insert на средне-больших таблицах.
3. merge_exclude_columns даёт ещё 30-50% поверх merge на широких таблицах.

На очень больших дельтах разница нивелируется — обе стратегии деградируют.

Production gotchas

1. Mixing strategies in one project — одни модели на delete+insert, другие на merge, иногда команда не знает почему. Решение — convention в README: «merge для всех mutable таблиц, кроме когда warehouse не поддерживает». Команда применяет одинаково.

2. unique_key забыли указать — dbt с merge упадёт сразу при компиляции. С delete+insert компиляция пройдёт, но DELETE будет делать TRUNCATE (удалит всё). Catastrophe.

3. Race condition — два concurrent runs на одной модели. Обе читают source, обе делают DELETE+INSERT/MERGE. Транзакции warehouse обычно защищают от corruption, но дубли в target возможны (один процесс не видит изменения другого до COMMIT).

4. merge_exclude_columns не работает на DuckDB до 1.4 — silent (просто игнорируется). Проверьте через explicit query на target после run.

5. DELETE кладёт замок на target — на Snowflake long-running DELETE блокирует SELECT’ы из target. BI-дашборды могут зависнуть на минуты. Решение — incremental_predicates (следующий урок) для ограничения scope DELETE.

Попробуй сам

В своём проекте:

1. Найдите все incremental-модели на delete+insert. Проверьте — поддерживает ли ваш warehouse merge? Если да — переведите 2-3 модели на merge и измерьте время.
2. Для таблицы с auditable timestamps (created_at, inserted_at) добавьте merge_exclude_columns=['created_at'] и убедитесь, что значения не перезаписываются.
3. Попробуйте симулировать non-unique unique_key: возьмите fact-таблицу с unique_key='customer_id' (заведомо не уникальный). Запустите dbt run, посмотрите, что произойдёт. На Snowflake/Postgres получите Cardinality Error.