Incremental strategies — merge, delete+insert, append
The problem
A events table is too large to rebuild every run. You want new events added and changed events updated, without duplicating event_id. dbt incremental models support several strategies; official documentation says the right one depends on data volume, the reliability of your unique_key, and what the adapter actually supports.
Official support on current dbt v1 latest (excerpt from the official table; check that page for the full list):
| Adapter | append | merge | delete+insert | insert_overwrite | microbatch |
|---|---|---|---|---|---|
| dbt-snowflake | yes | yes | yes | yes | yes |
| dbt-databricks | yes | yes | yes | yes | yes |
| dbt-duckdb | yes | yes | yes | — | yes |
| dbt-bigquery | — | yes | — | yes | yes |
insert_overwrite is not listed for dbt-duckdb. Do not copy a BigQuery insert_overwrite config onto DuckDB or Postgres and expect it to run.
A working example
{{
config(
materialized='incremental',
unique_key='event_id',
incremental_strategy='merge'
)
}}
select event_id, customer_id, event_time, payload
from {{ ref('stg_events') }}
{% if is_incremental() %}
where event_time >= (
select coalesce(max(event_time), '1970-01-01') from {{ this }}
)
{% endif %}
Executed with dbt-core 1.12.7 and dbt-duckdb 1.11.1. After a full refresh, events_merged contained four events, including event_id = 4 with payload logout. After stg_events was rebuilt so that event 4’s payload became logout_corrected and events 5–6 appeared, a successful incremental merge left six unique rows, and event 4 was logout_corrected. A unique data test on event_id passed.
Official docs on what the built-in strategies mean:
merge— insert keys that do not exist, update keys that do (SCD1-like). If you omitunique_key, official docs say it behaves likeappend.delete+insert— delete theunique_keyfrom the target, then insert the new rows for those keys. Official docs note it can be useful when a key is not truly unique or whenmergeis unsupported, and that it is not SCD2 (use snapshots for history).append— insert selected rows and do not update or delete. Official docs: it does not check for duplicates. If the same record appears again, it is inserted again.insert_overwrite— replace affected partitions, not individual rows. Official docs: for partitioned tables.microbatch— split large time-series work on anevent_timecolumn into batches.
On Snowflake, official docs also document incremental_predicates and the aliases DBT_INTERNAL_DEST / DBT_INTERNAL_SOURCE to limit how much of the existing table a merge scans. That form was not executed here.
The gotcha
An earlier incremental merge in this same project failed with:
MERGE INTO command cannot affect the same target row more than once.
stg_events had two rows for event_id = 4 with the same event_time. A QUALIFY ROW_NUMBER() … ORDER BY event_time DESC therefore did not pick a winner. Official dbt merge documentation says a unique_key helps resolve duplicates in the destination; it does not make a two-row source safe. Deduplicate the incremental source, and put a deterministic tie-breaker in that window Add a deterministic tie-breaker (a source rank, a load id, or a second column) so the window cannot return two winners.
append plus a sloppy is_incremental() filter is how people grow silent duplicates. Official docs are explicit: append will insert again.
When not to use this
- The table is small. A table or view materialization is easier to reason about.
- You need history of old versions. That is a snapshot, not
merge. - You do not have a reliable
unique_keyand were about to usemergeanyway. Official docs: without a key,mergeappends. - The strategy is not in your adapter’s official support table (
insert_overwriteon DuckDB, for example).
The merge model, the duplicate-source MERGE error, the later successful merge (event 4 updated to logout_corrected, events 5–6 inserted, unique test passing), and the adapter list are from a local dbt-duckdb 1.11.1 run plus official dbt incremental-strategy documentation. Snowflake and Databricks strategies were not executed on those platforms.