Home / Guides
SQL to dbt

dbt Incremental Models, Shown with Real Runs

By · November 6, 2026 · 10 min read

The short version

A dbt incremental model is a table that dbt builds in full on the first run and then only adds to: on later runs, the SQL inside {% if is_incremental() %} filters the source down to new or changed rows, usually by comparing a timestamp with max(updated_at) from {{ this }}. Add a unique_key and a changed row replaces the old one; leave it out and every selected row is appended, duplicates included. A strict > max(updated_at) filter silently misses rows that arrive late with an older timestamp, so subtract a lookback window. --full-refresh rebuilds the table from scratch, and on_schema_change decides what happens when you add a column.

Everything below was run on dbt-core 1.11.7 with dbt-postgres 1.10.0 against PostgreSQL 16.14, on one raw_orders table of 200,000 rows. Timings are what dbt printed on a laptop.

How dbt incremental models work

The incremental models page puts it in two sentences: the first time the model runs, the table is built from all rows of the source; on later runs dbt transforms only the rows you tell it to filter for and inserts them into the table that already exists.

is_incremental() is true only when all three hold: the model already exists as a table, --full-refresh was not passed, and the model is configured with materialized='incremental'. {{ this }} is the model's own table in the database, which is how the filter can ask what was loaded last time.

This is the model I started with, the cutoff exactly as dbt's incremental best-practice guide writes it:

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

select order_id,
       customer_id,
       status,
       amount,
       updated_at
from {{ source('shop', 'raw_orders') }}

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

Run 1: the full build

No table yet, so is_incremental() is false and the where never reaches the database:

21:26:33  1 of 1 OK created sql incremental model dbt_incremental_models_shown_with_real_runs.orders  [SELECT 200000 in 0.17s]

dbt writes compiled SQL under target/, next to dbt_project.yml. What it ran is in target/run/inc/models/orders.sql (blank lines and indentation trimmed):

create  table "postgres"."dbt_incremental_models_shown_with_real_runs"."orders"
as
(
select order_id,
       customer_id,
       status,
       amount,
       updated_at
from "postgres"."dbt_incremental_models_shown_with_real_runs"."raw_orders"
);

Run 2: only the new and changed rows

Then the source moved: 500 new orders, and 300 existing orders went from new to paid with a fresh updated_at.

INSERT 0 500
UPDATE 300
21:26:44  1 of 1 OK created sql incremental model dbt_incremental_models_shown_with_real_runs.orders  [INSERT 0 800 in 0.25s]

800 rows, the 500 plus the 300. target/compiled/ holds the model's select with the Jinja resolved, and now the filter is in it, {{ this }} replaced by the table name:

where updated_at > (select max(updated_at) from "postgres"."dbt_incremental_models_shown_with_real_runs"."orders")

target/run/ holds the statements that changed the table (whitespace trimmed). dbt first put the filtered rows in a temporary table (create temporary table "orders__dbt_tmp172643920894", visible in logs/dbt.log), then:

delete from "postgres"."dbt_incremental_models_shown_with_real_runs"."orders" as DBT_INTERNAL_DEST
where (order_id) in (
    select distinct order_id
    from "orders__dbt_tmp172643920894" as DBT_INTERNAL_SOURCE
);
insert into "postgres"."dbt_incremental_models_shown_with_real_runs"."orders" ("order_id", "customer_id", "status", "amount", "updated_at")
(
    select "order_id", "customer_id", "status", "amount", "updated_at"
    from "orders__dbt_tmp172643920894"
)

That is delete+insert, which the dbt-postgres configs page lists as the default when unique_key is set (append when it is not). So the "INSERT 0 800" hides 300 deletes. Result:

rows | keys | updated_to_paid
200500 | 200500 | 300

Whether merge would serve you better is its own topic: dbt incremental: merge vs delete+insert.

Run 3: the late-arriving update the filter missed

The table's max(updated_at) was now 2026-10-15 09:00. Then order 1000 was refunded at 08:30, but the loader delivered that change only now, along with one brand new order stamped 10:00:

UPDATE 1
INSERT 0 1
21:26:57  1 of 1 OK created sql incremental model dbt_incremental_models_shown_with_real_runs.orders  [INSERT 0 1 in 0.19s]

One row: the new order. The refund is older than the cutoff, so updated_at > max(updated_at) skipped it. The run is green and the table is wrong. EXCEPT both ways between source and model shows it:

side | order_id | customer_id | status | amount | updated_at
only in raw | 1000 | 1001 | refunded | 380.99 | 2026-10-15 08:30:00+00:00
only in dbt | 1000 | 1001 | paid | 380.99 | 2026-10-01 01:40:00+00:00
(2 rows)

No later run picks it up either: the cutoff only moves forward. dbt's guide describes this case under late-arriving facts: rows already newer than the late ones set the max(updated_at), and the late records never make it to the model. The guide's examples are a whole load arriving late, or some rows landing in a load after the rows they belong with.

The lookback window fix

The guide's fix is to subtract a window from the cutoff, so every run re-reads the last few hours (or days) and the unique_key keeps the re-read rows from duplicating. One line changed:

{% if is_incremental() %}
where updated_at >= (select max(updated_at) - interval '3 hours' from {{ this }})
{% endif %}
21:27:08  1 of 1 OK created sql incremental model dbt_incremental_models_shown_with_real_runs.orders  [INSERT 0 802 in 0.26s]

rows | keys
200501 | 200501

order_id | status | updated_at
1000 | refunded | 2026-10-15 08:30:00+00:00

side | order_id | customer_id | status | amount | updated_at
(0 rows)

802 rows went through the delete and insert: everything stamped from 07:00 to 10:00, including the refund. The row count stayed at 200,501 because each of them replaced itself. That is the price, rows reprocessed on every run, and the guide is plain that a window only slows the drift: with a 3-day window, a record 4 days late is still missed. Size the window from how late your loader actually runs.

What unique_key changes

To see it, I copied the model to orders_no_key with the same lookback filter and no unique_key, then ran the project three times with no new source data:

21:27:17  2 of 2 OK created sql incremental model ...orders_no_key  [SELECT 200501 in 0.09s]
21:27:19  2 of 2 OK created sql incremental model ...orders_no_key  [INSERT 0 802 in 0.07s]
21:27:22  2 of 2 OK created sql incremental model ...orders_no_key  [INSERT 0 802 in 0.07s]

model | rows | keys
orders | 200501 | 200501
orders_no_key | 202105 | 200501

1,604 duplicate rows after two runs of nothing changing. Its target/run/ file has the insert and no delete. The docs say exactly this: without a unique_key, most adapters are append-only, inserting every row the SQL returns "without regard for whether the rows represent duplicates". The append strategy does not check whether a record already exists, so a lookback window without a unique_key is a duplicate generator.

The same section warns that unique_key columns should contain no nulls, or the model may fail to match rows and write duplicates, and that a key appearing more than once in the new rows or the table may make the run fail depending on the strategy. A unique test on the key catches both.

--full-refresh

The flag makes is_incremental() return false for all models, so the filter disappears and the table is rebuilt from the whole source. It fixed the duplicated model:

$ dbt run -s orders_no_key --full-refresh
21:27:33  1 of 1 OK created sql incremental model ...orders_no_key  [SELECT 200501 in 0.25s]

rows | keys
200501 | 200501

The docs say the flag drops the existing table before rebuilding it. On Postgres the log showed a gentler order: dbt built orders_no_key__dbt_tmp, renamed the live table to orders_no_key__dbt_backup, renamed the new one into place, and only then dropped the backup. The old table stayed readable until the swap.

The guide suggests a regular full refresh, weekly or monthly at a quiet time, to reset the drift that late rows cause even with a lookback. When to stop being incremental at all: dbt incremental vs full refresh.

on_schema_change when you add a column

I added one computed column to the model, case when amount >= 500 then 'large' else 'small' end as size_band, inserted one source row, and ran it without touching any config:

21:27:45  1 of 1 OK created sql incremental model ...orders  [INSERT 0 803 in 0.14s]

column_name
order_id
customer_id
status
amount
updated_at
(5 rows)

Green run, no size_band. That is the default, on_schema_change: ignore: an added column does not appear in the target table. With on_schema_change='fail' the same run stops instead:

21:27:47  1 of 1 ERROR creating sql incremental model ...orders  [ERROR in 0.13s]
...
  Compilation Error in model orders (models/orders.sql)
    The source and target schemas on this incremental model are out of sync!
    ...
       Source columns not in target: [PostgresColumn(column='size_band', dtype='text', char_size=None, numeric_precision=None, numeric_scale=None)]
       Target columns not in source: []
       New column types: []

With on_schema_change='append_new_columns', dbt ran alter table ... add column "size_band" text and carried on:

21:27:54  1 of 1 OK created sql incremental model ...orders  [INSERT 0 803 in 0.19s]

size_band | rows
None | 199699
large | 400
small | 403

Only the 803 rows in this run's window got a value. The docs are explicit that none of the on_schema_change settings backfill old rows. The fourth option, sync_all_columns, also drops columns that left the model and applies type changes. A full refresh filled the rest:

21:27:57  1 of 1 OK created sql incremental model ...orders  [SELECT 200502 in 0.28s]

size_band | rows
large | 101258
small | 99244

Is the incremental run faster?

Not at 200,000 rows: the full build took 0.17s and the incremental runs 0.14 to 0.26s. So I grew the source to 3,000,502 rows and timed it again.

Run, 3.0 million rows Rows dbt time
--full-refresh, no indexes 3,000,502 1.65s, 1.82s
incremental, 1,000 new rows, no indexes 1,803 0.75s
incremental, no new rows, no indexes 1,002 0.87s
--full-refresh, with the two indexes below 3,001,502 3.27s
incremental, index on the model only 2,002 0.48s
incremental, index on the model only 2,001 0.34s
incremental, indexes on model and source 3,001 0.21s
incremental, indexes on model and source 3,000 0.24s
--full-refresh, with indexes 3,003,502 3.38s

Without indexes, each incremental run scans the whole source for the window, scans the whole model for max(updated_at), and runs the delete ... where order_id in (...) against a table with no index on order_id. dbt-postgres takes an indexes config on incremental models and creates them with the table:

indexes=[{'columns': ['order_id'], 'unique': True}, {'columns': ['updated_at']}]

That and a create index on raw_orders (updated_at) on the source brought a run down to about 0.2 seconds, while the full rebuild got slower because it now builds two indexes on 3 million rows. Each run still reprocessed its 3-hour window, 3,000 rows here, whether or not anything changed.

Not covered here

Keeping every historical version of a row instead of the latest one is a snapshot's job: dbt incremental vs snapshot. Splitting a big event table into time batches that dbt fills and retries one by one is microbatch: dbt microbatch vs incremental. If this table came out of a stored procedure with a watermark table, convert a stored procedure to dbt models starts there.

Sources