---
name: dbt-incremental-model-strategy
source: https://app.decimal.ai/s/dbt-incremental-model-strategy@1/SKILL.md
source_sha256: 928be430362b
---

# 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.

2. **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.

3. **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.

4. **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.