dbt Incremental Models: An Advanced Guide to Getting Them Right

dbt incremental models are one of the most powerful features in modern data transformation stacks. They let you process only new or changed rows rather than rebuilding an entire table on every run, which dramatically reduces warehouse compute costs and transformation time as data volumes grow. They are also one of the features most commonly implemented incorrectly. This guide covers advanced patterns, the subtle behaviors that trip teams up, and how to make incremental models work correctly at scale.

The Basics (Quickly)

An incremental model in dbt is a SQL model with materialized='incremental'. On the first run, dbt builds the full table. On subsequent runs, it processes only rows that pass the is_incremental() filter — typically rows newer than the maximum timestamp or ID already in the destination table.

-- models/orders_transformed.sql
{{ config(materialized='incremental', unique_key='order_id') }}
 
SELECT
    order_id,
    customer_id,
    total_amount,
    created_at,
    updated_at
FROM {{ source('app_db', 'orders') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

This is the template. The advanced concerns start immediately after getting this to compile.

Choosing the Right unique_key

The unique_key tells dbt which column(s) define row uniqueness. When a new run processes a row whose unique_key already exists in the destination, dbt updates it (merge/upsert) rather than inserting a duplicate.

Single column keys work when a single column unambiguously identifies a row. Order IDs, user IDs, event GUIDs.

Composite keys are needed when uniqueness is defined across multiple columns — for example, (user_id, event_date) for a daily user metrics table. Pass a list: unique_key=['user_id', 'event_date'].

What happens without a unique_key: If you omit unique_key, dbt uses an APPEND strategy — it inserts all new rows without checking for duplicates. Retries after failures will create duplicate rows. Only use append-only models when the source data genuinely has no updates and duplicates are impossible.

Understanding on_schema_change

on_schema_change controls what happens when the SQL model selects new columns that do not yet exist in the destination table, or when columns are removed.

• ignore (default) — new columns in the model are silently excluded from the incremental run. Old columns not in the model are retained in the destination table. This causes silent data loss and schema drift.
• fail — the incremental run fails if any schema mismatch is detected. Forces you to handle schema changes explicitly.
• append_new_columns — new columns are added to the destination table. Removed columns are retained with nulls for new rows.
• sync_all_columns — adds new columns and removes deleted columns, matching the destination schema to the model output exactly.

Recommended default: fail. You want to know when your model's schema changes, not have changes silently dropped. Set up a process for handling schema changes explicitly: run dbt run --full-refresh when a schema change is intentional.

The High-Water Mark Filter and Its Failure Modes

The standard incremental filter WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) has several failure modes worth understanding.

Clock skew and late-arriving rows: If a row is written to the source with a timestamp a few seconds before the query runs but the pipeline's MAX(updated_at) was calculated at that same moment, the row can be missed. Use a lookback buffer: WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}) - INTERVAL '5 minutes'. Pair this with a unique_key to handle the resulting duplicates via merge.

NULL updated_at: If updated_at is null for some rows, MAX(updated_at) will exclude them in the subquery comparison. Explicitly handle nulls: COALESCE(updated_at, created_at) or filter out null rows from the incremental model.

Non-monotonic timestamps: If source rows can be updated with past timestamps (corrective updates written with original event times), the high-water mark pattern will miss them. Consider CDC or periodic full reconciliation for these sources.

Merge Strategies by Warehouse

dbt abstracts merge behavior, but the underlying SQL varies by warehouse.

• Snowflake and Redshift: dbt generates a MERGE statement using the unique_key to match existing rows and update or insert accordingly.
• BigQuery: uses MERGE for standard incremental. BigQuery also supports insert_overwrite (partition replacement) as a merge strategy — useful for large partitioned tables where a full merge across the table is expensive.
• DuckDB: supports MERGE as of recent versions; older versions used delete-then-insert.

For very large tables (hundreds of billions of rows), a full-table MERGE can be slow and expensive even with a unique_key. In this case, use partition-based replacement: filter the incremental model to the current processing partition, and use insert_overwrite or equivalent to replace only that partition.

Microbatch Incremental (dbt 1.9+)

dbt 1.9 introduced the microbatch incremental strategy as a first-class concept. Microbatch models define an event_time column and a batch size (hourly, daily). dbt handles partition management automatically — on each run, it processes only the most recent batch window and replaces that partition.

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='event_timestamp',
    batch_size='day',
    lookback=3
) }}

The lookback parameter tells dbt to reprocess the last three days on each run, handling late-arriving data without manual high-water mark management. This is a significant improvement over manually managing lookback windows.

When to use microbatch: For event-based data with a reliable event timestamp, where late data arrival within a known window is expected. Not appropriate for tables with frequent updates to historical rows.

Full Refresh Behavior and When to Use It

Running dbt run --full-refresh drops and rebuilds the incremental table from scratch. This is necessary when:

• The model schema has changed and on_schema_change='fail' prevented the incremental run
• Historical data needs to be reprocessed (transformation logic changed, backfill needed)
• The incremental state has become inconsistent due to a pipeline failure that corrupted the high-water mark

Build a process around full refreshes: they are more expensive (full table rebuild) and should not be run ad hoc. In CI, run models with --full-refresh against a staging environment before deploying logic changes to production.

Testing Incremental Models

Incremental models are harder to test than full-refresh models because their behavior depends on the existing state of the destination table. A model that works correctly on first run can behave incorrectly on subsequent runs.

Test specifically:

• Idempotency: run the model twice and assert the destination table is identical after both runs (no duplicates, no row count change).
• Late data handling: insert a row with a past timestamp into the source and confirm it is picked up on the next run (if your lookback window covers it).
• Schema change behavior: add a column to the source and confirm your on_schema_change setting handles it as expected.

Use dbt's --select to run incremental models against a staging environment with representative data before promoting changes to production.

Building dbt transformation layers that are correct, efficient, and maintainable at scale requires both SQL knowledge and data engineering judgment. If your team is scaling a dbt project and hitting the limits of standard incremental patterns, Clixo's data engineering team works with product teams on transformation architecture and data platform design.