Performance and Query Optimization in dbt

You Cannot Tune a Model You Haven't Measured

Performance tuning starts with identifying which models are actually slow and actually expensive — not the models that feel like they should be slow, and not the model someone complained about last quarter. dbt gives you two independent sources of truth here, and they answer slightly different questions: the per-model timing dbt itself reports during a run, and the warehouse's own query history, which dbt makes searchable by automatically tagging every query it issues.

Per-model timing in dbt run output

Every dbt run or dbt build reports how long each individual model took to build, right in its console output. This is the first and cheapest place to look — no warehouse console, no separate query, just reading the output you already get from a normal run.

$ dbt run --select finance

Running with dbt=1.8.3
Concurrency: 4 threads (target='prod')

1 of 6 START sql view model finance.stg_stripe__payments ... [RUN]
1 of 6 OK created sql view model finance.stg_stripe__payments  [SUCCESS 1 in 0.84s]
2 of 6 START sql table model finance.int_payments_joined ... [RUN]
2 of 6 OK created sql table model finance.int_payments_joined  [SUCCESS 1 in 4.21s]
3 of 6 START sql table model finance.fct_revenue ........... [RUN]
3 of 6 OK created sql table model finance.fct_revenue ...... [SUCCESS 1 in 187.63s]
4 of 6 START sql table model finance.fct_payments .......... [RUN]
4 of 6 OK created sql table model finance.fct_payments ..... [SUCCESS 1 in 6.02s]

Finished running 6 models in 0 hours 3 minutes and 21.44 seconds.

fct_revenue at 187 seconds against everything else finishing in single digits is an immediate, unambiguous signal — that one model dominates this run's total time, and it is the first place to look before touching anything else. This kind of output-scanning is cheap enough to make a habit of after every production run, not just when someone complains.

Warehouse query history, filtered by dbt's automatic query tags

dbt run output tells you how long a model took during that specific invocation, but it doesn't tell you about compute cost, how a model's runtime compares over time, or how it behaves under real production concurrency rather than a single interactive run. For that, dbt automatically attaches a structured comment to every query it sends — including the invocation ID, the model's name, and which command triggered it — which most warehouses surface directly in their query history, making it possible to filter and aggregate dbt's query history the same way you would any other workload.

/* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "analytics",
     "target_name": "prod", "node_id": "model.my_project.fct_revenue"} */
create or replace table analytics.finance.fct_revenue as (
    select ...
)

select
    regexp_substr(query_text, '"node_id": *"([^"]+)"', 1, 1, 'e') as dbt_node_id,
    count(*) as run_count,
    avg(total_elapsed_time) as avg_ms,
    sum(credits_used_cloud_services) as total_credits
from snowflake.account_usage.query_history
where query_text ilike '%"app": "dbt"%'
  and start_time > dateadd('day', -30, current_timestamp())
group by 1
order by avg_ms desc
limit 20

This query answers a materially different question than the console output does: not "how long did this model take just now," but "which models have been the most expensive, on average, over the last month of production runs" — the right lens for prioritizing tuning effort, since a model that is occasionally slow due to a one-off warehouse hiccup is a very different problem from a model that is reliably, consistently expensive every single day.

A View Queried Constantly by BI Is Often Better as a Table

Earlier modules in this track covered view versus table as a correctness and freshness decision — a view is always current, a table is a point-in-time snapshot from the last run. Performance tuning revisits the same choice through a different lens entirely: not "which one is correct for this model's freshness needs," but "which one minimizes total compute spent, once you account for every query that will ever hit this model, not just the cost of building it."

A view has zero build cost and recomputes its full defining query on every single downstream query. A table has a real build cost, paid once per dbt run, and then serves every downstream query cheaply, as a plain read of pre-computed data. Whether a view or a table wins on total cost depends entirely on the ratio between how often a model is rebuilt and how often it's queried downstream.

Let:
  B = cost to build the model's full query once
  Q = cost to read a pre-built table once (much cheaper than B, typically)
  N = number of downstream queries between rebuilds

As a VIEW:  total cost ≈ N × B     (every query re-runs the full query)
As a TABLE: total cost ≈ B + N × Q  (one build, then N cheap reads)

If a BI dashboard refreshes this model's view 200 times a day, and B
is a 20-second join+aggregation, that's ~4,000 seconds of recomputed
warehouse compute daily for a model that only actually changes once,
on the nightly dbt run. Materializing it as a table instead: one
20-second build, then 200 cheap reads of a pre-computed table.

This is exactly the scenario Part 01's Callout flagged: a cheap-to-build model can still be an expensive line item in a warehouse bill if it's queried often enough as a view. The fix is almost always the same one-line change — flip materialized from the unconfigured view default to table — but knowing to look for this pattern requires connecting query-history data (Part 01) to the materialization decision, rather than assuming a model's build time alone tells the whole cost story.

merge, delete+insert, and append Are Also a Performance Decision, Not Just a Correctness One

An earlier module in this track covered incremental_strategy purely as a correctness question: does this model's data ever get updated after it first lands, and if so, does the warehouse support an efficient native MERGE. That framing is correct, but it leaves out a real performance dimension worth tuning deliberately once correctness is settled: among the strategies that are all individually correct for a given model, they are not equally fast, and the gap between them widens dramatically as batch size and match-rate change.

merge's cost is driven by the size of the join between the incoming batch and the existing table — specifically, how efficiently the warehouse can locate matching keys. On a table that isn't clustered or sorted in a way that aligns with the merge key, that join can require scanning far more of the existing table than the incoming batch's size alone would suggest.delete+insert pays a similar cost on its delete step, plus the overhead of two separate statements instead of one. append avoids the matching cost entirely, at the cost of never checking for or handling duplicates — which is exactly why Part 03's correctness framing in the earlier module restricts it to genuinely immutable data.

The practical performance tuning move here is not usually switching strategies — correctness constraints from the earlier module still apply — but making the chosen strategy cheaper to execute, most commonly by ensuring the underlying table is clustered or sorted on the same column the merge or delete condition filters and matches on, which is exactly what Part 04's warehouse-specific config passthrough addresses directly.

cluster_by and Friends — Warehouse-Native Performance Knobs, Configured Directly in dbt

dbt's config() block isn't limited to the handful of adapter-agnostic keys covered elsewhere in this track — materialized, unique_key,incremental_strategy. Every adapter also exposes warehouse-native performance configs directly through the same config() block, letting a model's file be the single place that controls both its dbt-level behavior and its underlying physical storage layout on the warehouse.

On Snowflake, the most commonly used one is cluster_by, which sets a clustering key on the resulting table — a physical hint to Snowflake about how to co-locate rows on disk so that queries filtering or joining on the clustered column can skip scanning irrelevant micro-partitions entirely, rather than scanning the whole table and filtering after the fact.

{{
  config(
    materialized='incremental',
    unique_key='payment_id',
    incremental_strategy='merge',
    cluster_by=['revenue_date']
  )
}}

select
    payment_id,
    order_id,
    customer_id,
    amount_cents,
    revenue_date
from {{ ref('int_payments_joined_to_orders') }}

{% if is_incremental() %}
  where revenue_date > (select max(revenue_date) from {{ this }})
{% endif %}

This one config line means every downstream query filtering on revenue_date — which, for a revenue fact table, is nearly every query a BI dashboard runs — can prune most of the table's micro-partitions before scanning a single row. It's also directly relevant to Part 03's incremental performance point: a merge or delete+insert whose match condition is scoped byrevenue_date benefits from exactly the same clustering, since the warehouse can use the same physical layout to narrow which micro-partitions the merge's join needs to touch.

{{
  config(
    materialized='incremental',
    partition_by={'field': 'revenue_date', 'data_type': 'date'},
    cluster_by=['customer_id']
  )
}}

select ...

BigQuery's partition_by is a stronger guarantee than Snowflake'scluster_by — a query filtering on a partitioned column can skip entire partitions' worth of storage before billing for any bytes scanned at all, which on BigQuery's bytes-scanned pricing model translates directly into dollars saved, not just wall-clock time.cluster_by on top of that further sorts rows within each partition, helping a query that also filters on customer_id narrow further within whichever partitions it does scan.

A Full Rebuild Is Rare But Expensive — Make It Cheaper When It Has to Happen

The earlier incremental models module covers when a full-refresh is necessary — a changedunique_key, a fixed incremental filter, a deliberate backfill. From a pure performance angle, the goal is different: given that a full-refresh is occasionally unavoidable, how do you make that specific, expensive run as cheap as possible when it does happen, since it is by construction the single most expensive operation any incremental model ever performs.

Scoping a full-refresh to only the models that actually need it

The most common and avoidable mistake is running dbt run --full-refresh against an entire project, or an entire tag, when only one model's logic actually changed. Every other incremental model in that selection gets rebuilt completely from scratch for no reason at all — pure wasted compute.

# Expensive: rebuilds EVERY incremental model in the finance tag
# from scratch, even the 40 models whose logic didn't change at all
dbt run --full-refresh --select tag:finance

# Correct: rebuilds only the one model whose incremental logic
# actually changed
dbt run --full-refresh --select fct_revenue

Using warehouse-native cloning to avoid recomputation entirely, where available

On warehouses that support zero-copy cloning — Snowflake's CLONE being the most widely used — a full-refresh triggered purely to reset a table's state (rather than to genuinely recompute every row under changed logic) can sometimes be replaced with a much cheaper clone-based reset, though this is a manual, warehouse-level operation outside dbt's own full-refresh mechanism rather than a dbt config. It's worth knowing about as an option specifically for the "I need to reset this table to a known good state" case, distinct from "I need to recompute every row because the transformation logic changed," which genuinely does require a real rebuild.

Splitting a full-refresh into date-bounded chunks for very large tables

For a table too large to comfortably full-refresh in one shot — where a single rebuild risks a warehouse timeout, or simply an unacceptably long maintenance window — a manual, chunked rebuild processes the historical range in bounded slices, trading a longer total wall-clock time for a bounded, predictable cost and risk per chunk, rather than one enormous all-or-nothing operation.

-- Rather than one dbt run --full-refresh spanning 3 years of history
-- in a single operation, some teams add a dbt variable-driven date
-- range and invoke the model repeatedly across bounded chunks:

{% if var('backfill_start_date', none) %}
  where order_date >= '{{ var("backfill_start_date") }}'
    and order_date <  '{{ var("backfill_end_date") }}'
{% endif %}

# Then run it in bounded slices, each a manageable, independently
# resumable unit of work instead of one multi-hour operation:
dbt run --select fct_revenue --vars '{"backfill_start_date": "2023-01-01", "backfill_end_date": "2023-04-01"}'
dbt run --select fct_revenue --vars '{"backfill_start_date": "2023-04-01", "backfill_end_date": "2023-07-01"}'
# ... continuing forward in bounded quarters

snowflake_warehouse — Routing Heavy Models to a Bigger Compute Cluster, Cheap Models to a Smaller One

Most warehouses let you provision compute at different sizes — Snowflake calls them "warehouses," essentially independently-sized compute clusters that can be started, stopped, and billed separately. A common mistake is running an entire dbt project against one single warehouse size, sized for whatever the heaviest model needs — which means every cheap, thin staging model pays for compute capacity it never actually uses, and a genuinely heavy model may still be under-provisioned if the single warehouse size was a compromise across very different workloads.

dbt's snowflake_warehouse config lets an individual model override which Snowflake warehouse it runs against, right in its own config() block — the same mechanism used for materialization or clustering, applied to compute sizing instead.

{{
  config(
    materialized='incremental',
    unique_key='payment_id',
    incremental_strategy='merge',
    cluster_by=['revenue_date'],
    snowflake_warehouse='transform_large_wh'
  )
}}

select ...

{{
  config(
    materialized='view',
    snowflake_warehouse='transform_small_wh'
  )
}}

select ...

This lets a project's compute spend track its actual workload shape: a small handful of genuinely heavy mart-level joins and aggregations run against a larger, more expensive-per-second warehouse only while they're actually running, while the much larger number of thin staging and simple intermediate models run against a small, cheap warehouse that suits their actual resource needs — rather than every model in the project being billed at whatever size the heaviest model requires.

A project-level default, set once in dbt_project.yml under the models block for a whole directory (mirroring the pattern from the earlier project-structure module), is the practical way to apply this at scale rather than configuring every model file individually — most marts route to a "large" warehouse by directory default, most staging models route to a "small" one, and only genuinely unusual individual models need an explicit per-model override on top of that default.

models:
  my_project:
    staging:
      +snowflake_warehouse: transform_small_wh
    intermediate:
      +snowflake_warehouse: transform_small_wh
    marts:
      +snowflake_warehouse: transform_large_wh

--threads: Running Independent Branches of the DAG Concurrently

dbt does not build models one at a time by default. The --threads flag (or thethreads setting in a profile) controls how many models dbt attempts to build concurrently, and it does so with full respect for the DAG's dependency order: a model never starts building until every model it depends on has finished. What --threads actually buys you is running independent branches of that DAG — parts of the graph with no dependency relationship to each other — at the same time, rather than needlessly serializing work that has no reason to wait.

stg_stripe__payments  ─┐
                        ├──> int_payments_joined_to_orders ──> fct_revenue
stg_app__orders       ──┘

stg_marketing__campaigns ─┐
                           ├──> int_orders_attributed_to_campaigns ──> fct_campaign_roi
stg_app__orders (shared) ──┘

# The finance branch (payments/orders -> revenue) and the marketing
# branch (campaigns/orders -> campaign ROI) share stg_app__orders as
# a common input, but otherwise have NO dependency relationship to
# each other. With threads > 1, dbt can build both branches'
# independent portions concurrently once their shared input is ready.

With --threads 1, dbt builds strictly one model at a time, in a single valid topological order — correct, but leaving any real concurrency opportunity in the DAG completely unused. Raising --threads to 4 or 8 lets dbt build up to that many models simultaneously, provided the DAG has that many models simultaneously eligible to run (meaning all of their own upstream dependencies have already finished).

# profiles.yml
my_project:
  target: prod
  outputs:
    prod:
      type: snowflake
      threads: 8
      ...

# Or override for one specific invocation:
dbt run --threads 16

A practical way to find the right thread count for a given project and warehouse: start at a moderate value like 4, watch whether the warehouse's own concurrency limits or query queueing become a bottleneck as you raise it, and stop increasing once total wall-clock time for a fulldbt build stops improving — the point past which more threads just means more queries contending for the same underlying compute rather than genuinely running faster in parallel.

fct_revenue: From 187 Seconds and a Full-Table Scan to 9 Seconds

Returning to the exact model flagged in Part 01's console output —fct_revenue taking 187 seconds against a two-billion-row underlying payments history — here is the specific, staged sequence of dbt-level changes that were applied, and the measured improvement each one produced, using the warehouse query-history technique from Part 01 to confirm the gains rather than assuming they worked.

Starting point: the model as originally written

{{ config(materialized='table') }}

select
    payment_id,
    order_id,
    customer_id,
    amount_cents,
    revenue_date
from {{ ref('int_payments_joined_to_orders') }}
where counts_as_revenue

Three separate problems were compounding here, matched directly against Parts 02, 03, and 04 of this module: the model was a full table rebuild on every single run rather than incremental, meaning all two billion historical rows were recomputed fromint_payments_joined_to_orders every time, regardless of how few rows had actually changed; there was no clustering key at all, so even the full rebuild's underlying scan of the intermediate model gained nothing from Snowflake's micro-partition pruning; and the model ran against the same shared, moderately-sized warehouse as every other model in the project, competing for the same compute during the nightly run's busiest window.

Change 1 — incremental materialization

{{
  config(
    materialized='incremental',
    unique_key='payment_id',
    incremental_strategy='merge'
  )
}}

select
    payment_id,
    order_id,
    customer_id,
    amount_cents,
    revenue_date
from {{ ref('int_payments_joined_to_orders') }}
where counts_as_revenue

{% if is_incremental() %}
  where revenue_date > (select max(revenue_date) from {{ this }})
{% endif %}

Before: 187.63s (full table rebuild, ~2B rows scanned every run)
After incremental + merge: 41.2s (only rows since last max(revenue_date) processed)

This alone was the single largest gain, for the reason Part 02's math predicts directly: the model went from recomputing its entire two-billion-row history on every run to processing only the handful of days' worth of new payments since the last run.

Change 2 — cluster_by on revenue_date

{{
  config(
    materialized='incremental',
    unique_key='payment_id',
    incremental_strategy='merge',
    cluster_by=['revenue_date']
  )
}}
...

Before: 41.2s (merge scanning more micro-partitions than the incoming batch's date range needed)
After cluster_by: 18.6s (merge's match condition prunes to only the recent micro-partitions)

The merge's join condition already filtered effectively on the new-rows side after Change 1, but without clustering, Snowflake still had to consider a wider set of the existing table's micro-partitions than the incoming batch's narrow date range actually needed — clustering on the same column the incremental filter and the merge both revolve around let the warehouse prune far more of the existing table before the merge's join even ran, exactly the mechanism described in Part 04.

Change 3 — routing to a dedicated, larger warehouse for this specific run window

{{
  config(
    materialized='incremental',
    unique_key='payment_id',
    incremental_strategy='merge',
    cluster_by=['revenue_date'],
    snowflake_warehouse='transform_large_wh'
  )
}}
...

Before: 18.6s, contending with ~40 other concurrent models on the shared warehouse during the nightly run
After dedicated warehouse: 9.1s (no contention for compute during its build window)

The final change didn't reduce the actual amount of work the query performed — it removed contention. On the shared warehouse, fct_revenue's merge was queued behind, and competing for compute slots with, dozens of other concurrently running models during the busiest part of the nightly dbt build. Moving it to its own appropriately sized warehouse, per Part 06, meant it no longer had to share that compute with anything else during its own build window.

A One-Time Fix Is Not a Performance Strategy — Track Regressions Before They're Incidents

Every technique in this module up to now addresses fixing a model that is already known to be slow or expensive. The harder, longer-term problem is noticing a model that is becoming slow or expensive before it becomes a fire — a table that grows a little every day, an incremental filter whose match rate creeps upward, a warehouse that gets a little more contended every quarter as more models get added to it. Part 08's case study measured a one-time before-and-after; a mature performance practice measures continuously.

The same warehouse query-history technique from Part 01 generalizes directly into a recurring check rather than a one-off investigation: instead of running the query once to triage a known problem, schedule it to run regularly and compare each model's trend over time, flagging any model whose average runtime or cost has grown meaningfully since the last check.

with this_week as (
    select
        regexp_substr(query_text, '"node_id": *"([^"]+)"', 1, 1, 'e') as dbt_node_id,
        avg(total_elapsed_time) as avg_ms_this_week
    from snowflake.account_usage.query_history
    where query_text ilike '%"app": "dbt"%'
      and start_time > dateadd('day', -7, current_timestamp())
    group by 1
),

four_weeks_ago as (
    select
        regexp_substr(query_text, '"node_id": *"([^"]+)"', 1, 1, 'e') as dbt_node_id,
        avg(total_elapsed_time) as avg_ms_baseline
    from snowflake.account_usage.query_history
    where query_text ilike '%"app": "dbt"%'
      and start_time between dateadd('day', -35, current_timestamp())
                          and dateadd('day', -28, current_timestamp())
    group by 1
)

select
    this_week.dbt_node_id,
    four_weeks_ago.avg_ms_baseline,
    this_week.avg_ms_this_week,
    this_week.avg_ms_this_week - four_weeks_ago.avg_ms_baseline as ms_regression
from this_week
join four_weeks_ago using (dbt_node_id)
where this_week.avg_ms_this_week > four_weeks_ago.avg_ms_baseline * 1.5
order by ms_regression desc

A model surfaced by this query — one that has genuinely gotten 50% or more slower over four weeks with no obvious code change — is exactly the kind of early warning that lets a team apply Part 03's strategy tuning or Part 04's clustering fix proactively, on their own schedule, rather than discovering the same regression reactively when a nightly build finally blows through its maintenance window or a warehouse bill spikes unexpectedly at month's end.

Wiring a regression check into CI, rather than running it manually

The trend query above is useful run by hand, but its real value compounds once it's automated — run on a schedule, with its output posted somewhere the team actually looks, rather than depending on someone remembering to run it. A lightweight version of this pattern many teams adopt is a scheduled job, entirely separate from the dbt project itself, that runs the trend query weekly and posts any newly-flagged regression to a shared channel before it has a chance to compound further.

# Runs weekly, independent of the actual dbt build schedule
# 1. Run the trend-comparison query from above against query history
# 2. For any model exceeding the regression threshold (e.g. 1.5x),
#    post its name, the baseline, and the current average to a
#    shared alerting channel
# 3. A human triages the alert against this module's checklist:
#    incremental match rate, clustering, warehouse contention

0 9 * * 1  run_weekly_dbt_perf_regression_check.sh

This closes the loop between Part 01's diagnostic technique and Part 08's fix pattern: instead of discovering a regression only when it's severe enough to blow through a maintenance window, catching it early at the 1.5x threshold — well before it reaches the 20x-worse state a genuinely neglected model can quietly drift into — keeps each individual fix small, cheap, and easy to apply with confidence.

The last piece worth stating plainly: none of Part 09's monitoring replaces the diagnostic and tuning techniques covered in Parts 01 through 08 — it only changes when they get applied. A team without any regression monitoring still has access to every fix in this module, but only ever discovers the need for them reactively, after a model has already become slow enough to notice without instrumentation. A team with monitoring wired in applies the exact same fixes, just earlier and cheaper, which is the entire case for treating performance tuning as an ongoing practice rather than a one-time cleanup project.

Tie this back to the broader project-structure discipline from the previous module in this track, too: a well-layered project makes performance regressions easier to localize in the first place. A regression flagged against a thin, single-purpose mart is trivial to reason about — there's exactly one join or aggregation it could be. A regression flagged against a sprawling, do-everything model that never got split into staging, intermediate, and marts requires untangling which of its many responsibilities actually got slower, turning a five-minute diagnosis into a much longer one. Good structure and good performance practice reinforce each other far more than either discipline alone would suggest.

Five Misconceptions About dbt Performance Tuning

Three Companies, Three Performance Wins

5 Interview Questions — With Complete Answers

The Performance Mistakes That Cost the Most Later

Performance Problems You Will Hit — And Exactly Why They Happen