dbt Incremental Models — A Field Guide

A table materialization rebuilds the entire result set on every dbt run. That is fine at a million rows and painfully slow — or expensive — at a billion. Incremental models solve this by processing only new or changed rows after the first full build, at the cost of more moving parts to get right.

The basic shape of an incremental model

An incremental model is a normal SQL model with materialized='incremental' and an is_incremental() conditional block that only applies on runs after the first. On the very first run, or a full-refresh run, dbt builds the table from the entire query; on every subsequent run, the `is_incremental()` block adds a filter so only new rows get processed and appended.

{{
  config(
    materialized='incremental',
    unique_key='event_id'
  )
}}

select
    event_id,
    user_id,
    event_type,
    occurred_at
from {{ source('raw', 'events') }}

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

{{ this }} refers to the model's own existing table in the warehouse, which is why the filter can ask "give me only rows newer than what I already have." is_incremental() evaluates to false on the first run (the table does not exist yet) and on any run with --full-refresh, so the query always falls back to a full build when needed.

Merge vs. append — the strategy choice

The append strategy (dbt's default for most adapters) just inserts new rows — correct only if source rows never get updated after they are first seen, which is rare outside pure event logs. The merge strategy (needs unique_key) updates existing rows that changed and inserts new ones, which is what most incremental models actually need — an order that gets refunded a week later should update in place, not create a duplicate row.

Handling schema changes safely

on_schema_change controls what happens when the model's column set changes between runs. ignore (the default) silently drops new columns from being added to the target table — usually not what you want. append_new_columns adds new columns without failing the run. sync_all_columns also removes columns that disappeared from the query, which is more correct but riskier if a column drop was accidental.

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

When to reach for incremental at all

Incremental models add real complexity — a full-refresh path to maintain, a unique key to get right, a lookback window to tune. That cost is worth paying once a table's full rebuild takes long enough to slow down your daily run, typically tens of millions of rows or more depending on warehouse size. Below that, a plain table materialization that rebuilds every time is simpler to reason about and has no risk of the incremental logic silently drifting from correct.

Wrapping up

Start every model as a plain table or view, and only convert to incremental once a full rebuild is measurably too slow or expensive. When you do, default to a merge strategy with a real unique_key and a lookback window wide enough to catch late-arriving data — the append-only default is a trap for anything but strictly immutable event data.