Incremental Models in dbt: Merge Strategies, Late Data, and Full-Refresh Discipline

You will take a growing events model from a naive full rebuild to a correct incremental model: picking a merge strategy for the right reasons, surviving late-arriving and updated records, and knowing exactly when to swallow the cost of a full refresh. Incremental models are where dbt projects quietly go wrong — not with errors, but with numbers that drift from the truth while every run reports success.

What “incremental” really changes

A table model rebuilds everything on every run. An incremental model processes only new or changed rows and folds them into the existing table. The savings in compute and time are real, but you have traded a simple guarantee — the output is exactly what the SQL produces from the full source — for a set of obligations:

• You must identify which source rows are new or changed.
• You must tell dbt how to fold them in (the incremental strategy).
• You must handle rows that arrive late, change after arrival, or get deleted at the source.
• You must periodically verify the incremental result still matches a full rebuild.

Every incremental bug you will ever debug is one of those four obligations being shirked.

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge'
) }}
 
select
    event_id,
    session_id,
    event_type,
    event_timestamp,
    received_at,
    payload
from {{ ref('stg_events') }}
 
{% if is_incremental() %}
    where received_at > (select max(received_at) from {{ this }})
{% endif %}

The is_incremental() block is the heart of it: on incremental runs, the filter restricts the query to rows newer than the high-water mark already in the target. Everything subtle about incremental modelling lives in that filter and in what it might miss.

The strategies, compared honestly

Judgements that follow from the table:

• Default to merge (or delete+insert) when rows can be updated. Order statuses change. Subscription rows mutate. If a record can change after it first appears, append guarantees your table diverges from the source a little more every day.
• append is legitimate exactly when the source is a true immutable event log and you can prove duplicates cannot occur upstream — or where you deduplicate deliberately in a downstream model. “The source is probably append-only” is how duplicate revenue happens.
• Microbatch deserves a serious look for event data. Because each run processes bounded time windows and replaces them wholesale, late data inside a reprocessed window is handled by construction rather than by watermark cleverness, and backfills stop being a special procedure.

Late data and the watermark trap

The naive watermark filter above has a classic flaw: it assumes records arrive in order and never change. Real sources violate both assumptions constantly.

• Out-of-order arrival: an event with received_at earlier than your current max arrives in a later load (a delayed mobile sync, a retried batch). The filter excludes it forever.
• In-place updates: a row you already loaded gets updated at the source, and its created_at does not move. Filter on created_at and you will never see the update. Filter on updated_at and you catch updates — but only if the source maintains updated_at honestly, which you should verify rather than assume.

The standard mitigation is a lookback window: process a trailing slice of history on every run and let the merge strategy reconcile it.

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}
 
select
    order_id,
    customer_id,
    status,
    gross_amount,
    created_at,
    updated_at
from {{ ref('stg_orders') }}
 
{% if is_incremental() %}
    -- Reprocess a trailing window so late updates reconcile via the merge.
    -- Size the window from how late your source actually delivers changes,
    -- not from optimism.
    where updated_at >= (
        select dateadd(day, -3, max(updated_at)) from {{ this }}
    )
{% endif %}

The lookback window is a cost/accuracy dial. Three days of reprocessing on a small table is nothing; on a huge one it can eat the savings that justified incremental in the first place. Measure how late your source’s changes actually arrive — query the source for the distribution of updated_at - created_at on changed rows — and size the window from evidence. Also note what a lookback cannot fix: a change older than the window. That is what full refreshes are for.

Deletes: the obligation everyone forgets

Most incremental setups never propagate hard deletes. The row disappears from the source, your watermark filter never sees it (there is nothing to see), and your target keeps a ghost row indefinitely.

Your options, in order of preference:

1. Soft deletes at the source. If the source marks rows deleted with a flag or timestamp, updates flow through the merge like any other change. Push for this whenever you influence the source system.
2. Periodic full refresh reconciles everything, deletes included, on a schedule proportionate to how much silent drift you can tolerate.
3. Change data capture from the source gives you real delete events; if deletes matter and the source cannot soft-delete, that is a pipeline design question, not a dbt tuning question.

What you must not do is assume deletes propagate because “we use merge.” Merge reconciles the rows in your batch. A deleted row is, by definition, not in your batch.

on_schema_change: pick a policy, not a default

When the source adds a column, on_schema_change decides what happens:

• ignore — new columns are dropped silently. Safe, and a great way to never notice your source evolved.
• append_new_columns — new columns are added to the target. Reasonable default for most marts.
• sync_all_columns — adds new columns and removes ones missing from the model. Powerful and dangerous: a renamed source column becomes a drop-and-add, discarding history.
• fail — the run errors until a human resolves it. The right choice for models under a data contract or feeding finance.

Whatever you choose, choose it explicitly per model based on who consumes the output, and revisit it when a model gets promoted from internal to published.

Full-refresh discipline

Full refreshes are the reset button, and healthy projects use them deliberately rather than as an admission of defeat:

• In CI, always full-refresh. CI builds with --full-refresh validate that the model’s logic produces the right answer from scratch, uncontaminated by whatever state an incremental target had accumulated. An incremental-only CI can pass while the model’s full-rebuild logic is broken.
• Schedule periodic full refreshes for high-stakes models — weekly or monthly depending on drift risk and rebuild cost — and compare row counts and key aggregates against the incremental table before swapping, when the stakes justify it.
• After any change to the model’s logic, the next production run should be a full refresh (or a deliberate backfill of the affected window). Changed logic applied only to new rows creates a table where history and recent data were computed by different rules. This is the single most common cause of “the numbers changed and nobody knows why.”
• Know your escape hatch. dbt run --full-refresh --select model_name per model, and document which models are too expensive to full-refresh casually, with the backfill procedure written down before you need it at 2 a.m.

Failure modes worth testing for

Build tests that target incremental-specific corruption:

• unique on the unique_key, run against the target — catches append duplicates and fan-outs.
• A reconciliation singular test comparing target row counts and a checksum-style aggregate against the source over a recent window — catches watermark misses while they are still small.
• Freshness checks on the target itself: if the incremental job silently processes zero rows because a filter broke, the target’s max timestamp stops advancing, and a freshness-style assertion is how you notice before stakeholders do.

Verdict

Use incremental models when a table is big enough that full rebuilds hurt — and not before. For small and medium tables, a full rebuild every run is simpler, self-healing, and usually cheaper than the engineering time incremental debugging consumes.

When you do go incremental: merge on a tested unique_key, a watermark on the column that actually moves when rows change, a lookback window sized from measured lateness, an explicit on_schema_change policy, full refreshes in CI and after every logic change, and a scheduled reconciliation against the source. Teams that treat incremental as “the same model, but faster” end up with tables nobody trusts. Teams that treat it as a small distributed-systems problem — watermarks, idempotency, reconciliation — get the savings without the drift.