Incremental Models In-Depth

Analogy categoryAnalogies

Why Incremental Models Exist

A table materialization rebuilds the entire model from scratch on every dbt run, which is fine for small models but becomes expensive and slow on tables with billions of event rows. An incremental model solves this by persisting the table once, then on every subsequent run only processing and merging in the new or changed rows since the last run, guarded by the is_incremental() Jinja function. The first run (or a full-refresh run) builds the whole table; every run after that only touches the delta, which can turn a 40-minute nightly build into a 2-minute one on large event tables.

🏏

Cricket analogy: This is like a scorer who doesn't re-tally an entire season's statistics after every match — they only add the new match's runs and wickets to the existing season tally, which is far faster than recalculating from ball one of the season.

The is_incremental() Macro and Filtering

Inside an incremental model's SQL, you wrap a WHERE clause in {% if is_incremental() %} to filter the source down to only new rows on incremental runs — typically comparing an updated_at or event timestamp column against the max value already in {{ this }}, the model's own existing table. is_incremental() evaluates to false on the very first run (the table doesn't exist yet) and on any run with --full-refresh, so the model builds unfiltered in those cases, and true on normal subsequent runs, applying the filter. Getting this filter wrong — for example, filtering on a column that isn't monotonically increasing — is the most common source of silently missing data in incremental models.

🏏

Cricket analogy: This is like a stats system only pulling deliveries bowled after the timestamp of the last-recorded ball in the database, but building the full historical ball-by-ball log from scratch the very first time the system is set up.

sql

-- models/marts/fct_events.sql
{{
    config(
        materialized='incremental',
        unique_key='event_id',
        incremental_strategy='merge'
    )
}}

select
    event_id,
    user_id,
    event_name,
    occurred_at
from {{ ref('stg_app__events') }}

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

Incremental Strategies: merge, delete+insert, append

The incremental_strategy config controls how new rows are reconciled with the existing table. merge (the default on warehouses like Snowflake, BigQuery, and Databricks) uses the unique_key to update existing rows and insert new ones, which correctly handles late-arriving updates to records already in the table. delete+insert deletes any existing rows matching the incoming batch's unique_key values, then inserts the new batch, which is useful on warehouses without native MERGE support. append simply inserts new rows with no deduplication at all, appropriate only for strictly immutable, append-only event data where a row is never updated after it's written.

🏏

Cricket analogy: This is like a scorecard system where merge updates a batsman's row if he's given not out on review and adds new rows for new batsmen (merge), versus a simpler system that just appends every new ball bowled with no correction possible after the fact (append).

unique_key can be a list of columns, e.g. unique_key=['user_id', 'event_date'], when no single column is unique on its own. dbt will generate the merge/delete-insert logic against the combined key.

If you change a model's schema (add/remove columns) or its filtering logic in a way that historical data wouldn't reflect, run dbt run --full-refresh --select fct_events to rebuild it from scratch. Otherwise the incremental logic will only apply the new definition to new rows, leaving old rows with the stale schema or logic — a common source of subtle bugs.

Incremental models persist a table and only process new/changed rows on subsequent runs, guarded by is_incremental().
is_incremental() is false on the first run and on --full-refresh, so the model builds fully unfiltered in those cases.
The WHERE clause filter should use a monotonically increasing column like an updated_at timestamp compared against max({{ this }}).
incremental_strategy controls reconciliation: merge (update+insert via unique_key), delete+insert, or append (no dedup).
unique_key can be a composite list of columns when no single column is unique.
Use --full-refresh after schema or logic changes so historical rows reflect the new definition.
Incremental models trade some complexity for dramatically lower compute cost on large, frequently-updated tables.