Home / Data Platform / dbt & Quality

Data PlatformHow-toIntermediate

Optimization: dbt run time and warehouse cost - incremental models, materialisations and slim CI

dbt projects get slower and more expensive as they grow because everything is rebuilt as tables on every run and CI rebuilds the world. Find the slowest models, make big ones incremental with the right strategy, pick materialisations deliberately, use slim CI with state comparison and tune threads and warehouse sizes.

Deepak Bhardwaj3 min read

Applies todbt Core 1.7+ and dbt Cloud on Snowflake, Databricks, BigQuery, Redshift, Fabric/Synapse; run_results.json and manifest.json artifacts

1. Find the slow models

jq -r '.results[] | [.execution_time, .unique_id] | @tsv' target/run_results.json | sort -rn | head -20

Also look at warehouse query history for dbt queries (query tags help: +query_tag: dbt_{{ model.name }} on Snowflake). Usually 10% of models take 80% of time and cost.

2. Materialisations on purpose

Materialisation Use for
view Light transformations, rarely queried, small upstream
table Small/medium models queried often
incremental Large fact tables, append-mostly or with reliable change keys
ephemeral Simple CTE helpers (avoid deep nesting, which bloats SQL)
materialized_view / dynamic tables Where the platform maintains it efficiently and freshness rules fit

A common default: staging as views, marts as tables, big facts incremental.

3. Incremental models with the right strategy

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

select * from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
{% endif %}
Strategy When
append Immutable events, no updates
merge Updates by key (watch merge cost on huge tables)
delete+insert Platforms where merge is slow; replace matching keys
insert_overwrite Partitioned tables: replace changed partitions only
microbatch (dbt 1.9+) Time-series facts processed in time batches, easy backfills

Add a lookback window (e.g. updated_at > max - interval '3 days') for late-arriving data, and protect history from --full-refresh on critical models (full_refresh: false; see the full-refresh Sev1 post).

4. Slim CI

Don't rebuild the whole project for every pull request:

dbt build --select state:modified+ --defer --state ./prod-artifacts --target ci
  • state:modified+: changed models and everything downstream.
  • --defer: unchanged upstream models are read from production instead of rebuilt.
  • Keep production manifest.json from the last deploy as the state artifact.
  • CI schema per PR, dropped afterwards.

5. Tests that don't cost a fortune

  • Tests on big tables: use where configs to test recent data only (config(where="order_date >= current_date - 7")).
  • Prefer unique/not_null on keys of incremental models, run selectively.
  • Store failures only where you investigate them.

6. Threads and compute

  • threads: enough to run independent models in parallel (often 8–16), limited by warehouse concurrency.
  • Separate warehouse/cluster for dbt, sized by the duration you need (see the Snowflake warehouse post).
  • Schedule heavy rebuilds (weekly full refresh of rebuildable models) off-peak.

7. Model hygiene

  • Remove unused models (no downstream refs, no exposures, no queries in 90 days).
  • Avoid select * in marts; select only needed columns.
  • Push filters early (staging) to shrink data before joins.

Tip

Track total run time and warehouse credits per dbt job over time. Every slow model you fix shows up in that chart, and a regression after a PR is easy to spot.

Checklist

  • Slowest models identified from run_results
  • Materialisations reviewed; big facts incremental
  • Correct incremental strategy with lookback and full-refresh protection
  • Slim CI with state:modified+ and defer
  • Tests scoped on big tables
  • Threads, compute and unused models reviewed

Related fixes