How to Cut dbt Run Costs With Incremental Models
A dbt model materialised as table rebuilds completely on every run. On a 200-million-row table that’s a full scan and a full write, twice a day, whether or not anything changed. Snowflake and BigQuery bill for that scan.
Incremental models only process new or changed rows. The trade is real: you take on correctness risk around late data and deletes, in exchange for run times and bills that drop by an order of magnitude.
How do you convert a table model to incremental?
Three changes to the model file.
{{ config(
materialized='incremental',
unique_key='event_id',
incremental_strategy='merge',
on_schema_change='append_new_columns'
) }}
SELECT
e.event_id,
e.user_id,
e.event_type,
e.occurred_at
FROM {{ ref('stg_events') }} e
{% if is_incremental() %}
WHERE e.occurred_at >= (SELECT MAX(occurred_at) FROM {{ this }})
{% endif %}
The {% if is_incremental() %} block is the whole mechanism. On the first run it’s skipped and the full history loads. On subsequent runs dbt appends the filter, so the query reads only recent rows.
unique_key is what makes the merge idempotent. Two keys use unique_key=['user_id','event_date'] — but be careful, composite keys on BigQuery behave differently and merge requires the columns to be non-null.
on_schema_change='append_new_columns' is the guardrail people forget. Without it, a source adding a column fails the incremental run with a type mismatch, and the failure message names the model, not the column.
Which incremental strategy should you use?
dbt picks a default per adapter. Override it deliberately.
| Strategy | Warehouse | Behaviour |
|---|---|---|
append |
all | Inserts everything, no dedupe. Fastest. |
merge |
Snowflake, BigQuery, Postgres | Upsert on unique_key. Default. |
delete+insert |
Postgres, Redshift | Deletes matching keys, inserts new. Safer for wide tables. |
insert_overwrite |
BigQuery, Spark | Replaces whole partitions. Efficient at scale. |
append is correct only for immutable event streams. Use it on a table that gets updates, and you silently double your row count within a week.
insert_overwrite on BigQuery is the one worth learning if you have partitioned tables. It replaces only the partitions touched by the run, which is dramatically cheaper than merge on a 2 TB table — a merge there scans the whole table to find matching rows. The cost of that mistake shows up on the invoice, not in the logs.
On Redshift, delete+insert beats merge on wide tables because Redshift’s merge implementation is a delete followed by an insert anyway, with extra overhead.
How do you handle late-arriving data?
The MAX(occurred_at) filter above is the naive version, and it’s wrong the first time an event arrives three hours late. It’s already downstream of the filter and never gets picked up.
Two fixes, both worth knowing:
Lookback window. Subtract a buffer from the max:
WHERE e.occurred_at >= (SELECT MAX(occurred_at) - INTERVAL '3 days' FROM {{ this }})
Then the merge dedupes the overlap. Costs a bit more per run, eliminates most late-data loss. Size the window to your worst realistic lag — 3 days is generous for most event streams, 1 hour for well-instrumented ones.
Partition-level reprocessing. With insert_overwrite, reprocess the last N partitions each run. On BigQuery this is both cheaper than a lookback scan and simpler to reason about.
What neither fix handles: hard deletes at the source. dbt sees the row simply missing. Soft deletes (deleted_at) are the standard workaround, and the staging model should pass that column through faithfully.
How do you know the change actually saved money?
Measure before and after, on the same model.
- Run
dbt build --select model_nameon the full-refresh version and note the duration from the logs. - Note the warehouse bytes scanned. On BigQuery:
SELECT total_bytes_billed FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE job_type='QUERY' ORDER BY creation_time DESC LIMIT 20. On Snowflake:SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY. - Convert to incremental, run twice (the first run is a full build), and re-measure.
A typical conversion on a 100M-row event table goes from 90 seconds and 40 GB scanned to 6 seconds and 300 MB. The second number is what the invoice sees.
If the numbers didn’t move, look at the model’s dependencies. An incremental model downstream of a full-refresh table still pays for the upstream scan on every run. Optimising one model in a chain of six rarely shows up.
What breaks in incremental models?
dbt test runs against your dev schema, not production. Tests that pass in dev can fail in prod if the data shapes differ. Run dbt build against a prod clone before releasing.
Skipping a day. If the scheduler misses a run, MAX(occurred_at) still points at the last successful load, so the next run just picks up more rows. This actually self-heals — unless you also have a window-based filter assuming contiguity. Prefer the MAX anchor.
Full refresh becomes dangerous. After six months of incremental, dbt run --full-refresh reads 200 million rows and may time out. Rehearse it quarterly; it’s the crisis recovery path and nobody tests it until they need it.
Schema drift. A renamed source column breaks every incremental run from that point. Alert on failures rather than checking dashboards.
When is incremental the wrong choice?
For any model under about a million rows. The overhead of unique_key merges and the reasoning burden aren’t worth it — a full rebuild of a 500k-row table takes 4 seconds.
Also wrong for models with logic that depends on the whole history, like a running total or a first-purchase flag. Those need the full table or a proper window function over a bounded partition. Bolting an incremental filter onto them produces numbers that are subtly wrong for months before anyone notices.
Quick Summary
materialized='incremental'plus anis_incremental()filter reprocesses only recent rows.- Always set
unique_keyand usemergeunless the stream is immutable. - Add a 1-3 day lookback to catch late-arriving events;
MAX()alone loses them. insert_overwriteon partitioned BigQuery tables is far cheaper than merge at scale.- Verify savings with bytes-billed or query-history views, not just wall clock.
- Under a million rows, full refresh is simpler and fast enough — don’t bother.
