dbt Incremental Model Strategy

What this does

Turns "make this dbt model incremental" into a model that is idempotent — running it twice produces the same table, never a doubled one. The trap: a model marked materialized='incremental' still re-scans and re-inserts rows on every run unless you gate the scan and tell dbt how to reconcile the new rows against what is already there. Miss either and you either double-count on rerun or silently drop rows that arrive late. This skill encodes the four decisions that make an incremental model correct.

When to use

Any time you author or repair a dbt model that materializes incrementally over an append-heavy source — event streams, click logs, order lines, transaction facts — and the goal is that scheduled reruns (and backfills) don't corrupt the row counts.

The four decisions (apply ALL of them — a naive append gets every one wrong)

1. Declare a unique_key. Without it, an incremental run appends every selected source row,

so any row that was already loaded on a prior run lands a second time → duplicates and inflated counts. Set unique_key to the column (or list of columns) that uniquely identifies a row in the destination grain. This is what lets dbt replace-on-match instead of blindly appending. If the grain is a composite (e.g. order_id + line_no), pass the list, not a single column.

1. Gate the source scan with is_incremental() AND a lookback window. Wrap the incremental

filter in an {% if is_incremental() %} block so a full run (first build or --full-refresh) scans everything and an incremental run scans only recent rows. The naive filter is a strict watermark — where event_at > (select max(event_at) from {{ this }}) — which drops late-arriving rows: any event whose event_at is older than the current max but that landed in the source after the last run is never picked up. Use an overlapping lookback window instead: sql {% if is_incremental() %} where event_at >= (select coalesce(max(event_at), '1900-01-01') from {{ this }})

• interval '3 days'

{% endif %} The window re-scans a bounded tail so late rows are caught. Reprocessing that tail is only safe because decision 1 (unique_key) makes the re-inserted rows replace rather than duplicate — the two decisions are a pair. Size the window to the worst-case source lateness, not to convenience.

1. Choose incremental_strategy on purpose — merge vs delete+insert.

• merge (default on Snowflake/BigQuery/Databricks): needs a unique_key; issues a single

MERGE that updates matched rows and inserts new ones. Prefer it when the adapter supports it and you have a clean unique key. Add merge_update_columns if only some columns should be overwritten on a match.

• delete+insert: deletes the destination rows whose key is in the incoming batch, then

inserts the batch. Choose it when the adapter has no efficient MERGE, when the key is composite and MERGE is awkward, or when you want the whole matched partition rebuilt. On partition-oriented warehouses, insert_overwrite by partition is the same idea at partition grain. State which one you picked and why — don't leave it to the adapter default and hope.

1. Name the full-refresh trigger. An incremental model NEVER back-corrects history on its own:

if you change the transformation logic, past rows keep their old values until the table is rebuilt from scratch. Rebuild with dbt run --full-refresh --select my_model (or set config(full_refresh=true) to force it, false to protect a huge table from an accidental one). Call this out whenever the SELECT logic changes, and flag models where a full refresh is prohibitively expensive so backfills are planned, not stumbled into.

Skeleton

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

select event_id, user_id, event_at, payload
from {{ source('app', 'events') }}
{% if is_incremental() %}
where event_at >= (select coalesce(max(event_at), '1900-01-01') from {{ this }})
                  - interval '3 days'
{% endif %}

Read it as: full build scans all history; every scheduled run scans a 3-day tail and MERGEs it on event_id, so late rows are captured and reruns are idempotent. Drop any one of the four decisions and this breaks.

Why not just append

materialized='incremental' with no unique_key and a bare is_incremental() guard is the common first draft. It looks incremental and passes a first run. It fails the moment a run overlaps previously-loaded data (a retry, an overlapping schedule, a late row inside the window) — those rows append a second time. Idempotency is the whole point of the pattern; the four decisions above are what deliver it.