dbt Performance Tuning

As a dbt project grows, run times increase. A project that once finished in five minutes may take an hour after adding hundreds of models and millions of rows. Performance tuning is the process of identifying slow models, understanding why they are slow, and applying the right fixes to make runs fast again.

Where Time Goes in a dbt Run

Time is spent in three places:

1. Warehouse compute time
   The SQL query runs slowly inside the warehouse.
   Fix: optimise the SQL, change materialisation, add clustering.

2. Model count
   Too many models rebuild from scratch every run.
   Fix: use incremental materialisation or ephemeral for intermediates.

3. Sequential bottlenecks
   A slow parent model blocks all children from starting.
   Fix: break the slow model into smaller parallel models or optimise the parent.

Finding Slow Models

From the Terminal

dbt prints run time per model at the end of every run. Look for models taking more than a few minutes:

$ dbt run

1 of 12 OK  stg_orders ...... [OK in 0.3s]
2 of 12 OK  stg_customers ... [OK in 0.2s]
3 of 12 OK  int_orders_joined [OK in 47.3s]   ← slow
4 of 12 OK  fct_orders ...... [OK in 8.1s]

From dbt Cloud Run History

The Run History tab in dbt Cloud shows a visual timeline of model run durations. Sort by duration to immediately surface the slowest models.

From run_results.json

cat target/run_results.json | python3 -c "
import json, sys
results = json.load(sys.stdin)['results']
slow = sorted(results, key=lambda x: x['execution_time'], reverse=True)[:5]
for r in slow:
    print(f\"{r['execution_time']:.1f}s  {r['unique_id']}\")
"

Fix 1: Switch to Incremental Materialisation

The most impactful change for large tables. Instead of rebuilding millions of rows every run, process only new rows:

Before (full refresh every run, takes 45 minutes):
  {{ config(materialized='table') }}
  select * from {{ source('events', 'raw_events') }}

After (incremental, takes 30 seconds after first run):
  {{ config(
      materialized='incremental',
      unique_key='event_id'
  ) }}
  select * from {{ source('events', 'raw_events') }}
  {% if is_incremental() %}
    where event_at > (select max(event_at) from {{ this }})
  {% endif %}

Fix 2: Use Ephemeral for Intermediate Models

Intermediate models materialised as views re-execute their SQL every time a downstream model queries them. Using ephemeral inlines the logic as a CTE, letting the warehouse optimise the whole query at once:

Before (view: re-executed per query):
  {{ config(materialized='view') }}

After (ephemeral: inlined as CTE in the one model that uses it):
  {{ config(materialized='ephemeral') }}

Fix 3: Increase Thread Count

Threads control how many models run in parallel. More threads = more concurrent warehouse queries:

# profiles.yml
dev:
  threads: 4    ← run 4 models at once

prod:
  threads: 8    ← run 8 models at once

Increasing threads helps when many independent models exist at the same DAG level. It does not help for sequential chains where each model waits for its parent.

Fix 4: Warehouse-Specific Table Optimisations

Snowflake: Clustering Keys

{{ config(
    materialized='table',
    cluster_by=['order_date', 'region']
) }}

BigQuery: Partitioning and Clustering

{{ config(
    materialized='table',
    partition_by={
        "field": "order_date",
        "data_type": "date"
    },
    cluster_by=['region', 'status']
) }}

Redshift / Postgres: Sort Keys and Distribution

{{ config(
    materialized='table',
    sort=['order_date'],
    dist='customer_id'
) }}

Fix 5: Optimise Slow SQL

Open the compiled SQL for the slow model and run it with EXPLAIN ANALYZE in your warehouse. Look for:

Problem                           Fix
-------------------------------   -----
Full table scan on large table    Add a WHERE clause to filter early
Repeated subqueries               Replace with CTEs or ref() to a materialised model
Cartesian join (missing ON)       Add the missing join condition
DISTINCT on large result set      Push DISTINCT into a subquery or use GROUP BY
Unoptimised window function       Partition by a date column to reduce sort size

Fix 6: Selector-Based Runs

In development, only run the models you are actively changing:

# Run one model and its children only
dbt run --select stg_orders+

# Run only changed models (requires state comparison)
dbt run --select state:modified+

Fix 7: Defer Unchanged Models (CI)

In CI, use --defer so unchanged models read from the production schema instead of rebuilding:

dbt build --select state:modified+ --defer --state ./prod-artifacts

Performance Tuning Checklist

[ ] Identify slowest models with run_results.json or dbt Cloud
[ ] Switch large full-refresh tables to incremental
[ ] Use ephemeral for intermediate models used by only one downstream model
[ ] Increase thread count in profiles.yml
[ ] Add clustering / partitioning to large mart tables
[ ] Review slow SQL with EXPLAIN ANALYZE
[ ] Use --select selectors in development to avoid rebuilding everything
[ ] Use --defer in CI to skip unchanged models