Robust upsert patterns with dbt incremental merge strategy

incremental + merge is the core dbt feature for delivering the standard upsert (update + insert) pattern on a data warehouse.

This article covers unique_key design, dedup, safe watermarking, performance, and schema-change handling — the exam-favorite topics together with the practical know-how you need on the job.

Big picture of the merge strategy and when to use it

Setting incremental_strategy=merge on a dbt incremental model runs an upsert against the target table: update on key match, insert on no match. This avoids full reloads and lets you efficiently apply only the newly arrived data.

A working merge requires a properly specified unique key and a correct incremental selection. From an exam perspective, you should be able to articulate the risks of a missing or duplicated unique_key (non-deterministic updates, MERGE errors).

• The unique_key must be logically unique. Always dedupe upstream.
• Use is_incremental() to narrow the load. In practice, add a lookback to absorb late-arriving data.
• On full_refresh the table is rebuilt. Declare index and cluster settings explicitly in the model config.

UPSERT flow (incremental + merge)

Minimal merge incremental configuration

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

with src as (
  select * from {{ source('app', 'orders') }}
  {% if is_incremental() %}
    where updated_at >= (select coalesce(max(updated_at), '1970-01-01') from {{ this }})
  {% endif %}
)

select * from src

unique_key design and dedup in practice

A clean unique_key is the key to a successful merge. If business keys are unstable, consider introducing a surrogate key, and assume the source delivers duplicates so that upstream dedup is non-negotiable.

On the exam, when asked what happens if the unique_key duplicates, the expected answer is to use a window function upstream to deterministically pick a single row (e.g., most recent wins).

• When business keys can change, introduce a stable surrogate key.
• Pick exactly one row per key deterministically using update timestamps or versions.
• Null keys are not unique, so always attach a not_null test.

Dedup flow

Use a window function to collapse duplicates to one row

with staged as (
  select *,
         row_number() over (
           partition by id
           order by updated_at desc
         ) as rn
  from {{ source('app', 'orders') }}
), dedup as (
  select * from staged where rn = 1
)
select * from dedup

Practical upsert: watermark with a lookback

To absorb late-arriving data and timestamp jitter, a safer choice than a plain max(updated_at) is a lookback threshold that rewinds slightly. Rewind a fixed window from the target's max updated_at and re-evaluate source rows in that range.

This approach can pull in some duplicates, so upstream dedup plus a unique_key-driven merge is required.

• Tune the lookback window from hours to days based on your data-arrival SLA.
• Always handle the first run where the target's max timestamp is null.
• Prefer an update-driven column (updated_at). Avoid relying on created_at alone.

Lookback concept

Complete example: incremental + merge with lookback

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

{% set lookback_hours = 24 %}

with base as (
  select * from {{ source('app', 'orders') }}
), window as (
  select coalesce(
           dateadd('hour', -{{ lookback_hours }}, max(updated_at)),
           cast('1970-01-01' as timestamp)
         ) as watermark
  from {{ this }}
), filtered as (
  select b.*
  from base b
  {% if is_incremental() %}
  where b.updated_at >= (select watermark from window)
  {% endif %}
), dedup as (
  select *, row_number() over (partition by id order by updated_at desc) as rn
  from filtered
)
select * from dedup where rn = 1

Partitioning, clustering, and incremental pruning

On large tables, the physical design (partitions and clusters) and incremental filtering directly drive query cost. Use dbt's config to apply the right settings per adapter and minimize the scanned range.

incremental_predicates lets you specify extra predicates applied to both target and source, on adapters that support it. Check your adapter's documentation for support.

• Use partition_by on a time column on warehouses that support it.
• Speed up merge match-finding with cluster or sort keys.
• Keep incremental predicates simple and choose columns the statistics can actually use.

Pruning illustration

Optimization example (adapter-dependent)

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id',
    partition_by={'field': 'order_date'},          -- e.g. BigQuery/Spark family
    cluster_by=['id'],                              -- e.g. BigQuery/Snowflake clustering equivalents
    incremental_predicates=["order_date >= dateadd('day', -7, current_date)"]
) }}

select * from {{ ref('stg_orders_clean') }}
{% if is_incremental() %}
where order_date >= dateadd('day', -7, current_date)
{% endif %}

Schema changes and deletes (on_schema_change and soft delete)

In incremental models, on_schema_change controls behavior when the schema changes. append_new_columns adds new columns, fail aborts on mismatch, and sync_all_columns synchronizes column differences on supported adapters.

Handling actual deletes is not always covered by the default merge behavior. In practice, a soft delete with an is_deleted flag is safer and easier to keep historically consistent. If you must hard-delete, make precise impact scoping and tests mandatory.

• on_schema_change depends on adapter support. For the exam, be able to explain each mode and when to use it.
• Prefer soft deletes. Align any hard deletes with your audit requirements.
• After a schema change, also consider running full_refresh.

Soft delete flow

Upsert logic that includes soft delete (flag propagation)

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id',
    on_schema_change='append_new_columns'
) }}

with src as (
  select id,
         updated_at,
         status,
         case when status = 'deleted' then true else false end as is_deleted
  from {{ ref('stg_orders_clean') }}
  {% if is_incremental() %}
    where updated_at >= (select coalesce(max(updated_at), '1970-01-01') from {{ this }})
  {% endif %}
)

select * from src

Exam-prep checklist (for the Analytics Engineer exam)

The exam frequently asks about incremental fundamentals (unique_key, the role of is_incremental, and what full_refresh means) and when merge is the right choice. Be ready to explain the on_schema_change options, how to write dedup logic, and safe watermarking strategies.

Attaching tests (not_null/unique and generic tests) to models to guarantee data quality is another commonly evaluated point.

• unique_key is required. Dedup deterministically inside the model.
• Control the load range with is_incremental(); use a lookback for late arrivals.
• Be able to articulate each on_schema_change mode and when to use which.

Exam imagery

Minimal quality-test example (schema.yml excerpt)

version: 2
models:
  - name: dim_orders
    columns:
      - name: id
        tests:
          - not_null
          - unique
      - name: updated_at
        tests:
          - not_null

Check your understanding

Frequently asked questions