When should a dbt model be incremental?

When a full rebuild takes long enough to hurt and the source is append-mostly. Incremental models add complexity and a class of correctness bugs, so keep a model as a table until its build time is a real problem — most models never need it.

What does is_incremental() actually check?

It returns true only when the model is materialized as incremental, the target relation already exists, and `--full-refresh` was not passed. On the first run it is false, so the model builds the whole history from the same SQL file.

What is a unique_key in an incremental model?

The column dbt uses to decide whether an incoming row updates an existing one or is inserted. Without it, incremental runs append blindly and any reprocessed row becomes a duplicate. With it, dbt issues a merge or a delete-then-insert.

Why does my incremental model miss rows?

Almost always late-arriving data. Filtering on `max(timestamp)` from the target skips anything that lands with an earlier timestamp after that run. Fix it with a lookback window — filter to a few days before the maximum, and use a `unique_key` so the reprocessed rows update rather than duplicate.

Incremental Models | SkillByExample

An incremental model builds its whole history the first time and only the new rows after that. The mechanism is one macro and one placeholder, and the correctness problems come entirely from what you put between them.

The shape

-- models/marts/order_events.sql
{{ config(
    materialized='incremental',
    unique_key='order_id'
) }}

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

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

Two pieces do the work. is_incremental() is false on the first run and true afterwards, and {{ this }} resolves to the model’s own relation — the table as it exists right now.

dbt run --select order_events

13:02:44  Running with dbt=1.9.1
13:02:44  Found 6 models, 2 seeds, 1 source, 431 macros
13:02:44
13:02:44  1 of 1 START sql incremental model main.order_events ........... [RUN]
13:02:44  1 of 1 OK created sql incremental model main.order_events ...... [OK in 0.09s]
13:02:44
13:02:44  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

“created” — the table did not exist, so dbt built it in full and ignored the where clause entirely:

create table "bookshop"."main"."order_events" as (
    select order_id, customer_id, ordered_at, status, amount
    from "bookshop"."main"."stg_orders"
);

Add a new order to the seed data and run again:

13:05:19  1 of 1 START sql incremental model main.order_events ........... [RUN]
13:05:19  1 of 1 OK created sql incremental model main.order_events ...... [OK in 0.11s]

Same line, very different SQL:

create temporary table "order_events__dbt_tmp" as (
    select order_id, customer_id, ordered_at, status, amount
    from "bookshop"."main"."stg_orders"
    where ordered_at > (select max(ordered_at) from "bookshop"."main"."order_events")
);

delete from "bookshop"."main"."order_events"
where order_id in (select order_id from "order_events__dbt_tmp");

insert into "bookshop"."main"."order_events"
select * from "order_events__dbt_tmp";

That is the whole trick. Select the new rows into a temp table, remove any existing rows with the same unique_key, insert. Reading this generated SQL is the fastest way to debug an incremental model that is behaving oddly.

Without a unique_key

{{ config(materialized='incremental') }}

The delete step disappears — dbt just appends:

insert into "bookshop"."main"."order_events"
select * from "order_events__dbt_tmp";

Fine for immutable event streams. Wrong for anything reprocessable: run the same window twice and the rows are simply there twice.

duckdb bookshop.duckdb -c "select order_id, count(*) from main.order_events group by 1 having count(*) > 1"

┌──────────┬──────────────┐
│ order_id │ count_star() │
├──────────┼──────────────┤
│     1007 │            2 │
└──────────┴──────────────┘

No error, no warning — just a duplicate that inflates every downstream sum. This is why the unique test on an incremental model’s key is not optional.

The bug that costs you rows

The where ordered_at > max(ordered_at) filter assumes data arrives in timestamp order. It does not. An order created at 23:58 that reaches the warehouse at 00:04, after a run at 00:00, has a timestamp earlier than the maximum — so it is never selected again.

# run at 00:00 — max(ordered_at) becomes 2026-01-12 23:58
# 00:04 — order 1008 lands, ordered_at 2026-01-12 23:52
# run at 01:00 — where ordered_at > '2026-01-12 23:58' → 1008 never selected

duckdb bookshop.duckdb -c "select count(*) from main.stg_orders; select count(*) from main.order_events"

┌──────────────┐
│ count_star() │
├──────────────┤
│            8 │
└──────────────┘
┌──────────────┐
│ count_star() │
├──────────────┤
│            7 │
└──────────────┘

One row short, permanently, with every command reporting success. The fix is a lookback window — reprocess a few days of overlap and let unique_key deduplicate:

{% if is_incremental() %}
where ordered_at >= (
    select coalesce(max(ordered_at), '1900-01-01'::date) - interval 3 day
    from {{ this }}
)
{% endif %}

13:14:08  1 of 1 START sql incremental model main.order_events ........... [RUN]
13:14:08  1 of 1 OK created sql incremental model main.order_events ...... [OK in 0.12s]

┌──────────────┐
│ count_star() │
├──────────────┤
│            8 │
└──────────────┘

Three days of rows are re-selected on every run and the delete+insert replaces them rather than duplicating. You pay a little extra compute for correctness — a good trade every time. The coalesce matters too: an empty target returns null, and x >= null is null, so without it a rebuilt-empty model stays empty forever.

Strategies

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

Strategy	What it does	Where
`append`	insert only, no dedup	everywhere
`delete+insert`	delete matching keys, then insert	Postgres, Redshift, DuckDB, Snowflake
`merge`	single `MERGE INTO` statement	Snowflake, BigQuery, Databricks, Spark
`insert_overwrite`	replace whole partitions	BigQuery, Databricks, Spark
`microbatch`	dbt splits the window into batches for you	dbt 1.9+

merge on Snowflake compiles to one atomic statement:

merge into "analytics"."main"."order_events" as DBT_INTERNAL_DEST
using "order_events__dbt_tmp" as DBT_INTERNAL_SOURCE
    on DBT_INTERNAL_SOURCE.order_id = DBT_INTERNAL_DEST.order_id
when matched then update set
    status = DBT_INTERNAL_SOURCE.status,
    amount = DBT_INTERNAL_SOURCE.amount
when not matched then insert (order_id, customer_id, ordered_at, status, amount)
    values (...)

insert_overwrite is the one to reach for on partitioned warehouses — replacing whole days is far cheaper than matching row by row:

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

When the columns change

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

Add a column to the model and run without a full refresh:

13:22:47  1 of 1 START sql incremental model main.order_events ........... [RUN]
13:22:47
13:22:47  In model.bookshop.order_events, new columns detected:
13:22:47    - discount_amount (DOUBLE)
13:22:47
13:22:47  1 of 1 OK created sql incremental model main.order_events ...... [OK in 0.13s]

Value	Behaviour
`ignore` (default)	new column silently dropped from the insert
`append_new_columns`	added to the table, null for existing rows
`sync_all_columns`	adds and removes to match the model
`fail`	error out and make you decide

The default is the dangerous one — you add a column, the run succeeds, and the column is missing until someone full-refreshes months later. Set this explicitly on every incremental model.

Rebuilding

dbt run --select order_events --full-refresh

13:26:11  1 of 1 START sql incremental model main.order_events ........... [RUN]
13:26:11  1 of 1 OK created sql incremental model main.order_events ...... [OK in 0.10s]

is_incremental() returns false, the filter is skipped, the table is dropped and rebuilt from scratch. Any incremental model must survive this — if a full refresh produces different numbers from the incremental path, the incremental logic is wrong. Scheduling a weekly full-refresh is a cheap insurance policy against exactly that drift.

Protect genuinely un-rebuildable models — where the source has already aged out — with:

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

13:28:30  1 of 1 SKIP relation main.order_events ......................... [SKIP]

Practice

1. Build an incremental model, add a row, and confirm only that row is processed.

# first run
13:34:02  1 of 1 OK created sql incremental model main.order_events ...... [OK in 0.09s]
# after adding one order
13:34:41  1 of 1 OK created sql incremental model main.order_events ...... [OK in 0.05s]

The second run is faster, and the generated SQL in target/run/ shows the where clause that made it so. On a real table the difference is minutes against hours.

2. Remove unique_key and run the same window twice.

┌──────────────┬──────────────┐
│ total_orders │ distinct_ids │
├──────────────┼──────────────┤
│           16 │            8 │
└──────────────┴──────────────┘

Every row doubled, with no failure anywhere. Add unique and not_null tests on the key of every incremental model — they are the only thing standing between this and a wrong dashboard.

3. Simulate late-arriving data and watch a row go missing.

# stg_orders: 8 rows
# order_events: 7 rows

Then add the three-day lookback and rerun:

# order_events: 8 rows

The missing row returns without a full refresh, because the widened window re-selected it and unique_key replaced rather than duplicated it. Size the window to your worst observed arrival delay, not your typical one.

4. Add a column with on_schema_change='fail'.

13:42:19  1 of 1 ERROR creating sql incremental model main.order_events .. [ERROR in 0.04s]

13:42:19    Compilation Error in model order_events (models/marts/order_events.sql)
13:42:19      The source and target schemas on this incremental model are out of sync!
13:42:19      Source columns not in target: discount_amount
13:42:19      Please update the target or use the --full-refresh flag

Loud and specific. Compare that with the default ignore, which returns PASS and quietly drops the column — for a schema change you actually want the failure.

Next: Jinja and macros — the templating layer that generates all this SQL.