dbt Performance at Scale: Threads, Materializations, and Per-Model Cost Attribution

The dbt run that broke me took four hours and forty minutes. Nine hundred models, a nightly job that started at 1 a.m. and was supposed to be done before the 6 a.m. Tableau extracts. It usually finished at 5:40. When anything went wrong, it did not.

The first thing everyone suggests is more threads. We went from 8 to 24. The run took four hours and thirty-five minutes. Five minutes for triple the concurrency, because the run was not thread-bound — it was bound by a chain of eleven models, each waiting on the last, that accounted for two hours and ten minutes all by itself.

That is the lesson that reorganized how I think about dbt performance: you are not optimizing a workload, you are optimizing a graph. Everything below follows from that.

Find the critical path before you change anything

run_results.json has an execution time per node. The naive move is to sort it descending and fix the top ten. That finds slow models. It does not find the models that make the run long.

What you want is the longest dependency chain weighted by execution time. Load run_results.json and manifest.json and walk it:

# scripts/critical_path.py
import json
from functools import lru_cache

with open("target/manifest.json") as f:
    manifest = json.load(f)
with open("target/run_results.json") as f:
    results = json.load(f)

# node_id -> seconds
timings = {
    r["unique_id"]: r["execution_time"]
    for r in results["results"]
    if r["status"] in ("success", "pass")
}

nodes = manifest["nodes"]

@lru_cache(maxsize=None)
def path_cost(node_id):
    """Longest upstream chain ending at node_id, in seconds."""
    own = timings.get(node_id, 0.0)
    parents = [
        p for p in nodes.get(node_id, {}).get("depends_on", {}).get("nodes", [])
        if p in timings
    ]
    if not parents:
        return own, [node_id]
    best_cost, best_path = max((path_cost(p) for p in parents), key=lambda x: x[0])
    return own + best_cost, best_path + [node_id]

cost, path = max((path_cost(n) for n in timings), key=lambda x: x[0])
print(f"Critical path: {cost / 60:.1f} minutes across {len(path)} models\n")
for node in path:
    print(f"  {timings.get(node, 0):7.1f}s  {node}")

Run that once and the conversation changes. On our project it printed eleven models and two hours ten. Six of them were fast; they were on the path because of a chain of dependencies that did not need to be serial. Two were genuinely slow. Three did not need to exist.

flowchart TD
  a["stg_events (4m)"] --> b["int_sessionized (38m)"]
  b --> c["int_attributed (26m)"]
  c --> d["fct_sessions (19m)"]
  d --> e["fct_attribution (31m)"]
  e --> f["rpt_channel_daily (12m)"]
  g["40 other models"] -.->|"parallel, off path"| f

Adding threads speeds up the dotted branch. It does nothing for the solid one.

Threads: where the returns stop

threads in profiles.yml is how many models dbt will run concurrently. The right number is bounded by three things, and the smallest one wins:

1. Graph width. If your DAG never has more than 12 runnable nodes at once, thread 24 is thread 12 with extra connections.
2. Warehouse concurrency. On Snowflake, a warehouse has a concurrency limit before queries queue; on BigQuery you hit slot contention. Queuing does not make anything faster, and on auto-scaling warehouses it makes things more expensive by spinning up clusters for queued work.
3. Adapter connection limits. Each thread holds a connection.

My starting point on a large project is 8, then measure. I raise it only when I can see runnable-but-not-running nodes in the timing data, and I check the warehouse’s queue metrics after each change.

# profiles.yml
jaffle_analytics:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: xy12345.us-east-1
      warehouse: transforming_l
      # Measured: graph width peaks at ~14; warehouse queues past 12.
      threads: 12
      client_session_keep_alive: false
    ci:
      type: snowflake
      warehouse: transforming_xs
      # CI builds a small subgraph. High threads, tiny warehouse.
      threads: 16

Note the different warehouse sizes. A common mistake is running CI on the production warehouse: CI builds a handful of models, so it wants concurrency on a small warehouse, not a large one sitting mostly idle. Pairing this with Slim CI is where most projects find their first big spend reduction, because the default of building everything on every PR is usually the second largest line item after the nightly run.

The counterintuitive part: on a warehouse that queues, reducing threads sometimes reduces wall-clock time, because queued queries still hold their place and the scheduler thrashes. If you have never tested downward, test downward once.

Materialization is a cost decision, not a style choice

Most projects have their materializations set by habit rather than by measurement. Four rules I apply.

Views for anything cheap and rarely queried. A staging model over a small source is a view. It costs nothing to build and the read cost is paid only by whoever queries it. If a view is read forty times an hour by a dashboard, that changes.

Tables for anything on the critical path or read repeatedly. The build cost is paid once per run and amortized across every read.

Incremental only when you can prove it. This is where I see the most waste. Incremental has real overhead: a merge, a scan of this to find the watermark, and a predicate that may not prune. On a table where the merge target is a billion unclustered rows, a MERGE touching 200k rows can be slower and more expensive than a CREATE TABLE AS of the whole thing, because the merge reads micro-partitions all over the table to find matches.

{{
    config(
        materialized='incremental',
        unique_key='event_id',
        -- Snowflake: restrict the merge target so it can prune.
        -- Without this the MERGE scans the whole table for matches.
        incremental_predicates=[
            "DBT_INTERNAL_DEST.occurred_at >= dateadd('day', -7, current_date)"
        ],
        cluster_by=['occurred_at'],
        on_schema_change='append_new_columns'
    )
}}

select
    event_id,
    user_id,
    event_type,
    occurred_at,
    payload
from {{ ref('stg_events__raw') }}
{% if is_incremental() %}
    -- 3-day lookback for late arrivals; measured against source lag p99.
    where occurred_at >= (
        select coalesce(max(occurred_at), '1900-01-01'::timestamp) - interval '3 days'
        from {{ this }}
    )
{% endif %}

incremental_predicates is the single highest-leverage config on a large incremental model and it is missing from most projects I audit. Without it, the merge has no way to know the target rows are all recent. With it, the warehouse prunes to the last week of micro-partitions, which is the difference between reading four terabytes and reading forty gigabytes. The pruning mechanics are worth understanding in detail if you are on Snowflake — see micro-partitions and pruning.

Materialized views and dynamic tables for freshness-driven models. If a model exists because someone wants data within five minutes, running it every five minutes with dbt is not the tool. Snowflake dynamic tables do incremental refresh with a declared lag, and they take the model off your critical path entirely.

Per-model cost attribution

Run time is a proxy. Money is the actual constraint, and dbt does not tell you what a model costs. You have to join it yourself.

dbt tags every query it issues with a comment containing the node name, and warehouses log that. On Snowflake:

-- Per-model credits for the last 7 days.
-- Requires query_tag or the dbt query comment; both work.
with dbt_queries as (
    select
        query_id,
        try_parse_json(query_tag):node_id::string as node_id,
        warehouse_size,
        total_elapsed_time / 1000.0               as elapsed_seconds,
        credits_used_cloud_services,
        start_time
    from snowflake.account_usage.query_history
    where start_time >= dateadd('day', -7, current_timestamp())
      and query_tag ilike '%node_id%'
),

warehouse_rate as (
    -- Credits per second by size. Adjust to your contract.
    select 'X-SMALL' as warehouse_size, 1.0 / 3600 as credits_per_second
    union all select 'SMALL',   2.0 / 3600
    union all select 'MEDIUM',  4.0 / 3600
    union all select 'LARGE',   8.0 / 3600
    union all select 'X-LARGE', 16.0 / 3600
)

select
    q.node_id,
    count(*)                                              as runs,
    round(sum(q.elapsed_seconds) / 60, 1)                 as total_minutes,
    round(sum(q.elapsed_seconds * r.credits_per_second), 2) as credits,
    round(sum(q.elapsed_seconds * r.credits_per_second) * 3.00, 2) as usd_estimate
from dbt_queries q
join warehouse_rate r using (warehouse_size)
group by 1
order by credits desc
limit 30;

To make node_id available, set the query tag in a hook:

# dbt_project.yml
models:
  +pre-hook: "{{ set_query_tag() }}"

-- macros/set_query_tag.sql
{% macro set_query_tag() %}
    {% if execute and target.type == 'snowflake' %}
        alter session set query_tag = '{{
            {"node_id": model.unique_id, "invocation_id": invocation_id} | tojson
        }}'
    {% endif %}
{% endmacro %}

Now you have a dollar figure per model. The first time I ran this, the top result was a model that rebuilt a full table nightly to serve a dashboard that three people opened, twice a month. It cost more than the entire marketing mart. Nobody would have guessed, because it ran in eleven minutes and eleven minutes does not feel expensive.

Cross-reference the cost list against information_schema.access_history or your BI tool’s query log. Models with high cost and zero reads are free money. I have never audited a project over four hundred models and found fewer than fifteen of them.

Warehouse-side wins that are not dbt’s fault

Half of what looks like a dbt performance problem is a SQL problem. The patterns that hurt most at scale, all covered in more depth in SQL anti-patterns that wreck performance:

• A join that fans out and is then deduplicated with distinct. The fan-out happens first, and you pay for every intermediate row.
• where date_trunc('day', occurred_at) = '2026-05-01' instead of a range predicate, which defeats partition pruning on every warehouse I have used.
• Window functions over an unpartitioned table where a qualify row_number() = 1 on a filtered subset would do.
• Re-reading a wide source in six CTEs when one pass would serve all six.

Pull the query profile for the two slowest models on your critical path before you touch config. Half the time the answer is a predicate, not a materialization.

Deleting models is a performance strategy

The cheapest model is the one that does not run. On the nine-hundred model project, we found 140 models with no downstream consumers, no BI queries in ninety days, and no exposures. Deleting them cut seventeen minutes off the nightly run and roughly nine percent of warehouse spend, and it took one afternoon.

-- Models built in the last 30 days that nothing has queried.
-- Snowflake; adapt object naming for other warehouses.
with built as (
    select distinct
        lower(table_catalog || '.' || table_schema || '.' || table_name) as full_name
    from analytics.information_schema.tables
    where table_schema in ('ANALYTICS', 'ANALYTICS_INTERMEDIATE')
),

read as (
    select distinct
        lower(value:objectName::string) as full_name
    from snowflake.account_usage.access_history,
         lateral flatten(input => base_objects_accessed)
    where query_start_time >= dateadd('day', -90, current_timestamp())
)

select built.full_name as unread_object
from built
left join read using (full_name)
where read.full_name is null
order by 1;

Check the output against exposures and reverse-ETL syncs before you drop anything. Then drop it. A model kept “just in case” is a subscription you renew every night.

Where teams get this wrong

Raising threads as the first move. It is the only knob that feels like tuning and it is almost never the constraint. Measure graph width first.

Incremental everywhere. Small tables get merge overhead for no benefit; huge unclustered tables get merges that scan everything. Neither is faster than the table build it replaced.

Incremental without incremental_predicates. The model looks incremental and the merge scans the full target. This is the most common expensive mistake I find on Snowflake projects.

Building everything in CI. A PR touching one model should build one model plus children. Anything else is paying production prices for a code review.

Optimizing the slowest model. If it is off the critical path, making it twice as fast changes the run duration by zero.

No cost attribution at all. Without a per-model dollar figure, optimization is guided by which model annoys someone. That is not the same as which model is expensive.

FAQ

What is a reasonable thread count?

Start at 8 for a large project, measure graph width from run_results.json, and raise only to that width. Watch warehouse queuing after every change. Sixteen is a lot; thirty-two is almost always someone hoping.

Should I split one big dbt project into several?

Not for performance. Splitting does not reduce total work, it just moves the coordination problem into cross-project handoffs. Split for ownership boundaries, using the folder and access rules in my dbt project structure guide first.

Does a bigger warehouse make dbt faster?

For a single large model, often yes and sometimes at the same total cost, because a warehouse twice the size that finishes in half the time bills the same credits. For a wide graph of small models, no — you pay double for idle compute. Consider separate warehouses sized for different model groups, selected by tag.

How do I speed up the parse step?

Trim unguarded run_query calls, which force warehouse round-trips at parse time, and use the partial parsing cache in CI by persisting target/partial_parse.msgpack between runs. On projects with heavy Jinja, this alone can take thirty seconds off every command, as covered in macros and Jinja patterns.

Is --defer useful in production, not just CI?

Yes, for ad hoc rebuilds. Deferring unbuilt upstream models to production lets an engineer rebuild one mart in their dev schema without rebuilding the graph behind it. That is often the difference between a two-minute check and a forty-minute one.

How often should I re-run the critical path analysis?

Monthly, and after any large merge. The path moves as models are added and as data volume grows. The chain that dominated last quarter is frequently not the one dominating now.

What this means for your pipelines

dbt performance work has a natural order and most teams do it backwards. Compute the critical path first, because it tells you which of your nine hundred models are the only ones that matter. Set threads to the measured graph width, not to a number that sounds ambitious. Then attribute cost per model, so the next hour of optimization goes where the money is rather than where the irritation is.

The materialization choices follow from that data. Views for cheap and rarely read. Tables for the critical path. Incremental only with a predicate that prunes and a lookback window you measured. Dynamic tables or materialized views for anything whose real requirement is freshness rather than a nightly batch.

And before all of it, delete. Every large project carries models that nothing reads, and they are pure cost with no counterargument. That is the only optimization with no trade-off, which makes it the one to do first — and the one, in my experience, that teams put off the longest because it feels like admitting something rather than fixing it.