dbt Incremental & Cost Optimization

The reasoning

First run: `is_incremental()` is false because the target does not exist, so the guarded filter is not emitted at all. The compiled SQL is a select over everything, and the run SQL is `create table ... as (...)`. Identical in shape to a `table` model.

Second run: the guard is true, so the filter compiles in — usually a comparison against `max(updated_at)` in `{{ this }}`, the table being built. dbt then does something the model file never mentions: it materializes your select into a temporary relation, and issues a second statement to combine that temporary relation with the target. Reading `target/run/` is the only way to see this.

Which combining statement you get depends on the strategy, and the default is adapter-specific — `merge` on Snowflake, BigQuery and Databricks, `append` or `delete+insert` elsewhere. That is worth stating explicitly, because an answer that says "it does a MERGE" is right on some warehouses and wrong on others.

The consequence to carry into every other question: an incremental model's output is a function of every run it has ever had, not just of its inputs. A `table` model is correct by construction. An incremental one is correct only if the filter, the key and the strategy agree — which is why the check that matters is comparing it against a full refresh.

The reasoning

`append` just inserts the new batch. No key, no comparison, cheapest, and it duplicates anything that arrives twice — so it is right only for immutable event streams where you can guarantee at-most-once delivery, and wrong almost everywhere else.

`delete+insert` deletes the rows whose unique key appears in the new batch, then inserts the batch. Two statements: on adapters without transactional DDL there is a window where the row exists nowhere, and a failure between them leaves the target missing rows. It is the fallback where merge is unavailable.

`merge` is a single MERGE statement matching on the unique key, updating on match and inserting otherwise. Atomic, and it exposes `merge_update_columns` / `merge_exclude_columns` so you can update some columns and preserve others — which is how you keep a `first_seen_at` while refreshing everything else.

`insert_overwrite` replaces whole partitions rather than individual rows: dbt works out which partitions the new batch touches and swaps them wholesale. On BigQuery and Spark it is the cheapest correct option for partitioned tables, because it never scans the target to find matches. It needs the model partitioned on the same column the batch is bounded by, and it will silently wipe a partition that the batch covers only partially. `microbatch` is the newer, declarative version of the same idea.

The reasoning

`unique_key` is what turns an insert into an upsert. With it, dbt matches incoming rows against the target and updates or replaces; without it, there is no matching step at all and the batch is appended. The append strategy makes that explicit; other strategies fall back to appending when no key is given.

The failure is quiet in exactly the way that matters. The run succeeds, row counts go up, and a `sum(amount)` grouped by customer is now wrong by the corrected amount. Nothing in dbt objects, because appending rows is what you asked for.

What catches it is the comparison against a full refresh, which sees each order once. That is the check to run periodically on any incremental model, and it is the reason a `unique` test on the key belongs on every incremental model that claims one — it fails the moment the second version lands.

When is no unique key correct? When the model genuinely is an append-only event log and the source guarantees each event is emitted once, or when your batch boundary makes re-delivery impossible. Both are stronger guarantees than most pipelines have, which is why the safe default is to set a key and use merge, and to relax it only with a reason.

The reasoning

`incremental_predicates` adds conditions to the merge's ON clause against the target side. The intent is real: without it a merge scans the whole target to find matches, and on a table with billions of rows partitioned by date that is most of your cost. Restricting the target to recent partitions lets the warehouse prune.

The correctness condition is the one to state: the predicate must cover every row the batch could match. If the batch can contain a correction for a row older than the predicate window, that row is invisible to the merge, and "no match" means "insert" — a duplicate, in a model whose whole point was that the key was unique.

So the predicate and the source filter have to agree. If you tolerate three days of lateness in the incoming batch, the predicate needs at least three days of target, and preferably more. Getting them out of step is easy because they live in different parts of the config and nothing checks that they are consistent.

The safer alternative when the warehouse supports it is `insert_overwrite` or `microbatch`: replace whole partitions rather than matching rows, so there is no scan-versus-match trade-off to get wrong. And whichever you pick, a `unique` test on the key is what turns this from a silent duplication into a failing build.

The reasoning

You declare `event_time`, `batch_size` and `begin` instead of writing a filter. dbt splits the run into one batch per period, generates the `where event_at >= ... and event_at < ...` bounds for each, and executes them independently. Your model body has no `is_incremental()` guard and no watermark subquery in it at all.

Three things follow. Batches are independent, so one failing batch does not stop the others and can be retried alone — where a single monolithic incremental statement is all-or-nothing. Batches are idempotent by construction, because each one replaces a bounded window rather than appending relative to whatever is already there. And late data is handled by re-running a window rather than by widening a lookback.

The backfill is the demonstration. `dbt run --select fct_events --event-time-start 2026-03-01 --event-time-end 2026-04-01` reprocesses March and nothing else — no full refresh, no vars, no edited model. That is the operation every hand-rolled incremental model makes awkward.

The requirements to name: the upstream models or sources must also declare `event_time`, or dbt cannot filter the inputs per batch and will warn that each batch is reading everything. `begin` sets where a first run starts, so a careless value means an enormous first build. And it is adapter-dependent, so check support before designing around it.

The reasoning

The bug is that `> max(updated_at)` uses a watermark that the run itself advances, so anything arriving with an earlier timestamp is below the line forever. Subtracting a window from the watermark means each run reconsiders the last N days, so a row that is up to N days late gets picked up on a subsequent run.

`unique_key` is not optional here, and this is the part people miss. Reprocessing a window means re-selecting rows that are already in the target. Without a key, they are appended and you have duplicated N days of data — you have swapped losing rows for double-counting them. With a key and merge, they update in place and the result matches a full refresh.

The cost is straightforward: every run processes N days instead of one, so you pay roughly N times the incremental compute. On a large model that is real money, which is why N should come from measured lateness rather than from a round number. Look at the distribution of ingestion time minus event time in your source and pick a percentile you can defend.

What the window does *not* do is make the model correct. It bounds the lateness you tolerate; anything later is still lost. So a lookback belongs with two other things: a monitor on how often rows arrive outside the window, and a periodic full refresh or full-refresh comparison for anything where a silently missing row would matter.

The reasoning

The merge matches source rows against target rows on the key. Two source rows with the same key both match nothing on a first run and both get inserted; on a later run, behaviour depends on the adapter — some warehouses raise an error when a MERGE would update the same target row twice, and others pick one non-deterministically. None of them is what you wanted.

The fix is to make the batch unique before it reaches the merge: rank within the key by whatever defines recency and keep the first. `row_number() over (partition by order_id order by updated_at desc) = 1`, or `qualify` where the warehouse supports it, which is the same thing without the CTE.

The ordering column matters more than it looks. `order by updated_at desc` is ambiguous when two rows share a timestamp, and a non-deterministic tie-break means your model can return different numbers on two runs over identical input. Add a tie-breaker — an ingestion sequence, a file offset, the primary key — so the choice is total.

Where the duplicates come from is worth asking rather than just handling: a CDC feed emitting every intermediate state, an at-least-once loader replaying a file, or a source where the key is not actually unique. The last one means your `unique_key` is wrong and the model's grain claim is false, which no amount of ranking fixes.

The reasoning

The non-negotiable case is a logic change. Change the SQL of an incremental model and only new rows get the new logic; everything already in the table keeps the old. The result is a table that is half one definition and half another, with no column to tell you which. Full-refresh in the same deploy, or the model is quietly wrong from then on.

The scheduled case is drift. Late arrivals outside the lookback, deleted source rows the model never removes, corrections that missed the window — these accumulate silently. A weekly or monthly full refresh resets it. Where a full rebuild is too expensive, the substitute is a reconciliation job that compares the incremental table against a recomputation over a bounded period and alerts on a difference, which costs far less than rebuilding and catches the same problem.

The models that must never be full-refreshed are the ones whose input no longer exists: a model over a source with a retention window, or one that captures something not reproducible. Those are effectively snapshots, and they need the same protections — excluded from any job that can pass the flag.

For sixty models, make it a config rather than a habit: a tag for the refresh cadence, a scheduled job per tag, and an explicit list of models excluded from full refresh with a comment saying why. And put a full refresh into the deploy process for any pull request that changes an incremental model's SQL, so nobody has to remember.

The reasoning

Start from what you give up. A `table` model is correct by construction: it is a pure function of its inputs, rebuilt from scratch every run, so it cannot drift and it cannot be half-built by an old version of the logic. Incremental trades that away for compute. So the question is not "can this be incremental" but "is the saving worth the correctness risk".

The conditions that make it worth it: the model takes long enough to matter — minutes, not seconds; the new data each run is a small fraction of the total; there is a reliable, monotonic timestamp to filter on; and the historical rows genuinely do not change. Miss the last one and you have built a table that silently ignores updates.

The conditions that argue against it, even for an expensive model: history mutates in ways your filter cannot see, such as a status column that changes on old orders; the source has no trustworthy timestamp; the model is a wide aggregation whose result changes when *any* contributing row changes, which is most `group by` models over a mutable source; or the model feeds a financial report where a missing row is a bigger problem than the bill.

The pragmatic middle: incremental with a lookback and a scheduled full refresh, or a partitioned rebuild — recompute the last N days as a table each run and union it with older data. And the honest smallest answer: measure first. A model people assume is expensive is often two minutes, and two minutes of compute is cheaper than the incremental bug you will eventually ship.

The reasoning

First: the watermark subquery. `select max(updated_at) from {{ this }}` is a full scan of the target unless the column is clustered, partitioned or indexed in a way the warehouse can use. On a large table that is a significant cost paid on every run, before any new data is touched.

Second: the merge. Matching a batch against the target scans the target to find rows with those keys. Without partitioning or clustering on the merge key — or `incremental_predicates` to bound it — that is another full pass. A rebuild reads the source once and writes; a merge reads the source, writes a temp relation, reads the target, and writes again.

Third: the filter may not be pruning. If the source is not partitioned or sorted on `updated_at`, `where updated_at > x` still scans everything and simply discards most of it. You have added work without removing any.

Fourth, and easy to miss: writing. A merge produces small scattered writes where a rebuild produces one large sequential one, and on columnar storage the small writes fragment the table over time until it needs compacting. That is a cost that grows, so the run gets slower month by month rather than all at once.

How to check rather than guess: read the warehouse's query profile for each of the two statements the run issues. It will tell you immediately whether the time is in the watermark, the match, or the write — and the answer decides whether the fix is clustering, predicates, `insert_overwrite`, or going back to a table.

The reasoning

`state:modified` compares each node against a stored manifest and selects the ones whose SQL, config or contract changed. The trailing plus adds their descendants, because a change to a model can break anything downstream of it. That is the selection half.

`--defer --state <path>` is the other half and the one people leave out. A changed model's *parents* may be unmodified, so they will not be built — and in an empty CI schema they do not exist. Deferral tells dbt to resolve any ref it did not build against the relation in the stored state, which in practice means production. Without it the job fails with missing relations; with it, CI builds four models and reads the rest.

The plumbing: a production job uploads its `manifest.json` after every successful run — S3, GCS, or your CI provider's artifact store — and the pull-request job downloads it before running. dbt Cloud does this for you, which is one of the concrete things it sells.

Two refinements worth adding. `--favor-state` forces deferral even for relations that happen to exist in the CI schema, which stops a stale leftover from a previous pull request quietly being used. And use `@` rather than `+` when the downstream models have unmodified parents you cannot defer to — `state:modified` combined with `@` builds the ancestry those children need. Finally, tear the CI schema down afterwards, or you accumulate a schema per pull request forever.

The reasoning

The stale manifest is the biggest one and the least obvious. `state:modified` is a comparison, so its quality is entirely the quality of the reference. If the production job failed for three days, or the artifact upload is broken, or CI silently falls back to an old manifest, then the diff is against the wrong baseline and the selection is wrong. A missing manifest can select nothing at all, and a job that builds nothing exits successfully — a green tick meaning "no tests ran".

Macro changes are the classic gap. dbt does compare macro checksums and will mark dependent models modified, but only where it can see the dependency; a macro invoked dynamically, or one that reads something outside the project, breaks that chain. Package upgrades have the same shape — the macro is in `dbt_packages/`, not in your diff.

Environment differences: a var defined differently in the production job, an `env_var` that changes, a `target.name` branch. CI runs under the CI target, so any code that behaves differently by target is exercising the wrong branch by construction — the same reason CI always takes the first-run branch of an incremental model.

The mitigations: alert when the manifest is older than a day and fail the job rather than proceeding; run a full build on a schedule — nightly or weekly — so nothing can hide behind the diff indefinitely; and force a full run on pull requests that touch `macros/`, `packages.yml` or `dbt_project.yml`, which is a one-line rule in most CI systems.

The reasoning

`dbt retry` is the direct answer. It resumes the previous invocation from its point of failure using `target/run_results.json`, so the 39 successful models are left alone and the failed one plus everything skipped after it are attempted again. It preserves the original selectors and flags, which is what makes it safer than reconstructing the command by hand.

The manual equivalent is `dbt build --select result:error+ --state target/`, which selects the nodes that errored and everything downstream of them. It is more flexible — you can add `result:fail+` to include test failures, or combine with other selectors — and it is what you reach for when you want to change something about the rerun.

The precondition for both is that `run_results.json` from the failed run still exists. In an ephemeral CI container or a fresh orchestrator pod it does not, unless the job uploads artifacts on failure as well as on success. Doing that is the small piece of plumbing that makes 3am recoverable, and it is very often missing.

The related habit: fix forward rather than rerunning blindly. A model that failed on a data condition will fail again, so retry is for transient causes — a warehouse timeout, a lock, a credential blip. And check what the partial run left behind: with `dbt build`, downstream models were skipped rather than built on bad data, which is exactly why `build` is the right production command.

The reasoning

dbt runs up to `threads` nodes at once, respecting dependencies. So the floor on your run time is the critical path — the longest chain of models where each waits for the one before. If your project is a hundred models in a chain of twenty, thirty-two threads changes nothing, and the fix is to reshape the DAG, not the config.

The other bound is the warehouse. Each thread is a connection running a query, so thirty-two threads means thirty-two concurrent queries competing for the same compute. On Snowflake they queue or trigger a multi-cluster scale-out you are paying for; on BigQuery you hit slot contention; on Postgres or Redshift you hit connection and memory limits. Past a point, more threads makes each query slower and the total run no faster.

How to choose: raise it and measure, watching total run time rather than thread count. Eight to sixteen is a common landing zone for a warehouse with room; four is right for a small or shared instance. Set it per target — production can afford more concurrency than a shared dev warehouse where you are one of ten people.

The thing worth saying to an interviewer: thread count is a last-resort lever. If the run is slow, the payoff is almost always in the critical path, in materialization choices, or in one or two models doing most of the work — and `run_results.json` tells you which, per node, in seconds. Tune from that, not from the knob.

The reasoning

Measure first. `run_results.json` gives execution time per node for every run; load a few weeks of them into a table and you have a cost profile. In every project I have seen, five to ten models account for most of the time, and the interesting question is which — not "how do we make dbt faster".

Then materializations, in this order. Tables that could be views: a mart rebuilt nightly and queried twice a month is paying for nothing. Tables that should be incremental: a full recompute over an append-only source is the clearest win. Incremental models that are secretly full rebuilds because the guard is always false — worth grepping for, because they are invisible. And view chains that push cost onto every consumer query, which does not appear in your run time at all.

Then what is being built that nobody reads. Models with no downstream consumer and no exposure are pure cost; `dbt ls` plus your exposures gives you the list. Also test cost, which people forget — a `relationships` test on two billion-row tables is a large join every night, and `where` filters or `store_failures` with a sampled scope often make it affordable.

Then the structural levers: partitioning and clustering on the big incremental targets so merges prune; splitting one hourly schedule into the models that genuinely need hourly and the rest daily; and slim CI, because in a busy repository the pull-request builds can be a large fraction of the total spend and nobody attributes it there.

The last thing, and the one that keeps it fixed: attribute cost back to models continuously. Warehouse query tags carrying the dbt node name — `query_tag` or `labels`, set once via a config — mean the warehouse's own cost views can be grouped by model, and the conversation stops being about dbt and becomes about five specific tables.

The reasoning

The mechanism first: a merge has to find the target rows matching the incoming keys. If the target is unordered with respect to those keys, that is a full scan. Partitioning on the date column the batch is bounded by lets the warehouse skip every partition the batch cannot touch; clustering on the merge key lets it skip micro-partitions within what remains.

The configs are adapter-specific and that is the point of the question. BigQuery takes `partition_by` and `cluster_by` in the model config; Snowflake takes `cluster_by`; Databricks takes `partition_by` and liquid clustering; Redshift has sort and dist keys. dbt passes them into the DDL it generates, so they are one config block rather than a manual ALTER.

The configs have to agree with the filter. Partitioning by `event_date` only helps if the incremental filter and any `incremental_predicates` are expressed on `event_date` — a filter on a timestamp column that is not the partition column prunes nothing. This is the mistake that makes people conclude partitioning did not help.

How you know it worked: bytes scanned, from the warehouse's query profile or information schema, for the merge statement specifically — not total run time, which mixes in everything else. Take a baseline before, change one thing, compare. On BigQuery it is the bytes-billed figure; on Snowflake, partitions scanned versus total. If that number has not moved, the config is not doing what you think regardless of what the wall clock says.

Two cautions. Over-partitioning — by hour on a table that has thousands of rows per hour — produces many small files and makes everything slower, which is the small-files problem in another costume. And clustering is maintained by the warehouse in the background at a cost, so clustering a table that is rewritten wholesale every night is money spent on ordering data you are about to discard.

The reasoning

A view stores no rows. Selecting from a view that selects from a view that selects from a source produces one query with all three bodies inlined, executed in full every time. Your dbt run time measures how long it took to *define* those views, which is essentially zero, and tells you nothing about what they cost.

So the cost sits in the BI tool's queries, in analysts' ad-hoc work, and in downstream models — none of which is attributed to dbt. The tell is a warehouse bill dominated by queries you did not write, on a project whose run is fast.

The fix is to materialize where the read-to-build ratio justifies it: anything read many times between builds becomes a table. Marts especially — they are the read-heavy end by definition. Staging can stay views because they are thin and are usually read once per downstream build.

The depth limit matters independently of cost: warehouse optimizers degrade on deeply nested views, and somewhere around three or four levels — sooner if there are window functions or non-trivial aggregations — plans stop being sensible and a query that should take seconds takes minutes. Materializing one model in the middle of a long chain often fixes the whole thing.

How to find it: group your warehouse's query history by the relations referenced and look for the views appearing in the most expensive queries. That gives you the ranked list of views to consider materializing, which is a far better basis than intuition about which model looks heavy.

The reasoning

With `table` or `incremental`, dbt decides when the data changes — the model is exactly as fresh as your last run. With `materialized_view`, dbt issues the DDL once and then the warehouse keeps it current on its own schedule. dbt's job shrinks to managing the definition and reacting when it changes.

That is a genuine win when consumers need fresher data than your run cadence provides and the transformation is simple enough for the warehouse's MV support — which is usually the constraint. Every warehouse restricts what an MV can contain: Snowflake dynamic tables and BigQuery MVs both have limits around joins, window functions and non-deterministic expressions, and you will discover them by hitting them.

The costs: refresh is billed continuously rather than in a predictable nightly window, so it moves cost from a line you watch to one you do not. Your dbt run is no longer the authoritative statement of what happened. And `on_configuration_change` decides whether a definition change triggers an apply, a full rebuild, or a warning you ignore — worth setting deliberately.

The rule I would give: use MVs where the requirement is latency on a simple shape and the alternative would be running dbt every fifteen minutes. Use incremental where the logic is non-trivial, where you need to control cost, or where you need to be able to point at a run and say what it produced.

The reasoning

Deferral is not CI-specific. Given a manifest from a successful production run, `--defer` tells dbt that any ref it did not build in this invocation should resolve to the relation in that state rather than to your dev schema. So a single model builds in seconds and reads production upstream.

The practical setup is to keep a recent production manifest on disk — a script that pulls the artifact from wherever your production job uploads it — and alias the command. Some teams wire it into `dbt_project.yml` defaults or a shell function so `dbt build --select my_model` defers automatically in dev.

`--favor-state` is the companion flag and it matters more than it looks. By default, deferral only kicks in for relations that do not exist in your dev schema — so a stale copy of a model you built three weeks ago wins over production, and you debug against data that is neither. `--favor-state` prefers the state relation regardless, which is almost always what you want.

The caveats: you are reading production data locally, so it is inappropriate where that data is restricted, and you must never write to it — deferral affects how refs resolve, not where your model builds, but a hardcoded relation name would. And an incremental model deferred to production is production's accumulated table, so testing a change to its filter needs a full refresh into your own schema instead.

The reasoning

Find them first: `run_results.json` carries per-node timing for tests exactly as it does for models, so the expensive ones are a query away. It is usually a small number — `relationships` between two large facts, `unique` on a wide composite key, `accepted_values` over a billion rows.

The cheapest real saving is scoping. A `where` config on a test restricts what it scans: `where: "created_at >= dateadd(day, -7, current_date)"` on a `unique` test checks the window where a new duplicate could plausibly appear, at a fraction of the cost. Be explicit that this is a trade — the test can no longer see a violation in older data — so pair it with a full-scope version on a weekly schedule.

Then remove duplication. A contract with a `not_null` constraint makes a `not_null` test redundant. A `unique` test on a surrogate key that is generated by a hash of columns already tested individually adds little. And tests copied onto every layer — the same `not_null` on staging, intermediate and mart — cost three times and catch the same thing once; test at the boundary where the data enters and at the boundary where it leaves.

Then cadence. Not every test needs to run on every build. Split into a fast set that gates the run — key uniqueness, not-null on join keys, source freshness — and a thorough set on a schedule, selected by tag. `dbt build` runs the fast ones inline so bad data does not propagate; the slow ones run nightly or weekly and alert rather than block.

What not to cut: anything asserting grain. A `unique` test on a model's key is the cheapest insurance in the project against the most expensive class of bug, and it should survive any optimisation pass.