Your incremental model rewrites every file to change 0.95% of the rows

A MERGE that changes 0.95% of a 400 GB table rewrites 100% of its data files. An incremental model is what you reach for once a table gets that big. On scattered updates it makes the bill worse — by about 1.4% on the arithmetic below, because the rewrite unit is the file, not the row.

A dbt incremental run spends two shares of the target table: the share its ON clause scans, and the share its MERGE rewrites. Call the sum the rebuild budget. One unit is the honest ceiling, two the generous one. Scattered updates across 256 MB files spend two.

What a dbt incremental model does on every run

A dbt incremental run builds your model SQL into a temporary relation, then merges it into the table that already exists. Two statements, not one.

dbt’s page on incremental models describes the second run as an insert, where dbt “transforms only the rows in your source data that you tell dbt to filter for”. The page then makes the claim this article argues with — “Using an incremental model limits the amount of data that needs to be transformed, vastly reducing the runtime of your transformations.”

The macro that picks the branch carries a fourth condition the documentation page doesn’t list. In is_incremental.sql in dbt-adapters, {% if not execute %} sits above the three documented tests with {{ return(False) }} indented under it, so during parsing it returns false.

Line 46 of incremental.sql in dbt-adapters v1.17.3 is where the cost hides: {% do run_query(get_create_table_as_sql(True, temp_relation, sql)) %}, so the delta gets written once into a temporary table and read straight back out. An incremental run has three places to spend, not one: the source read, the round trip through it, and the merge’s own work on the target.

Does a dbt merge scan the whole target table?

Yes, unless you hand it a predicate that says otherwise, and the sentence that admits it is dbt’s own. The incremental strategies page says this about the default:

While the merge strategy is useful for keeping tables current, it’s best suited for smaller tables or incremental datasets. It can be expensive for large tables because it scans the entire destination table to determine what to update or insert.

Each adapter’s configuration page says the same, which is where this came from rather than memory: merge is the default on dbt-snowflake, dbt-bigquery and dbt-databricks, the last for Delta and Hudi file formats only.

The source side isn’t safe either. dbt’s BigQuery configuration reference says the merge approach “requires scanning all source tables referenced in the model SQL, as well as destination tables.” The read-side saving rests on pruning the merge doesn’t arrange, and a January 2023 forum thread has the receipt: a model that processes 5GB on a full refresh “will merge 0 rows and process 10GB” incrementally.

Call the share of the target the ON clause actually touches beta. Read yours off the account: ACCOUNT_USAGE.QUERY_HISTORY on Snowflake carries partitions_scanned, “Number of micro-partitions scanned”, beside partitions_total. Divide one by the other on the merge statement. There is one way to drive beta to zero while still writing merge, and nobody picks it for that reason. It comes back at the end.

Counting the files a MERGE rewrites, not the rows that changed

A merge rewrites whole data files, so the bytes it moves are set by file layout, not by how many rows you touched. Databricks says so in one line on its deletion vectors page: “Without deletion vectors, modifying a single row requires rewriting the entire Parquet file containing that record.”

Apache Iceberg defaults the same way, and nobody quotes it because of where the line sits. In the Write properties table of the Iceberg configuration page, write.merge.mode is row 52 of 54, with write.update.mode at 50 and write.delete.mode at 48, all three carrying copy-on-write in the Default column. Counted 29 October 2026. Snowflake buries its version just as well. Not on the micro-partitions page, which gives only “between 50 MB and 500 MB of uncompressed data”, but in the usage note on dropping columns in the ALTER TABLE reference: “Write operations (insert, update, delete, etc.) on 1 or more rows in that micro-partition cause the micro-partition to be re-written.”

Kettlebridge is invented — a company of 31 people selling mooring and berth management software, not a market we sell into. Its figures are chosen so you can recompute them, not measured from anybody’s warehouse: a 400 GB orders table, 2 KB rows, two million rows changed a day, all carried from an earlier piece here.

Work the table down one rung at a time. 400 GB is 409,600 MB; divide by 256 and you get 1,600 files at the Databricks autotune target. One 256 MB file is 262,144 KB, so at 2 KB a row it holds 131,072 rows. The table is 409,600 MB times 1,024 divided by 2 KB, or 209,715,200 rows, which puts churn at 2,000,000 / 209,715,200 = 0.0095367, or 0.9537% a day. Now the survival question. Each row in a file misses it with probability 1 - 0.0095367 = 0.9904633, whose natural log is -0.0095825, and multiplying by that file’s 131,072 rows gives an exponent of -1,256.

A second route gets there without rows-per-file. Throw 2,000,000 changed rows at 1,600 files at random: one row misses a given file with probability 1 - 1/1,600, whose log is -0.000625195, and two million of those gives -1,250. Expected untouched files, 1,600 x e^-1,250. Two inputs, two exponents six apart, one answer.

Every one of the 1,600 files is rewritten to change 0.95% of the rows.

Phi, the share of the target’s bytes a merge rewrites, is 1 here. Snowflake is the one place I would check rather than assert. Phi might sit under 1 there, because the published range and that rewrite sentence are a bound rather than a measurement of how a MERGE batches the work. Read partitions_scanned against partitions_total first. And I have quoted that range as 16 to 512 MB for years. I had it wrong.

The crossover churn ratio is 0.00053%, and the table in front of you is at 0.95%

Allow the merge at most half the target’s bytes, give the scan the other half, and the crossover is ln 2 divided by rows per file: 0.000529% on a 256 MB file of 2 KB rows, about 1,800 times below Kettlebridge.

The rebuild budget. Beta plus phi has to come in under 1 when the source scan is the same either way, and under 2 when the incremental run prunes its source perfectly; at or above that line, rebuild the table. Half each is the conservative allocation under the first rule. Both terms can be read rather than guessed, and clustered changes collapse the budget to the familiar answer — the thing incremental models earned their reputation on.

A full refresh reads 409,600 MB of source and writes 409,600 MB of target: 819,200 MB. One incremental run, at its best case of a perfectly pruned source, spends these megabytes:

830,918.75 divided by 819,200 is 1.0143. In gigabytes, 811.44 against 800. The budget gets there from the other end; the run spends beta + phi = 2 against a generous ceiling of 2 - 3c = 1.9714, where c is the 0.0095367 churn and 3c is three times the 3,906.25 MB delta. Take the source pruning away and the total becomes 409,600 + 7,812.5 + 819,200 = 1,236,612.5 MB, 51% more rather than 1.43%.

Changed rows land uniformly at random across the key space, the scattered case, so a file of r rows survives untouched with probability (1-c)^r and phi = 1 - (1-c)^r. Set phi to 0.5: c* = ln 2 / r = 0.693147 / 131,072 = 0.0000052883, and 0.0095367 / 0.0000052883 = 1,803. Under the generous rule phi is allowed 1 - 3c = 0.9714, survival need only clear 0.0286102, r* = ln(0.0286102) / ln(1-c) = 370.9 rows, c* = 0.00271%, and Kettlebridge is 352 times past that one. Either way the table rebuilds.

A ratio that large buying only 1.43% is no contradiction. Phi tops out at 1, so past saturation the model stops getting worse. It just never gets better.

Derate the churn and nothing moves. At a quarter, 0.2384%, the exponent goes from -1,256 to -313; at half, -626; four times the other way, -5,098. Phi is still 1 at all three. Derate what decides it instead. Shrink the file 256x, to 1 MB: r falls to 512 rows, the exponent to 512 x -0.0095825 = -4.906, survival to e^-4.906 = 0.0074, phi to 0.9926. Still over budget. Phi reaches 0.5 only at r = ln 2 / 0.0095825 = 72.3 rows, a 145 KB Parquet file at 2 KB a row. Below roughly 72 rows per data file the merge wins on scattered changes, and nobody writes a file that size, so treat it as a bound rather than a knob.

A bigger table makes the incremental model worse, not better

Doubling the table leaves the threshold where it was; crossing 10 TB moves it the wrong way. “For tables smaller than 2.56 TB, the autotuned target file size is 256 MB” — that is Databricks’ page on file size, so Kettlebridge at 800 GB and four million changed rows a day still gets 256 MB files. Rows per file stay at 131,072, c* stays at 0.000529%, and only the file count moves, 1,600 to 3,200. Past 10 TB the same page says “the target file size is 1 GB”. Then r is 1,048,576 KB divided by 2 KB, or 524,288 rows, and c* is 0.693147 / 524,288 = 0.000132%. The bigger table has the lower threshold. The autotuner answers size with size, and a bigger file carries more innocent rows that ride along with every rewrite.

Deletion vectors, copy-on-write, and where the rewrite goes instead

Deletion vectors collapse the rewrite to roughly the size of the change, then hand the same bytes back to you later. The Databricks deletion vectors page says they “mark rows as modified in metadata” instead, and that they “accelerate DELETE, UPDATE, and MERGE operations”.

Check the version boundaries first; there are three and they don’t line up: Databricks Runtime 14.3 LTS and above to write with “all optimizations”, 12.2 LTS and above to read, and MERGE from 14.3 LTS without Photon but 12.2 LTS with it. Enablement splits by format too. “All Apache Iceberg v3 tables include deletion vectors by default”, while on Delta Lake “you must explicitly enable deletion vectors”.

None of that is the fix, because the rewrite is deferred rather than cancelled. The same page tells you to run REORG TABLE ... APPLY (PURGE), a command that “rewrites all data files containing records with deletion vector changes”. And compaction won’t quietly do it for you: “File compaction events don’t have strict guarantees for resolving changes recorded in deletion vectors.”

incremental_predicates fixes the scan and leaves the rewrite alone

An incremental predicate drops beta from 1 to the share of the table inside the window and leaves phi exactly where it was. The worked example on dbt’s incremental strategies page limits the scan to seven days, and on a two-year table that is 7/730 = 0.009589, so beta falls to 0.0096 and the scan from 409,600 MB to 409,600 x 7 / 730 = 3,927.67 MB, or 3.84 GB. The aliases are fixed rather than guessable. That example’s own comment spells them out: “DBT_INTERNAL_DEST and DBT_INTERNAL_SOURCE are the standard aliases for the target table and temporary table, respectively…”

models:
  - name: orders
    config:
      materialized: incremental
      unique_key: order_id
      incremental_strategy: merge
      incremental_predicates:
        # limits the scan of the existing table to the last 7 days of data
        - "DBT_INTERNAL_DEST.ordered_at > dateadd(day, -7, current_date)"

Phi is untouched, because the predicate prunes which files get looked at and not which get written. Worse, it is only correct when the changed rows live inside the window. A target row the ON clause cannot reach is not matched, a merge that doesn’t match inserts, and you have quietly duplicated keys rather than updated them. dbt won’t catch it either. From the same page: “dbt does not check the syntax of the SQL statements.”

So I wouldn’t add incremental_predicates to rescue a model whose updates are scattered. It is correct exactly when phi is already small, the same assumption wearing a second hat, and on a table shaped like Kettlebridge’s we delete the is_incremental() branch and let it build as a table. The model gets shorter, the drift problem goes away, and the bill doesn’t move.

When we run a full refresh instead, and the flag that won’t let us

We rebuild whenever beta plus phi clears the budget, which on a table with scattered updates is always. dbt’s own best-practice page on incremental models is honest about what the upkeep buys: incremental models “inevitably drift from the source data over time”. Its remedy, offered for “incremental models of manageable size”, is “a full refresh on the weekend (or any low point in activity)”. A model whose correctness depends on a weekly rebuild has a rebuild in it already.

Now the annoying part, checkable in a minute. --full-refresh on the command line does not win. The full_refresh resource config reference says a resource set to false “never performs a full refresh, regardless of whether you pass the --full-refresh flag in the dbt command”, and that “You cannot override an existing full_refresh config”. The escape hatch silently does nothing on the model you are trying to rescue. A second default costs another minute: under Default behavior on the incremental models page, on_schema_change is “set by default” to ignore, so a column you add “will not appear in your target table”. Line 15 of incremental.sql hard-codes it.

The configuration that drives beta to zero while still writing merge, as promised: leave unique_key out. dbt says that without one the merge “behaves like the append strategy”, and line 30 of merge.sql in the same release shows how: {% do predicates.append('FALSE') %}. What your warehouse receives is merge into ... on (FALSE) when not matched then insert. A constant ON clause scans nothing, so beta really is 0 — and every run inserts duplicates. It isn’t the only route. incremental_strategy: append never reads the target either, and is dbt-postgres’s default without a unique_key. One layer upstream, warehouse bills climbing on flat data is where file count turns into money. Our data engineering work often starts by deleting one of these models, which is cheaper than tuning it.