Performance, Cost Optimization, and Scalability
Performance questions test whether you can make a dbt project fast and cheap at scale - reducing bytes scanned, matching materializations to access patterns, sizing compute correctly, and avoiding needless rebuilds - without breaking correctness.
Scan reduction comes first
Most warehouse cost is bytes read. Filter on partition columns as early as possible, ensure incremental_predicates reach the warehouse, cluster or sort on the columns used in joins and filters, and avoid select * in staging so column pruning works. Aggregate in intermediate models instead of repeatedly joining full-grain tables in several marts.
Materialization and incremental strategy
Match cost to access pattern: tables for frequently queried marts, incremental merge or insert_overwrite for large facts, views for cheap staging, materialized views where the platform manages refresh. Small tables should not be incrementally merged - a full rebuild is cheaper. microbatch suits many small time batches with fewer warehouse round trips.
Compute sizing
Threads parallelize models; warehouse size parallelizes inside a query. Raising threads on an undersized warehouse produces queueing, not speed. Give long-running models their own warehouse so they do not block fast ones, keep auto-suspend short, and separate loading from transforming.
Scaling the run
state:modified+ limits pull-request runs to changed work plus descendants. dbt clone creates zero-copy environments on some platforms. Nightly full refreshes of the whole project are rarely necessary: target them with tags and accept longer runtimes only where correctness demands it. Tests cost money too - constrain them with limit and where.
Worked example
A daily mart joins five tables, scans 2 TB, and takes 40 minutes. Diagnosis: no partition filter on the 2 TB fact, threads set to 16 on a medium warehouse, and a nightly --full-refresh. Fix: filter the fact to the last three days inside the is_incremental() branch, convert to incremental merge with incremental_predicates, cluster the fact on order_date, drop threads to 8, and replace the nightly full refresh with a weekly tagged rebuild. Scan falls to tens of GB and runtime to minutes.
Traps
- Adding threads to fix a slow warehouse, which only deepens the queue.
- A daily
--full-refreshscheduled "for safety". - Lookback windows so wide that incremental models scan the entire table.
- Full-table tests running on every schedule.
- Rebuilding unchanged models because state selection was never configured.