Performance and Cost Optimization

Analogy categoryAnalogies

Every dbt run's cost is ultimately warehouse compute cost — Snowflake credits, BigQuery bytes scanned, or Redshift cluster time — so optimizing dbt performance and optimizing dbt spend are the same exercise viewed from different angles. The two highest-leverage levers are materialization strategy (choosing table, view, incremental, or ephemeral appropriately per model) and reducing the volume of data scanned or recomputed per run, since warehouses typically bill by either compute time or bytes processed, and a model that reprocesses its entire history on every run multiplies both.

🏏

Cricket analogy: It's like a fast bowler managing workload across a five-Test series — bowling every over at full pace (rebuilding everything every run) burns them out, while pacing effort strategically (incremental builds) sustains performance across the whole series.

Incremental Models and Materialization Choice

The single biggest cost lever for large fact tables is switching from table (full rebuild every run) to incremental materialization, using a strategy like merge or delete+insert with an is_incremental() block that filters to only new or updated rows since the last run, typically based on a timestamp column with a lookback window to catch late-arriving data. Views cost nothing to store but re-execute their SQL on every query, making them appropriate for lightweight staging models, while ephemeral models are inlined as CTEs into downstream queries with no separate object at all, avoiding storage but potentially duplicating computation if referenced by many downstream models.

🏏

Cricket analogy: It's like a scorer updating only the latest over's tally onto a running scorecard rather than re-tallying the whole match from ball one after every over — incremental models update only new rows, not the full history.

Query and Storage-Level Optimization

Beyond materialization, warehouse-specific physical design choices meaningfully cut cost: clustering keys in Snowflake or partitioning by a date column in BigQuery let the warehouse prune unscanned micro-partitions entirely, so a query filtered to last_7_days scans gigabytes instead of the full table's terabytes. dbt exposes these as model config — cluster_by, partition_by, and incremental_strategy — directly in the model's config block or a dbt_project.yml default, and pairing them with the concurrency setting threads in profiles.yml (which controls how many models dbt builds in parallel) lets teams balance total run wall-clock time against warehouse concurrency slot limits and credit consumption.

🏏

Cricket analogy: It's like a fielding captain setting a targeted field for a specific batter's weakness rather than a generic field for everyone — partitioning lets the warehouse 'set a field' that prunes irrelevant data instantly.

sql

-- models/marts/fct_orders.sql
{{
  config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    partition_by={'field': 'order_date', 'data_type': 'date'},
    cluster_by=['customer_id']
  )
}}

select
  order_id,
  customer_id,
  order_date,
  order_total
from {{ ref('stg_orders') }}

{% if is_incremental() %}
  -- only process new/updated rows, with a lookback window for late-arriving data
  where order_date >= (select dateadd('day', -3, max(order_date)) from {{ this }})
{% endif %}

dbt's query-tagging (via the query_comment config or warehouse-specific query tags) lets you attach the invoking model's name to every SQL statement dbt sends, which is invaluable for filtering warehouse cost dashboards like Snowflake's QUERY_HISTORY view down to exactly which dbt models are the most expensive to run.

Over-parallelizing with a high threads count in profiles.yml can backfire: on warehouses with limited concurrency slots (like a small Redshift WLM queue), too many simultaneous dbt model builds cause queueing that increases total wall-clock time even though more models are technically running 'in parallel.'

Warehouse compute cost and dbt run performance are two views of the same underlying spend, since most warehouses bill by compute time or bytes scanned.
Switching large fact tables from table to incremental materialization (with is_incremental() and a lookback window) is the highest-leverage cost lever.
Views cost nothing to store but recompute on every query; ephemeral models avoid storage but can duplicate computation across many references.
partition_by and cluster_by config let the warehouse prune unscanned data, cutting bytes scanned dramatically for filtered queries.
threads in profiles.yml controls dbt's build parallelism, but too high a value can cause warehouse queueing and worsen wall-clock time.
Query tagging attaches the invoking dbt model's name to warehouse queries, enabling cost attribution per model.
Optimization should target the specific bottleneck (storage cost, scan cost, or run duration) rather than applying every technique uniformly.