How to Cut dbt Run Costs With Incremental Models

October 28, 2026

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.

  1. Run dbt build --select model_name on the full-refresh version and note the duration from the logs.
  2. 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.
  3. 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 an is_incremental() filter reprocesses only recent rows.
  • Always set unique_key and use merge unless the stream is immutable.
  • Add a 1-3 day lookback to catch late-arriving events; MAX() alone loses them.
  • insert_overwrite on 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.
Tags: