dbt Project Performance Tuning: Threads, Build Order, Materialization Choice, defer

dbt performance is the difference between a 45-minute CI pipeline that blocks every deploy and a 4-minute slim build that lets three engineers ship before lunch — and the gap between the two is almost entirely a choice about dbt threads, dbt build order, dbt materialization, and dbt defer, not about the SQL inside any single model. Warehouses have gotten faster every year; dbt projects have gotten slower every year, because teams keep adding models faster than they retire them, and the default materialized='view' that felt fine at 40 models becomes a full-table rebuild bomb at 400 models. A 15-minute morning build isn't a warehouse problem — it's a materialization + build-order problem that never got the senior-DE attention it needed.

This guide is the senior-analytics-engineer walkthrough you wished existed the first time an interviewer asked "walk me through how you'd cut a 45-minute dbt project to under 10 minutes" or "when do you reach for dbt incremental strategy merge versus insert_overwrite?" or "what's a dbt slim ci pipeline and how does dbt state modified compose with dbt defer?" It covers dbt threads and warehouse concurrency (the ceiling is the DAG, not the threads count), the critical-path build order and why parallelism cannot beat it, the four-axis materialization decision matrix, dbt incremental strategy choices (append, merge, delete+insert, insert_overwrite) with unique_key and on_schema_change, and the defer --state target/ pattern that plus state:modified+ selection collapses a full-project CI into a 3-model rebuild against the production manifest. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the tuning axis with the optimization practice library →.

---

On this page

• Why dbt performance is a senior-DE topic in 2026
• Threads and DAG parallelism
• Materialization strategy — table / view / incremental / ephemeral
• Incremental strategies and micro-batches
• defer, slim CI, and the fusion engine
• Cheat sheet — dbt performance recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why dbt performance is a senior-DE topic in 2026

A 45-minute dbt build blocks CI, blocks deploys, and drains the goodwill you'd rather spend on data-quality work

The one-sentence invariant: once your dbt run crosses the ten-minute mark, every CI job stops running per PR, every incident becomes a "wait for the build" incident, and every new model gets added by an engineer who has stopped watching the output — which is exactly when the project starts accumulating expensive full-refresh models that will haunt you for the next twelve months. A modern warehouse can chew through 40 gigabytes of joined data in seconds; a dbt project spends most of its wall-clock waiting for models that a senior would have marked incremental, an intermediate CTE that could have been ephemeral, or a linear reference chain that a small refactor could have parallelised. Performance tuning a dbt project is not a warehouse problem — it is a graph problem, a materialization problem, and a dbt defer discipline problem. The engineer who understands those three lays down a 4-minute CI on a project that used to take 45 minutes and never looks back.

The four axes interviewers actually probe.

• Threads. How many models run in parallel, capped by threads in profiles.yml, capped again by warehouse concurrency (Snowflake warehouse size, BigQuery slots, Redshift WLM queues). More threads only helps until the DAG or the warehouse becomes the bottleneck.
• Build order and the critical path. The DAG has a longest chain — the critical path. Wall-clock time is bounded from below by the sum of runtimes on that chain. Parallelism speeds everything except the critical path; the only way to beat the wall-clock is to shorten the chain (split a big model, denormalise a mid-tier ref, refactor a linear stack into a fanout).
• Materialization. The single biggest lever you own. table rebuilds every run and pays the whole cost upfront; view builds instantly but pushes the cost to every reader; incremental rebuilds only new rows for a fraction of the full-table cost; ephemeral inlines the SQL as a CTE and never materialises anything. Picking the right materialization per model routinely cuts wall-clock by 5–10×.
• defer and state selection. dbt run --defer --state target/ lets a CI run reference production artifacts for any unchanged upstream. Combined with --select state:modified+, CI builds only the models that actually changed on this branch — a 40-minute full build becomes a 4-minute slim build.

Why "just add more threads" is the wrong answer.

• The DAG is the ceiling. A linear chain of 10 models, each taking 3 minutes, takes 30 minutes on 1 thread and on 32 threads. Parallelism cannot compress a chain that a thread has to walk end-to-end.
• Warehouse concurrency is the second ceiling. A small Snowflake warehouse runs 8 concurrent queries by default. Setting threads: 32 in dbt just puts 24 queries in the warehouse queue where they wait for a slot — the wall-clock is unchanged, but the warehouse metadata gets flooded.
• Credit cost scales with size, not thread count. Cranking warehouse size to X-Large so that 32 threads all run in parallel does speed the run — and quadruples the credit spend for one dbt build. A senior answer trades credits for wall-clock explicitly, not accidentally.
• The practical ceiling. Most production dbt projects on Snowflake sit at threads: 8 on a Medium (16 slots) or threads: 16 on a Large (32 slots). Beyond that, you're paying warehouse credits for queue depth, not for parallelism.

What a good dbt run actually looks like.

• Model roles. Staging models (view or ephemeral), intermediate models (ephemeral or view), fact/dimension marts (table or incremental), reporting layers (view for freshness, table for cost).
• Ratio. A healthy 100-model project might be 40 views (staging), 30 ephemerals (intermediates), 20 incrementals (facts), and 10 tables (dims). The 20 incrementals are the models that would otherwise dominate the build.
• Runtime distribution. After tuning, the top three models by runtime are the incrementals — that is the healthy shape. If the top three are views or full_refresh tables, you've left multiples on the table.
• CI shape. Every PR runs dbt build --select state:modified+ --defer --state target/prod-manifest. Median PR touches 3 models; median CI runtime is under 5 minutes.

What interviewers listen for.

• Do you say "the DAG is the ceiling" or "the critical path bounds wall-clock" in the first breath when asked about parallelism? — senior signal.
• Do you frame materialization as "the biggest speed lever" rather than "a style choice"? — senior signal.
• Do you mention defer + state:modified as the CI answer without prompting? — required answer.
• Do you push back on "just crank the warehouse size" with the credit-cost argument? — required answer.

Worked example — the 45-minute build that shouldn't be 45 minutes

Detailed explanation. A team owns a dbt project with 220 models. Morning dbt run takes 45 minutes. CI runs the same 45-minute build on every PR. The team's default reflex is "give us a bigger warehouse." A senior engineer walks the numbers before touching the warehouse and finds three fixable causes: (1) every mart is materialized='table' with no incrementalisation, (2) 40 staging models are table when they could be view or ephemeral, (3) CI does a full build with no --defer. Adding warehouse size fixes none of these; changing materializations and adding defer fixes all three.

• The symptom. 45-minute dbt run on Medium Snowflake warehouse; 45-minute CI on every PR.
• The naive fix. Bump the warehouse to X-Large. Cost 8× per credit-hour; wall-clock drops maybe 40%.
• The real bug. 60 materialized='table' models where 40 should be view and 20 should be incremental. CI has no --defer.
• The senior fix. Retag materializations, add defer to CI, keep the Medium warehouse.

Question. Given the model inventory in the input table, propose the target materialization mix and the CI invocation. Quantify the expected wall-clock drop before you touch warehouse size.

Input.

Model layer	Count	Current materialization	Median runtime per model	Total runtime today
Staging	80	view	2 s	160 s
Intermediate	40	table	20 s	800 s
Marts (facts)	20	table (full refresh)	90 s	1800 s
Marts (dims)	30	table	15 s	450 s
Reporting	50	view	1 s	50 s
Total	220	—	—	~2700 s ≈ 45 min (serial equivalent)

Code.

# dbt_project.yml — set the layer-level defaults
models:
  my_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral    # inline as CTEs, no artefact
    marts:
      dims:
        +materialized: table
      facts:
        +materialized: incremental
        +incremental_strategy: merge
        +unique_key: event_id
        +on_schema_change: append_new_columns
    reporting:
      +materialized: view

# CI invocation — slim, defer to prod artifacts
dbt build \
  --select state:modified+ \
  --defer \
  --state ./ci/prod-manifest \
  --threads 8 \
  --target ci

Step-by-step explanation.

1. Staging stays as view — the whole point of staging is a thin projection that lives in the warehouse's query cache. Rebuilding staging as a table is pure waste on any project where the raw source has good clustering.
2. Intermediate models flip from table to ephemeral. An ephemeral model does not create a warehouse object — dbt inlines its SQL as a CTE in every downstream model that references it. You lose the ability to select * from intermediate.my_model in the UI, but you save 40 × 20 s = 800 seconds of full-refresh rebuild every run.
3. Facts flip from table (full refresh) to incremental with merge on a unique_key. A 90-second full rebuild of a 500M-row fact becomes a 6-second merge of the day's 200k new rows. Twenty facts × 84 s saved = 1680 seconds recovered.
4. Dims stay as table — dimensions are small and re-derive cleanly from staging every run. incremental on a 100k-row dim is over-engineering.
5. The CI invocation adds --defer --state ./ci/prod-manifest. The manifest is downloaded from the last successful production run. state:modified+ selects only the models changed on this branch plus their downstream lineage; every unchanged upstream reference resolves to the production table via defer. A PR touching 3 models rebuilds those 3, not the 220.

Output.

Change	Before	After	Wall-clock saved
Intermediate: table → ephemeral	800 s	0 s (inlined)	800 s
Facts: table → incremental merge	1800 s	~120 s (incrementals)	1680 s
Staging + reporting + dims	660 s	660 s	0 s
Serial total (morning run)	2700 s	780 s	1920 s (71% cut)
CI (3-model PR, with defer)	2700 s	~90 s	2610 s (96% cut)

Rule of thumb. Never raise warehouse size before you audit materializations and add defer. The materialization + defer refactor is a one-week project that routinely cuts 60–90% of wall-clock — a warehouse bump costs 4× the credits for a 40% wall-clock cut on the same load. Do the refactor first; the warehouse bump is what you do if the refactor doesn't close the gap.

Worked example — the credit budget that nobody's watching

Detailed explanation. A finance partner walks into the data team stand-up: "our Snowflake bill jumped from $18k to $47k last month, why?" The senior engineer opens the query history and finds 62 full_refresh runs of a 4TB fact model (someone put it in a cron), a view on top of an unfiltered 800M-row table that gets scanned by five downstream views, and a nightly dbt run --full-refresh that a junior added last quarter to "make sure things are fresh." None of these are dbt performance bugs in the sense of slow wall-clock — they are dbt performance bugs in the sense of cost. The four axes tune both.

• The full-refresh cron. Someone added dbt run --full-refresh to a nightly job to "keep data clean." That command rebuilds every incremental model as if it were empty — 4TB scan × 30 days.
• The unfiltered view. A staging view has no filter clause; five downstream views each select * from it. Every dashboard query fans out into a 4-billion-row plan.
• The credit spike. 62 full-refresh × 4TB = 248TB of scan on an X-Large warehouse. At ~$5/credit and 16 credits/hour, this is thousands of dollars a week.

Question. Diagnose the credit spike, propose the four config fixes that solve it, and estimate the monthly saving.

Input.

Model	Materialization	Rows	Runs/month	Data scanned/run
fct_events	incremental	4B (500M/day new)	60 (2×/day, 1 full-refresh)	4 TB (full) / 40 GB (incr)
stg_events	view	4B	0 (no materialisation)	4 TB per downstream reader
int_events_enriched	table	4B	30	4 TB
Downstream views (5)	view	—	500 dashboard hits/day	4 TB each hit

Code.

# dbt_project.yml — fix each anti-pattern
models:
  my_project:
    staging:
      stg_events:
        +materialized: view
        # add an explicit filter on the SQL to stop full 4TB scans
    intermediate:
      int_events_enriched:
        +materialized: incremental
        +incremental_strategy: insert_overwrite
        +partition_by: {field: event_date, data_type: date, granularity: day}
    marts:
      facts:
        fct_events:
          +materialized: incremental
          +incremental_strategy: merge
          +unique_key: event_id
          +on_schema_change: append_new_columns
          +full_refresh: false   # explicit — prevents --full-refresh at the CLI

# Cron — never run --full-refresh unattended
# WRONG (deleted):
#   0 2 * * *  cd /opt/dbt && dbt run --full-refresh --target prod

# RIGHT (new):
0 2 * * *  cd /opt/dbt && dbt run --target prod

# Full refresh is now a manual operation, gated on the +full_refresh: false flag
# To do a real full refresh: dbt run --full-refresh --select fct_events --target prod
# The +full_refresh: false setting refuses the flag unless overridden per-invocation.

-- stg_events — add the filter that stops 4TB fanout
{{ config(materialized='view') }}

SELECT
  event_id,
  event_ts,
  event_date,
  user_id,
  event_type,
  payload
FROM {{ source('raw', 'events') }}
WHERE event_date >= dateadd('day', -90, current_date())  -- only 90 days for staging

Step-by-step explanation.

1. The nightly --full-refresh cron gets deleted. Every fact model gets +full_refresh: false in the config so a stray dbt run --full-refresh at the CLI is refused unless the operator overrides at the model-level. The cost of one accidental full refresh of a 4TB fact is roughly 100 warehouse credits — worth an explicit safety flag.
2. int_events_enriched flips from table to incremental with insert_overwrite partitioned by event_date. Every run rewrites only yesterday's partition; the other 89 days stay intact. Warehouse scan drops from 4 TB per run to ~40 GB.
3. stg_events gets an explicit 90-day filter. Downstream views now scan 90 days of data instead of the entire 4-billion-row raw table. Five downstream views × 500 dashboard hits/day × 4TB drops to ~90 GB per hit — three orders of magnitude cheaper.
4. fct_events keeps incremental_strategy = merge because the workload has late-arriving updates. insert_overwrite (partition rewrite) is cheaper but requires idempotency on the partition boundary; merge is the safer default for event streams with UPSERT semantics.
5. on_schema_change: append_new_columns handles the "new field showed up in the source" case without a manual --full-refresh. The next incremental run just adds the column with NULLs for older rows.

Output.

Fix	Monthly scan before	Monthly scan after	Credits saved
Drop nightly full-refresh	4 TB × 30 = 120 TB	40 GB × 30 = 1.2 TB	~1000 credits
Intermediate: table → incremental	4 TB × 30 = 120 TB	40 GB × 30 = 1.2 TB	~1000 credits
Staging: unfiltered view → 90-day view	4 TB × 15000 = 60 PB	90 GB × 15000 = 1.4 PB	~40000 credits
Total monthly saving	—	—	~$29k at $5/credit

Rule of thumb. dbt performance is not just wall-clock — it is credit-per-model-run. An incremental model that finishes in 6 seconds still costs credits proportional to scan volume. Audit the top ten models by scan volume every quarter; the outliers are almost always fixable with a materialization change or a staging filter.

Worked example — the interview-style diagnosis question

Detailed explanation. A common senior interview opener: "here's a slow dbt project, tell me what you'd do first." The right shape of answer is a 60-second prioritised checklist that goes materialization → build order → threads → defer, in that order, because that order is the impact-per-effort ranking. Any candidate who starts with "increase threads" or "bigger warehouse" is signalling they haven't tuned a dbt project in production.

• First: audit materialisations. Every table model is a candidate for view, ephemeral, or incremental. The candidate with the highest median runtime is the top target.
• Second: find the critical path. dbt run --profiles-dir . -- --log-level info produces per-model timing; the longest chain is the ceiling.
• Third: right-size threads. threads in profiles.yml should match warehouse concurrency, not exceed it.
• Fourth: add defer. CI switches to --defer --state ./ci/prod-manifest --select state:modified+.

Question. Given a dbt project you know nothing about, describe the first hour of triage — commands you'd run, files you'd read, and the sequence of hypotheses you'd test.

Input.

Signal	Where to look
Current wall-clock	dbt run output; CI history
Model inventory	dbt ls --output json
Materialization mix	dbt ls --output json + jq on config.materialized
Per-model runtime	run_results.json after a run; --log-format json
Critical path	manifest.json graph + run_results.json timing
Warehouse config	Snowflake SHOW WAREHOUSES; BigQuery slot reservations
CI invocation	.github/workflows/*.yml or equivalent

Code.

# 1. Inventory — how many models, what materializations, what layers
dbt ls --output json --output-keys "resource_type name config.materialized" \
  | jq -r 'select(.resource_type == "model") | "\(.config.materialized)\t\(.name)"' \
  | sort | uniq -c | sort -rn
# example output:
#   82  view          stg_...
#   40  table         int_...
#   30  table         dim_...
#   28  incremental   fct_...

# 2. Per-model runtime from the last successful run
jq -r '.results[] | "\(.execution_time)\t\(.unique_id)"' target/run_results.json \
  | sort -rn | head -20
# top 20 slowest models — start tuning here

# 3. Find the critical path — models on the longest chain
dbt ls --select "+fct_top_slow_model" --output json | jq -r '.name'
# every ancestor of your slowest model is a candidate for a materialization change

# 4. Threads + warehouse
grep -A5 'threads:' profiles.yml
# and in Snowflake:
#   SELECT warehouse_size, min_cluster_count, max_cluster_count FROM ... WHERE name = 'DBT_WH';

# 5. Set up defer for CI — download last prod manifest as an artifact
# .github/workflows/dbt-ci.yml
- name: Download prod manifest
  uses: actions/download-artifact@v4
  with:
    name: prod-manifest
    path: ./ci

- name: dbt build (slim CI)
  run: |
    dbt build \
      --select state:modified+ \
      --defer \
      --state ./ci \
      --threads 8 \
      --target ci

Step-by-step explanation.

1. Step 1: read the model inventory. Count how many models sit at each materialization. A healthy 200-model project is roughly 40% view, 20% ephemeral, 20% incremental, 10% table, 10% other. Anything with 50%+ table is a target for triage.
2. Step 2: pull per-model runtime from run_results.json. The top 20 by execution_time are 80% of the wall-clock. Focus tuning there; do not spread effort evenly across 200 models.
3. Step 3: find the critical path. dbt ls --select "+fct_top_slow_model" prints every upstream ancestor. That chain is the longest sequence a single thread must walk; every node on it is either an incrementalisation candidate or a refactor candidate.
4. Step 4: check threads: in profiles.yml against warehouse concurrency. Snowflake Medium runs 8 concurrent queries; if threads is 16 you're wasting half of them in the warehouse queue.
5. Step 5: wire up defer. Every CI job downloads the last successful production manifest as a build artifact. --defer --state ./ci --select state:modified+ reduces the CI build to the changed subgraph. If defer is already on, verify the state path is fresh (a stale manifest from 3 weeks ago defeats defer).

Output.

Triage step	Time budget	Expected finding
Materialisation inventory	5 min	40+ table models that should be view or incremental
Top-20 by runtime	5 min	3–5 models = 60% of wall-clock
Critical-path walk	15 min	4–8 nodes on the longest chain
Threads vs warehouse	10 min	threads over- or under-set by 2–4×
Defer audit	20 min	CI does full build; needs --defer --state
Total triage	~1 hour	Prioritised backlog with expected wall-clock impact

Rule of thumb. The right first hour is diagnosis, not tuning. Land a written triage summary before you touch a single .yml — the changes you propose after diagnosis are cheap; the changes you propose before diagnosis are usually wrong.

Senior interview question on dbt performance triage

A senior interviewer often opens with: "You inherit a dbt project. dbt run takes 45 minutes, CI takes 45 minutes, the team's on a Snowflake Medium warehouse and paying $30k/month. Walk me through your first two weeks — what you'd measure, what you'd change, and how you'd justify each change with numbers."

Solution Using a materialization audit + defer + slim CI rollout

# Step 1 — the materialization audit produces a target inventory
# dbt_project.yml
models:
  my_project:
    staging:
      +materialized: view                # 80 models, was mixed
    intermediate:
      +materialized: ephemeral           # 40 models, was 'table'
    marts:
      dims:
        +materialized: table             # 30 models, small enough for table
      facts:
        +materialized: incremental       # 20 models, was 'table'
        +incremental_strategy: merge
        +unique_key: event_id
        +on_schema_change: append_new_columns
        +full_refresh: false             # gate the escape hatch
    reporting:
      +materialized: view                # 50 models

# Step 2 — profiles.yml right-sizes threads to warehouse concurrency
# ~/.dbt/profiles.yml
my_project:
  target: prod
  outputs:
    prod:
      type: snowflake
      threads: 8                        # match Snowflake Medium concurrency
      warehouse: DBT_WH
      role: DBT_ROLE
      database: ANALYTICS
      schema: PROD
      account: xy12345.us-east-1
    ci:
      type: snowflake
      threads: 8
      warehouse: DBT_CI_WH              # separate CI warehouse, X-Small
      role: DBT_CI_ROLE
      database: ANALYTICS
      schema: "PR_{{ env_var('PR_NUMBER') }}"
      account: xy12345.us-east-1

# Step 3 — CI switches to slim build via defer + state:modified
# .github/workflows/dbt-ci.yml
- name: Download prod manifest
  uses: actions/download-artifact@v4
  with:
    name: dbt-prod-manifest
    path: ./ci

- name: dbt build (slim)
  run: |
    dbt build \
      --select state:modified+ \
      --defer \
      --state ./ci \
      --threads 8 \
      --target ci

Step-by-step trace.

Change	Before	After	Effect
staging: mixed → view	200 s	160 s	small
intermediate: table → ephemeral	800 s	0 s	inlined
facts: table → incremental merge	1800 s	120 s	15×
dims: keep as table	450 s	450 s	unchanged
reporting: view	50 s	50 s	unchanged
threads: 16 → 8	queue depth 8	queue depth 0	~10% faster
CI: full → defer + state:modified+	2700 s	~90 s	30×

Morning dbt run drops from 45 minutes to ~13 minutes; CI drops from 45 minutes to ~1.5 minutes on a median 3-model PR. The Snowflake warehouse bill for the dbt workload drops proportionally to scan volume — roughly 60%. The team ships the change over two weeks: week 1 for the audit + materialization diff, week 2 for the defer rollout and CI cutover.

Output:

Metric	Before	After
dbt run wall-clock	45 min	13 min
CI wall-clock (median PR)	45 min	1.5 min
Snowflake credits/month (dbt workload)	6000	2400
Warehouse spend saved	—	~$18k/month
Engineer time waiting on builds	~90 min/day	~10 min/day

Why this works — concept by concept:

• Materialization audit first — the single biggest lever. Changing 40 intermediate table models to ephemeral and 20 fact table models to incremental merge recovers 90% of the wall-clock savings before you touch any other knob.
• ephemeral for intermediates — an ephemeral model has no warehouse artefact; dbt inlines the SQL as a CTE in every downstream. You lose the ability to select * from intermediate.foo in Snowsight, but you save 40 model runs per build.
• incremental with merge on facts — event-style facts almost always benefit from merge on a unique_key. Late-arriving updates are handled; the incremental filter (is_incremental()) restricts the scan to the last day/hour. Full refresh becomes a rare manual operation, not a nightly cron.
• defer + state:modified+ — CI's superpower. state:modified+ selects the changed models plus everything downstream on this branch. --defer --state ./ci resolves every reference to an unchanged model to the production version — no rebuild required. A 3-model PR becomes a 3-model CI.
• Cost — one senior-engineer-week for the materialization audit, one week for the defer rollout. The saved warehouse bill (~$18k/month) pays back in the first two weeks. O(1) tuning cost per quarter after the initial refactor.

SQL
Topic — sql
SQL dbt materialization and performance problems

Practice →

Optimization Topic — optimization Optimization problems on dbt tuning

Practice →

---

2. Threads and DAG parallelism

threads: N sets how many models run in parallel — but the DAG's critical path is still the ceiling

The mental model in one line: dbt threads controls dbt's parallelism cap; warehouse concurrency (Snowflake warehouse size, BigQuery slots, Redshift WLM queues) is the second cap; the DAG's longest chain — the critical path — is the third and unbreakable cap, and only refactoring the DAG can beat it. Every other threads-versus-warehouse interview question is a consequence of these three caps and which one you're currently bottlenecked on.

The three caps.

• dbt-side cap: threads in profiles.yml. How many models dbt is willing to run concurrently. If the graph has 20 ready-to-run nodes and threads: 8, dbt runs 8 at a time and queues the other 12 in its internal scheduler.
• Warehouse-side cap: concurrency. Snowflake warehouse concurrency defaults to 8 for the smallest warehouses and scales with size; BigQuery has slot reservations (or the on-demand slot pool); Redshift has WLM queues. Exceeding this cap does not fail — the queries queue at the warehouse, invisible to dbt.
• Graph-side cap: the critical path. The longest chain of dependencies in the DAG. Sum of runtimes on that chain is the absolute minimum wall-clock; no amount of parallelism can shorten it below that number.

When you're bottlenecked on threads.

• Symptom. dbt logs show many models [QUEUED]; warehouse dashboard shows the warehouse at low utilisation.
• Fix. Raise threads until warehouse utilisation approaches 100%, then stop.
• Trap. Raising threads past the warehouse-concurrency cap doesn't help — the queries queue in the warehouse instead of in dbt. Same wall-clock; harder to observe.

When you're bottlenecked on warehouse concurrency.

• Symptom. Warehouse dashboard shows queries queued; Snowflake QUERY_HISTORY shows queued_provisioning_time or queued_overload_time non-zero.
• Fix. Increase warehouse size (Small → Medium → Large) or add a multi-cluster warehouse so more queries run in parallel. Trade credits for wall-clock explicitly.
• Trap. Some workloads bottleneck on warehouse memory rather than concurrency — a Large might not help if the individual models are memory-heavy; you may need warehouse size rather than cluster count.

When you're bottlenecked on the critical path.

• Symptom. Warehouse at high utilisation, threads set correctly, wall-clock still doesn't drop when you add more parallelism.
• Fix. Shorten the critical path. Split a big model into two that can run in parallel; denormalise a mid-tier ref so two downstream models can share the upstream instead of chaining through it; convert a linear stack into a fanout.
• Trap. The critical path is not always the model with the highest runtime — it's the longest chain ending in your leaf models. A 2-minute model on a 10-node chain matters more than a 5-minute model on a 2-node chain.

Sizing threads to your warehouse.

• Snowflake. Match threads to MAX_CONCURRENCY_LEVEL on the warehouse (default 8 for X-Small, 8 for Medium, 8 for Large — Snowflake keeps concurrency flat and adds size for parallelism per query). Multi-cluster: threads = MAX_CONCURRENCY_LEVEL × max_cluster_count.
• BigQuery. Threads = concurrent slot budget; if you have 500 slots reserved and average slot use per query is 50, you can safely run 10 threads.
• Redshift. Threads = number of concurrent queries the WLM queue is configured to accept.
• Postgres. Threads = max_connections minus admin headroom minus what your app is using.

Common interview probes on threads.

• "What happens when threads exceeds warehouse concurrency?" — queries queue at the warehouse, wall-clock unchanged.
• "Why does adding threads not always help?" — the DAG's critical path is the ceiling.
• "What's the ideal number of threads?" — match warehouse concurrency, then leave.
• "How do you find the critical path?" — dbt run timing + graph inspection.

Worked example — threads at 8 on a Medium Snowflake warehouse

Detailed explanation. A team runs a 120-model dbt project on a Snowflake Medium warehouse. Default threads: 4 in profiles.yml; morning build takes 25 minutes. They raise to threads: 8 and see build drop to 15 minutes. They raise to threads: 16 and see no additional drop. Walk through why 8 is the right answer for a Medium warehouse and what would have to change to justify going higher.

• Starting point. threads: 4, wall-clock 25 min.
• After the fix. threads: 8, wall-clock 15 min (40% faster).
• Diminishing returns. threads: 16, wall-clock still ~15 min.
• Why. Snowflake Medium runs 8 concurrent queries; queries 9–16 queue at the warehouse.

Question. Design the threads configuration that maximises parallelism without wasting cost. If the team wants to push further, propose two paths (multi-cluster vs bigger warehouse) and their trade-offs.

Input.

Config	threads	Snowflake MAX_CONCURRENCY_LEVEL	Warehouse size	Wall-clock
Before	4	8	Medium	25 min
After	8	8	Medium	15 min
Over-provisioned	16	8	Medium	15 min
Option A	8	8	Medium + multi-cluster (2)	8 min
Option B	8	8	Large	8 min

Code.

# profiles.yml — match threads to warehouse concurrency
my_project:
  target: prod
  outputs:
    prod:
      type: snowflake
      threads: 8                    # matches MAX_CONCURRENCY_LEVEL of the warehouse
      warehouse: DBT_WH
      role: DBT_ROLE
      database: ANALYTICS
      schema: PROD
      account: xy12345.us-east-1
      # Snowflake-specific parallelism knobs
      query_tag: "dbt:{{ target.name }}:{{ invocation_id }}"
      client_session_keep_alive: false

-- Snowflake — check the warehouse concurrency setting
SHOW WAREHOUSES LIKE 'DBT_WH';
-- name    | size   | min_cluster_count | max_cluster_count | scaling_policy
-- DBT_WH  | MEDIUM | 1                 | 1                 | STANDARD

-- Check for warehouse queuing over the last hour
SELECT query_id,
       start_time,
       execution_status,
       queued_provisioning_time / 1000.0 AS queued_prov_s,
       queued_overload_time     / 1000.0 AS queued_over_s,
       total_elapsed_time       / 1000.0 AS total_s
FROM   SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE  warehouse_name = 'DBT_WH'
  AND  start_time > dateadd('hour', -1, current_timestamp())
  AND  queued_overload_time > 0
ORDER  BY queued_overload_time DESC
LIMIT  20;

-- Add a second cluster to double parallelism
ALTER WAREHOUSE DBT_WH SET
    MAX_CLUSTER_COUNT = 2
    SCALING_POLICY    = 'STANDARD';

Step-by-step explanation.

1. threads: 8 matches the Snowflake Medium's MAX_CONCURRENCY_LEVEL (8 concurrent queries per cluster). Every ready-to-run model gets a warehouse slot immediately; no dbt-side or warehouse-side queuing.
2. Going to threads: 16 puts 8 more models into warehouse queue. Snowflake's queued_overload_time becomes non-zero. From dbt's perspective, wall-clock is unchanged; from Snowflake's, the extra threads consume no credits (the query is just queued) but they clutter the query history.
3. Option A — add a second cluster (MAX_CLUSTER_COUNT = 2). Snowflake spins up a second Medium cluster when the first one is saturated. Effective concurrency doubles to 16; wall-clock drops from 15 to ~8 minutes. Cost: 2× credit spend during the peak of the run (each cluster billed per second).
4. Option B — bump warehouse size to Large. Same 8 concurrency slots, but each query gets more compute per slot. Individual model runtime drops; wall-clock drops from 15 to ~8 minutes. Cost: 2× credit spend for the whole run (Large is 2× Medium credit-per-hour).
5. The trade-off: Option A (multi-cluster) is cheaper when the load is bursty — Snowflake only spins up the second cluster during the peak. Option B (bigger warehouse) is cheaper when models are individually slow — bigger compute per query cuts the per-model time. Most dbt workloads are bursty at the peak of the run, so Option A is usually the better first move.

Output.

Config	threads	Effective concurrency	Wall-clock	Credit factor
Medium, 4 threads	4	4	25 min	1.0×
Medium, 8 threads	8	8	15 min	1.0×
Medium, 16 threads	16	8 (queued)	15 min	1.0×
Medium + multi-cluster	8	16	8 min	~1.5× (bursty billing)
Large, 8 threads	8	8 (more compute)	8 min	2.0×

Rule of thumb. Match threads to warehouse concurrency. Never set threads higher — you're not getting speed, you're paying for queue depth in warehouse metadata. To go faster, add a cluster (bursty billing) or bump the size (flat billing) — pick based on whether the peak is short (cluster) or the models are individually slow (size).

Worked example — the critical path that no parallelism can beat

Detailed explanation. A team runs dbt run --threads 16 on a Large Snowflake warehouse and still sees 20-minute builds. They add more threads. Nothing helps. Investigating the DAG shows a linear chain of 5 models — stg_orders → int_orders_enriched → int_orders_scored → int_orders_finalised → fct_orders — where each takes 4 minutes. The chain is 20 minutes end-to-end no matter how much parallelism runs elsewhere. Threads don't help; the critical path does.

• The DAG. 100 models total; 95 sit outside the critical chain and finish in parallel in ~5 minutes.
• The critical chain. 5 models × 4 min each = 20 minutes on the critical path.
• Wall-clock. Max(5-minute parallel side, 20-minute critical chain) = 20 minutes.

Question. Diagnose the critical path, then propose two refactor patterns (split + fanout, denormalise the mid-tier) that would shorten it. Quantify the expected wall-clock.

Input.

Model	Depends on	Runtime
stg_orders	source(raw.orders)	4 min
int_orders_enriched	stg_orders	4 min
int_orders_scored	int_orders_enriched	4 min
int_orders_finalised	int_orders_scored	4 min
fct_orders	int_orders_finalised	4 min
95 other models	(various)	≤ 5 min each in parallel

Code.

# 1. Find the critical path — every ancestor of the slowest leaf
dbt ls --select "+fct_orders" --output name
# stg_orders
# int_orders_enriched
# int_orders_scored
# int_orders_finalised
# fct_orders

# 2. Confirm each is on the critical path (no parallel siblings)
dbt ls --select "1+int_orders_scored" --output name
# int_orders_enriched     <- only parent
# int_orders_scored
# int_orders_finalised    <- only child

-- Refactor A — split int_orders_enriched into two independent branches
-- int_orders_enriched.sql becomes two models that both feed int_orders_scored

-- int_orders_pricing.sql
{{ config(materialized='ephemeral') }}
SELECT o.order_id, o.customer_id, p.list_price, p.discount_pct
FROM {{ ref('stg_orders') }}    o
JOIN {{ ref('stg_products') }}  p ON p.product_id = o.product_id

-- int_orders_customer.sql
{{ config(materialized='ephemeral') }}
SELECT o.order_id, c.segment, c.region
FROM {{ ref('stg_orders') }}    o
JOIN {{ ref('stg_customers') }} c ON c.customer_id = o.customer_id

-- int_orders_scored now depends on BOTH — they run in parallel
-- int_orders_scored.sql
SELECT p.order_id, p.list_price * (1 - p.discount_pct) AS net_price, c.segment, c.region
FROM   {{ ref('int_orders_pricing') }}  p
JOIN   {{ ref('int_orders_customer') }} c USING (order_id)

# Refactor B — denormalise int_orders_enriched into fct_orders directly
# Skip int_orders_scored and int_orders_finalised; do the work in fct_orders
# dbt_project.yml
models:
  my_project:
    marts:
      facts:
        fct_orders:
          +materialized: incremental
          +incremental_strategy: merge
          +unique_key: order_id

Step-by-step explanation.

1. dbt ls --select "+fct_orders" prints every ancestor of the leaf. The 5-model chain becomes visible as a linear list. If any model on the chain has multiple parents, the chain forks; a purely linear list is a critical-path signal.
2. Refactor A: split int_orders_enriched into int_orders_pricing and int_orders_customer. These two models have no dependency on each other and can run in parallel. int_orders_scored now waits for both, but each is 2 minutes instead of one 4-minute model. Chain length drops from 5 × 4 min = 20 min to 4 × 4 min + max(2,2) = 18 min. Small saving.
3. Refactor B: collapse two nodes (int_orders_scored and int_orders_finalised) into fct_orders. The chain becomes stg_orders → int_orders_enriched → fct_orders — 3 × 4 min = 12 min. Bigger saving because we removed two hops.
4. The trade-off: Refactor B (denormalisation) is faster but sacrifices the intermediate models that other teams might reference. Refactor A (parallel split) preserves the intermediates and gives smaller wins.
5. In practice, senior engineers combine both: identify which intermediates are actually referenced outside the critical chain (usually 1–2) and denormalise the rest. The critical chain drops to 2–3 nodes; wall-clock drops proportionally.

Output.

Refactor	Chain length	Wall-clock	Trade-off
Original	5 nodes × 4 min	20 min	baseline
A: parallel split	4 nodes × 4 min + 1 parallel pair	~18 min	preserves intermediates
B: denormalise	3 nodes × 4 min	12 min	drops intermediates
A + B combined	2–3 nodes × 4 min	8–12 min	denormalise most, split rest

Rule of thumb. When threads don't help, the critical path is the bottleneck. Find it with dbt ls --select "+leaf_model", then attack the longest node on the chain first — either shorten it (incrementalise, add a filter) or eliminate it (denormalise into a downstream model). No amount of warehouse tuning will beat a badly-shaped DAG.

Worked example — many small warehouses vs one big warehouse

Detailed explanation. A team asks: should we run dbt on one Large warehouse with threads: 8, or three Medium warehouses (one per target) with threads: 8 each? Both have the same credit-per-hour cost; the question is whether isolation buys anything. The senior answer: isolation buys workload separation (dbt CI does not contend with dashboard queries), but it does not buy speed for a single dbt run. Speed comes from concurrency and compute per query, not from having multiple warehouses.

• Option A. One Large warehouse, threads: 8. Dashboards and dbt share the warehouse.
• Option B. Three Medium warehouses (dbt-prod, dbt-ci, dashboards), each threads: 8 where applicable.
• Credit cost. Large = 8 credits/hour; 3× Medium = 3 × 4 = 12 credits/hour. B is 50% more expensive if all three run 24/7; less if they auto-suspend.

Question. Compare the two topologies for a workload that has (a) dbt-prod at 6am, (b) dbt-ci throughout the day, (c) dashboards throughout the day. Recommend a topology and justify.

Input.

Workload	Time window	Concurrent queries
dbt-prod	6:00–6:20 daily	~8
dbt-ci (median PR)	10–20× per day, 2 min each	~4
Dashboards	8:00–20:00 daily	~2

Code.

-- Option A — single Large warehouse (shared)
CREATE OR REPLACE WAREHOUSE DBT_WH
    WITH WAREHOUSE_SIZE   = 'LARGE'
    AUTO_SUSPEND          = 60
    AUTO_RESUME           = TRUE
    MIN_CLUSTER_COUNT     = 1
    MAX_CLUSTER_COUNT     = 1;

-- Option B — three separate warehouses
CREATE OR REPLACE WAREHOUSE DBT_PROD_WH
    WITH WAREHOUSE_SIZE   = 'MEDIUM'
    AUTO_SUSPEND          = 60;

CREATE OR REPLACE WAREHOUSE DBT_CI_WH
    WITH WAREHOUSE_SIZE   = 'X-SMALL'   -- CI works fine on X-Small
    AUTO_SUSPEND          = 60;

CREATE OR REPLACE WAREHOUSE DASHBOARD_WH
    WITH WAREHOUSE_SIZE   = 'MEDIUM'
    AUTO_SUSPEND          = 60
    MIN_CLUSTER_COUNT     = 1
    MAX_CLUSTER_COUNT     = 3;          -- multi-cluster for dashboard bursts

# profiles.yml — Option B routes each target to its own warehouse
my_project:
  target: prod
  outputs:
    prod:
      type: snowflake
      warehouse: DBT_PROD_WH
      threads: 8
    ci:
      type: snowflake
      warehouse: DBT_CI_WH
      threads: 4       # X-Small has only 4 concurrent slots

Step-by-step explanation.

1. Option A pools everything on one Large warehouse. Auto-suspend after 60s idle keeps costs down; when idle, the warehouse costs nothing. During peak (6:00–6:20), dbt-prod runs on the Large — fast because bigger compute per query.
2. But when dashboards fire at 10am and dbt-ci fires simultaneously, all three workloads compete for the same 8 concurrency slots. Dashboards can suffer tail latency because a heavy dbt-ci model is holding a slot for 90 seconds.
3. Option B isolates the three workloads. dbt-ci runs on an X-Small (cheapest, fine for the CI's median 3-model build via defer). Dashboards get a dedicated Medium with multi-cluster for burst. dbt-prod gets its own Medium — no contention with dashboards.
4. Cost math: auto-suspend means each warehouse only bills when active. A Medium billed for 20 minutes/day (dbt-prod) costs 20/60 × 4 = ~1.3 credits/day. An X-Small billed for 2 hours/day (dbt-ci) costs 2 × 1 = 2 credits/day. Total daily cost for Option B is competitive with Option A on the same workloads, and isolation wins.
5. The right answer for a workload that must not contend is Option B. For a workload where dbt runs at 3am and dashboards at 10am with no overlap, Option A is fine — pool the compute, save on idle overhead.

Output.

Metric	Option A (shared Large)	Option B (three warehouses)
dbt-prod peak wall-clock	12 min (Large compute)	15 min (Medium compute)
Dashboard p99 latency during peak	+2 s (contention)	flat
dbt-ci wall-clock	~2 min	~2 min
Isolation	poor	good
Estimated credits/day	~15	~18

Rule of thumb. Separate warehouses for CI, prod builds, and interactive dashboards buy isolation, not speed. Isolation matters when workloads overlap in time; speed comes from matching threads to warehouse concurrency and picking the right warehouse size per workload. In 2026, most senior deployments run at least a dedicated CI warehouse — the auto-suspend model makes it nearly free.

Senior interview question on threads and DAG parallelism

A senior interviewer might ask: "You've set threads: 8 on a Snowflake Medium and your build is still slow. What's your diagnostic sequence, and how do you decide between raising threads, bumping the warehouse, or refactoring the DAG?"

Solution Using a three-cap diagnostic with critical-path attack

# Step 1 — establish which cap you're hitting
# Cap 1: dbt-side — are models queued?
dbt run --log-format json 2>&1 | jq -r '
  select(.info.name == "ThreadFinished") | .info.msg
' | tail -20
# Look for "Skipping model ..." or long gaps between "Started" and "OK"

# Cap 2: warehouse-side — are queries queued in Snowflake?

-- Snowflake diagnostic — queue time in the last dbt run
SELECT query_id,
       query_tag,
       start_time,
       queued_provisioning_time / 1000.0 AS q_prov_s,
       queued_overload_time     / 1000.0 AS q_over_s,
       total_elapsed_time       / 1000.0 AS total_s
FROM   SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE  query_tag LIKE 'dbt:prod:%'
  AND  start_time > dateadd('hour', -1, current_timestamp())
ORDER  BY start_time
LIMIT  200;

-- Non-zero queued_overload_time = warehouse concurrency is the cap
-- Zero queued time but slow total = the query itself is slow (bigger warehouse)

# Cap 3: DAG-side — find the critical path
# Run once to populate manifest + run_results
dbt run

# Get per-model runtime
jq -r '.results[] | "\(.execution_time)\t\(.unique_id)"' target/run_results.json \
  | sort -rn | head -10

# For each of the top-10 slowest, find its critical chain
dbt ls --select "+model.my_project.fct_orders" --output name

Step-by-step trace.

Cap	Signal	Action
dbt-side	many [QUEUED] in log, warehouse idle	raise threads
warehouse-side	non-zero queued_overload_time	bump warehouse size or add cluster
DAG-side	high threads, warehouse busy, wall-clock unchanged	shorten the critical path

The three caps are diagnosed in order. dbt-side is usually the simplest — threads: 4 on a warehouse that can handle 8 is a common misconfiguration. Warehouse-side shows up as Snowflake queue time in QUERY_HISTORY. DAG-side is the final answer when threads and warehouse are both correctly sized and the build is still slow — the fix is a graph refactor, not a config change.

Output:

Cap	Fix	Cost
dbt-side	raise threads to match warehouse concurrency	zero
warehouse-side	bump size (2×), add cluster (bursty billing), or split workloads	linear in credits
DAG-side	shorten critical path (split, denormalise, refactor)	one week of senior effort

Why this works — concept by concept:

• Three caps in order — always diagnose dbt-side before warehouse-side before graph-side. Each cap is cheap to check; blindly raising threads or warehouse size wastes credits.
• Query-tag discipline — query_tag in profiles.yml lets Snowflake QUERY_HISTORY filter to dbt-only queries. Without it, dashboard queries pollute the diagnostic and you'll chase phantom slowness.
• run_results.json is the source of truth — every dbt run writes per-model timing to target/run_results.json. Parsing it with jq is faster and more accurate than eyeballing the stdout log.
• Critical path via +leaf_model — dbt ls --select "+fct_orders" prints the ancestors chain. Once you know the chain, every node on it is a candidate for materialisation change or refactor.
• Cost — three caps, three diagnostic steps, roughly 30 minutes of triage. The alternative — cranking warehouse size blindly — wastes credits proportional to the tuning window.

SQL
Topic — sql
SQL parallelism and critical-path problems

Practice →

Optimization Topic — optimization Optimization problems on warehouse concurrency

Practice →

---

3. Materialization strategy — table / view / incremental / ephemeral

materialized= is the single biggest speed lever in dbt — one config line, 5–10× wall-clock impact per model

The mental model in one line: table pays the full rebuild cost every run; view pays nothing at build time and pushes cost to every reader; incremental pays only for new rows; ephemeral pays nothing and never creates an artefact — pick per model, not per project, and the wall-clock story lines up almost automatically. Every other dbt materialization interview question is a consequence of which lever you pulled on which model.

Table — the "just rebuild it" default.

• Build cost. Full rebuild every run. CREATE OR REPLACE TABLE ... AS SELECT ... scans every source row every time.
• Query cost. Cheapest possible — the reader hits a materialised table with statistics and clustering.
• When to use. Slow-changing dimensions (customers, products, geographies), small fact aggregates that fit in a few million rows, anything where the reader-side cost dominates and the build-side cost is a one-time evening job.
• When to avoid. Large facts with more than a few hundred million rows — the full-rebuild cost dominates every run.

View — the "no rebuild" free build.

• Build cost. Effectively zero. CREATE OR REPLACE VIEW ... AS SELECT ... writes a definition, not data.
• Query cost. Whatever the underlying SELECT costs, every time a reader queries the view. Snowflake caches the result set for identical queries; other warehouses vary.
• When to use. Thin staging projections (SELECT a, b, c FROM raw.t), thin renames, thin filter layers. Anything the reader will materialise anyway when it references you.
• When to avoid. Any expensive join or aggregate that gets re-run by every reader — you push the cost to N readers instead of paying it once.

Incremental — the "only new rows" pattern.

• Build cost. Full rebuild on first run (or when --full-refresh); scan-of-new-rows on every subsequent run. Typically 1–5% of the full-rebuild cost.
• Query cost. Same as table — reader hits a materialised table.
• When to use. Event streams, append-only or upsert-mostly facts, anything time-partitioned. If your SELECT has a WHERE event_date >= ... clause that can filter to the incremental window, you have an incremental candidate.
• When to avoid. Tables that get 30%+ of their rows updated every run (the incremental filter can't scope tightly enough); tables where the source schema shifts constantly (on_schema_change can help, but adds complexity).

Ephemeral — the "inline as CTE" pattern.

• Build cost. Zero. Ephemeral models are not materialised at all — dbt inlines the SQL as a CTE in every downstream model.
• Query cost. Whatever the CTE evaluates to, inlined into every downstream's plan. Modern query optimisers push filters through CTEs, so the cost is often equivalent to a table for read paths.
• When to use. Intermediate models that are referenced by 1–3 downstreams and are cheap to re-evaluate. Anything you'd write as a CTE anyway.
• When to avoid. Intermediates referenced by 5+ downstreams (the SQL gets duplicated in every downstream plan); anything you want to SELECT * from in Snowsight (no artefact = no browseable object).

The decision matrix.

• Small + read-heavy → table. Dims, small facts, snapshot tables.
• Thin projection or rename → view. Staging models, filter layers.
• Large event stream with time filter → incremental. Every fact with >100M rows.
• Intermediate with 1–3 downstreams → ephemeral. Enrichment CTEs.
• Intermediate with 5+ downstreams → table or view. Ephemeral would duplicate SQL 5× in plans.

Common interview probes on materialization.

• "When would you pick view over table?" — thin projection where the reader's cost is bounded by the source's own layout.
• "What does ephemeral actually do?" — inline as a CTE; no warehouse artefact.
• "Why not use incremental everywhere?" — the incremental filter must scope tightly; if your table has global updates, incremental is a foot-gun.
• "How do you handle schema changes on an incremental?" — on_schema_change: append_new_columns or sync_all_columns.

Worked example — a 10-model project with mixed materializations

Detailed explanation. A team has a 10-model mini-project: 3 staging models, 3 intermediates, 2 dims, 1 fact, 1 reporting view. Walk through the right materialization for each and explain the reasoning. The exercise is small enough to fit in one interview whiteboard; the reasoning generalises to 200-model projects.

• Source. raw.events (500M rows), raw.customers (100k rows), raw.products (50k rows).
• Layer counts. 3 staging, 3 intermediate, 2 dim, 1 fact, 1 reporting.
• Cadence. Daily build; CI on every PR.

Question. For each of the 10 models, pick a materialization and justify. Produce the dbt_project.yml and per-model config where different from the layer default.

Input.

Layer	Model	Size	Notes
Staging	stg_events	500M	passthrough with 90-day filter
Staging	stg_customers	100k	passthrough
Staging	stg_products	50k	passthrough
Intermediate	int_events_pricing	derived	joins events + products, 2 downstreams
Intermediate	int_customer_lifetime	derived	joins events + customers, 4 downstreams
Intermediate	int_orders_flags	derived	derived flags, 1 downstream
Mart	dim_customers	100k	referenced by 6 downstreams
Mart	dim_products	50k	referenced by 4 downstreams
Mart	fct_events	500M	event stream, time-partitioned by day
Reporting	rpt_daily_kpis	daily agg	queried by dashboards every 10 min

Code.

# dbt_project.yml — layer defaults + model overrides
models:
  my_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
      # Override — int_customer_lifetime is referenced 4× and expensive to compute
      int_customer_lifetime:
        +materialized: table
    marts:
      dims:
        +materialized: table
      facts:
        +materialized: incremental
        +incremental_strategy: merge
        +unique_key: event_id
        +on_schema_change: append_new_columns
        +partition_by: {field: event_date, data_type: date, granularity: day}
        +cluster_by: [event_date, event_type]
    reporting:
      +materialized: table       # dashboards query this every 10 min; view would re-run the query 6×/hr
      +cluster_by: [report_date]

-- fct_events.sql — an incremental fact with is_incremental filter
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='event_id',
    on_schema_change='append_new_columns',
    partition_by={'field': 'event_date', 'data_type': 'date'}
) }}

SELECT
  e.event_id,
  e.event_ts,
  cast(e.event_ts AS date)         AS event_date,
  e.customer_id,
  p.product_id,
  p.list_price * (1 - p.discount_pct) AS net_price,
  e.event_type,
  e.payload
FROM   {{ ref('stg_events') }}          e
LEFT JOIN {{ ref('int_events_pricing') }} p USING (order_id)

{% if is_incremental() %}
  -- Only new events since the last build
  WHERE e.event_ts > (SELECT coalesce(max(event_ts), '1900-01-01') FROM {{ this }})
{% endif %}

Step-by-step explanation.

1. Staging (3 models) → view. Every staging model is a passthrough with a filter. Building them as tables would waste ~500M rows × 3 of full scans every run. Views are effectively free at build time and let the downstream models push filters through.
2. Intermediate (3 models) → mostly ephemeral, one exception. int_events_pricing and int_orders_flags each have 1–2 downstreams — ephemeral inlines them as CTEs and skips the build. int_customer_lifetime has 4 downstreams and does an expensive customer-level aggregation — ephemeral would duplicate the aggregation 4× in every downstream plan; materialising as table computes it once.
3. Dims (2 models) → table. Small (100k, 50k rows), referenced by 4–6 downstreams; the full-rebuild cost is trivial (< 5 seconds each), and readers benefit from statistics + clustering. Never use view for dims that are joined into a 500M-row fact.
4. Fact (1 model) → incremental with merge. Event stream with 500M rows, time-partitioned by day. The is_incremental() filter scopes each run to event_ts > max(event_ts) — only new events. merge on event_id handles late-arriving updates. on_schema_change: append_new_columns handles the "new field showed up" case without a full refresh.
5. Reporting (1 model) → table. Dashboards hit rpt_daily_kpis every 10 minutes; if it were a view, each hit would re-run the underlying aggregation. Materialising once per day + clustering by report_date gives dashboards sub-second reads. The daily build cost is a few seconds; the amortised cost across 144 dashboard hits per day is essentially zero per hit.

Output.

Model	Materialization	Build cost	Reader cost	Justification
stg_events	view	~0	pushed through	thin passthrough
stg_customers	view	~0	pushed through	thin passthrough
stg_products	view	~0	pushed through	thin passthrough
int_events_pricing	ephemeral	~0	inlined	2 downstreams
int_customer_lifetime	table	20 s	fast	4 downstreams, expensive agg
int_orders_flags	ephemeral	~0	inlined	1 downstream
dim_customers	table	3 s	fast	6 downstreams, small
dim_products	table	2 s	fast	4 downstreams, small
fct_events	incremental merge	8 s (incr)	fast	500M event stream
rpt_daily_kpis	table	4 s	ms per hit	144 dashboard hits/day

Rule of thumb. Layer default + per-model override is the right pattern. Set staging → view, intermediate → ephemeral, dims → table, facts → incremental at the project level; override only where a specific model's reference count or size argues for something different. Never override at random; every override should have a one-line comment on why.

Worked example — the ephemeral trap in a wide fanout

Detailed explanation. A team makes int_customer_lifetime ephemeral because "it's an intermediate model." Six downstream models reference it. Suddenly every downstream is slow. The ephemeral SQL gets inlined into every downstream plan; the customer-level aggregation is now computed six times per build, once per downstream. Investigating shows that materialising it as table (paying the 20s aggregation cost once) is 5× faster overall than ephemeral (paying the 20s aggregation cost six times).

• Setup. int_customer_lifetime aggregates 500M events per customer.
• Ephemeral mode. SQL inlined in 6 downstream plans; aggregation runs 6× per build.
• Table mode. Aggregation runs once; 6 downstreams read the small aggregated table.

Question. Diagnose the ephemeral fanout, propose the fix, and quantify the wall-clock and credit cost of each.

Input.

Config	Aggregation runs	Downstream reads
ephemeral	6 (once per downstream plan)	pushed into plan
table	1	6 point reads on a small table

Code.

-- Bad — int_customer_lifetime as ephemeral
-- Every downstream inlines this as a CTE and re-computes the aggregation
{{ config(materialized='ephemeral') }}

WITH customer_events AS (
  SELECT customer_id,
         count(*)         AS event_count,
         sum(net_price)   AS lifetime_value,
         min(event_ts)    AS first_event_ts,
         max(event_ts)    AS last_event_ts
  FROM   {{ ref('fct_events') }}
  GROUP  BY customer_id
)
SELECT * FROM customer_events

-- Good — same model as table
-- Aggregation runs ONCE; 6 downstreams do a point-read on the small result
{{ config(materialized='table') }}

WITH customer_events AS (
  SELECT customer_id,
         count(*)         AS event_count,
         sum(net_price)   AS lifetime_value,
         min(event_ts)    AS first_event_ts,
         max(event_ts)    AS last_event_ts
  FROM   {{ ref('fct_events') }}
  GROUP  BY customer_id
)
SELECT * FROM customer_events

# Diagnose — how many downstreams reference this model?
dbt ls --select "int_customer_lifetime+" --output name | wc -l
# 7   (1 + 6 downstreams)

# If wc -l > 3 on an expensive intermediate, materialise as table.

Step-by-step explanation.

1. Ephemeral inlines the SQL as a CTE in every downstream. From dbt's perspective, int_customer_lifetime has no artefact — it's just a template that gets pasted into every downstream's compiled SQL.
2. When 6 downstream models each reference int_customer_lifetime, the customer-level aggregation runs six times in six separate query plans. Query optimisers cannot dedupe across plans; they can only dedupe within one plan.
3. Materialising as table computes the aggregation once (~20s), then the six downstreams each do a point-read on the small result table (probably 100k rows at customer-level, versus the 500M event source).
4. The rule: ephemeral is efficient when the downstream count is 1–2 AND the SQL is cheap to re-evaluate. It's a trap when either the downstream count is high or the SQL is expensive.
5. dbt ls --select "model+" counts descendants; > 3 descendants on an expensive intermediate is a hard signal to materialise as table.

Output.

Config	Wall-clock (aggregation)	Wall-clock (downstream reads)	Total
ephemeral	6 × 20 s = 120 s	6 × 0.5 s = 3 s	123 s
table	1 × 20 s = 20 s	6 × 1 s = 6 s	26 s

Rule of thumb. Ephemeral is not free when downstream count is high. Count descendants with dbt ls --select "model+"; if the count is > 3 and the model does aggregation, use table instead. Ephemeral is for cheap 1-to-1 intermediates, not for expensive fanouts.

Worked example — view fanout burning credits at query time

Detailed explanation. A team ships a KPI dashboard on top of a chain of views: stg_events (view) → int_events_enriched (view) → int_customer_lifetime (view) → rpt_customer_summary (view). The dashboard queries rpt_customer_summary every 5 minutes. Each dashboard hit expands the view chain into a plan that scans 500M events, joins to customers, and re-aggregates. The build is fast (view → view → view builds in seconds), but the dashboard credit spend is enormous — 288 hits per day × 500M-row scan.

• Setup. Four-view chain ending in a dashboard.
• Build cost. ~0 (all views).
• Query cost. Every dashboard hit re-runs the full chain.
• Fix. Materialise the leaf (rpt_customer_summary) as a table; dashboard reads a small table.

Question. Design the materialization that keeps build cost low and dashboard cost low. Quantify the credit impact.

Input.

Layer	Rows	Cost per view expansion
stg_events	500M	scanned in plan
int_events_enriched	500M (derived)	joined in plan
int_customer_lifetime	500k (agg)	aggregated in plan
rpt_customer_summary	500k	final projection

Code.

-- Materialise the leaf as a table with a scheduled refresh
{{ config(
    materialized='table',
    cluster_by=['as_of_date']
) }}

SELECT
  as_of_date,
  customer_id,
  event_count,
  lifetime_value,
  first_event_ts,
  last_event_ts,
  ntile(10) OVER (ORDER BY lifetime_value) AS ltv_decile
FROM   {{ ref('int_customer_lifetime') }}

-- Optional — cache in the reporting layer with `dbt run --select rpt_customer_summary`
-- scheduled every 30 min if the dashboard needs sub-30-min freshness

# dbt_project.yml — override the reporting layer default
models:
  my_project:
    reporting:
      +materialized: table
      # Explicit refresh cadence per model
      rpt_customer_summary:
        +materialized: table
        +cluster_by: [as_of_date]

Step-by-step explanation.

1. The view chain: stg → int_enriched → int_lifetime → rpt. Every dashboard hit fully expands the chain. Snowflake caches result sets for identical queries, but the dashboard passes different filter parameters per hit; caches miss.
2. Each hit scans 500M events. At 288 hits/day and roughly 1 Snowflake credit per 100 GB scanned on a Medium warehouse, this is thousands of credits per month for one dashboard.
3. Materialising rpt_customer_summary as table runs the chain once per build (say, every 30 minutes). The dashboard reads a small 500k-row table — sub-second, essentially free per hit.
4. The build cost goes up by one full chain execution per 30 minutes (48 per day). The query cost goes down by 288 dashboard hits × 500M scan → 288 hits × 500k point read. Net credit spend drops by ~99%.
5. The alternative — leave everything as view and rely on Snowflake result cache — is fine only if the dashboard queries are truly cacheable (no per-user parameters, no time-relative filters). Most dashboards pass a date > current_date - 7 filter, which invalidates the cache every day; leaving the chain as views burns credits.

Output.

Config	Build cost/day	Dashboard cost/day	Total credits/day
All views	~5	~1500	~1505
Leaf as table (30-min refresh)	~240	~5	~245
Leaf as table (10-min refresh)	~720	~5	~725

Rule of thumb. Views on top of views are cheap to build and expensive to query. If a view is queried more than ~10 times a day and expands into a large scan, materialise it as a table with a scheduled refresh. The build cost is bounded by the refresh cadence; the query cost is bounded by the table size. Both are bounded; the view-chain cost is unbounded in query count.

Senior interview question on materialization strategy

A senior interviewer might ask: "Walk me through your decision tree for picking a materialization on a new model. What signals do you look at, and when do you deviate from the layer default?"

Solution Using a five-question decision tree

Five-question decision tree — pick a materialization
====================================================

Q1: Is this a passthrough / thin projection / rename?
     yes  → view
     no   → Q2

Q2: Is this an intermediate with 1–3 downstreams AND cheap SQL?
     yes  → ephemeral
     no   → Q3

Q3: Is this a fact / event stream with a natural time filter?
     yes  → incremental (merge for upserts; insert_overwrite for partition rewrites)
     no   → Q4

Q4: Is this a small dim (< 5M rows) referenced by many downstreams?
     yes  → table
     no   → Q5

Q5: Is this queried by dashboards more than ~10 times a day?
     yes  → table with scheduled refresh
     no   → view (last resort — accept that readers pay the cost)

# dbt_project.yml — layer defaults reflect the tree
models:
  my_project:
    staging:
      +materialized: view              # Q1 default
    intermediate:
      +materialized: ephemeral         # Q2 default
      # Overrides — high-fanout aggregations
      int_customer_lifetime:
        +materialized: table
    marts:
      dims:
        +materialized: table            # Q4 default
      facts:
        +materialized: incremental      # Q3 default
        +incremental_strategy: merge
        +unique_key: event_id
        +on_schema_change: append_new_columns
    reporting:
      +materialized: table              # Q5 default (dashboards)
      # Overrides — internal-only reports queried rarely
      rpt_finance_monthly:
        +materialized: view

Step-by-step trace.

Model	Q1	Q2	Q3	Q4	Q5	Decision
stg_orders	yes	—	—	—	—	view
int_orders_pricing	no	yes	—	—	—	ephemeral
fct_events	no	no	yes	—	—	incremental merge
dim_customers	no	no	no	yes	—	table
rpt_daily_kpis	no	no	no	no	yes	table + refresh
int_customer_lifetime	no	no (7 downstreams)	—	—	—	table (override)

The tree collapses the decision to five yes/no questions; the answer falls out. Every model gets classified in seconds. The layer defaults encode the common case; per-model overrides encode the exceptions.

Output:

Layer	Default	When to override
staging	view	~never
intermediate	ephemeral	high fanout or expensive aggregation → table
dims	table	dim > 50M rows → incremental
facts	incremental	fact < 5M rows → table
reporting	table	internal-only + rarely queried → view

Why this works — concept by concept:

• Five questions cover 95% of models — passthrough, cheap intermediate, event stream, small dim, dashboard leaf. The remaining 5% get manual decisions from the senior engineer.
• Layer defaults + per-model overrides — the layer default handles the common case in one line; overrides handle the exceptions with one-line comments. Auditors and new hires can read the config and predict the materialization from the layer.
• Fanout signal drives ephemeral vs table — dbt ls --select "model+" is the objective test. > 3 descendants + expensive SQL = table. ≤ 3 descendants + cheap SQL = ephemeral.
• Reader count drives view vs table for leaves — every reporting view queried > 10 times a day is a materialisation candidate. The credit math flips at that cadence.
• Cost — five questions per model, ~30 seconds per decision. On a 200-model project, ~100 minutes to classify everything. Layer defaults amortise the cost across future models to essentially zero.

SQL
Topic — sql
SQL materialization decision problems

Practice →

ETL Topic — etl ETL problems on model layer choice

Practice →

---

4. Incremental strategies and micro-batches

incremental_strategy decides how new rows land — append, merge, delete+insert, insert_overwrite

The mental model in one line: append writes new rows and never touches old ones (append-only streams); merge UPSERTs on a unique_key (event streams with corrections); delete+insert deletes matching rows and re-inserts (bulk backfill by partition); insert_overwrite rewrites a whole partition atomically (BigQuery + Spark, time-partitioned tables) — the wrong choice does either silent data corruption or 100× the credit spend.

Append — the append-only default.

• Semantics. New rows are appended; existing rows are never touched.
• Cost. Cheapest of the strategies — no scan of the existing table for updates.
• When to use. Event streams that are truly append-only (log events, immutable metrics), micro-batches from streaming systems where the source guarantees no duplicates.
• When to avoid. Any source that emits corrections, retries, or late-arriving updates — append will duplicate them.

Merge — the UPSERT default.

• Semantics. For each incoming row, UPDATE if unique_key matches, INSERT otherwise.
• Cost. Scans the existing table's index on unique_key for every incoming row. Moderate cost; scales with incoming batch size, not existing table size (on properly-clustered tables).
• When to use. Event streams with corrections, dimension-style incremental facts, anything with a natural primary key and possible updates.
• When to avoid. Very large batches (millions of rows) where the per-row merge overhead dominates — consider insert_overwrite instead.

Delete+insert — the "backfill by key" pattern.

• Semantics. DELETE rows matching the incoming unique_key values, then INSERT the new versions.
• Cost. Two writes per touched row; scans the existing table for matching keys.
• When to use. Backfills where you want to replace a specific set of rows (e.g., "reload day X"); rare compared to merge and insert_overwrite in production.
• When to avoid. High-volume incrementals — merge is generally faster because it's one operation not two.

Insert_overwrite — the "rewrite the partition" pattern.

• Semantics. For each partition present in the incoming data, DROP the partition on the target and INSERT the new version atomically.
• Cost. One partition drop + one partition insert. Fastest strategy for time-partitioned tables where you always rebuild yesterday's partition (or the last N partitions).
• When to use. Time-partitioned facts on BigQuery, Spark, Databricks; ETL flows that reprocess a day's worth of events at a time; any workload where idempotency at the partition level is the correctness contract.
• When to avoid. Non-partitioned tables (there's no partition to overwrite); tables where you don't reprocess entire partitions (you'd overwrite good data).

The unique_key requirement.

• For merge. Required; the key(s) that identify a row for UPSERT.
• For delete+insert. Required; the key(s) whose matching rows get deleted before insert.
• For append. Not used; nothing to key on.
• For insert_overwrite. Not used at row-level; the partition key is what gets overwritten, not a row-level unique_key.

The on_schema_change axis.

• ignore (default in some adapters). New columns in the source are silently dropped from the target.
• fail. Raises an error if schema changes; forces manual --full-refresh.
• append_new_columns. New source columns get added to the target as new columns with NULLs for older rows. The safe default for most incrementals.
• sync_all_columns. Adds new + drops removed columns. Aggressive; only for tightly-managed pipelines.

Common interview probes on incremental strategies.

• "When do you pick merge over insert_overwrite?" — merge for row-level UPSERTs, insert_overwrite for partition-level rewrites.
• "What's the unique_key requirement for each strategy?" — merge and delete+insert require it; append and insert_overwrite don't (at the row level).
• "How do you handle a new column in the source?" — on_schema_change: append_new_columns.
• "What does is_incremental() do?" — returns True inside a Jinja block when the model is running incrementally (not on first run and not with --full-refresh).

Worked example — a merge-strategy fact for event upserts

Detailed explanation. An event table has ~50M new events per day plus occasional corrections to the last 3 days of events (source system emits event_id updates when a payload is corrected). The right strategy is merge on event_id with an is_incremental() filter scoped to the last 3 days — the incremental run scans only the last 3 days of the target for possible UPDATEs, then INSERTs the new day's events.

• Setup. 500M-row event table, +50M/day, ~1M corrections/day scattered across last 3 days.
• Wrong choice. append — duplicates the corrections.
• Right choice. merge on event_id with a 3-day lookback window.

Question. Write the fct_events model with merge strategy, a 3-day lookback, and on_schema_change: append_new_columns. Explain each config knob.

Input.

Parameter	Value
Target rows	500M
New rows/day	50M
Corrections	up to 1M across last 3 days
Lookback	3 days
Unique key	event_id

Code.

-- models/marts/facts/fct_events.sql
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='event_id',
    on_schema_change='append_new_columns',
    cluster_by=['event_date', 'event_type']
) }}

WITH new_and_corrected_events AS (
  SELECT
    e.event_id,
    e.event_ts,
    cast(e.event_ts AS date)      AS event_date,
    e.customer_id,
    e.event_type,
    e.payload,
    e.updated_at
  FROM   {{ ref('stg_events') }}  e

  {% if is_incremental() %}
    -- Scope to last 3 days of source events + any late arrivals in the source
    WHERE  e.event_ts >= dateadd('day', -3, current_date())
        OR e.updated_at >= (SELECT max(updated_at) FROM {{ this }})
  {% endif %}
)

SELECT * FROM new_and_corrected_events

# First run — full refresh; builds all 500M rows
dbt run --select fct_events --full-refresh

# Every subsequent run — merges the last 3 days + late arrivals
dbt run --select fct_events

# Occasional full rebuild (e.g., schema-level change)
dbt run --select fct_events --full-refresh

Step-by-step explanation.

1. materialized='incremental' tells dbt to run CREATE TABLE ... AS SELECT on first run and MERGE INTO ... USING (...) on subsequent runs.
2. incremental_strategy='merge' picks the UPSERT strategy. dbt compiles the incremental SELECT into a MERGE INTO {{ this }} USING (<query>) src ON tgt.event_id = src.event_id WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ....
3. unique_key='event_id' is the merge key. It must be unique in the incoming batch (otherwise the merge fails with The MERGE statement attempted to UPDATE or DELETE the same row more than once). If the source has natural duplicates, add a qualify row_number() over (partition by event_id order by event_ts desc) = 1 before the SELECT.
4. is_incremental() returns True on non-first, non-full-refresh runs. The filter inside the block scopes the source scan to the last 3 days plus any rows in the source with updated_at > max(updated_at) in the target. This bounds the batch size; without it, every incremental run would scan the whole source.
5. on_schema_change='append_new_columns' handles the case where the source added a new field. On the next incremental run, dbt detects the new column, adds it to the target with NULLs for older rows, and populates it for the new batch. No manual --full-refresh needed.

Output.

Run type	Data written	Wall-clock
First run (full refresh)	500M	~5 min
Incremental (typical day)	50M new + 1M corrections	~8 s
Incremental after schema change	50M new + 1M corrections + new column	~10 s

Rule of thumb. For event streams with corrections, always use merge on the natural key. Scope is_incremental() to a bounded time window (last N days) plus a updated_at-based late-arrival filter. on_schema_change: append_new_columns is the safe default for evolving schemas.

Worked example — an insert_overwrite fact on BigQuery

Detailed explanation. BigQuery has native support for partition-level operations that map perfectly to insert_overwrite. A 5-billion-row event table partitioned by event_date should use insert_overwrite — every incremental run rewrites yesterday's partition (and maybe the last 3 for late arrivals). Merge would be more expensive because BigQuery would scan the whole clustered index for each unique_key match; insert_overwrite drops the partition and reinserts in one atomic operation.

• Setup. BigQuery table, 5B rows, partitioned by DATE(event_ts).
• Reprocessing pattern. Every night, reprocess the last 3 days.
• Right choice. insert_overwrite — atomic partition swap.

Question. Write the model with insert_overwrite on a BigQuery partitioned table. Explain the BigQuery-specific dbt config.

Input.

Parameter	Value
Warehouse	BigQuery
Target rows	5B
Partition column	event_date (DATE)
Reprocess window	last 3 days
Adapter	dbt-bigquery

Code.

-- models/marts/facts/fct_events.sql (BigQuery version)
{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={
        'field':         'event_date',
        'data_type':     'date',
        'granularity':   'day'
    },
    cluster_by=['event_type', 'customer_id']
) }}

WITH events AS (
  SELECT
    event_id,
    event_ts,
    DATE(event_ts)         AS event_date,
    customer_id,
    event_type,
    payload
  FROM {{ ref('stg_events') }}

  {% if is_incremental() %}
    -- Rewrite the last 3 partitions
    WHERE DATE(event_ts) IN UNNEST([
      DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY),
      DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),
      CURRENT_DATE()
    ])
  {% endif %}
)

SELECT * FROM events

# dbt_project.yml — for BigQuery facts default to insert_overwrite
models:
  my_project:
    marts:
      facts:
        +materialized: incremental
        +incremental_strategy: insert_overwrite
        +partition_by: {field: event_date, data_type: date, granularity: day}
        +on_schema_change: append_new_columns

Step-by-step explanation.

1. incremental_strategy='insert_overwrite' tells the BigQuery adapter to compile the model into a MERGE INTO tgt USING (...) src ON FALSE WHEN NOT MATCHED BY SOURCE AND tgt.event_date IN (...) THEN DELETE WHEN NOT MATCHED THEN INSERT .... The net effect is: for every partition present in the incoming batch, drop it on the target, then insert the new version.
2. partition_by={'field': 'event_date', 'data_type': 'date', 'granularity': 'day'} is the BigQuery-specific partitioning config. dbt uses it both to create the table with correct partitioning on first run and to compile the correct partition-drop SQL on incremental runs.
3. cluster_by=['event_type', 'customer_id'] sets BigQuery clustering keys. Together with partitioning, this gives dashboard queries fast filter pushdown.
4. The is_incremental() filter scopes the batch to exactly 3 partitions (D-2, D-1, D). insert_overwrite will drop and reinsert exactly those 3 partitions. Any older partition is untouched.
5. The critical property: idempotency. Running the incremental twice in a row produces the same target state (drop + insert the same partitions). This is stronger than merge, which requires unique keys to be truly unique across the batch.

Output.

Run type	Partitions written	Rows written	Wall-clock
First run	all 730 (2 years)	5B	~15 min
Incremental (typical day)	3 (D-2, D-1, D)	~150M	~30 s
Incremental (schema change)	3 + new column added	~150M	~35 s

Rule of thumb. For BigQuery + partitioned facts, insert_overwrite is almost always the right strategy. Merge is unnecessarily expensive on BigQuery because it scans clustered indexes per row; insert_overwrite is atomic per partition. Combine with partition_by and cluster_by for the full pattern.

Worked example — micro-batches from Kafka via dbt

Detailed explanation. A team streams Kafka events into Snowflake via Snowpipe. Events land in raw.kafka_events in batches every 60 seconds. The team wants a dbt-managed downstream fact with the last 15 minutes of freshness — dbt run --select fct_events runs every 5 minutes and merges the new events. This is the "micro-batch" pattern that dbt handles cleanly with incremental + a tight is_incremental() filter + short-cadence orchestration.

• Setup. Kafka → Snowpipe → raw.kafka_events; dbt runs every 5 min on fct_events.
• Freshness target. 5–10 minutes end-to-end.
• Right choice. Incremental merge on event_id, is_incremental() filter with a 10-minute lookback.

Question. Design the incremental model, the orchestration cadence, and the freshness contract. Show the run cost per minute.

Input.

Parameter	Value
Source	raw.kafka_events (Snowpipe-loaded)
Batch size (5-min window)	~2M rows
Freshness SLA	< 10 min
dbt cadence	every 5 min
Warehouse	Snowflake Small (auto-suspend 60s)

Code.

-- models/marts/facts/fct_events.sql (micro-batch merge)
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='event_id',
    on_schema_change='append_new_columns',
    cluster_by=['event_ts']
) }}

WITH new_events AS (
  SELECT
    event_id,
    event_ts,
    customer_id,
    event_type,
    payload,
    ingested_at
  FROM   {{ source('raw', 'kafka_events') }}

  {% if is_incremental() %}
    -- 10-minute lookback on ingested_at to catch late arrivals from Snowpipe
    WHERE ingested_at >= dateadd('minute', -10, (SELECT max(ingested_at) FROM {{ this }}))
  {% else %}
    -- First run — only last 24 hours to bound the initial load
    WHERE ingested_at >= dateadd('day', -1, current_timestamp())
  {% endif %}
)

SELECT * FROM new_events

# orchestration (dbt Cloud job or Airflow DAG) — every 5 minutes
# Airflow DAG snippet
schedule_interval: "*/5 * * * *"

# Airflow task
dbt_run_events = BashOperator(
    task_id="dbt_run_fct_events",
    bash_command="cd /opt/dbt && dbt run --select fct_events --target prod",
)

-- Downstream freshness contract test
{{ config(severity='warn') }}

SELECT
  DATEDIFF('minute', max(ingested_at), current_timestamp()) AS staleness_min
FROM {{ ref('fct_events') }}
HAVING DATEDIFF('minute', max(ingested_at), current_timestamp()) > 10

Step-by-step explanation.

1. The Kafka batches land in raw.kafka_events every 60 seconds. Snowpipe adds an ingested_at timestamp on load; that's the anchor for the is_incremental() filter.
2. Every 5 minutes, the Airflow DAG runs dbt run --select fct_events. The is_incremental() filter looks at max(ingested_at) on the target and scans the source for anything ingested in the last 10 minutes (5-minute safety margin for late arrivals).
3. The merge strategy handles the rare case where an event ID appears in two consecutive batches (Snowpipe retries). Without merge, the target would accumulate duplicates.
4. The severity='warn' freshness test runs on the same 5-min cadence; if max(ingested_at) is older than 10 minutes, it warns. Wired to Slack alerts.
5. The warehouse auto-suspends after 60s; the Small warehouse spins up in ~1s. A typical run: ~5s of compute (2M rows merged) + ~10s of orchestration overhead. Credit cost: ~1 credit per hour of dbt runs × 12 runs/hour = 12 credits × $/credit for 24h = manageable.

Output.

Metric	Value
Freshness (end-to-end)	6–10 min
Rows per micro-batch	~2M
Wall-clock per micro-batch	~15 s
Credits per hour	~12
Freshness SLA met	> 99%

Rule of thumb. dbt handles micro-batches well down to about 5-minute cadence. Below that, you're paying more in orchestration overhead than compute; use a streaming platform (Materialize, RisingWave, dbt Streaming) instead. Above 5 minutes, dbt incremental + tight is_incremental() filter + auto-suspend warehouse is the clean pattern.

Senior interview question on incremental strategies

A senior interviewer might ask: "Walk me through how you'd pick between merge, insert_overwrite, delete+insert, and append for a new fact model. What are the tie-breakers?"

Solution Using a four-question strategy selector

Four-question strategy selector — pick incremental_strategy
==========================================================

Q1: Is the source truly append-only (no corrections, no updates)?
     yes → append
     no  → Q2

Q2: Do you reprocess an entire partition at a time (whole day of events, etc)?
     yes → insert_overwrite (BigQuery, Spark, Databricks)
                            → delete+insert (Snowflake, Postgres, Redshift)
     no  → Q3

Q3: Does the source have a natural unique key (event_id, order_id)?
     yes → merge on unique_key
     no  → Q4 — no incremental; use table with a lookback filter

Q4: (fallback) — table with a `WHERE ts > current_date - N` filter for the
    lookback window; loses history beyond N days but keeps costs bounded.

# dbt_project.yml — strategy defaults by adapter
models:
  my_project:
    marts:
      facts:
        # Snowflake / Postgres / Redshift default
        +materialized: incremental
        +incremental_strategy: merge
        +unique_key: event_id
        +on_schema_change: append_new_columns

        # BigQuery / Spark / Databricks override
        # +incremental_strategy: insert_overwrite
        # +partition_by: {field: event_date, data_type: date, granularity: day}

Step-by-step trace.

Model	Q1 (append-only?)	Q2 (partition reprocess?)	Q3 (unique key?)	Decision
fct_page_views	yes (immutable)	—	—	append
fct_events (Snowflake, corrections)	no	no	yes (event_id)	merge on event_id
fct_events (BigQuery, partition-rewrite)	no	yes	—	insert_overwrite
fct_backfill_reload	no	yes (specific days)	yes (day + id)	delete+insert
fct_no_key_wide_dim	no	no	no	table with 30-day filter

Every fact model falls into one of four buckets. The Snowflake/BigQuery split is the most common — merge on Snowflake, insert_overwrite on BigQuery. The append case is rare in practice (most "append-only" sources emit corrections eventually). Delete+insert is niche — reserved for scheduled backfills of specific date ranges.

Output:

Adapter	Default strategy	Alternate
Snowflake	merge	delete+insert (backfills)
Postgres	merge	delete+insert
Redshift	merge	delete+insert
BigQuery	insert_overwrite	merge (small tables)
Spark / Databricks	insert_overwrite	merge (Delta Lake)

Why this works — concept by concept:

• Match strategy to adapter primitives — insert_overwrite compiles into fast partition-swap on BigQuery/Spark; merge compiles into an efficient MERGE INTO on Snowflake/Postgres/Redshift. Using the wrong strategy for the adapter costs 5–10× per run.
• unique_key discipline — merge and delete+insert both require unique_key. If the source has natural duplicates, dedupe with qualify row_number() over ... = 1 before the SELECT. Skipping this results in cryptic "cannot update same row twice" errors.
• is_incremental() lookback — every incremental filter should specify a lookback window (event_ts > current_date - N). Without it, incremental runs either scan the whole source (expensive) or miss late arrivals (correctness bug).
• on_schema_change — append_new_columns handles the common case (source added a field) automatically. fail is the strict alternative for tightly-managed pipelines. Never leave this at ignore in production.
• Cost — four questions per fact model, ~1 minute per decision. On a 30-fact project, ~30 minutes to lock down every strategy. The alternative — one wrong strategy on a large fact — routinely costs weeks of debugging + credit spend.

SQL
Topic — sql
SQL incremental strategy problems

Practice →

ETL Topic — etl ETL problems on micro-batch and merge

Practice →

---

5. defer, slim CI, and the fusion engine

--defer --state target/ + --select state:modified+ turn every PR into a 3-model rebuild against the production manifest

The mental model in one line: dbt defer teaches CI to resolve unchanged {{ ref('...') }} references to the production version of the model instead of building the model in CI, and state:modified+ selects only the models actually changed on this branch — combined, they collapse a 40-minute full-project CI into a 4-minute slim rebuild of just the changed subgraph. This is the single highest-leverage change any team can ship for dbt performance.

What defer actually does.

• Without defer. Every {{ ref('upstream_model') }} in a CI run compiles to <ci_schema>.upstream_model. If CI doesn't build upstream_model, the reference fails — so CI must build every ancestor of every changed model. That's the "full build in CI" default.
• With defer + --state target/prod-manifest. CI reads the production manifest (manifest.json from the last prod run). For any {{ ref('upstream_model') }} where upstream_model is not in the CI's selected set, dbt resolves the ref to <prod_schema>.upstream_model — the production version of the model.
• Result. CI builds only the changed subgraph; every unchanged upstream reference points to prod. A PR that touches 3 models rebuilds those 3, not the 220 upstreams.

What state:modified does.

• The selector. --select state:modified picks every model whose SQL, config, or dependencies changed between the current branch and the deferred manifest.json (from --state).
• The + suffix. state:modified+ extends the selection downstream — the changed models plus everything that references them. Without +, downstream tests wouldn't run.
• The + prefix. +state:modified would extend upstream (ancestors), but that defeats defer — you'd build unchanged upstreams. Rarely correct.
• state:modified.body / .configs / .persisted_descriptions. More granular selectors that ignore metadata-only changes.

The manifest lifecycle.

• Production writes the manifest. After every successful prod run, upload target/manifest.json to a shared location (S3, GCS, artifact store). This is the deferred state.
• CI downloads the manifest. Before every CI run, download the latest prod manifest to ./ci/. dbt build --state ./ci reads it.
• The freshness problem. If prod runs daily and CI runs on every PR, the manifest can be up to 24 hours stale. Usually fine; if you rename a model and merge, subsequent CI runs against the new prod manifest see the new name.

Slim CI as a pattern.

• The 4-line invocation. dbt build --select state:modified+ --defer --state ./ci --threads 8.
• The wins. Build time drops from full-project (40 min) to changed-subgraph (2–5 min). Warehouse compute drops proportionally. Iteration speed on data PRs matches iteration speed on code PRs.
• The gotchas. Sources are always "not deferred" (dbt can't defer sources); if a PR modifies a source, all downstream builds must run. Schema-differing models across dev and prod need --favor-state (favor state model even if a same-named ref exists in CI).

dbt-fusion — the Rust engine story.

• What it is. A ground-up Rust rewrite of the dbt engine (parser + compiler + graph). Announced 2024, alpha through 2025, GA in 2026.
• What it improves. Compile speed. Parsing 500 models drops from ~30s (Python) to ~2s (Rust). For CI runs where the compile step was a meaningful fraction of the total time, this is a big win.
• What it doesn't (yet) change. Runtime — the SQL that runs on the warehouse is the same; wall-clock savings are all on the parse + compile side. Full runtime savings via native model execution ("dbt runtime for Rust") are on the roadmap but not GA in 2026.
• When to adopt. Any CI where compile is > 15% of total time; parity is high with the Python engine for standard adapters. Older adapters or custom macros may need porting.

Common interview probes on defer + slim CI.

• "What's the difference between --defer and --state?" — --state points at the manifest; --defer tells dbt to resolve unchanged refs to that manifest's schema.
• "Why is state:modified+ almost always what you want?" — the + includes downstreams so tests run.
• "How stale can the deferred manifest be?" — up to one prod-run cadence; usually 24h; refresh manifest after every prod run.
• "When does defer break?" — when a model is renamed and the manifest hasn't caught up; when a source is modified; when a downstream requires a specific dev-only test fixture.

Worked example — the 4-line slim-CI invocation

Detailed explanation. The team's CI currently runs dbt build --target ci on every PR — a full 220-model, 40-minute build. Switching to slim CI is a one-day project: publish the prod manifest as an artifact, download it in CI, add --defer --state --select state:modified+ to the CI command. Median PR builds go from 40 minutes to under 5 minutes.

• Before. dbt build --target ci — 40 minutes.
• After. dbt build --select state:modified+ --defer --state ./ci --target ci — ~3 minutes median.
• Setup. Prod run publishes target/manifest.json as a workflow artifact; CI downloads it.

Question. Design the GitHub Actions workflow that (a) publishes the prod manifest after every successful prod run, (b) downloads it before every CI run, and (c) runs slim CI.

Input.

Component	Value
CI system	GitHub Actions
Artifact store	GitHub Actions artifacts (30-day retention)
Prod cadence	daily at 06:00 UTC
CI cadence	every PR

Code.

# .github/workflows/dbt-prod.yml
name: dbt Prod Build
on:
  schedule:
    - cron: "0 6 * * *"
  workflow_dispatch:

jobs:
  prod-build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: {python-version: '3.11'}

      - name: Install dbt
        run: pip install dbt-snowflake==1.9.0

      - name: dbt build (full prod)
        run: dbt build --target prod --threads 8

      - name: Upload prod manifest as artifact
        uses: actions/upload-artifact@v4
        with:
          name: dbt-prod-manifest
          path: target/manifest.json
          retention-days: 30

# .github/workflows/dbt-ci.yml
name: dbt CI (slim)
on:
  pull_request:
    branches: [main]

jobs:
  slim-ci:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: {python-version: '3.11'}

      - name: Install dbt
        run: pip install dbt-snowflake==1.9.0

      - name: Download latest prod manifest
        uses: dawidd6/action-download-artifact@v3
        with:
          workflow: dbt-prod.yml
          name: dbt-prod-manifest
          path: ./ci
          workflow_conclusion: success

      - name: dbt build (slim CI)
        env:
          PR_NUMBER: ${{ github.event.number }}
        run: |
          dbt build \
            --select state:modified+ \
            --defer \
            --state ./ci \
            --target ci \
            --threads 8

Step-by-step explanation.

1. The dbt-prod.yml workflow runs daily at 06:00 UTC. After a successful dbt build --target prod, it uploads target/manifest.json as a workflow artifact called dbt-prod-manifest. Retention 30 days.
2. The dbt-ci.yml workflow triggers on every PR. It downloads the latest successful dbt-prod-manifest via the download-artifact action into ./ci/.
3. The slim CI command runs dbt build --select state:modified+ --defer --state ./ci. --state ./ci tells dbt to read the manifest from ./ci/manifest.json. --defer tells dbt to resolve unchanged refs to the schema in that manifest (which is the prod schema).
4. --select state:modified+ diffs the current branch's manifest against the prod manifest. Only models with SQL/config/dep changes plus their downstreams are selected. Median PR selects 2–5 models.
5. Each CI PR gets its own schema (PR_123, PR_124) via env_var('PR_NUMBER') in profiles.yml. Isolation between concurrent PRs is free; cleanup runs weekly via a scheduled DROP SCHEMA job.

Output.

Metric	Full CI	Slim CI
Models built per PR	220	3 (median)
Wall-clock	40 min	3 min
Warehouse credits	~40	~3
Iteration speed	slow	code-PR speed

Rule of thumb. Every dbt team should be on slim CI. The setup is one day of engineering; the payback is every future PR forever. If your team is still running full CI, that's the highest-priority dbt performance change on your backlog.

Worked example — the schema-modified vs body-modified split

Detailed explanation. state:modified triggers on any change — SQL, config, or metadata. Sometimes you want to run CI only when the SQL body changed (a comment tweak or a description change shouldn't rebuild the model). state:modified.body is the finer-grained selector for that case.

• Setup. state:modified picks up everything (including comment-only changes).
• Finer selector. state:modified.body picks up only SQL body changes.
• Even finer. state:modified.body,state:modified.configs picks body + config, not metadata.

Question. Design a CI that runs a full build on SQL changes but skips rebuilds on metadata-only changes. Show the selector.

Input.

Change	state:modified	state:modified.body	state:modified.configs
SQL body changed	yes	yes	no
materialized: changed	yes	no	yes
Description changed	yes	no	no
Test added	yes	no	no

Code.

# Old CI — rebuilds on any change including comment tweaks
dbt build --select state:modified+ --defer --state ./ci

# New CI — rebuilds only on body or config changes
dbt build \
    --select state:modified.body+ state:modified.configs+ \
    --defer \
    --state ./ci

# For test-only changes, run only the tests without rebuilding the model
dbt test --select state:modified.tests --defer --state ./ci

# GitHub Actions — separate jobs by change type
jobs:
  build-and-test:
    steps:
      - name: Slim build (SQL / config changes only)
        run: |
          dbt build \
            --select "state:modified.body+" "state:modified.configs+" \
            --defer --state ./ci --target ci

      - name: Test-only run (metadata / test additions)
        run: |
          dbt test \
            --select "state:modified.tests" \
            --defer --state ./ci --target ci

Step-by-step explanation.

1. state:modified.body matches models whose SQL file content changed (any character change in the .sql file).
2. state:modified.configs matches models whose config (materialized, unique_key, partition_by, etc.) changed. Independent of SQL body.
3. state:modified.tests matches tests (schema.yml tests: sections, .sql singular tests) that were added or changed. Doesn't rebuild the model, but runs the new tests.
4. Splitting the CI into two jobs — build-and-test on body/config changes; test-only on test additions — means a PR that only adds a not_null test to an existing model doesn't rebuild the model. Fast, cheap.
5. In practice most teams use the combined selector state:modified.body+ state:modified.configs+ as the CI default and skip the test-only branch — the overhead of splitting is only worth it when your team frequently adds tests to existing models.

Output.

PR type	Old CI	New CI (split)
SQL body change	rebuilds model	rebuilds model
Config change	rebuilds model	rebuilds model
Comment / description change	rebuilds model	skip
Test-only addition	rebuilds model	runs test only

Rule of thumb. For most teams, state:modified.body+ state:modified.configs+ is the sane default — skip rebuilds on pure metadata changes. Reserve the test-only branch for teams that specifically want fast turnaround on test additions.

Worked example — dbt-fusion for compile speed

Detailed explanation. The Rust engine cuts parsing + compilation time. For a 500-model project, Python's parse can be 30s; fusion's is ~2s. On slim CI runs where the actual model build is only ~30s (3 models), a 30s compile is 50% of the total time. Fusion drops that to essentially zero — a 60s CI becomes a 30s CI.

• Setup. 500 models, slim CI builds 3 models per PR.
• Compile time. Python: 30s. Fusion: 2s.
• Model runtime. Same in both — the SQL runs on the warehouse.

Question. Show the setup for dbt-fusion adoption and quantify the CI speed-up for a typical slim-CI PR.

Input.

Metric	Python engine	Fusion engine
Parse + compile (500 models)	30 s	2 s
Model runtime (3 models via slim CI)	30 s	30 s
CI total wall-clock	60 s	32 s

Code.

# Install dbt-fusion (2026 release channel)
pip install dbt-fusion==2.0.0 dbt-snowflake==2.0.0

# Run identical dbt commands — fusion is the default engine when installed
dbt build \
    --select state:modified+ \
    --defer \
    --state ./ci \
    --target ci

# GitHub Actions — pin the fusion engine version
jobs:
  slim-ci:
    steps:
      - name: Install dbt-fusion
        run: |
          pip install \
            dbt-fusion==2.0.0 \
            dbt-snowflake==2.0.0

      - name: Verify fusion engine active
        run: dbt --version | grep -i fusion

Compile-time comparison — 500-model project
============================================

Python engine (dbt-core 1.9):
  Parse manifest      12 s
  Compile 500 models  18 s
  Total compile       30 s

Fusion engine (dbt-fusion 2.0):
  Parse manifest       1 s
  Compile 500 models   1 s
  Total compile        2 s

CI PR (3 models via slim CI):
  Python: 30 s compile + 30 s runtime = 60 s
  Fusion: 2 s compile + 30 s runtime = 32 s

Step-by-step explanation.

1. Installing dbt-fusion alongside dbt-snowflake (or another adapter) makes fusion the default engine. Existing dbt commands work unchanged.
2. Parse manifest goes from 12s to 1s. This is where fusion shines — the Rust parser walks the model graph, resolves refs, and builds the manifest in a fraction of the time the Python parser needs.
3. Compile 500 models goes from 18s to 1s. Compilation is Jinja rendering + macro expansion + SQL string interpolation; the Rust engine parallelises across cores.
4. Model runtime is unchanged — the compiled SQL runs on the warehouse. Fusion doesn't (yet) speed up the actual MERGE INTO ... or CREATE TABLE AS SELECT .... That's on the roadmap but not GA.
5. For slim CI, where 3-model runtime is often ~30s and compile is ~30s, halving the total wall-clock via fusion is a real win. On full builds where model runtime dominates, the win is smaller (from 40 min to 39 min 30 sec).

Output.

Scenario	Python	Fusion	Speed-up
Slim CI (3 models)	60 s	32 s	~2×
Full build (220 models)	40 min 30 s	40 min 2 s	~0%
dbt list	15 s	1 s	15×
dbt parse	12 s	1 s	12×

Rule of thumb. Adopt dbt-fusion when CI compile time is a meaningful fraction of your total CI budget. For projects with 200+ models on slim CI, the speed-up is meaningful. For full-build workloads where the warehouse dominates, fusion is a nice-to-have, not a must-have. Verify adapter compatibility for anything non-standard.

Senior interview question on defer, slim CI, and fusion

A senior interviewer might ask: "You inherit a dbt project with a 40-minute CI and no defer. Walk me through the rollout plan for slim CI, the gotchas you'd anticipate, and where you'd land on dbt-fusion."

Solution Using a three-phase slim-CI + fusion rollout

Three-phase rollout — slim CI + fusion adoption
================================================

Phase 1 — Manifest publishing (week 1)
  - Prod workflow uploads target/manifest.json as artifact after every prod run
  - Retention: 30 days
  - Verify: manifest downloadable from CI account

Phase 2 — Slim CI enablement (week 2)
  - CI workflow downloads latest prod manifest to ./ci/
  - Add --defer --state ./ci --select state:modified+ to dbt build command
  - Isolated per-PR schema via env_var('PR_NUMBER')
  - Weekly cleanup of stale PR schemas
  - Alert if state:modified+ selects > 50 models (usually means bad ref manifest)

Phase 3 — Fusion adoption (week 3, optional)
  - Install dbt-fusion alongside dbt-snowflake
  - Verify --version reports fusion engine
  - Compare CI wall-clock before/after
  - Roll back if adapter parity gaps found (custom macros, non-standard adapters)

Gotchas to anticipate
  - Renamed models: state:modified selects both old + new; usually fine, but check ancestor chain
  - Source changes: --defer doesn't help; downstream builds run against dev-loaded source
  - Manifest freshness: nightly prod cadence = up to 24h stale manifest; usually fine
  - Long-lived branches: manifest drift may accumulate; refresh weekly
  - Test-only changes: separate --select state:modified.tests if worth splitting

# Final CI config after rollout
name: dbt CI (slim + fusion)
on:
  pull_request:
    branches: [main]

jobs:
  slim-ci:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: {python-version: '3.11'}

      - name: Install dbt + fusion
        run: pip install dbt-fusion==2.0.0 dbt-snowflake==2.0.0

      - name: Download latest prod manifest
        uses: dawidd6/action-download-artifact@v3
        with:
          workflow: dbt-prod.yml
          name: dbt-prod-manifest
          path: ./ci
          workflow_conclusion: success

      - name: dbt build (slim CI)
        env:
          PR_NUMBER: ${{ github.event.number }}
        run: |
          dbt build \
            --select state:modified+ \
            --defer \
            --state ./ci \
            --target ci \
            --threads 8

      - name: Warn on wide changesets
        run: |
          COUNT=$(dbt ls --select state:modified+ --state ./ci --output name | wc -l)
          if [ "$COUNT" -gt 50 ]; then
            echo "::warning::state:modified+ selected $COUNT models — is the manifest stale?"
          fi

Step-by-step trace.

Phase	Activity	Duration	Wall-clock impact
1	Manifest publishing	1 day	none yet
2	Slim CI enablement	3 days	40 min → 3 min
3	Fusion adoption	2 days	3 min → 32 s
—	Gotcha handling	ongoing	maintain wins

After phase 2, the team has slim CI. Median PR CI drops from 40 minutes to ~3 minutes. After phase 3, fusion drops it further to ~30 seconds. The full rollout is one senior-engineer-week; the payback is every future PR forever.

Output:

Metric	Before	Phase 2	Phase 3
CI wall-clock (median PR)	40 min	3 min	32 s
Models built per PR	220	3	3
Warehouse credits per PR	40	3	3
Compile time	30 s	30 s	2 s
Iteration speed	painful	fast	matches code PRs

Why this works — concept by concept:

• Defer plus state:modified is the compound — defer alone helps only if you also select the changed subgraph; state:modified alone fails if any changed model has an unbuilt upstream. Together they collapse CI to the minimum viable rebuild.
• Manifest as the deferred state — the prod manifest.json encodes both the graph and the schema mapping. --state ./ci reads it; --defer uses it. No custom infrastructure required beyond artifact publishing.
• Per-PR schema isolation — env-driven schemas (PR_{{ env_var('PR_NUMBER') }}) mean concurrent PRs don't collide. Snowflake handles thousands of schemas cheaply; weekly cleanup keeps the count bounded.
• Fusion for compile — the Rust engine cuts parse + compile time by 10–15×. On slim CI where model runtime is short, this halves total wall-clock. Full-build workloads see smaller wins.
• Cost — one senior-engineer-week for the full three-phase rollout. Every future PR CI is 10–20× faster; the payback is measured in weeks, not months.

SQL
Topic — sql
SQL dbt CI and defer problems

Practice →

Optimization
Topic — optimization
Optimization problems on slim CI and state selection

Practice →

---

Cheat sheet — dbt performance recipes

• First principle. dbt performance is a graph problem, a materialization problem, and a defer discipline problem — in that order of impact. Warehouse tuning is the fourth axis, not the first. If your build is slow, audit materializations before touching Snowflake or BigQuery configs.
• Threads formula. threads = MAX_CONCURRENCY_LEVEL × max_cluster_count on Snowflake; threads = concurrent_slot_budget / avg_slots_per_query on BigQuery. Never set threads higher than warehouse concurrency — you pay for queue depth in warehouse metadata for zero speed gain.
• Warehouse concurrency reference. Snowflake X-Small / Small / Medium / Large / X-Large all default to MAX_CONCURRENCY_LEVEL = 8. Multi-cluster warehouses multiply concurrency; bigger size multiplies compute per query. Use cluster count for bursty peaks, size for individually-slow queries.
• Materialization decision matrix. Passthrough → view; cheap intermediate with ≤ 3 downstreams → ephemeral; small dim → table; event stream with time filter → incremental; expensive intermediate with 4+ downstreams → table (override); reporting leaf queried > 10×/day → table with refresh schedule.
• Incremental strategy picker. Truly append-only → append; partition-level reprocess on BigQuery/Spark → insert_overwrite; UPSERT on Snowflake/Postgres/Redshift → merge; scheduled backfill of specific keys → delete+insert. Fallback if no unique key → table with lookback filter.
• is_incremental() lookback pattern. Always include a bounded time window: WHERE event_ts >= dateadd('day', -3, current_date()) or WHERE ingested_at > (SELECT max(ingested_at) FROM {{ this }}). Without it, incremental runs either scan the whole source or miss late arrivals.
• on_schema_change default. append_new_columns for evolving sources — new columns auto-added with NULLs for older rows. fail for tightly-managed pipelines that require manual review. Never leave at ignore in production.
• defer + slim CI 4-line invocation. dbt build --select state:modified+ --defer --state ./ci --threads 8 --target ci. Median PR builds 2–5 models instead of 220. Ships in one day; pays back on every future PR.
• Manifest lifecycle. Every successful prod run uploads target/manifest.json as an artifact. Every CI run downloads the latest to ./ci/. Freshness < 24 hours is fine; refresh weekly on long-lived branches.
• state:modified variants. state:modified+ (default — changed + downstream); state:modified.body+ (SQL only, skip metadata); state:modified.configs+ (config changes); state:modified.tests (test additions only). Combine as needed.
• Critical-path attack. dbt ls --select "+fct_leaf_model" --output name prints the chain. Attack the longest node — incrementalise, refactor, or denormalise. No parallelism beats the critical path.
• Query-tag discipline. Set query_tag: "dbt:{{ target.name }}:{{ invocation_id }}" in profiles.yml. Snowflake QUERY_HISTORY filters cleanly on tag; you can attribute every credit to a specific dbt run.
• dbt-fusion adoption checklist. (1) 200+ models in project; (2) slim CI already enabled; (3) compile > 15% of CI wall-clock; (4) all adapters standard (no custom compilers). If all four are true, fusion halves CI wall-clock. If not, fusion is a nice-to-have.
• Warehouse isolation pattern. dedicated DBT_PROD_WH (Medium), DBT_CI_WH (X-Small, auto-suspend 60s), DASHBOARD_WH (Medium multi-cluster). Isolation buys workload separation; auto-suspend keeps idle warehouses free.
• Credit-first tuning. For every model in the top 10 by scan volume, ask "can this be incremental?" and "does the reader really need every row?" The credit answer often dominates the wall-clock answer at scale.

Frequently asked questions

How do I speed up a slow dbt project without buying a bigger warehouse?

Ninety percent of the time, dbt performance gains come from three changes that cost zero warehouse credits. First, audit materializations — every table model with a natural time filter is an incremental candidate; every intermediate with 1–3 downstreams is an ephemeral candidate; every dashboard-fed leaf queried more than ~10 times a day is a table candidate instead of a view. Second, add defer + state:modified+ to CI — a median PR should build 2–5 models, not the whole project. Third, right-size threads to match warehouse concurrency (MAX_CONCURRENCY_LEVEL on Snowflake, slot budget on BigQuery) — never set threads higher than concurrency. Only after those three should you consider bigger warehouses or multi-cluster. In practice, this refactor cuts wall-clock 60–80% on projects that never touched materialization strategy or defer.

table vs view vs incremental vs ephemeral — when do I pick each?

View for thin passthrough or rename models (staging layer default); the build is nearly free and the reader's cost is bounded by the source's own layout. Ephemeral for cheap intermediate transformations referenced by 1–3 downstreams; dbt inlines them as CTEs so there's no warehouse artifact. Table for slow-changing dimensions, small facts, dashboard leaves queried repeatedly, and expensive intermediates fanned out to 4+ downstreams — you pay the build cost once and reader cost is minimal. Incremental for fact / event streams larger than ~50M rows with a natural time filter; scoping is_incremental() to the last N days makes runs 20–100× faster than a full-refresh table. The wrong choice on a large model costs orders of magnitude more warehouse spend than it needs to; the right choice is usually obvious once you count downstream references and match the row size to the layer.

What is dbt defer and how does it work with state:modified?

--defer teaches dbt that any {{ ref('upstream_model') }} where upstream_model is not in the current run's selected set should resolve to that model's production schema, not the current target's schema. --state ./ci tells dbt where to find the deferred manifest.json (typically the last successful prod build's manifest, published as a CI artifact). --select state:modified+ selects only the models whose SQL, config, or dependencies changed on this branch plus their downstream lineage. Combined, the 4-line invocation dbt build --select state:modified+ --defer --state ./ci --target ci rebuilds only the changed subgraph in CI — every unchanged upstream reference reads the production version. A PR touching 3 models builds 3, not 220; CI drops from 40 minutes to 3.

Slim CI vs full build — what are the trade-offs?

Slim CI (state:modified+ --defer) builds only the changed subgraph — fast, cheap, and matches how code CIs work. The trade-offs: (1) unchanged upstream models are read from prod at their production schema, so any dev-only test fixtures or dev-only source data won't apply to references crossing into prod; (2) source-modified changes can't be deferred (dbt has no way to defer a source), so downstream builds run against the dev-loaded source; (3) the manifest can be up to 24 hours stale, occasionally causing "phantom" model selections that surprise the reviewer. Full build rebuilds the entire project every CI run — safest and most expensive; useful for release branches and pre-production merge queues. Most teams run slim CI on PR builds and a nightly full build for regression coverage — the pair covers both speed and correctness.

Does dbt-fusion make everything faster?

Fusion is the Rust rewrite of the dbt engine (parse + compile + graph). It makes compile faster — parsing a 500-model project drops from ~30 s (Python) to ~2 s (Rust). It does not make model runtime faster in 2026 — the SQL that runs on the warehouse is unchanged. The wins land on workloads where compile is a meaningful fraction of total CI time, typically slim CI on projects with 200+ models: a 60-second CI can become a 32-second CI. On full-build workloads (40 min for 220 models), fusion saves ~30 s of compile — a nice-to-have, not a game-changer. Adopt fusion after slim CI is in place and after you've verified adapter parity for any custom macros or non-standard adapters.

How many threads should I set in profiles.yml?

Match threads to your warehouse's concurrent-query capacity. Snowflake: threads = MAX_CONCURRENCY_LEVEL × max_cluster_count — for a single Medium warehouse that's 8 (single cluster, default concurrency); for a multi-cluster warehouse with MAX_CLUSTER_COUNT = 3, it's 24. BigQuery: threads ≈ concurrent_slot_budget / avg_slots_per_query — 500 reserved slots and ~50 slots/query means safe threads = 10. Redshift: threads = concurrent-query limit of the WLM queue. Postgres via a pool: threads = pool size minus admin headroom. Setting threads higher than the warehouse can absorb just puts queries in the warehouse queue — same wall-clock, harder to observe. Setting threads lower leaves warehouse capacity idle. When in doubt, start at 8 (matches most Snowflake single-cluster warehouses) and profile with SHOW WAREHOUSES / QUERY_HISTORY to see whether the warehouse is queuing or idle.

Practice on PipeCode

• Drill the SQL practice library → for the incremental-strategy, merge-key, and window-function patterns senior dbt interviewers probe.
• Rehearse on the ETL practice library → for the micro-batch, late-arrival, and partition-rewrite patterns that motivate dbt performance tuning in production.
• Sharpen the tuning axis with the optimization practice library → for the critical-path, materialization-choice, and slim-CI problems that appear on every senior analytics-engineering loop.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the materialization + defer intuition against real graded inputs.

Lock in dbt performance muscle memory

dbt docs explain the flags. PipeCode drills explain the decision — when merge beats insert_overwrite, when ephemeral beats table, when slim CI plus defer plus fusion cuts a 45-minute build to under a minute. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior analytics engineers actually face.

Practice SQL problems →
Practice optimization problems →