Merge versus insert overwrite for loading a completed show edition

The badge file lands again on the Thursday after the show closes. Same vendor, same edition, about 400 rows different from the file you loaded on Monday, and no note saying which 400. Someone has to decide whether the loader matches those rows into the fact table that already exists or throws the edition away and writes it again from the new file. The choice between merge versus insert overwrite gets made once, in a hurry, on a Thursday, and then it lives in your pipeline for five years.

Both strategies can produce a correct table. They cost different amounts, they fail in different directions, and the right answer moves with the size of the batch you are loading. That last part is what makes this worth thinking about rather than standardising on one.

What each strategy does to the table

The dbt documentation, current in 2026, describes merge plainly. It "inserts records with a unique_key that don't exist yet in the destination table and updates records with keys that do exist". Rows in the destination that the incoming batch says nothing about are left where they are.

Insert overwrite works at a coarser unit. The same page says it is "used to efficiently update partitioned tables by replacing entire partitions with new data" and that "It overwrites only the affected partitions, not the whole table." Toby Mao, writing in May 2023 on correctly loading incremental data at scale, puts the mechanism in physical terms: "Insert overwrite will atomically delete a folder before writing contents into it, ensuring idempotency."

The difference that matters for event data is the unit of replacement. Merge replaces rows. Insert overwrite replaces a partition. In a show warehouse the partition is almost always the edition, because an edition is the thing that closes, gets corrected, and then never changes again. Tobiko Data's SQLMesh documentation, current in 2026, makes the same split in its model kinds: an incremental model by time range is processed by missing intervals, while an incremental model by unique key inserts new keys and updates matching ones, and the documentation notes that the unique key kind is inherently non-idempotent.

Why does merge get expensive on a portfolio fact table?

Merge has to find the matching rows before it can change them, and finding them means reading the destination.

Take the shape most portfolios have. Eight shows, five years, so 40 editions in one scan fact table. If an average edition contributes 38,900 badge scans, the table holds about 1,556,000 rows. Now merge a correction of 400 rows into it. If the merge condition is nothing but scan identifier equals scan identifier, the engine evaluates that condition against all 1,556,000 rows. That is 3,890 rows read for every row you intended to change.

The dbt documentation states the general version of this directly: merge "is 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."

The fix is not exotic. Put the edition in the merge condition as well as the key, and the engine can prune to one partition. The scan drops from 1,556,000 rows to 38,900, which is 97 rows read per row changed. That is the honest comparison to hold in your head, because a merge with a partition predicate and a merge without one are different operations wearing the same word, and the second one is what people mean when they say merge does not scale.

The 400 row correction

With the partition predicate in place, merge on 400 rows reads 38,900 and writes 400.

Insert overwrite on the same correction reads the corrected file, which contains the whole edition, and writes 38,900 rows. To change 400 values you have rewritten 97 rows for every one that moved.

On a columnar store the write amplification is less brutal than that ratio suggests, because updating 400 rows scattered across an edition usually rewrites whole files anyway. Even so, merge is the better instrument here for a reason that has nothing to do with bytes. The vendor sent you a corrections file. It contains 400 rows because 400 rows changed. If you overwrite the partition with it, you delete 38,500 scans that were perfectly good, and the table is wrong in a way that no test on the corrections file can detect. Insert overwrite is only safe when the incoming file is authoritative for the entire partition, and a corrections file never is.

The 12,000 row re-extract

Now the other case. Three weeks after close, the badge vendor admits the timezone on the day two export was wrong and sends a full re-extract of the edition: 38,900 rows, of which about 12,000 carry a different timestamp from what you loaded.

Merge reads 38,900 and writes 12,000. Insert overwrite reads 38,900 and writes 38,900. The read cost is identical and the write ratio is now 3.2 rows written per row changed, against 97 in the correction case. The bytes argument for merge has mostly evaporated, and two arguments for overwriting have arrived.

The first is that the file is authoritative. It is the whole edition as the vendor now believes it to be, so replacing the whole edition is the operation you actually want to perform. The second is deletion. If the re-extract has 38,900 rows and your table has 39,140 because a test load leaked in, merge will leave those 240 orphans in place forever. They match no incoming key, so nothing tells merge to remove them. Insert overwrite removes them by construction, without anyone having to notice they were there.

Where the crossover sits

There is no universal threshold, and anyone who gives you one has stopped thinking. The variables are the share of the partition that is changing, whether the incoming file is complete, and whether rows can legitimately disappear.

The rule I use is a question about authority, asked before any arithmetic. Is this file the whole truth for this edition? If yes, overwrite it, whatever the row count. If no, merge it, whatever the row count.

Arithmetic decides only the cases where both answers are defensible. A 400 row batch against a 38,900 row edition is 1.0 per cent of the partition. A 12,000 row batch is 30.8 per cent. Somewhere between those the write cost of overwriting stops mattering, and in my experience it stops mattering earlier than people expect, because the operational cost of a merge condition that quietly misses a partition predicate is much higher than the cost of rewriting a few tens of thousands of rows once.

Insert overwrite has a second property that is worth more than either cost number. Because the partition is deleted and rewritten atomically, rerunning the same load twice produces the same table both times without anyone reasoning about it. Merge gets you there too, if the key is genuinely unique and the merge is written correctly, and that conditional is doing real work in the sentence.

What happens when the late file spans two editions?

This is the case that breaks the tidy version of the choice, and event data produces it constantly.

A lead retrieval export from a vendor who serves three of your shows can arrive as one file covering two editions plus a fragment of a third. Insert overwrite now needs the loader to split the file by edition and overwrite each partition separately, which is fine if every affected partition is fully represented in the file, and wrong if any of them is partial. Merge handles the mixed file without splitting it, at the price of the partition predicate you just lost.

The practical shape is a loader that does both. Split on the partition column, count rows per partition, and compare that count against what a complete edition should look like. Partitions that arrive complete get overwritten. Partitions that arrive as fragments get merged. That decision belongs in code with the counts logged, because the alternative is a person deciding it at 11pm during move-out. Which files arrive when, and how long you keep reprocessing them, is the late arriving data problem rather than this one.

Where this stops

Neither strategy protects you from a wrong file.

Both merge and insert overwrite are faithful executors. If the vendor's re-extract has the timezone wrong in the other direction, insert overwrite will replace 38,900 good rows with 38,900 bad ones atomically, idempotently, and in four seconds. The atomicity that makes the operation safe to rerun does nothing about the content. That is an argument for a row count and a checksum comparison before the write, and for keeping the raw file so the previous state can be rebuilt.

The other limit is that this whole comparison assumes your fact table is partitioned by something stable. If it is partitioned by load date, or not partitioned at all, insert overwrite has no meaningful unit to overwrite and the choice collapses to merge by default. Repartitioning a five year fact table is a real project with a real bill attached, and the byte budget for a full reload is the thing to work out before you commit to it rather than after.

Start with one query. Take the merge statement your loader runs today and read the ON clause. If it names only the natural key and never the edition or the date, you are scanning the whole fact table every time a vendor sends a correction, and adding one predicate to that clause is an afternoon's work that will keep paying for as long as the data platform exists.