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.