dbt Performance Tuning: Practical Tips to Cut Run Time

dbt run time is heavily influenced by the DAG critical path, materialization choices, incremental strategy, selection, parallelism, and DWH-specific optimizations. Rather than optimizing blindly, the fastest route is to factor the problem and tackle each piece in turn.

This article focuses on stable features from the official dbt documentation and packs in both the principles likely to appear on exams and the settings that actually move the needle in practice. First, surface the bottlenecks. Then cut the work with materializations and selection. Finally, lift the floor with parallelism and DWH features.

The Big Picture of the Execution Plan and Finding Bottlenecks

Before optimizing, measure where the time is actually going. dbt emits run_results.json and manifest.json, which let you inspect each node's start/end timestamps and dependencies. Shortening heavy nodes on the critical path is what shrinks total wall-clock time the most.

Stay aware of the DAG's shape. In a fan-out diagram, the longest narrow chain tends to be the critical path, and large joins, sorts, or re-aggregations on that chain are the usual culprits. Narrow your target first, then change the material (materialization).

• Check each model's run-time in seconds and status via run_results.json
• Prioritize the critical path. Shorten the longest chain in the fan-out
• Eliminate unnecessary recomputation. Cut over-aggregation upstream and wasteful DISTINCT
• Keep the staging layer light. Minimize selected columns and filter early to reduce data volume

DAG and critical-path illustration

 [src] ----> stg_orders ----> dim_customer ----> fct_sales ----> rpt_sales
    \                     \-> dim_product ----/
     \-> stg_events ---------------------------> fct_engagement

  Critical path: src → stg_orders → dim_customer → fct_sales → rpt_sales

Extract top bottlenecks from run_results.json (example)

jq -r '.results[] | {node: .unique_id, time: (.execution_time|tonumber)} | select(.time!=null) | [.time, .node] | @tsv' target/run_results.json \
  | sort -nr \
  | head -n 10

Picking Materializations and Using Incremental Models Effectively

The single most effective lever is the materialization. Choosing between view and table — both of which trigger full recomputation — and deciding whether incremental can shrink work from the second run onward will dominate your run time. Incremental requires a consistent unique_key and strategy (MERGE, DELETE+INSERT, INSERT_OVERWRITE, etc., depending on the adapter).

The base pattern: huge facts should be incremental, widely referenced dimensions should be tables, and small, frequently changing shaping logic should be views or ephemeral. Exams ask about this principle as well.

• Make incremental conditions explicit (append-only or with updates, where the recompute boundary lies)
• Keep unique_key and is_incremental conditions consistent. Design so MERGE never fails on duplicates or missing rows
• If upstream dependencies are not incremental, the benefit halves. Make upstream consistently incremental
• Use ephemeral for small reference tables to skip intermediate writes (within the same run only)

Safe incremental-model pattern (per-adapter strategy dispatch example)

{{
  config(
    materialized='incremental',
    unique_key='order_id',
    tags=['fact','critical'],
    **(
      {'incremental_strategy': 'merge'} if target.adapter in ['snowflake','databricks','spark'] else {}
    )
  )
}}

with src as (
  select * from {{ ref('stg_orders') }}
  {% if is_incremental() %}
    where _ingested_at >= dateadd(day, -2, current_timestamp)
  {% endif %}
)

select
  order_id,
  customer_id,
  order_date,
  total_amount
from src

Use Selection and State to Run Only the Minimum

Avoid running the full DAG every time and restrict execution to the minimum blast radius of your change. Define criteria in selectors.yml and combine state:modified with parent/child plus syntax — that is the standard pattern. Pairing --defer with --state lets you build the delta locally while depending on the production artifacts.

dbt build runs models, seeds, snapshots, and tests in one shot. If shrinking time is the goal, useful operational splits include running tests only on changes and pushing heavy data tests to overnight jobs.

• Use state:modified+ to target changes and their downstream only
• Use --defer --state to build the delta while referencing production models
• Split heavy data-quality tests into a batch job to shorten the dev loop
• Prioritize critical-tagged models via selectors

selectors.yml and CLI examples

# selectors.yml
selectors:
  - name: modified_and_downstream
    definition:
      union:
        - method: state
          value: modified
          children: true
        - method: tag
          value: critical

# Build only the delta (defer to production)
dbt build --select selector:modified_and_downstream --defer --state target

# Shorten the dev loop further (skip tests)
dbt build --select selector:modified_and_downstream --exclude resource_type:test --defer --state target

Optimizing Parallelism and Job Structure

Higher threads does not always mean faster. DWH concurrency limits, I/O bandwidth, and lock contention impose a ceiling. Start by measuring from a moderate parallelism level, and stay aware of DAG layers so you can split the critical path.

If intermediate-table writes are the bottleneck, replace them with ephemeral to cut writes. Conversely, if a heavy subquery is re-used by multiple downstream models, materialize it as a table to avoid recomputation.

• Tune threads in steps (e.g., 4 → 8 → 16)
• Pull models with heavy I/O contention out of parallel runs (controlled by tags)
• Use ephemeral to reduce writes; materialize logic shared across many models as a table
• Run long-running models first in a dedicated job, then parallelize the rest

profiles.yml and per-model parallelism control example

# profiles.yml (example)
my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: ...
      user: ...
      password: ...
      role: ...
      warehouse: XSMALL
      database: ...
      schema: ...
      threads: 8

-- Split heavy models into a separate job via tags
# In dbt Cloud: Step1: tag:heavy, Step2: tag:not heavy, etc.
# Local example
dbt run --select tag:heavy && dbt run --select tag:light

Safely Adopt DWH-Specific Optimizations

Use DWH features cautiously and measure as you go. On Snowflake, lean on the automatic micro-partition clustering as a baseline and only set explicit clustering keys when truly needed. Balancing result cache and warehouse size also matters. On Databricks, run Delta Lake OPTIMIZE or Z-ORDER via post-hooks to reduce scan volume.

In every case, the benefit depends on data distribution and query patterns. Do not apply these mechanically to every table — pick the columns deliberately and decide based on measurement.

• Snowflake: lean on result cache. Big win during development with many reruns
• Snowflake: limit clustering keys to update patterns and filter columns
• Databricks: use OPTIMIZE and Z-ORDER on large read-heavy tables
• Restrict post-hooks via tags or size conditions to avoid runaway cost

DWH optimization via post-hooks (conditional)

{{
  config(
    materialized='table',
    tags=['large','read_heavy'],
    post_hook=(
      [
        "OPTIMIZE {{ this }} ZORDER BY (order_date)",
      ] if target.adapter == 'databricks' else []
    )
  )
}}
select * from {{ ref('fct_sales') }}

Lighten Tests and CI to Speed Up Iteration

Optimize the count and weight of test runs without compromising on quality. Push heavy data tests to overnight runs and keep the dev loop focused on schema tests. Run tests only on models tied to state:modified, and split full regression into a daily job.

Snapshot and seed full reloads are also expensive. Keep their frequency minimal and use --full-refresh only when truly required.

• Development: run only generic tests on changed models
• Regression: bundle full tests and docs generation into an overnight job
• For seeds, consider a delta-load policy (depending on size and update frequency)
• Use --full-refresh deliberately. Set up a dedicated job to prevent misuse

Test selection examples and accident prevention

# Test only the delta (generic tests only)
dbt test --select state:modified+ test_type:generic --defer --state target

# Restrict full-refresh to a dedicated job
dbt build --full-refresh --select tag:allow_full_refresh

Check Your Understanding

Frequently Asked Questions