dbt Packages & Tests

Incremental strategies — merge, delete+insert, append

Official adapter support, what each strategy does to existing rows, and a merge that failed until the source was unique.

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:

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

Verification

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.

Official docs