Incremental Models After the Happy Path

The first incremental model teaches the comforting version: filter to rows newer than the max timestamp in {{ this }}, merge on a key, and stop rebuilding old history. That pattern is real. It is also the first ten percent of the subject.

The hard part starts when yesterday’s row arrives today, when one batch contains the same key twice, when the warehouse scans the entire target table to merge a thousand new records, or when a model’s schema changes after six months of accumulated history. Incremental models are not just a performance feature. They are stateful data structures. Once you understand that, the sharp edges make sense.

This chapter covers those edges, using the bookshop’s real incremental model — order_events, an append-only log of orders keyed on order_id — as its running example.

The key is the contract

unique_key is not just a merge setting. It is the identity contract for the rows in the target table.

For a one-row-per-order model like order_events, this is fine:

{{ config(
    materialized='incremental',
    unique_key='order_id'
) }}

For a model at a finer grain — say one row per payment, and the bookshop books several payments against a single order — order_id is wrong. stg_payments already hands you a clean payment_id, so unique_key='payment_id' is the natural choice. But when a source doesn’t give you a clean key — when a row is only identifiable by a combination of columns — you reach for a composite key:

{{ config(
    materialized='incremental',
    unique_key=['order_id', 'payment_method']
) }}

Composite keys are supported by modern dbt adapters, but they are only as good as the columns. If any component can be null, the merge predicate can behave differently across warehouses. A safer pattern is often a surrogate key built in SQL — which is exactly what stg_payments does with generate_surrogate_key(['id']):

select
    {{ dbt_utils.generate_surrogate_key(['payment_id']) }} as payment_key,
    order_id,
    payment_method,
    amount
from {{ ref('stg_payments') }}

That is exactly what stg_payments already does — it exposes a payment_key built this way — so unique_key='payment_key' gives the merge one not-null column to match on. When the grain really is composite, you pass the list of columns to generate_surrogate_key and get the same single hashed key back.

The failure mode to watch for is duplicate keys inside the incoming batch. A merge can update the same target row twice, error, or pick one source row depending on the adapter. Do not leave that to chance. Deduplicate before the merge:

with source_rows as (
    select * from {{ ref('stg_orders') }}
    {% if is_incremental() %}
      where order_date >= (
          select coalesce(max(order_date), date '1900-01-01')
          from {{ this }}
      )
    {% endif %}
),

deduped as (
    select *
    from source_rows
    qualify row_number() over (
        partition by order_id
        order by order_date desc
    ) = 1
)

select * from deduped

DuckDB supports qualify, but if your adapter does not, wrap the window in a subquery. The principle is what matters: the batch should contain one winner per target key before dbt asks the warehouse to merge it.

Max timestamp is not enough

The common filter — the one order_events actually ships with — is:

where order_date > (select max(order_date) from {{ this }})

That is fine when source updates are perfectly ordered and never delayed. Real pipelines are not so polite. An order dated Monday may land in the warehouse after an order dated Tuesday. If your target already advanced to Tuesday, the Monday row is invisible forever.

The usual fix is a lookback window:

{% if is_incremental() %}
where order_date >= (
    select max(order_date) - interval '3 days'
    from {{ this }}
)
{% endif %}

Now each run reprocesses a few recent days. The merge keeps the target idempotent, while the lookback catches late-arriving corrections. The window size is a business decision: long enough to cover normal lateness, short enough to keep each run cheap.

This is also why > is often fragile. If two rows share the same order_date and the first run only loaded one of them, > skips the other forever. >= with a merge (or a delete+insert, which is what order_events uses on DuckDB) is usually safer.

Bound the target scan

A merge has two sides: the new rows you selected and the existing target table it must search. Filtering the source side does not automatically make the target side cheap. On a large warehouse table, the merge can still scan years of target partitions to find matching keys.

dbt’s incremental_predicates let supported adapters add extra predicates to the target side of the merge:

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

DBT_INTERNAL_DEST is the alias dbt gives the target table inside its generated merge; DBT_INTERNAL_SOURCE is the incoming batch. The string you supply is ANDed into the merge’s join condition, so it constrains which target rows the warehouse is even willing to match against. On delete+insert — the strategy order_events runs on DuckDB — the same predicate is folded into the delete statement, bounding how much of the target gets touched. Either way, you are telling the engine “the rows that could possibly match live in this slice — don’t read the rest.”

On a real warehouse this is one of the largest cost levers you have. BigQuery bills by bytes scanned; a target predicate on the partition column turns the merge from a full-table scan into partition pruning, and the difference between scanning fourteen days and scanning three years of a fact table is the difference between cents and dollars per run. Snowflake does not bill by bytes, but the same predicate lets it skip micro-partitions it would otherwise have to open, which shows up directly as shorter warehouse time. The BigQuery flavor of the example is the same idea with different date syntax:

incremental_predicates=[
  "DBT_INTERNAL_DEST.order_date >= date_sub(current_date(), interval 14 day)"
]

Do not use this blindly. If an order from last year can be corrected today, a two-week target predicate prevents the merge from finding it and can insert a duplicate — the merge sees no matching target row inside its bounded slice and treats the correction as a brand-new insert. Target predicates are safe only when a business rule bounds the update horizon, and the predicate window should be at least as wide as your source lookback. If the source filter reprocesses three days but the target predicate only exposes one, the extra two days of corrections have nowhere to land.

Strategy is adapter-specific

incremental_strategy is where dbt stops pretending all warehouses behave the same.

Common strategies include:

• append: insert new rows only; no updates, no key matching.
• delete+insert: delete matching keys (or a bounded predicate) from the target, then insert the batch.
• merge: use the warehouse’s native MERGE statement to update-or-insert in one pass.
• insert_overwrite: replace whole partitions rather than individual rows.
• microbatch: split a time-series model into independent event-time batches (dbt 1.9+).

Not every adapter supports every strategy, and the defaults differ. On DuckDB — the adapter this series runs on — the default is delete+insert (which is exactly what order_events declares), and append and microbatch are also available. DuckDB does not implement a native merge strategy or insert_overwrite; those are warehouse features. When a section below shows merge, read it as Snowflake / BigQuery / Databricks behavior, and treat delete+insert as the portable stand-in you would actually run locally. merge is native on Snowflake, BigQuery, Databricks, and Spark; insert_overwrite is the partition-replacement strategy on BigQuery and Spark.

append is only safe when records never change and the grain is naturally append-only, like immutable events — run it twice on overlapping windows and you get duplicates, because nothing dedupes. merge is the comfortable default for mutable facts and dimensions. delete+insert gets you the same idempotency without a native MERGE, at the cost of a second pass over the affected keys. insert_overwrite wins when “replace the affected days” is cheaper and safer than row-level updates.

The decision is not aesthetic. It is about the table’s grain, mutation pattern, warehouse storage layout, and cost.

Control which columns the merge touches

By default, a merge updates every column of a matched row to the incoming values. That is usually what you want, but not always. Two configs narrow it, and they are mutually exclusive.

The examples below assume order_events has grown a couple of audit columns beyond its base grain — an updated_at you refresh on every change and a first_loaded_at you set once on insert. Those aren’t in the shipped model; they’re the natural additions that make column-level merge control worth reaching for.

merge_update_columns lists the only columns the merge is allowed to overwrite on a match. Everything else keeps its existing target value:

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    merge_update_columns=['status', 'updated_at']
) }}

This is how you preserve an audit column like first_loaded_at that you set once on insert and never want a later merge to clobber — leave it out of the list and the merge stops touching it.

merge_exclude_columns is the inverse: update everything except the named columns.

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    merge_exclude_columns=['first_loaded_at']
) }}

Beyond protecting audit columns, this is a genuine cost tool on wide tables. If a fact has a few volatile measures and a dozen stable descriptive columns, restricting the update to the volatile ones means the warehouse rewrites less data per merge. Both configs apply only to the WHEN MATCHED branch — inserts of new rows always write every column, because there is no existing row to preserve. And both are merge-only: they do nothing under delete+insert, append, or on DuckDB, where the whole matched row is deleted and re-inserted regardless.

Replace partitions instead of rows

On a partitioned warehouse, insert_overwrite swaps whole partitions rather than reconciling individual keys. It needs no unique_key. On BigQuery you declare the partition and let dbt overwrite dynamically:

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
      'field': 'order_date',
      'data_type': 'date',
      'granularity': 'day'
    }
) }}

select
    order_id,
    customer_id,
    order_date,
    status
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where order_date >= date_sub(current_date(), interval 3 day)
{% endif %}

dbt inspects the batch, finds every distinct partition it contains, and atomically replaces exactly those partitions in the target — the last three days here — leaving all other days untouched. On Spark the shape is the same with a partition_by=['order_date'] list.

When does this beat merge? When the grain is a partitioned fact and your unit of correction is a whole day (or hour, or month), not a single row. There is no per-row key matching, so it is cheaper than a merge on high-volume tables, and it sidesteps the duplicate risk that a merge carries when the source data has no reliable unique key — you are not matching keys at all, you are declaring “this partition now equals exactly these rows.”

The trade is that partition replacement is all-or-nothing per partition. The batch must contain the complete contents of every partition it touches. If you overwrite today’s partition with only the morning’s orders, the afternoon’s orders are deleted. That makes insert_overwrite a poor fit for trickle updates and a great fit for “recompute these days from the ground truth.” DuckDB has no native insert_overwrite; the portable equivalent is delete+insert with an order_date-bounded incremental_predicates, which deletes the affected window and reinserts it.

Microbatch is for event-time facts

dbt 1.9 introduced the microbatch strategy for large time-series incremental models. Suppose order_events grew large enough that its single high-water-mark query got expensive. Instead of one giant incremental query that reads {{ this }} and computes its own high-water mark, dbt slices the work into independent event-time windows and runs each as its own statement:

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='order_date',
    begin='2024-01-01',
    batch_size='day',
    lookback=3
) }}

select
    order_id,
    customer_id,
    order_date,
    status
from {{ ref('stg_orders') }}

Notice what is not there: no is_incremental() block and no where order_date > max(...). Under microbatch, dbt injects the event-time filter for each batch automatically. You write the model as if it selected all of history; dbt bounds it. The config knobs each do one job:

• event_time — the timestamp/date column that defines a row’s batch. Required. It must be an actual column in the model’s output; here that’s order_date, a real column of order_events.
• batch_size — the granularity of a batch: hour, day, month, or year. dbt aligns batch boundaries to calendar edges (a day batch is midnight to midnight in UTC).
• begin — the earliest batch. On the very first build, dbt materializes every batch from begin up to now. It is also the floor for any backfill.
• lookback — how many of the most recent batches to reprocess on every incremental run, on top of the newest one. It defaults to 1. Setting lookback=3 means each run recomputes the last three days, which is how microbatch catches late-arriving corrections — the same instinct as the lookback window earlier, but managed by the framework instead of hand-written SQL.

Each batch is materialized independently. For a day model with three days of lookback, an incremental run doesn’t fire one query — it fires several, one per day in the reprocessing window, and each one deletes that day’s rows from the target and inserts the batch’s output for that window. That per-batch delete-and-replace is why microbatch needs no unique_key: idempotency comes from the fact that reprocessing a day fully replaces that day. On DuckDB this is exactly the delete+insert you already know from order_events, scoped to one event-time window at a time.

This independence buys several concrete properties.

Backfill is a first-class command. To load a historical range, you don’t edit the model or run a manual full-refresh with a hand-cut filter. You pass the range on the CLI:

dbt run --select order_events \
    --event-time-start "2024-01-01" \
    --event-time-end "2024-04-01"

dbt materializes one batch per day across that quarter, in order, each isolated. A three-year backfill that would blow up as a single query becomes a stream of bounded, retryable statements.

A failed batch doesn’t fail the world. If day 2024-02-14 errors — a bad source row, a transient warehouse hiccup — the other batches still commit. dbt reports which batches failed, and dbt retry reruns only the failed batches, not the whole model. Compare that to a monolithic incremental model, where one bad row means rerunning everything.

Upstream scans shrink automatically. If stg_orders also declares event_time='order_date' in its config, dbt filters that ref to the current batch’s window too. A day-batch of order_events then reads only that day from staging instead of the whole table — the event-time filter propagates through the DAG, not just into the target.

Batches can run in parallel on adapters that support it. dbt decides automatically whether a model’s batches are safe to run concurrently, and you can override it:

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='order_date',
    batch_size='day',
    begin='2024-01-01',
    concurrent_batches=true
) }}

On a warehouse like Snowflake or BigQuery, concurrent batches genuinely parallelize a backfill. On DuckDB, which runs as a single in-process engine, the batches still execute correctly but you won’t see the same speedup — flag this as a place where “works on DuckDB for learning” and “scales on a cloud warehouse” diverge.

Microbatch is not for every incremental model. It fits facts with a real event-time column and a natural time partition: orders, payments, page views, shipments, inventory movements. It is the wrong tool for a dimension where each row is just “the latest customer record” with no meaningful event time — there, a merge on customer_id is the right shape, and forcing a batch column onto it only adds ceremony.

Schema changes are policy decisions

Incremental tables live for a long time. Your model will change. New columns appear, types change, columns are removed, business logic gets corrected.

The on_schema_change config controls what dbt does when the model’s columns no longer match the target. order_events sets it explicitly:

{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='append_new_columns'
) }}

Common choices are:

• ignore (the default): keep going; the merge only touches columns present in both, and new model columns are dropped from the incoming set.
• fail: stop so a human decides.
• append_new_columns: add new columns to the target but do not remove old ones. This is what order_events uses — a new column starts flowing without a full refresh, and nothing you already have gets dropped.
• sync_all_columns: try to align the target with the model, including dropping columns the model no longer produces.

The edge cases matter more than the list. sync_all_columns is the one to respect: aligning the target means it will issue an ALTER TABLE ... DROP COLUMN for anything the model stopped selecting, and that data is gone. On a long-lived fact that a dashboard reads, a quiet rename in the model can silently delete a column of history. append_new_columns is the gentler cousin — it only ever adds — but it cannot change a column’s type; a widened or retyped column still forces a full refresh. And regardless of setting, adding a new column never backfills values into old rows: they get NULL for every batch that ran before the column existed, because those rows were written by a version of the model that didn’t compute it.

The strategy changes the mechanics, too. on_schema_change is a merge/delete+insert/append concern — it manages the persisted target table. Under insert_overwrite, a schema change interacts with partition replacement: added columns show up only in the partitions you overwrite after the change, so an old, untouched partition keeps the old shape until you reload it. Under microbatch, on_schema_change applies per the same rules, but because backfills reprocess old windows, a sync_all_columns drop plus a wide backfill can rewrite far more history than you intended — check the setting before you kick off a multi-year reprocess.

The safest default in many production projects is fail or append_new_columns, not because sync_all_columns is bad, but because schema changes are contracts. If a BI dashboard depends on a column, quietly dropping it because a model changed is not kindness.

Logic changes are harder still. Suppose order_events gained a computed column — say an amount summed from stg_payments, the way the orders mart does — and you later corrected the formula. Old rows do not magically update unless your incremental filter reprocesses them — on_schema_change won’t help, because the column set didn’t change, only its computed values. Sometimes the honest answer is:

dbt run --full-refresh --select order_events

That is why full_refresh: false exists for models where a full rebuild is too expensive or dangerous:

{{ config(
    materialized='incremental',
    full_refresh=false
) }}

Use that guard sparingly. It protects state — a stray --full-refresh won’t nuke the table — but it also means you need a deliberate backfill path (a bounded microbatch range, or a temporary predicate) when logic changes.

Choosing a strategy

Most of this chapter collapses into one question: what is the table’s shape and how does it mutate? That determines the strategy; the adapter determines what you can actually name.

Table shape	Mutation pattern	Strategy	Adapter notes
Immutable event stream (clicks, page views)	append-only, never corrected	append	All adapters. Safe only if you never reprocess an overlapping window.
Large event-time fact (orders, shipments)	inserts + late corrections	microbatch	dbt 1.9+. delete+insert per batch on DuckDB; parallel batches on cloud warehouses.
Partitioned fact reloaded by period	recompute whole day/month	insert_overwrite	BigQuery / Spark native. On DuckDB/Snowflake use delete+insert + incremental_predicates.
Mutable dimension (latest customer)	row-level updates	merge	Native on Snowflake / BigQuery / Databricks. delete+insert on DuckDB.
Small-to-medium mutable fact	row-level updates	merge or delete+insert	delete+insert is the portable choice; merge where the adapter has it.
Anything on DuckDB	—	delete+insert (default), append, microbatch	No native merge or insert_overwrite; model the concept, deploy the warehouse-specific strategy elsewhere.

order_events sits on the second row — an event-time fact — but ships as plain delete+insert keyed on order_id rather than microbatch, because at the bookshop’s volume the simple high-water-mark query is still cheap. Read the matrix as guidance, not law. A mutable dimension on BigQuery might still use insert_overwrite if it’s partitioned by load date; a small fact might stay a plain table until it’s actually slow. The point is to reach for the strategy the data’s behavior asks for, then check it against what your adapter supports — and to notice when “runs green on DuckDB” is teaching the shape rather than the production strategy.

Test the stateful parts

Incremental models need the normal tests: uniqueness, not-null keys, relationships, accepted values. They also need tests that protect the stateful assumptions:

models:
  - name: order_events
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: order_date
        data_tests:
          - not_null

For late-arriving patterns, add reconciliation checks. If the source has 10,000 orders and the target has 9,991, a unique key test will still pass. Row-count and sum checks, often with tolerances, catch drift:

data_tests:
  - dbt_utils.equal_rowcount:
      compare_model: ref('stg_orders')
      config:
        severity: warn

Unit tests can check the SQL logic, but they cannot fully simulate the adapter’s merge behavior. For serious incremental models, run an integration test against a scratch schema: seed an initial target, run once, mutate the source, run again, and inspect the final table. For a microbatch model, the equivalent is to run a small backfill over a fixed range, corrupt one source day, dbt retry, and confirm only that day changed.

Final thoughts

An incremental model is a promise: this table can be updated in pieces and still mean the same thing as a full rebuild. Keeping that promise takes more than is_incremental(). You need a real key, a late-arrival strategy, a bounded target scan, an adapter strategy that matches the warehouse, controls over which columns and partitions each run touches, a schema-change policy, and tests that detect drift. Once those are in place, incremental models stop being a clever trick and become a production tool.

Next: Tests That Read Like Assertions