dbt Incremental Models in Production: unique_key, Merge, and Late Arrivals

A full-refresh table is simple and honest: every morning, it wipes the target table clean and recalculates everything from day one. But once your tables grow past millions of rows, full rebuilds become painfully slow and expensive.

That’s where incremental models come in. But here’s the catch: incremental modeling isn’t magic automagic SQL — it is a strict write contract. You must explicitly tell dbt which rows to read, which rows to update, and how to handle retries without duplicating data.

💡 The Notebook Analogy (In Plain English)

• Full refresh: Imagine keeping a daily journal by tearing out every page each night and rewriting all 500 pages from memory just to append today’s entry. It’s clean, but by month six your hand cramps and you run out of ink ($$$).
• Incremental model: You flip to the last written page and only write today’s new paragraphs. If someone edited an entry from two days ago (a “late arrival”), you flip back and update just that specific sentence (unique_key).
• The silent trap: If you forget to specify a unique_key, dbt just glues today’s notes onto the end. If your pipeline fails and retries three times, you now have three duplicate copies of every single order!

Fast Reference: The 3 Core Moving Parts

Component	What it Controls	What Happens If You Get It Wrong
is_incremental()	The READ: Filters upstream tables to only recent rows.	Omit this, and you scan your entire billion-row table every single run!
unique_key	The WRITE: Tells the warehouse how to identify matching records.	Omit this, and every pipeline retry duplicates your revenue numbers.
lookback window	The SAFETY NET: Re-scans the last 3-7 days instead of just today.	Skip this, and delayed mobile app orders or credit card refunds vanish forever.

flowchart LR
  stg[Staging / Source Data] --> filt{is_incremental Filter?}
  filt -->|Full Refresh / Initial Build| all[Scan All Historical Rows]
  filt -->|Incremental Run| win[Scan Lookback Window (e.g. Last 3 Days)]
  all --> write[Write Strategy]
  win --> write
  write --> merge[MERGE on unique_key: Safe & Idempotent]
  write --> append[APPEND only: Fast but duplicates on retry!]

The filter decides the READ. The unique_key decides the WRITE. They are two separate knobs.

Incremental is a write pattern

materialized='incremental' does not mean “only new rows.” It means: on the first run (and on --full-refresh) dbt builds the relation like a table. On later runs it adds or merges whatever your SELECT returns.

That SELECT is your job. dbt will not infer a watermark. If you omit the is_incremental() filter, every run scans the entire upstream model and then merges it. You paid for incremental ceremony and still did a full rebuild — plus merge overhead.

-- models/marts/fct_orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    incremental_predicates=[
      "DBT_INTERNAL_DEST.ordered_at >= dateadd('day', -7, current_date())"
    ],
    on_schema_change='fail'
) }}

with src as (
    select
        order_id,
        customer_id,
        status,
        amount,
        ordered_at,
        updated_at
    from {{ ref('stg_orders') }}
    {% if is_incremental() %}
    where updated_at >= dateadd(
        'hour',
        -6,
        (select coalesce(max(updated_at), '1970-01-01') from {{ this }})
    )
    {% endif %}
)

select * from src

Three pieces, three jobs:

1. unique_key — the grain of the MERGE match.
2. is_incremental() filter — what you read from upstream.
3. incremental_predicates — what slice of the target the engine is allowed to scan during the merge.

Leave any one out and you get a different incident.

unique_key is the merge grain

On Snowflake, BigQuery, and Databricks, incremental_strategy='merge' emits a MERGE keyed on unique_key. One row per key in the SELECT. Two source rows with the same order_id in one incremental batch is undefined: warehouses pick a winner or the merge fails. Dedup in a CTE first if the extract can replay.

Without unique_key, most adapters append. A retry of the same window inserts yesterday again. That is the same class of bug as an Airflow load that only INSERTs — idempotency is a write pattern, not a scheduler setting.

Composite keys are normal:

{{ config(
    materialized='incremental',
    unique_key=['account_id', 'event_date'],
    incremental_strategy='merge'
) }}

The key must be unique in the model you are writing, not “unique enough in the source today.” Put unique + not_null tests on those columns. The Python quality checks post is the same rule on the pandas path: grain first, then volume.

A unique_key that is not the business grain (for example id from an extract that reissues ids) will overwrite the wrong row. The merge succeeds. The dashboard is wrong.

The is_incremental filter

is_incremental() is false on:

• The first build of the relation
• --full-refresh
• A full refresh forced by on_schema_change in some setups

It is true on a normal subsequent dbt run. The filter belongs on the source of rows, usually {{ ref('stg_…') }} or a narrow intermediate — not as a HAVING after you already scanned everything.

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

That snippet is the tutorial version. It is also how late arrivals disappear.

updated_at must move when the row you care about changes. If the source only stamps ingested_at on first landing, a corrected amount never re-enters the window. Use the column the producer actually bumps, or you are filtering on the wrong clock.

Do not wrap the filter column in a function (where date(updated_at) = current_date()). Same as any warehouse query: you kill pruning. Compare with a range.

Lookback and late arrivals

A late arrival is a row whose event time is old but whose updated_at (or landing time) is new — or worse, a row that shows up after you already advanced the watermark past its event time.

Example: the fact watermarks on max(ordered_at). An order from Tuesday is corrected on Thursday. Tuesday is behind the watermark. The merge never sees it.

Example two: you watermark on max(updated_at) with no lookback. The source writes in small batches. Clock skew, a delayed CDC flush, or a transaction that commits late can land with an updated_at slightly behind the max you already stored. Those rows are invisible forever unless you full-refresh.

The production habit is a lookback: re-read a window behind the max, and let unique_key overwrite.

{% if is_incremental() %}
where updated_at >= dateadd(
    'hour',
    -6,
    (select max(updated_at) from {{ this }})
)
{% endif %}

Six hours is a starting point, not a law. Size it from how late the source actually is — CDC lag, a twice-daily dump, a partner SFTP that arrives at 04:00 with yesterday’s corrections. Write the reason in a model comment. Changing the lookback in Slack after an incident is how the next incident looks identical.

If the source is partitioned by date and late data only revisits recent partitions, prefer a partition replace (delete+insert or insert_overwrite) on ordered_at::date plus a two- or three-day lookback. Merge on a skinny key is right when rows update in place across many dates. Overwrite is right when a day is the unit of replay.

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

select
    order_date,
    count(*) as orders,
    sum(amount) as revenue
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where order_date >= dateadd('day', -2, (select max(order_date) from {{ this }}))
{% endif %}
group by 1

unique_key here is the partition you delete before insert, not a degenerate merge key. A retry of the same two days replaces those days. It does not append a third copy.

Predicates: do not merge-scan the whole fact

A merge that matches on order_id but scans five years of the target is an incremental model with a table-sized bill. incremental_predicates (adapter support varies; Snowflake and Databricks are the usual homes) restrict the target scan.

The predicate must be true for every row you might update. If a late arrival can touch a row older than seven days, a seven-day predicate will skip it and the merge will look successful. Align the predicate with the lookback, or with a hard business rule (“we never correct orders older than 14 days”).

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    incremental_predicates=[
      "DBT_INTERNAL_DEST.ordered_at >= dateadd('day', -14, current_date())"
    ]
) }}

Cluster or partition the target on the same column you predicate. Otherwise the predicate is a residual filter after a full read.

Schema change and full refresh

on_schema_change='fail' is the default I want on facts. A new column should be a PR, not a silent append of NULLs or a surprise rebuild in prod.

When you change unique_key or the grain, do not merge. Full refresh (or rebuild into a new relation and swap). Merging a new grain onto old keys leaves orphans and duplicates that tests will not explain.

--full-refresh in prod is a load-bearing operation: warehouse time, downstream readers, and any extra incremental models that ref() this one. Schedule it. Do not discover it mid-incident because someone added a column with sync_all_columns on a 2B-row fact.

Two runs in CI, not one

A single dbt build on an empty PR schema only tests the full-build path. Incremental bugs live on the second run.

dbt build --select fct_orders
dbt build --select fct_orders

The second invocation should be a no-op on unchanged fixtures, or a merge of the rows you deliberately updated. Assert:

• Row count did not double
• A fixture row with a bumped updated_at changed
• A fixture row outside the lookback did not change

Warehouse tests on grain still belong on the table. Incremental Python checks on the slice you are about to load will not see last month’s leftover duplicates — pair the layers.

flowchart TD
  src[stg_orders] --> win[Lookback on updated_at]
  win --> dedup[One row per unique_key]
  dedup --> merge[MERGE into fct_orders]
  merge --> tests[unique + not_null on key]
  late[Late CDC / corrected order] --> win

Late rows re-enter through the lookback. unique_key overwrites. Tests catch a broken grain after the write.

Strategies, briefly

Strategy	Use when
merge	Rows update in place; you have a stable unique key
delete+insert	You can name a replaceable partition (day, hour, batch id)
append	Immutable events, and you will never retry the same window
insert_overwrite	Partitioned tables where a partition is the unit of replay

append plus retries is how facts grow a second Monday. If you cannot name a key or a partition, you do not have an incremental design yet. Keep it a table.

Microbatch (event-time buckets, newer dbt) is a structured lookback. It does not free you from unique_key or from late data that arrives after the bucket closed. Same contract, smaller default window.

Pitfalls

• Filter on max(event_time) with no lookback. Corrections and late CDC miss the cut. Watermark the change column and look back.
• No unique_key on merge. You appended. A retry doubled the window.
• Two rows per key in one batch. Dedup with a window (row_number — see SQL window functions) before the merge.
• is_incremental() filter omitted. Full upstream scan every run. The logs say incremental. The warehouse knows better.
• Predicate tighter than the lookback. Merge skips the row you just read. Silent miss.
• Changing grain without full refresh. Old keys remain. New keys collide. Tests flap for a week.
• on_schema_change='sync_all_columns' on a large fact. Prod rebuilds itself because someone added a comment column.

Production checklist

• unique_key matches the business grain; unique + not_null tests on those columns.
• is_incremental() filter is a range on a column that moves when the row changes.
• Lookback is documented with why that duration exists.
• incremental_predicates (if used) are at least as wide as the lookback.
• Strategy matches the replay unit: merge for in-place updates, delete+insert / overwrite for partitions.
• on_schema_change='fail' on facts.
• CI runs the model twice. Second run must not duplicate rows.
• Full refresh is a planned job when grain or key changes — not an accident in the deploy.

FAQ

Why did yesterday’s rows double after a retry? No unique_key, or strategy is append. Incremental then inserts whatever the SELECT returns. Add a key and merge, or delete+insert the partition you are rebuilding.

Why is a corrected order from last week still wrong? Your filter is updated_at >= max(updated_at) with no lookback, or you watermarked ordered_at. Late and corrected rows sit behind that max. Widen the window and merge on order_id.

Can I skip unique_key if the source is append-only? Only if a retry cannot produce the same window twice. Airflow retries. You will backfill. Prefer a key or a replaceable partition anyway.

Should CI run --full-refresh only? That tests the first-build path. Incremental bugs are on the second run. Build twice against fixtures.

When do I full-refresh prod? When the grain, unique_key, or a non-additive schema change makes the existing table a lie. Schedule it. Do not merge onto a broken relation and hope tests converge.

The first project post gets you staging, marts, and tests. Incremental is the next contract: filter what you read, key what you write, look back far enough that late data can still land. If any of those three are folklore, keep the model a table until they are not.