How to create an incremental model in dbt

A table model rebuilds everything on every run. For a fact table with millions of rows that grows a little each day, that is wasted time and credits. An incremental model builds the table once, then on each run selects only the rows that are new or changed and merges them into the existing table.

Copy and paste

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

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

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

dbt run -s fct_orders                   # first run: full build; later runs: only new rows
dbt run -s fct_orders --full-refresh    # rebuild from scratch

A key made of several columns:

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

A lookback window for late rows (Snowflake syntax):

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

How a run works

is_incremental() is true when the table already exists, the model is incremental and the run is not a --full-refresh. On the first run it is false, so the whole query runs and builds the table.
{{ this }} is the existing table. The filter reads its latest updated_at and keeps only the newer source rows.
dbt puts the selected rows in a temporary table, then merges them into the target on unique_key: existing keys are updated, new keys inserted.

In the example project, after the first run order 4 changed status and order 5 arrived. The second run selected exactly those two rows, updated order 4 and inserted order 5, and every order still appears once.

The unique key

Without unique_key dbt only appends. A changed row arrives as a second copy.
The key must be unique within each batch. If the new rows contain the same key twice, Snowflake’s merge fails; see how to fix unique_key duplicates.
Keys must not be null. A null never matches, so rows with a null key are inserted again on every run.

Strategies on Snowflake

incremental_strategy decides how the new rows are written. On Snowflake the default is merge.

Strategy	Does	Use when
`merge` (default)	updates matching keys, inserts the others	rows can change after they are loaded
`delete+insert`	deletes the target rows with the batch’s keys, then inserts the batch	a key covers several rows, such as all the rows of one day
`append`	inserts the batch, no key needed	events that never change
`insert_overwrite`	replaces the data selected by the batch	rebuilding whole partitions
`microbatch`	processes the data in time batches by `event_time`	large event tables, backfills by period

When the columns change

By default an incremental model ignores columns added to its query: the existing table keeps its old columns. on_schema_change='append_new_columns' adds new columns to the table, sync_all_columns also removes dropped ones, and fail stops the run. Changes in logic still need a full refresh.

Tested with dbt Core 1.12 (DuckDB adapter, where the default strategy is delete+insert). The Snowflake strategies come from dbt-snowflake 1.12.