You've built a dbt project that runs beautifully in development — models compile cleanly, tests pass, and your stakeholders are happy. Then you hit production. The full job that processes 18 months of order history now runs for 47 minutes, your warehouse costs spike at the end of every business day, and the on-call engineer gets paged because a downstream dashboard timed out again. Sound familiar?
Slow dbt models are rarely a dbt problem. They're a data problem manifesting through dbt. The query dbt generates is just SQL, and SQL performance is governed by the same rules it always has been: how the warehouse scans data, how it joins and aggregates it, what it can skip, and how much compute you've thrown at the problem. dbt adds one more dimension: materialization strategy, which determines whether your model runs every query fresh or pre-computes results that downstream consumers can reuse. Getting optimization right means working at all three layers simultaneously — the query itself, the warehouse infrastructure, and the materialization approach.
By the end of this lesson, you'll be able to systematically diagnose why a dbt model is slow, read query execution plans in both Snowflake and BigQuery, use warehouse-level monitoring to separate "slow query" problems from "too much concurrent load" problems, and make informed decisions about when to use incremental models, table materializations, and partitioning strategies.
What you'll learn:
This lesson assumes you're comfortable with:
ref() and source() do)target/compiled/)You do not need to be a warehouse DBA. We'll explain the warehouse-specific tooling as we go.
The most common optimization mistake is trying to fix a dbt model by changing the dbt YAML or Jinja logic without ever looking at the SQL the warehouse actually executes. Before you touch anything, get the compiled SQL.
After a dbt run, your compiled SQL lives in target/compiled/<project_name>/models/<path>/<model_name>.sql. For incremental models, this shows the SELECT statement dbt uses for the incremental load, but it does not show the final merge or insert-overwrite statement. To see that, look in target/run/<project_name>/models/<path>/<model_name>.sql — this file contains the full DDL that dbt executed, including the MERGE or INSERT OVERWRITE wrapper.
Copy that SQL out of the target/run/ file. Paste it directly into your warehouse query editor. Now you're working with reality, not an abstraction.
Here's a realistic example of what you might find when you open that file for a slow incremental model:
-- target/run/my_project/models/marts/fct_orders.sql
merge into "ANALYTICS"."MARTS"."FCT_ORDERS" as DBT_INTERNAL_DEST
using (
select
o.order_id,
o.customer_id,
o.order_date,
o.status,
sum(oi.quantity * oi.unit_price) as order_total,
count(oi.line_item_id) as line_item_count
from "ANALYTICS"."STAGING"."STG_ORDERS" o
join "ANALYTICS"."STAGING"."STG_ORDER_ITEMS" oi
on o.order_id = oi.order_id
where o.updated_at >= (
select max(updated_at) from "ANALYTICS"."MARTS"."FCT_ORDERS"
)
group by 1, 2, 3, 4
) as DBT_INTERNAL_SOURCE
on DBT_INTERNAL_SOURCE.order_id = DBT_INTERNAL_DEST.order_id
when matched then update set ...
when not matched then insert ...
This SQL has at least three potential performance problems visible immediately: a correlated subquery inside the WHERE clause that re-scans the target table, a join between two staging tables with no obvious filter on stg_order_items, and a MERGE operation that requires Snowflake (or BigQuery) to match every source row against every target row. Let's learn how to confirm whether these are actually the bottlenecks.
Snowflake's Query Profile is one of the most powerful diagnostic tools in the modern data stack, and most people only glance at it. Here's how to actually use it.
Run your compiled SQL directly in the Snowflake worksheet. After it completes, click "Query ID" in the results pane, then click "Query Profile" in the top-right corner. Alternatively, go to Activity > Query History, find your query, and click the Query ID link.
The Query Profile shows a directed acyclic graph (DAG) of query operators. Each node in the graph is a physical operation the warehouse performed: a table scan, a join, an aggregation, a sort. The nodes are colored and sized to reflect their relative cost.
The three numbers to look at first:
Bytes scanned — shown at the TableScan nodes. This tells you how much raw data Snowflake had to read. If you're scanning 500GB for a query that produces 10,000 rows, something is wrong upstream.
Partitions scanned vs. total partitions — this ratio is critical. Snowflake automatically partitions data via micro-partitions. If your query scanned 4,800 out of 4,800 partitions, you have zero partition pruning. This is the most common source of slowness in Snowflake models and it usually means your WHERE clause isn't filtering on the column Snowflake uses to order the micro-partitions.
Spillage to local/remote disk — look for "Spilling" labels on Join or Aggregate nodes. When Snowflake can't fit an operation in memory, it spills to local disk (tolerable) or remote disk (very expensive). Remote disk spill is the single most actionable warning sign in a query profile.
Warning: Disk spillage in Snowflake almost always means your virtual warehouse is undersized for this specific operation, not that the whole warehouse needs to be larger. A complex GROUP BY over 200M rows might require a Large warehouse while most of your other models run fine on Small. Scaling up for spillage is sometimes correct, but first check whether the query itself can be rewritten to reduce the data volume at the spilling step.
Finding the expensive operator:
Click each node in the query DAG. The right panel shows timing, row counts in vs. out, and bytes processed. A join node that takes in 50M rows on the left side and 80M rows on the right side and produces 400M rows out is almost certainly a fan-out problem — you have a many-to-many join where you expected many-to-one. This is a logical bug, not a performance tuning problem, and no amount of warehouse resizing will fix it.
Look for nodes where rows out >> rows in. That's fan-out. Look for nodes where rows out << rows in but bytes scanned is still huge. That's a missing filter — Snowflake had to read all the data just to throw most of it away.
BigQuery handles performance diagnostics differently because it's a serverless, slot-based engine. You don't size a warehouse — instead, BigQuery allocates slots (units of compute) to your query dynamically, or from a reservation if you're on flat-rate pricing.
To find the execution details, run your query in the BigQuery console. After it completes, click "Execution Details" in the results panel (not "Results" — the tab next to it). You'll see a breakdown of query stages.
BigQuery decomposes your query into stages, each of which is a set of operations that can run in parallel. Each stage shows:
The most expensive stage is usually a JOIN or a GROUP BY on a large table. In BigQuery, look for stages where the output bytes are dramatically smaller than the input bytes but the slot time is enormous — that's a sign you're filtering late in the plan rather than early.
Tip: BigQuery's query planner is generally good at predicate pushdown, but it can be fooled by non-deterministic functions, certain CASE expressions, and subqueries that reference session variables. If you see a full table scan on a partitioned table, check whether your partition filter is wrapped in a function like
DATE()orTIMESTAMP_TRUNC()that prevents BigQuery from recognizing it as a partition-pruning predicate.
Understanding bytes billed vs. bytes processed:
BigQuery bills on bytes processed, and a query's billing is determined at plan time, not at execution time. A query that scans a 2TB table but returns 10 rows still costs you 2TB. Partition pruning and column projection (only selecting the columns you need) are the two primary cost levers in BigQuery.
To check whether your dbt model is doing efficient column projection, look at the compiled SQL. If you're doing SELECT * at any stage — even inside a CTE — you're pulling every column through the execution plan until BigQuery can prune it. BigQuery is columnar, so this is less catastrophic than in a row store, but it still increases shuffle bytes unnecessarily.
Before you spend hours optimizing query logic, make sure your slow model isn't slow because of contention rather than query complexity. A query that runs fine in isolation at 10am might run for 3x as long at 6pm when eight other models are running concurrently.
Snowflake Resource Monitors are primarily a cost-control tool, but the Query History UI is where you diagnose contention. Go to Activity > Query History and set the filter to your warehouse name and a time window that covers your slow run. Sort by "Queued" time.
If queries are spending significant time in "Queued" state — meaning the warehouse accepted the query but couldn't start it because all threads were busy — you have a concurrency problem, not a query-complexity problem. The solution is different:
auto_suspend and min_cluster_count/max_cluster_count in your warehouse config), or split your dbt job into separate warehouse targets for heavy models vs. light models.In your dbt profiles.yml or in model-level configs, you can direct specific models to different warehouses:
# models/marts/fct_orders.yml
models:
- name: fct_orders
config:
snowflake_warehouse: TRANSFORM_LARGE
This means your heavyweight fact table rebuild runs on a Large warehouse while your staging models run on a Small warehouse, without changing anything else in your pipeline.
In BigQuery, navigate to the BigQuery console, then to Administration > Resource Management (or use the BigQuery Admin project if you're on flat-rate). The Slot Utilization chart shows you whether your project is consistently maxed out on available slots.
If slot utilization is at 100% for extended periods during your dbt run, your queries are queuing behind each other. Increasing slot reservations or spreading model execution across time windows will help more than query optimization will.
For on-demand BigQuery (no flat-rate), each project gets 2,000 concurrent slots by default. You can monitor per-job slot usage with this query against the INFORMATION_SCHEMA:
select
job_id,
query,
total_slot_ms,
total_bytes_processed,
total_bytes_billed,
creation_time,
end_time,
timestamp_diff(end_time, creation_time, second) as duration_seconds
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where creation_time >= timestamp_sub(current_timestamp(), interval 24 hour)
and job_type = 'QUERY'
and state = 'DONE'
order by total_slot_ms desc
limit 50
This gives you your 50 most slot-intensive queries from the past 24 hours. Match these against your dbt model run times (which you can get from dbt's run_results.json artifact) to identify which specific models are consuming disproportionate resources.
dbt's four materializations — view, table, incremental, and ephemeral — aren't just implementation choices. They fundamentally change how query cost flows through your pipeline. Understanding the trade-offs means understanding that you're always trading build cost against query cost.
A view has zero build cost — dbt just creates a SQL view definition. But every time a downstream model or BI tool queries that view, it re-executes the full underlying SQL. If you have a complex view that joins three tables and performs multiple window functions, and that view is referenced by 12 downstream models, you're running that expensive SQL 12 times per dbt job plus every dashboard query.
Views are correct when:
Views are a trap when:
A table materialization runs the full model SQL on every dbt run and replaces the entire table. This is expensive to build but cheap to query. Downstream consumers always read from a pre-computed, indexed/clustered physical table.
Tables become the right choice when:
The failure mode of table is when people apply it to models that are expensive to build and that only change a small fraction of their data each run. A fact table with 3 years of order history that only adds today's rows doesn't need to be fully rebuilt every night. That's what incremental is for.
Incremental models are the most powerful and most misused materialization in dbt. The idea is simple: on the first run, build the full table. On subsequent runs, only process new or changed rows and merge them in. The performance gain can be enormous — processing 10,000 new rows instead of rebuilding 500M rows.
But incremental models have two failure modes that make them slower than tables in practice:
Failure Mode 1: The incremental filter isn't actually selective.
The standard dbt incremental pattern looks like this:
-- models/marts/fct_orders.sql
{{
config(
materialized='incremental',
unique_key='order_id'
)
}}
select
o.order_id,
o.customer_id,
o.order_date,
o.status,
sum(oi.quantity * oi.unit_price) as order_total,
count(oi.line_item_id) as line_item_count
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} oi
on o.order_id = oi.order_id
{% if is_incremental() %}
where o.updated_at > (select max(updated_at) from {{ this }})
{% endif %}
group by 1, 2, 3, 4
The problem here is that stg_order_items is not filtered at all. You're joining all of stg_orders (filtered) against all of stg_order_items (not filtered). For a fact table with 100M order items, that join still scans the full items table on every incremental run.
Fix it by filtering both sides:
{% if is_incremental() %}
with recent_orders as (
select order_id
from {{ ref('stg_orders') }}
where updated_at > (select max(updated_at) from {{ this }})
),
recent_items as (
select oi.*
from {{ ref('stg_order_items') }} oi
inner join recent_orders ro on oi.order_id = ro.order_id
)
select
o.order_id,
o.customer_id,
o.order_date,
o.status,
sum(ri.quantity * ri.unit_price) as order_total,
count(ri.line_item_id) as line_item_count
from {{ ref('stg_orders') }} o
join recent_items ri on o.order_id = ri.order_id
where o.order_id in (select order_id from recent_orders)
group by 1, 2, 3, 4
{% else %}
select
o.order_id,
o.customer_id,
o.order_date,
o.status,
sum(oi.quantity * oi.unit_price) as order_total,
count(oi.line_item_id) as line_item_count
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} oi
on o.order_id = oi.order_id
group by 1, 2, 3, 4
{% endif %}
Failure Mode 2: The MERGE operation scans the entire target table.
In both Snowflake and BigQuery, a MERGE statement has to match source rows against target rows. If the target table has 500M rows and you're merging in 10,000 new rows, Snowflake may still scan all 500M target rows looking for matches — unless there's a clustering key or partition specification that lets the warehouse skip irrelevant data.
In Snowflake, solve this with a cluster_by config that matches your unique_key lookup pattern:
# models/marts/fct_orders.yml
models:
- name: fct_orders
config:
materialized: incremental
unique_key: order_id
cluster_by: ['order_date']
Clustering by order_date means the most recent orders are co-located in the same micro-partitions. When the MERGE goes looking for matching order_id values among recent orders, Snowflake can skip all the micro-partitions containing old orders.
In BigQuery, solve this with partitioning and a merge_update_columns strategy that includes the partition column:
# models/marts/fct_orders.yml
models:
- name: fct_orders
config:
materialized: incremental
unique_key: order_id
partition_by:
field: order_date
data_type: date
granularity: day
cluster_fields:
- customer_id
- status
incremental_strategy: merge
With this config, BigQuery's MERGE only scans partitions that contain rows from your source dataset's date range, instead of scanning the entire 3-year history.
Tip: In BigQuery, always verify partition pruning is working after adding a partition config. Run the incremental query manually with
EXPLAINor check the bytes billed in the console. A common gotcha: if your source data'sorder_datecolumn is a TIMESTAMP and your partition config specifiesdata_type: date, BigQuery may not prune partitions. UseTIMESTAMP_TRUNC(order_date, DAY)or cast explicitly.
ephemeral models are CTEs that get inlined into every model that references them. They have zero materialization overhead — no table, no view — but every downstream model that uses them includes the full CTE computation inline. If three downstream models reference the same ephemeral model, that SQL runs three times.
Ephemeral is correct for simple transformations that are cheap to compute and only used by one model. It's wrong for any computation you'd want to reuse, or for any computation complex enough that running it three times matters.
One of the biggest mistakes in optimization work is declaring victory based on a single test run. Warehouse performance varies based on data caching, concurrent load, and micro-partition freshness (in Snowflake). Here's a rigorous benchmarking approach.
In Snowflake, result caching means the second run of an identical query returns instantly from cache — not because you optimized it, but because the warehouse cached the result. Before benchmarking, run:
alter session set use_cached_result = false;
This disables result caching for your session. Now each run actually executes the query.
For a fair comparison between an old and a new model version, run each version at least three times with cache disabled and take the median execution time. Throw out the first run if the warehouse was freshly resumed (it won't have any local data cache loaded).
In Snowflake, every query execution has a unique Query ID. Record the IDs for your "before" runs and your "after" runs. You can then compare their Query Profiles side by side, looking specifically at:
In BigQuery, check the job information for bytes billed (before/after) and total slot ms consumed. Bytes billed is the cleanest performance metric because it's deterministic — it doesn't vary based on concurrent load.
dbt's run_results.json artifact (written to target/run_results.json after every run) contains per-model execution timing. For production benchmarking, you can parse this file to build a history of model execution times:
import json
from pathlib import Path
from datetime import datetime
results_path = Path("target/run_results.json")
with results_path.open() as f:
run_results = json.load(f)
print(f"Run completed at: {run_results['metadata']['generated_at']}")
print(f"\n{'Model':<50} {'Status':<12} {'Seconds':>10}")
print("-" * 74)
for result in sorted(
run_results["results"],
key=lambda r: r.get("execution_time", 0),
reverse=True,
):
node_id = result["unique_id"].split(".")[-1]
status = result["status"]
execution_time = result.get("execution_time", 0)
print(f"{node_id:<50} {status:<12} {execution_time:>10.2f}s")
Run this after each experiment to get a ranked view of model execution times. Compare the output before and after your optimization.
In this exercise, you'll take a deliberately slow incremental model, profile it, and optimize it using the techniques in this lesson.
You'll need a Snowflake or BigQuery environment with dbt configured. Create the following source tables (adjust syntax for your warehouse):
-- Create staging tables with enough rows to see performance differences
-- In Snowflake:
create or replace table analytics.staging.stg_events as
select
seq4() as event_id,
uniform(1, 1000000, random()) as session_id,
uniform(1, 50000, random()) as user_id,
dateadd(second, uniform(0, 60*60*24*365, random()), '2022-01-01'::timestamp) as event_timestamp,
case uniform(1, 5, random())
when 1 then 'page_view'
when 2 then 'click'
when 3 then 'purchase'
when 4 then 'signup'
else 'logout'
end as event_type,
uniform(1, 1000, random())::float / 10 as event_value
from table(generator(rowcount => 50000000));
create or replace table analytics.staging.stg_sessions as
select
seq4() as session_id,
uniform(1, 50000, random()) as user_id,
dateadd(second, uniform(0, 60*60*24*365, random()), '2022-01-01'::timestamp) as session_start,
case uniform(1, 3, random())
when 1 then 'web'
when 2 then 'mobile'
else 'api'
end as channel
from table(generator(rowcount => 1000000));
Create this model in your dbt project at models/marts/fct_user_sessions.sql:
{{
config(
materialized='incremental',
unique_key='session_id'
)
}}
select
s.session_id,
s.user_id,
s.session_start::date as session_date,
s.channel,
count(e.event_id) as event_count,
sum(case when e.event_type = 'purchase' then e.event_value else 0 end) as purchase_value,
count(distinct e.event_type) as unique_event_types
from {{ ref('stg_sessions') }} s
left join {{ ref('stg_events') }} e
on s.session_id = e.session_id
{% if is_incremental() %}
where s.session_start >= (
select dateadd(day, -1, max(session_start)) from {{ this }}
)
{% endif %}
group by 1, 2, 3, 4
Run it once to build the full table (dbt run --full-refresh --select fct_user_sessions), then run it again incrementally. Time the incremental run.
Open the compiled SQL from target/run/. Paste it into your warehouse query editor and run it. Open the Query Profile (Snowflake) or Execution Details (BigQuery). Answer these questions:
stg_events?Apply the following changes:
cluster_by: ['session_date'] config (Snowflake) or partition_by on session_start (BigQuery)stg_events is also filtered to only recent sessionsdbt run --full-refresh to rebuild with new clustering, then run incrementally againCompare the Query Profile before and after. You should see a significant reduction in partitions scanned on stg_events.
"I added clustering but it's still scanning all partitions"
Clustering in Snowflake takes time to take effect on existing data. After you add a cluster_by config and run --full-refresh, Snowflake needs to reorganize the micro-partitions in the background. Check the clustering state with:
select system$clustering_information('ANALYTICS.MARTS.FCT_USER_SESSIONS', '(session_date)');
If average_depth is greater than ~2, the data isn't well-clustered yet. This can take minutes to hours on large tables. You can also explicitly call:
alter table ANALYTICS.MARTS.FCT_USER_SESSIONS cluster by (session_date);
"My incremental model runs fine but produces wrong results after a late-arriving data fix"
The lookback window in your incremental filter (dateadd(day, -1, max(...))) determines how far back you reprocess. If source data corrections go further back than your lookback window, those corrections won't be picked up. Solutions:
dbt run --full-refresh on a schedule (e.g., weekly) to catch any correctionsupdated_at to the current timestamp when corrections are made, so the filter catches them"My view-stacked-on-view model is timing out"
Three views deep is often where Snowflake and BigQuery start to produce query plans with 10+ table scans because every view dereferences all its upstream views into a flat plan. The fastest fix is to materialize the most-referenced middle layer as a table. Use dbt's --store-failures option and query history to identify which model in your chain is referenced most.
"BigQuery says 0 bytes processed but the query still took 30 seconds"
This is BigQuery's result cache. The 0 bytes is accurate — BigQuery retrieved the result from cache and didn't scan anything. But the 30-second latency is coming from query slot scheduling overhead and result retrieval. For dashboards that need sub-5-second latency, consider BigQuery BI Engine reservations or materializing results into a smaller summary table.
"dbt's MERGE is slower than replacing the whole table"
This actually happens. When your incremental batch is large (>20% of the target table), a MERGE operation touches most of the table anyway and adds the overhead of the merge logic on top. In this case, switching to incremental_strategy: insert_overwrite (BigQuery) or using a partition-based replace strategy can be faster than a row-level MERGE. In dbt, set:
config:
materialized: incremental
incremental_strategy: insert_overwrite
partition_by:
field: order_date
data_type: date
This tells dbt to delete and replace entire partitions rather than merging row-by-row, which BigQuery can do extremely efficiently.
You now have a systematic approach to diagnosing and optimizing slow dbt models. The workflow looks like this: start with the compiled SQL in target/run/, profile it in the warehouse's native tooling (Query Profile for Snowflake, Execution Details for BigQuery), check whether you're solving a query-complexity problem or a concurrency problem using Query History and Resource Monitor data, then apply the right fix.
The most impactful optimizations in order of typical return:
To go deeper from here:
on_schema_change config for incremental models — it controls what happens when you add or remove columns, and the wrong setting can silently drop columns in productiondbt docs site to map which downstream consumers depend on which models — this helps you prioritize optimization work by impactThe biggest shift in thinking this lesson asks you to make is to stop treating dbt performance as a dbt problem and start treating it as a SQL performance problem that dbt is surfacing. The warehouse is doing the work. Your job is to help it do less of it.