5. Incremental models

Incremental models are the biggest performance win dbt offers and the biggest source of quietly wrong data. Both facts have the same cause: you are telling dbt which rows to skip, and dbt believes you.

The shape

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

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

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

Three pieces do the work:

• is_incremental() — true only when the relation already exists, this is not a --full-refresh, and the model is configured incremental. It is a plain Jinja boolean; the where clause is textually absent on a first run.
• {{ this }} — the model’s own relation. You are querying the table you are about to update.
• unique_key — how dbt identifies a row it has seen before.

What dbt actually does

Not one statement. Two phases.

   PHASE 1  build a temp relation from your SELECT
            (with the incremental filter applied)

     create temporary table stg_orders__dbt_tmp as (
         select ... where updated_at > (select max...)
     );

   PHASE 2  fold it into the real table, using
            the configured strategy

     merge into orders as target
     using stg_orders__dbt_tmp as source
       on target.order_id = source.order_id
     when matched then update set ...
     when not matched then insert ...

Your SQL only produces the candidate rows. The strategy decides what happens to them. Confusing those two is the root of most incremental bugs — people write a perfect filter and then wonder why duplicates appear, because they never set a unique_key.

The strategies

Availability varies by adapter; these are the common ones.

append

insert into orders select * from tmp;

Blind insert. Fast, no unique_key, no matching.

Only safe when rows are immutable and never re-delivered — append-only event logs. If a row can be updated at source, or your loader can deliver the same record twice, append gives you duplicates and no error.

merge

merge into orders as t using tmp as s
    on t.order_id = s.order_id
when matched then update set ...
when not matched then insert ...

The default on Snowflake, BigQuery and Databricks, and the right answer most of the time. Handles updates and inserts in one atomic statement. Requires unique_key.

The cost is that a merge scans the target to find matches. On a very large table this can dominate the run, and partition or cluster keys on the merge column matter enormously.

delete+insert

delete from orders where order_id in (select order_id from tmp);
insert into orders select * from tmp;

What adapters without a good MERGE use — Postgres, Redshift. Two statements, so wrap it in a transaction if your adapter does not; otherwise a failure between them leaves rows deleted and not replaced.

insert_overwrite

-- replace whole partitions

Partition-level replacement. Instead of matching rows, dbt deletes every partition present in the new data and inserts the new version wholesale. Native on BigQuery and Spark/Databricks.

   new data touches 2026-09-07, 2026-09-08

   → drop partitions 2026-09-07, 2026-09-08
   → insert all new rows

   partitions before 09-07: untouched

Much cheaper than merge on very large partitioned tables, because nothing is matched row by row. The requirement is that your SELECT must produce complete partitions — if it emits three rows for a day that has a million, you just deleted the other 999,997.

microbatch

Added in dbt 1.9. Instead of one statement over the whole increment, dbt splits the work into time-based batches and runs them independently.

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'microbatch',
    event_time   = 'order_placed_at',
    batch_size   = 'day',
    lookback     = 3
) }}

select * from {{ ref('stg_orders') }}

No is_incremental() block. dbt filters each batch for you from event_time, and the model reads as if it processes everything.

The win is that a failed batch does not fail the model, batches can run in parallel, and backfilling a date range is a flag rather than a hand-written where clause:

dbt run --select orders --event-time-start 2026-01-01 --event-time-end 2026-02-01

It is the best answer for large time-series models on adapters that support it. Check your adapter and dbt version before designing around it.

unique_key

unique_key = 'order_id'                     -- single column
unique_key = ['order_id', 'line_number']    -- compound

A compound key is a list, not a concatenated string. Passing 'order_id || line_number' will appear to work and will match wrongly at the edges.

If unique_key is not actually unique in the target, merge behaviour is undefined and adapter-specific — some warehouses error, some pick a row arbitrarily. This is a real and common bug. The fix is a test:

models:
  - name: orders
    tests:
      - unique:
          column_name: order_id

Every incremental model should have a uniqueness test on its unique_key. Without one, the failure is invisible until somebody notices a doubled revenue figure.

Where the natural key is compound, dbt_utils.generate_surrogate_key builds a hash column, which is easier to merge on and to test.

The late-arriving data problem

The classic incremental bug, and worth understanding properly.

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

Looks correct. Consider:

   10:00  run completes. max(updated_at) = 10:00
   10:01  a row arrives with updated_at = 09:58
          (its source system was slow)
   11:00  run: where updated_at > 10:00

   → the 09:58 row is never seen. ever.

The row is not late by dbt’s clock; it is late by the data’s. Any filter using a strict high-water mark drops it permanently.

The standard fix is a lookback window — deliberately reprocess a period you have already covered:

{% if is_incremental() %}
where updated_at > (
    select dateadd(day, -3, max(updated_at)) from {{ this }}
)
{% endif %}

Three days of rows are reprocessed every run. With merge and a correct unique_key that is harmless — the same rows overwrite themselves. The lookback only works if the strategy is idempotent. With append, a lookback creates three days of duplicates.

Size the window to how late your data actually gets, and make it larger than you think. Reprocessing costs a little compute; missing rows costs credibility. The microbatch strategy’s lookback setting is this idea made first-class.

on_schema_change

Your model adds a column. The target table does not have it. What now?

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

Value	Behaviour
ignore	Old default. New columns silently dropped.
fail	Error and stop.
append_new_columns	Add new columns; existing rows get NULL.
sync_all_columns	Add new, drop removed, alter types.

ignore is the dangerous one. You add a column, the run succeeds, and the column is not there. People lose an afternoon to this.

append_new_columns is the sensible default. Note that historical rows get NULL for the new column rather than a computed value — filling those in needs a --full-refresh.

sync_all_columns drops columns, and that is data loss on a table you cannot rebuild cheaply. Use it knowingly.

Full refresh

dbt run --select orders --full-refresh

Ignores is_incremental(), drops the table, rebuilds from scratch. The escape hatch for every incremental problem — changed logic, backfilled data, a schema change that needs recomputing, a filter that turned out to be wrong.

For tables where a rebuild would be genuinely catastrophic:

{{ config(full_refresh = false) }}

dbt then refuses to rebuild even when asked. Use it on tables built from a source that no longer exists — but understand that you have also given up your only recovery mechanism.

A good incremental model is one you could rebuild if you had to. If --full-refresh would take a week, you have an operational problem waiting.

Idempotence

The property that makes incremental models safe:

Running the model twice in a row produces the same table as running it once.

merge with a correct unique_key is idempotent. append is not. insert_overwrite is, provided partitions are complete. delete+insert is, within its transaction.

Test it. Run the model twice, compare row counts. If they differ, the model is not idempotent and a retry after a partial failure will corrupt it.

When not to use incremental

   ✗ the table rebuilds in under a few minutes
     → the complexity is not worth it

   ✗ the logic changes often
     → every change needs a full refresh anyway

   ✗ rows change arbitrarily far back in history
     → no filter is safe; a lookback would
       have to cover everything

   ✗ there is no reliable updated_at
     → you cannot write a correct filter

   ✗ deletes at source must be reflected
     → incremental sees inserts and updates,
       not absence

That last one catches people. If a row is deleted at source, nothing arrives to tell your model. The row stays in your table forever. Handling deletes needs either soft deletes at source, a periodic full refresh, or a snapshot (chapter 6).

Reach for incremental when a full rebuild is genuinely too slow or too expensive, and not before. A five-minute table rebuild is worth far more than a thirty-second incremental model nobody trusts.

What to take from this chapter

• Your SELECT produces candidate rows; the strategy decides what happens to them. Conflating the two causes most incremental bugs.
• is_incremental() is false on the first run and under --full-refresh; the filter is textually absent.
• merge is the default and usually right. append only for immutable, never-redelivered rows. insert_overwrite for large partitioned tables, and your SELECT must produce whole partitions. microbatch (1.9+) is the best answer for large time-series models where it is available.
• unique_key must actually be unique. Test it, always. Compound keys are a list, not a concatenated string.
• Late-arriving data is dropped permanently by a strict high-water mark. Use a lookback window, sized generously — and only with an idempotent strategy.
• Set on_schema_change. append_new_columns is the sensible default; ignore silently drops columns; sync_all_columns deletes data.
• --full-refresh is the escape hatch. A model you could not rebuild is an operational problem.
• Idempotence is the safety property: run it twice, get the same table. Verify it.
• Incremental sees inserts and updates, never deletes. Absence does not arrive.

Next: sources, seeds and snapshots — freshness checks, small reference data, and how dbt captures history that the source overwrites.