Tag: dbt

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.

Related reading: What really happens when you run a query · Micro-partitions and why pruning works · Why senior engineers write SQL differently · Stop recomputing unchanged models · Snowflake query profile docs

Our CI got slower and more expensive at exactly the same rate our test suite got better, and for a while nobody connected the two. We’d done everything the best-practice blog posts told us to: added dbt unit tests to lock down the gnarly transformation logic, wired them into CI so every pull request ran them, felt good about ourselves. Then the Snowflake bill for the CI account crept up, and up, and the person who opened it asked the reasonable question: “Why is *testing* our most expensive warehouse workload?”

Because dbt unit tests, for all their software-engineering framing, are not free the way unit tests in a normal codebase are free. A Python unit test runs in memory on the machine you’re already paying for. A dbt unit test compiles to SQL and runs on a Snowflake warehouse — and at the scale of a real suite, run on every pull request, that’s a lot of warehouse-seconds you didn’t budget for. This is the honest accounting of where that cost comes from, and how to keep the safety net without the bill.

One clarification up front, because the word “test” covers two very different things in dbt and conflating them muddies the whole cost conversation. This article is about unit tests — the dbt v1.8+ feature that validates your transformation logic against mock inputs — not data tests (not_null, unique, and friends) that query your real tables. Both cost credits, but they cost them differently, and unit tests have a hidden cost that surprises people.

TL;DR

→ dbt unit tests validate transformation logic with static mock inputs. But they don’t run in memory — each compiles to a real SQL query that executes on a Snowflake warehouse. Hundreds of tests × every CI run × every PR = real, recurring credit burn.

→ The hidden cost: a unit test’s direct parent models must exist in the warehouse before the test can run. Naively, that means building upstream models just to test one — you pay to materialize parents you don’t care about.

→ The fix for that specific trap: the --empty flag builds empty (zero-row) versions of the parent models, so they exist for the test to reference without paying to populate them.

→ dbt Labs is explicit: only run unit tests in development and CI, never in production. The inputs are static, so production runs burn compute for zero added signal.

→ Warehouse mechanics amplify it: Snowflake bills a 60-second minimum every time a warehouse resumes, so a suite that resumes the warehouse repeatedly pays that floor over and over.

→ The real levers: run only state:modified+ (tests for changed models, not the whole suite), use --empty parents, run on a dedicated XS warehouse with aggressive auto-suspend, and don’t unit-test logic the warehouse already guarantees (like min()).

How a dbt unit test actually runs

Here’s the thing the “just like software unit tests” framing hides. When you write a unit test in a normal language, the test harness loads your function into memory and calls it with fake arguments. Nothing leaves the machine. It’s effectively free and effectively instant.

A dbt unit test does something structurally different. dbt takes your mock input rows, your model’s SQL, and your expected output, and compiles them into a single SQL query — roughly, it injects your fake rows as inline literals, runs them through the actual transformation logic of the model, and compares the result to your expected rows. That compiled query then executes on your Snowflake warehouse like any other query. The “unit” is isolated in the sense that it uses mock data instead of real tables — but the execution is a genuine warehouse query, billed at the standard credit rate for the warehouse’s active time.

One query is cheap. The problem is arithmetic. A mature suite might have 300 unit tests. Run them on every pull request, and every push to every PR, across a team, and you’re issuing tens of thousands of compiled test queries a week. None is expensive alone; together they’re a line item. And unlike a data test that you might run once daily in production, unit tests fire in the tight inner loop of development, which is exactly where query volume is highest.

No single test query is expensive. The multiplication across a suite, every CI run, is — and two Snowflake billing mechanics quietly amplify it.

The hidden cost nobody mentions: parent materialization

A unit test needs its parent models to exist in the warehouse first. Build them naively and you pay to materialize upstream models just to test one — unless you use –empty.

This is the trap that turns a manageable cost into a surprising one. A dbt unit test runs against a model, and that model refers to its parents via ref(). For the compiled test query to resolve, the direct parent models have to exist in the warehouse. If they don’t, the test can’t run.

The naive reaction — and the one dbt’s own docs warn about — is to just build everything upstream first: dbt build, or dbt run the parents, then test. But that means you’ve now materialized a chain of upstream models, scanning and writing real data, purely so a logic test on one downstream model has something to reference. You paid full transformation cost to set up a test that was supposed to be about logic, not data.

The intended fix is the --empty flag. Running dbt run --select "stg_orders stg_customers" --empty builds empty versions of the parent models — they exist as objects in the warehouse with the right schema but zero rows, so they cost almost nothing to create. The unit test can now resolve its ref()s against those empty parents while still using its own mock data for the actual test. If you’re running unit tests in CI without --empty, this is very likely the single biggest chunk of your test-related spend, and it’s invisible until you look at what got built versus what got tested.

The warehouse mechanics that amplify it

Two Snowflake billing details make test-suite cost worse than a naive per-query estimate suggests.

First, the 60-second minimum. Snowflake bills warehouse compute per second, but with a 60-second floor every time a warehouse resumes from suspended. A single fast test query that takes two seconds still bills a full minute if it resumed a cold warehouse. If your CI pattern lets the warehouse suspend and resume repeatedly across a run, you pay that one-minute floor multiple times for work that totaled seconds.

Second, warehouse size is usually the wrong knob to reach for, but people reach for it anyway. Teams default to a LARGE or XLARGE warehouse “to be safe,” but unit tests operate on tiny mock datasets — a handful of rows. There is nothing for a big warehouse to parallelize. You’re paying 8x or 16x the per-second rate for a workload that an XSMALL handles identically. For unit testing specifically, warehouse size is close to pure waste above XSMALL.

The cost math, concretely

Let’s put rough numbers on it. Say you have 300 unit tests, a team of 6 engineers, and CI runs on every push. A realistic week might see 200 CI runs. If each run executes the full suite and — because nobody set up --empty — also materializes a chunk of the upstream DAG each time, you’re looking at hundreds of thousands of query-seconds plus repeated 60-second warehouse-resume floors.

Even at a modest XSMALL (1 credit/hour), the repeated resume floors alone add up: 200 runs a week that each cold-start the warehouse is 200 minutes — over 3 hours — of billed time that did almost no work. Add the parent materializations at full data volume and the number climbs fast. Now imagine someone “played it safe” with a MEDIUM warehouse (4 credits/hour): same work, 4x the bill. None of this bought you better tests. It bought you the same tests, slower to notice and more expensive to run.

The reframe that matters: unit-test cost scales with how you run the suite, not with how good your tests are. Two teams with identical test coverage can have a 10x difference in test spend based purely on --empty, warehouse size, and whether they run the whole suite or just what changed.

Keeping the safety net without the bill

Two teams with identical coverage can differ 10x in spend. These are the knobs that account for the gap, biggest win first.

Run only what changed. The biggest lever by far. In CI you rarely need the whole suite — you need the tests affected by this pull request. dbt’s state comparison lets you select state:modified+ to run only modified models and their downstream dependents. On a big project, a typical PR touches a handful of models, so this turns a 300-test run into a 15-test run. Same protection for the change at hand, a fraction of the queries.

Always build parents with --empty. Make it the default in your CI script, not an optimization you remember sometimes. Empty parents give the tests something to reference without paying to populate upstream models. This is the fix for the hidden cost above, and it’s a one-flag change.

Use a dedicated XSMALL CI warehouse with aggressive auto-suspend. Size it down — unit tests don’t benefit from more compute. Give it its own warehouse so test runs don’t tangle with analyst queries or production jobs, which also makes the cost trivially easy to attribute. Set auto-suspend low so it doesn’t idle-bill after the run, but be aware of the flip side: too-frequent suspend/resume cycles trigger the 60-second floor repeatedly, so tune it to your CI cadence rather than blindly to the minimum.

Never run unit tests in production. dbt Labs is unambiguous here, and it’s worth internalizing why: unit test inputs are static mock data, so the result is identical every time regardless of what’s in production. Running them against prod burns compute to re-confirm something that cannot have changed. Unit tests belong in development (test-driven work) and CI (catching regressions before merge) — full stop.

Don’t test what the warehouse already guarantees. dbt Labs recommends against unit-testing built-in functions like min() — they’re already exhaustively tested by Snowflake, and a fixture that checks them tells you nothing while still costing a query. Aim unit tests at logic that actually has edge cases: custom categorization, window functions, business rules, things you’ve had bugs in before. Every test you don’t write because it adds no signal is a query you never pay for.

The gotchas nobody warns you about

Incremental models need to exist before you unit-test them. Like other parents, an incremental model must be present in the database before its unit test runs, and the expected output of a unit test on an incremental model is the result of the materialization step (what gets merged/inserted), not the final table state. Build it with --empty first, same as any parent, and be precise about what you’re asserting.

Ephemeral parents force a format change. If the model under test depends on an ephemeral model, you can’t reference it the usual way — you have to provide that input as raw SQL (format: sql) in the test fixture. Not a cost issue, but a “why won’t this run” issue that eats debugging time.

The suite’s cost is invisible in aggregate dashboards. Test queries blend into total warehouse spend and look like noise. To actually see them, tag your CI runs — dbt’s query-comment with the invocation_id lets you filter QUERY_HISTORY to a single test run and total its cost. If you can’t measure per-invocation test spend, you can’t tell whether your fixes worked.

“Add more tests” has a non-zero marginal cost here. In a normal codebase, adding a unit test is free forever after. In dbt, every unit test you add is a query that runs on every relevant CI invocation for the life of the project. That’s not a reason to skip tests — it’s a reason to be deliberate: test high-value logic, skip the trivial, and let state:modified+ keep the per-run count proportional to the change, not the suite.

The one principle

A dbt unit test is not an in-memory assertion; it’s a warehouse query with a prerequisite. Treat it like one. Build parents empty, run only what changed, size the warehouse down, keep it out of production, and test logic that actually has edge cases. The goal isn’t fewer tests — it’s a test suite whose cost tracks the size of your changes, not the size of your suite. Do that, and unit testing stays the cheap safety net it’s supposed to be instead of the line item that makes someone ask why testing is your priciest workload.

Related reading: dbt: Unit tests (official docs) · Understanding costs for dbt Projects on Snowflake · dbt State on Snowflake: Skip Unchanged Models · Debugging Zero-Copy Clone Storage Costs in CI/CD · Orchestrating dbt With Airflow on Snowflake

You read about dbt State. You understood the pitch — skip unchanged models, cut compute, stop paying to rebuild what didn’t change. Then you opened your project and stared at an empty dbt_project.yml wondering where to actually start.

This is that guide. Not the concept. The implementation. Real configs, real gotchas, real Snowflake-specific decisions you’ll face inside the first hour of setup — covered here so you don’t have to learn them the hard way.

If you’re still fuzzy on what dbt State is and whether the pricing makes sense for your team, start with the conceptual breakdown here. This guide assumes you’ve made the decision and want to ship it.

TL;DR

→ dbt State is available in dbt Core v1.12+, Fusion, and the dbt platform. Enable via CLI, platform UI, or env var.

→ The most important config is lag_tolerance. Set tight in prod (4h), loose in dev (7d). Use Jinja so it’s one line, not two blocks.

→ On Snowflake, the “clone” reuse strategy uses Snowflake zero-copy cloning — it’s nearly instant and nearly free. This is why dbt State feels especially fast on Snowflake vs other warehouses.

→ Layer your lag_tolerance by folder: staging loose (data changes often), marts tight (business metrics need to be fresh).

→ Incremental model gotcha: the first time any incremental model runs, its compiled SQL changes (from full load to filtered load). dbt State treats this as a logic change and forces a downstream rebuild, even if lag_tolerance hasn’t elapsed. Expected behavior. Not a bug.

→ Track your skip rate using Snowflake’s QUERY_HISTORY view with the query_tag dbt sets automatically.

→ In CI: set DBT_ENGINE_MANAGE_STATE=true as an env var. Nothing else changes in your CI pipeline.

→ Target 40–60% skip rate in the first two weeks. Below 30% means your lag_tolerance is too tight. Above 80% means you’re missing rebuilds you actually need.

Step 1: Enable dbt State

There are three paths depending on what you’re running.

dbt platform (Cloud): Go to Account settings → State → Enable dbt State. Then on each job: Settings → Edit → Execution settings → Enable dbt State → Save. That’s it. No YAML changes required to turn it on.

dbt Core v1.12+ locally: Run dbt login in your terminal. It opens a browser window to authenticate with either your dbt platform account or the standalone app at app.state.dbt.com. Once authenticated, dbt State runs automatically on every dbt run or dbt build. You can override per-run with --manage-state or --no-manage-state.

CI pipelines: Set DBT_ENGINE_MANAGE_STATE=true as an environment variable. Pass your dbt State credentials as DBT_STATE_TOKEN from your secrets manager. No other pipeline changes needed.

Step 2: Configure lag_tolerance — the decision that matters most

The default lag_tolerance is 45 minutes. Meaning: if upstream data changed less than 45 minutes ago, dbt State will skip the downstream model. If data changed more than 45 minutes ago and the model hasn’t rebuilt, it will rebuild.

Forty-five minutes is a sensible global default, but in practice you want different tolerances for different environments and different parts of your DAG. Here’s the recommended starting config in dbt_project.yml:

models:
+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"

That single Jinja expression does the right thing in both environments without duplicating config blocks. In production, models rebuild if upstream data is more than 4 hours stale. In development — where you’re iterating, not serving dashboards — models wait 7 days before triggering a rebuild. Your dev environment borrows production data through the clone strategy and doesn’t thrash on every upstream source change.

Once you’ve run this for a week and have a feel for your actual skip rates, tune individual layers:

models:

+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"
your_project:
staging:
+state:
lag_tolerance: "{{ '1h' if target.name == 'prod' else '3d' }}"
marts:
+state:
lag_tolerance: "{{ '2h' if target.name == 'prod' else '7d' }}"

Staging models sit closer to raw sources that change frequently — tighter tolerance makes sense. Marts feed dashboards and executive reports — tighter tolerance there too, but not so tight that you’re rebuilding on every minor source refresh.

Step 3: Set pre_clone for development environments

pre_clone controls whether dbt State clones from production before running your model in a dev environment. This is where Snowflake’s zero-copy cloning makes dbt State genuinely fast — a clone of a 500GB fact table takes seconds and costs almost nothing in storage.

models:
+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"
pre_clone: "{{ 'always' if target.name in ['dev', 'ci'] else 'if_missing' }}"

The options are:

always — Clone from prod every time before running the model, even if a version already exists in your schema. Use this in dev when you want a fresh production baseline on every run.

if_missing — Default. Clone from prod only if the table doesn’t exist in your schema yet. After the first clone, subsequent runs work on your existing version.

never — Don’t clone. Build from scratch if the model needs to run. Rarely what you want.

For CI jobs, always makes sense — each CI run should start from the current production state. For personal dev schemas, if_missing is usually fine after your initial setup.

Step 4: Snowflake-specific config that pairs well with dbt State

Layer your warehouse sizing, materialization, and lag_tolerance together. dbt State skip rate is highest on large mart tables where Snowflake cloning is fastest.

A few Snowflake-specific configs pair directly with dbt State behavior.

query_tag for tracking. dbt sets a query tag automatically on Snowflake sessions. Use it to track which models are being built vs skipped:

# dbt_project.yml
models:
+query_tag: "dbt_{{ target.name }}_{{ model.name }}"

Then query Snowflake’s QUERY_HISTORY to see your actual skip rate and time saved:

SELECT
query_tag,
COUNT(*) AS query_count,
SUM(total_elapsed_time) / 1000 AS total_seconds,
SUM(credits_used_cloud_services) AS credits_used
FROM snowflake.account_usage.query_history
WHERE query_tag LIKE 'dbt_%'
AND start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP)
GROUP BY 1
ORDER BY credits_used DESC;

This gives you a concrete before/after when you want to demonstrate ROI.

transient vs permanent tables. By default, all dbt-created Snowflake tables are transient (no Fail-safe, 1-day time travel). Transient tables are slightly cheaper on storage. For models that dbt State frequently clones — large mart tables — transient is fine because you can always re-clone from production. For models you want full Snowflake time travel on, set transient: false explicitly:

models:
your_project:
marts:
+transient: false # Keep full time travel on business-critical marts
staging:
+transient: true # Transient fine — replaceable by clone

warehouse sizing per layer. dbt State reduces how many models actually run — which means you can tune warehouse sizes down without hurting throughput. If 40% of your models are being skipped, your MEDIUM warehouse is running at 60% utilization anyway. Consider dropping staging to XSMALL and only using MEDIUM for mart builds:

models:
your_project:
staging:
+snowflake_warehouse: XSMALL
intermediate:
+snowflake_warehouse: SMALL
marts:
+snowflake_warehouse: MEDIUM

Step 5: The incremental model gotcha you will hit

The incremental model first-run gotcha: compiled SQL changes between run 1 and run 2. dbt State sees this as a logic change. Expect a full downstream rebuild on run 2. After that, behavior stabilizes.

This one catches most teams in the first week. Here’s what happens.

The first time an incremental model runs, dbt executes a full load — no WHERE clause, because there’s no existing table to filter against. The compiled SQL looks like:

SELECT id, amount FROM raw_orders

On the second run, is_incremental() becomes true. The compiled SQL now looks like:

SELECT id, amount FROM raw_orders
WHERE id > (SELECT MAX(id) FROM fct_orders)

dbt State sees this as a query logic change on fct_orders. It then marks every downstream model that depends on fct_orders as needing a rebuild — even if their own lag_tolerance hasn’t elapsed and their own code hasn’t changed.

This is not a bug. It’s correct behavior — the incremental model’s SQL did change, semantically. But it means: the first two runs after enabling dbt State on an incremental model will look worse than steady-state. Run 1 is a full build. Run 2 triggers a full downstream rebuild. Run 3 onwards is where your skip rate actually stabilizes and starts saving you money.

If you’re evaluating dbt State’s ROI, don’t measure it on day 1 or day 2. Give it a week of runs.

Step 6: defer_to_target and environment setup

By default, dbt State defers to your production environment. If your production environment isn’t literally named “prod” in your dbt platform setup, you’ll need to be explicit:

# dbt_project.yml
models:
+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"
defer_to_target: production # name of your prod environment in the platform

For dbt Core users managing their own targets, this is set in profiles.yml:

# profiles.yml
my_project:
outputs:
prod:
type: snowflake
defer_to_target: production
# ... rest of connection config

In practice: make sure the environment you’re deferring to is actually up to date. If production last ran 48 hours ago and your dev environment is trying to clone from it, you’re cloning stale data. The lag_tolerance in dev being set to 7d means you’re fine with data that’s up to 7 days old in dev — that’s usually acceptable. Just be aware of the tradeoff.

What good skip rates look like on Snowflake

In the first two weeks after enabling dbt State on a mature Snowflake project, here’s what typical skip rates look like by layer:

Staging models: 20–40% skip rate. These sit close to raw sources that change frequently. You won’t skip many — and that’s expected.

Intermediate models: 40–65% skip rate. These transform staging output into business-ready structures. When staging is unchanged, intermediate skips cleanly.

Mart models: 55–80% skip rate. These are the expensive ones — large aggregations, heavy joins, wide tables. This is where dbt State pays for itself. A skipped mart model on a MEDIUM Snowflake warehouse is 30–60 seconds of compute you’re not paying for, every run.

If your overall skip rate is below 30%, your lag_tolerance is probably too tight — data is changing within the tolerance window and everything is rebuilding. Loosen it and see what happens. If your skip rate is above 80% in production, double-check that you’re not accidentally skipping models that should be rebuilding. Query QUERY_HISTORY and verify the freshness of your mart tables against your source freshness.

Three things that will trip you up

Volatile SQL functions force a rebuild. If any model uses CURRENT_TIMESTAMP(), RANDOM(), or UUID_STRING() in its SQL, dbt State treats the query as non-deterministic and won’t skip it — ever. These functions mean the compiled SQL could produce different results on every run, so skipping isn’t safe. Audit your models for volatile functions if your skip rate is suspiciously low.

LAST_ALTERED in Snowflake is misleading. Snowflake updates the LAST_ALTERED timestamp on a table whenever any metadata operation occurs — not just when data changes. If you’re using LAST_ALTERED in any custom freshness logic, it will report tables as “changed” more often than they actually are, leading to unnecessary rebuilds. dbt State doesn’t use LAST_ALTERED internally (it tracks actual data freshness via source freshness checks), but if you have custom macros that do, fix them.

Dynamic tables and dbt State don’t fully overlap yet. Snowflake’s dynamic tables refresh automatically based on target_lag. If you’re using the dynamic_table materialization in dbt, dbt State’s lag_tolerance config is separate from Snowflake’s target_lag. They’re two different systems tracking two different things. Don’t assume they’re in sync — they’re not. Manage them explicitly.

The full dbt_project.yml starting point

Here’s a production-ready starting config for a typical Snowflake dbt project with dbt State enabled:

name: 'your_project'
version: '1.0.0'
profile: 'your_project'

models:
+query_tag: "dbt_{{ target.name }}"
+state:
lag_tolerance: "{{ '4h' if target.name == 'prod' else '7d' }}"
pre_clone: "{{ 'always' if target.name in ['dev', 'ci'] else 'if_missing' }}"

your_project:
staging:
+materialized: view
+snowflake_warehouse: XSMALL
+state:
lag_tolerance: "{{ '1h' if target.name == 'prod' else '3d' }}"

intermediate:
+materialized: table
+transient: true
+snowflake_warehouse: SMALL

marts:
+materialized: table
+transient: false
+snowflake_warehouse: MEDIUM
+state:
lag_tolerance: "{{ '2h' if target.name == 'prod' else '7d' }}"

This gives you environment-aware lag tolerances, warehouse sizing tuned per layer, zero-copy clone behavior in dev and CI, and full Snowflake time travel on your business-critical mart tables.

One principle

dbt State doesn’t change what you build — it changes what you prove you don’t need to rebuild. The configs above aren’t magic. They’re your team’s explicit statement about how fresh each layer of your project needs to be, enforced automatically on every run. Get the lag tolerances right and the skip rate takes care of itself.

Related reading: dbt State: The concept, the cost math, and when it pays off · Official dbt State setup docs · lag_tolerance config reference · dbt Fusion: 30x Faster Parsing · Snowflake Query Execution: What Really Happens