How to Cut dbt Warehouse Costs With Incremental Models
Incremental models in dbt rebuild only new or changed rows instead of the whole table, and on a large fact table that routinely takes a build from 20 minutes to under 60 seconds. The trade-off is correctness: a wrong unique_key or a missed backfill produces quietly wrong numbers that no test will catch on its own.
This is the part of dbt that saves the most money and breaks the most dashboards. Both facts come from the same source — incremental logic is invisible at query time.
What does an incremental model actually do?
dbt runs your model as a normal SELECT, but wraps it in a filter. On the first run it creates the table. On later runs it selects only rows the filter matches — typically where updated_at > (select max(updated_at) from {{ this }}) — and merges them into the existing table.
Three strategies:
- append — inserts new rows only. Cheapest. Fails silently on updates.
- merge — upserts on
unique_key. The default choice for anything with updates. - delete+insert — removes matching partitions and reinserts. Good for batch-loaded partitions.
How do you configure one correctly?
{{ config(
materialized='incremental',
unique_key='event_id',
incremental_strategy='merge',
on_schema_change='append_new_columns'
) }}
select event_id, user_id, event_type, occurred_at
from {{ ref('stg_events') }}
{% if is_incremental() %}
where occurred_at > (select max(occurred_at) from {{ this }})
{% endif %}
Four details in that block decide whether the model stays correct:
- The
is_incremental()guard is required. Without it, the first run filters on a table that does not exist yet and fails. unique_keymust actually be unique. A duplicate key on merge means the last row wins, silently.on_schema_changeneeds a value. The defaultignoremeans upstream column additions never reach the target.append_new_columnsis the safe setting.- Filter on an immutable timestamp.
occurred_atfrom the source event never changes.updated_atchanges whenever a record is touched, which is what you want if late updates are possible.
Why do incremental models go wrong quietly?
The failure mode is data arriving out of order. Late events with timestamps older than your watermark get excluded by the filter forever. Nobody notices until a dashboard shows a gap three weeks back.
Common causes: mobile clients that batch-upload, upstream extracts with a multi-hour lag, and timezone mismatches putting rows slightly behind max(occurred_at).
Two defenses. Add a lookback window to the filter:
where occurred_at > (select max(occurred_at) from {{ this }}) - interval '3 days'
That re-processes three days every run. Cheap, and it absorbs most lag. Second defense: a dbt test that compares row counts between the incremental table and its source over a trailing window. A row-count test catches gaps a uniqueness test never will.
How much do incremental models actually save?
Depends entirely on the ratio of new to total rows. Realistic numbers from a mid-size warehouse:
| Scenario | Table rows | New per run | Full rebuild | Incremental |
|---|---|---|---|---|
| Events, hourly | 400M | 2M | 22 min | 45 s |
| Orders, daily | 12M | 60k | 6 min | 20 s |
| Sessions, hourly | 90M | 15M | 14 min | 8 min |
The third row is the lesson. When a run adds 15% of the table, incremental only saves about 40% — you still scan most of the data. Incremental helps when new rows are a small fraction of the total, not automatically.
When should you not use incremental models?
- Small dimension tables. A 5,000-row customer table rebuilds in under a second. Incremental adds risk for no gain.
- Tables where full history must be recomputable. Incremental state drifts. If an auditor asks you to reproduce last quarter’s numbers from source, a full rebuild is the only honest answer.
- Heavy logic that depends on the whole table. Ranking, window functions over all history, and percentiles recalculate differently on a partial input.
- Frequent schema change. Column adds and type changes on incremental models are where
on_schema_change='ignore'bites hardest.
Keep one full-refresh path available: dbt run --full-refresh --select marts.fct_events should always work. If it does not, the incremental config has rotted.
What does the cost picture look like overall?
Warehouse compute, not dbt, is the bill. Snowflake’s smallest warehouse costs about $2 per credit, and an XS warehouse burns one credit per hour it runs. A 20-minute rebuild every hour means the warehouse never sleeps — that is roughly $700/month in compute before any storage or BI.
Selective incremental builds plus --select state:modified+ in CI typically cuts that 50–70% for teams at this scale, mostly by avoiding unnecessary rebuilds rather than by making each build cheaper.
Good fit: fact tables with append-mostly data, event streams, and any model over a few million rows.
Poor fit: dimensions under 100k rows, models with whole-table window logic, and compliance pipelines that need reproducible full-history recomputation.
Quick Summary
- Incremental models rebuild only matching rows; real savings on append-mostly fact tables.
unique_key,is_incremental()guard, andon_schema_changeare the three settings that decide correctness.- Add a lookback window (2–3 days) to survive late-arriving data.
- Savings collapse when a run adds more than ~10% new rows.
- Always keep
--full-refreshworking; if it fails, the model config has drifted.
