Optimizing dbt Models for Modern Warehouses: An Author & Reviewer’s Guide

Two dbt models can be byte-for-byte identical in output, pass every test, and read cleanly in review — and one of them costs $50 a month while the other...

Two dbt models can be byte-for-byte identical in output, pass every test, and read cleanly in review — and one of them costs $50 a month while the other costs $5,000. The compiler already guarantees the SQL is correct. What it can’t tell you is how much data moved through the model to produce that correct answer, and that number is the entire ballgame. The trap is that a code review reads a model like prose — do the joins make sense, are the columns right — when the thing that actually determines the bill is invisible in the text and only shows up in the query profile.

So this guide is about reading the profile, not just the SQL. The single skill that separates engineers who write cheap models from ones who write expensive ones is the ability to look at a query profile and see where data volume balloons or lingers — then trace that back to the one line of SQL responsible. Everything below hangs off one mental model, and I’ll show you what each fix looks like in an actual profile, because “trust me, it’s faster” is worth a lot less than “here’s the partition count before and after.”

The one mental model: how much data survives each stage?

Before any checklist, ask one question of every model: how much data survives each stage of this query? A healthy model’s volume shrinks, roughly monotonically, from raw input to final output. A broken one has a stage where volume grows — or stays huge longer than it needs to — and that stage is almost always where your money goes.

Left: volume shrinks stage by stage — a well-shaped query. Right: the join ran before the filter, producing 12 TB of intermediate data from a 5 TB input. The SQL is syntactically perfect and still ruinous.

Every tactic in this post is a specific instance of that one idea: find the stage where volume grows or stays too big for too long, and fix that stage. That’s it. The rest is knowing the four places it usually happens and what each looks like in the profile.

Why this matters more on modern warehouses

Snowflake, BigQuery, Redshift, and Databricks all ship genuinely capable optimizers. They handle predicate pushdown, join reordering, and parallel execution for you, which means the physical tuning you’d have obsessed over on a 2005-era database — index hints, manual join order, rewriting for a specific plan — mostly doesn’t apply. The engine owns that layer now. If you want the mechanics of how that execution layer actually works, I covered it in what really happens when you run a query.

What the optimizer can’t fix is a logical mistake: joining before filtering, reading columns you don’t need, recomputing the same aggregation five times, or choosing ROW_NUMBER() when MAX() would do. Those decisions are baked into the SQL, and no optimizer can rewrite your intent. That’s why the highest-leverage review comments are almost never about syntax — they’re about which stage of the volume curve a change affects.

1. Read less data

The biggest lever, and the first thing to check. On a columnar warehouse, unused columns cost real I/O even though the query works either way:

-- Bad: pulls every column off a 200-column table
SELECT * FROM customers

-- Better: only what's used downstream
SELECT customer_id, country FROM customers

The one that quietly defeats people is partition pruning, because the query looks filtered but is structured so the engine can’t use the filter. Wrapping the filtered column in a function is the classic killer:

-- Bad: the function hides order_date from the optimizer
WHERE YEAR(order_date) = 2026

-- Good: a plain range predicate the engine can prune on
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01'

This is the single highest-value fix in the whole post, and it’s the one worth seeing rather than taking on faith

The profile tells the story the SQL hides: the function-wrapped predicate scanned all 512 partitions (1.42 TB, 94s); the range predicate pruned to 3 partitions (9 GB, 3.4s). Same result, same rows out — a ~150x difference in data read.

When a filter or join predicate isn’t reducing data the way you’d expect, check for a hidden function first — CAST(date AS DATE), UPPER(email), COALESCE(col, 0) all do the same damage. This is exactly why Snowflake maintains min/max metadata per micro-partition, and why a function over the column throws that metadata away; I unpacked that storage mechanism in how Snowflake stores data internally.

2. Join wisely

Joins are where a well-behaved query most often turns into a runaway one. The single question worth asking on every join in a review: is this actually the cardinality I think it is? A join you assumed was 1:1 becomes a many-to-many explosion the moment a source table has duplicate keys — 100M orders against 500M clicks on customer_id can produce tens of thousands of rows per customer, and it’s invisible until someone notices the output count is absurd.

The highest-value structural fix is to aggregate before you join, not after:

-- Bad: join the full 800M-row payments table, then aggregate
SELECT o.customer_id, SUM(p.amount)
FROM orders o
JOIN payments p ON o.customer_id = p.customer_id
GROUP BY o.customer_id

-- Better: reduce payments to 10M rows first, then join
WITH payments_agg AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM payments
    GROUP BY customer_id
)
SELECT o.customer_id, pa.total_amount
FROM orders o
JOIN payments_agg pa ON o.customer_id = pa.customer_id

Same result — but the join now processes 10M rows instead of 800M, because the reduction happened before the join instead of after. The profile makes the difference impossible to miss — watch the row count going into the join, and the spill:

Aggregating first shrinks the join’s input from 800M rows to 10M — which also eliminates the disk spill that was quietly dominating the runtime. Same output, ~11x faster.

Two more join checks worth a glance: watch for skew (one dominant key value — a 90%-US country column, or a flood of NULLs — creates wildly unbalanced work even when the total row count looks fine), and verify every join has a real predicate (a missing condition turns a join into a cartesian product, where row counts don’t grow, they multiply).

3. Don’t recompute what you already computed

Is the same large table scanned more than once? If two CTEs both pull from big_table, ask whether one pass can derive both results. Is an expensive expression — a long CASE block, a repeated subquery — computed several times instead of once in a CTE? And watch for SELECT DISTINCT used as a band-aid: it’s very often papering over a join producing duplicate rows it shouldn’t. If a model “suddenly needs” DISTINCT, that’s a prompt to find the join that changed, not to accept the DISTINCT as the fix.

4. Reduce before expensive operations — and question the tool itself

Push filters ahead of window functions: running ROW_NUMBER() over 5 billion rows when the same logic could run over 100 million after an earlier filter is a common, easy-to-miss cost. But the most valuable and most overlooked review question isn’t about tuning what’s there — it’s whether the approach itself is right:

-- Heavier than needed: full partition + sort to get "latest"
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY order_date DESC) AS rn
    FROM orders
) WHERE rn = 1

-- Often cheaper: if you only need the date, not the whole row
SELECT customer_id, MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id

If the goal is genuinely “the latest order date per customer,” MAX() with a GROUP BY does far less work than a full partitioned sort. ROW_NUMBER() earns its keep only when you need the entire row at the latest timestamp — a surprising number of “slow query” tickets are really “wrong tool for the job” tickets. In the profile, the tell is the WindowFunction node sorting billions of rows and spilling to disk, when the aggregate version never sorts at all:

The window function sorts all 5 billion rows and spills 210 GB to disk; MAX() never sorts. Same answer, and it also runs on a smaller warehouse — an ~8x time win on top of a halved credit rate.

For the reproducible-dedup case where you do need the whole row, the deterministic QUALIFY pattern is the right tool, which I covered in why senior engineers write SQL differently.

5. Materialization and recomputation — the dbt-specific one

This is where a lot of warehouse spend hides in plain sight, and it’s two questions. First: is this the right materialization? A very common finding is a model that’s been a plain table since day one, fully recomputed on every run, long after it grew large enough that full rebuilds stopped being free.

Left: the materialization decision, which is really a “how often does this change vs how expensive is it to rebuild” question. Right: who should catch each class of issue — push everything mechanical left toward the author and CI.

Second: is there a missed incremental opportunity? If a model recomputes five years of history every run when only yesterday’s data changed, that’s usually the single highest-value fix available — often bigger than every tactic above combined. The question to ask: does this model’s WHERE clause know about is_incremental(), or is it silently doing a full rebuild every time? The profile for a full-rebuild model is unmistakable — it scans the entire history on every run:

The full-rebuild model reprocesses 3.2 billion rows every single run to change a sliver of data; the incremental version scans one pruned day and merges 1.8M rows. This is where the order-of-magnitude cost wins usually hide.

The same “don’t reprocess what didn’t change” discipline is the whole premise of dbt state-based selection at the project level.

Who should catch each of these

Treating this whole list as “what the reviewer checks” is the wrong default — it makes review slow and contentious. Three owners share it. The author, before opening the MR, catches anything mechanically verifiable by running the query and looking at the output: SELECT *, an unfiltered scan, a repeated CTE. The CI pipeline catches what can be automated: pruning regressions, row-count guards, lint rules. The reviewer is left with what only a human can see — cross-model blast radius (“this join also feeds the finance mart”), organizational memory (“we already know this key is skewed”), and whether the approach fits the business need. The goal over time is to shrink the reviewer’s column: every item that graduates from “reviewer catches it” to “CI catches it” is a permanent win.

The gotchas nobody warns you about

A function on a filtered column silently disables pruning. YEAR(order_date), CAST, UPPER, COALESCE on the predicate column all throw away the partition metadata. The query looks filtered; the profile shows every partition scanned.

DISTINCT is usually a symptom, not a fix. If a model started needing SELECT DISTINCT, a join is producing duplicates it shouldn’t. Fix the join; don’t dedupe the mess.

The profile, not the SQL, tells you the truth. Two models with identical output can differ 100x in bytes scanned. If you’re optimizing without reading the profile, you’re guessing — check partitions scanned and bytes scanned before and after every change.

Full-rebuild tables are the biggest silent cost. A table materialization recomputing history every run often dwarfs every other inefficiency combined. Check materialization strategy before micro-optimizing the SQL.

ROW_NUMBER() is frequently the wrong tool. If you only need an aggregate, not the whole row, a GROUP BY is cheaper than a partitioned sort. Confirm the requirement before defaulting to a window function.

The one principle

Writing an efficient dbt model isn’t about SQL syntax — the compiler already guarantees correctness — it’s about mentally tracing the volume of data at each stage and asking whether that stage makes the data smaller or just makes more work for the next one. Modern warehouses optimize the physical layer for you; they can’t decide to aggregate before joining or reach for MAX() instead of ROW_NUMBER(). Those are logical choices made in the SQL, and the further upstream you catch them — author self-check, then CI, then reviewer — the cheaper they are. Learn to read the profile, and the order-of-magnitude wins stop being luck.