Updates into a lakehouse are where the table design shows
Incremental loads rarely fail on the night they are written. They fail in month five, when partitioning, file sizes and missing compaction finally add up.
The failure has a shape you start to recognise. An incremental load into a lakehouse table runs in a few minutes a night. It runs that way for four months, quietly, so nobody looks at it. Then one morning it takes six hours, overruns into the working day, and every dashboard downstream is serving yesterday. Nothing in the code changed. The table simply got old enough for its design to start showing.
Nearly every team plans for the append-only case, because the append-only case is easy. New rows land, old rows are never touched, and cost scales with what arrived rather than with what is already sitting there. Nearly no team actually has one. Corrections come back from the source system. Orders change status. A payment settles three days after it was authorised. A customer attribute changes and somebody wants the previous value kept. All of that arrives as an update, and an update is a different operation from an insert in a way that only becomes visible at size.
The thing worth internalising is that a merge does not cost what its row count suggests. It costs what your file layout makes it cost. Two tables holding identical data, receiving identical batches, can differ by an order of magnitude in merge time because of decisions taken when the table was created, usually by somebody thinking about query performance and not about writes at all.
What a merge is actually doing
In an open table format such as Iceberg or Delta, a table is a set of immutable data files plus metadata saying which of those files are currently live. There is no in-place edit of a row. So a merge has to do two separate things: find every file that could contain a row matching your incoming keys, and then produce a new version of the table that reflects the change. Both halves are governed by layout, not by how many rows you brought.
Under copy-on-write, matching a single row means rewriting the whole data file that row lives in. If your files are around 512 MB and tonight's batch touches one row in each of forty files, you have rewritten roughly twenty gigabytes to change forty rows. Under merge-on-read, the writer instead records the removals separately and appends the new versions, which makes the write fast and moves the cost onto every subsequent read, permanently, until something compacts it away.
A merge does not cost what its row count suggests. It costs what your file layout makes it cost.
The pattern, stated plainly
Partitioning is a statement about your updates
Most partitioning schemes are chosen for the read path. Partition by event date, because the dashboards filter on date. That is a reasonable instinct, and it is also the moment merge behaviour gets decided, because an engine can only skip files it can prove are irrelevant. If the incoming batch carries the partition column and the merge condition references it, the engine prunes down to a handful of partitions and the write is small. If the merge joins on a business key alone, the engine has no proof about location and has to consider the table.
This is why merges degrade with age rather than with volume. Fifty thousand updates against three months of history touch most of the files that exist. The same fifty thousand against three years of history touch the same rows, but now the engine reads far more metadata and opens far more files to locate them. The job did not get more work. It got more haystack. And if updates genuinely land at random against arbitrary old rows, with no correlation to the partition column, no partitioning scheme will rescue you. That is a signal to change the model rather than the table: the entity table probably wants to be derived from an append-only event log, not maintained by merging into a mutable dimension.
Small files, and the compaction nobody scheduled
Every merge writes new files. Micro-batch or streaming ingestion writes many small ones. Over months a table quietly accumulates tens of thousands of files well below the size the engine plans for, and the dominant cost shifts from reading data to opening files and walking metadata. Merge-on-read tables carry a second version of this, where accumulated delete files mean every read reconciles removals against data before it can return a row. Compaction addresses both, and compaction is the thing that never makes it into version one of a pipeline, because on a young table you cannot see what it buys. Six months later it is urgent, and it has become a large rewrite competing with the loads for the same compute.
- Schedule maintenance from day one Even while it does almost nothing for the first quarter. A job that has been running harmlessly for months is far easier to tune than one introduced in the middle of an incident.
- Expire snapshots as well as compacting Time-travel retention keeps superseded files alive. A table you compacted last night can still be holding every pre-compaction file if snapshot expiry never runs, so storage grows and metadata keeps getting slower.
- Alert on file count, not table size Average file size per partition is the metric that predicts the six-hour night. Total size in gigabytes will look entirely unremarkable right up to the morning it matters.
Late arrivals should set the lookback, and they are measurable
Most incremental models carry a lookback window: reprocess the last three days, or seven, on the assumption that anything older has stopped changing. The number is nearly always chosen by feel. Too short and corrections are lost silently, which is the worse failure because nothing breaks and the numbers are simply wrong. Too generous and you pay for a wide merge every single night in order to catch a handful of rows.
It does not have to be a guess. For a period you already trust, compare the source system's updated-at against the event's own timestamp and look at the whole distribution rather than the maximum. Retail returns, logistics proof-of-delivery and payment settlement all have long thin tails, and one freak record should not size your nightly job. A window that covers the bulk of the distribution, paired with a periodic full reconciliation that catches the stragglers, is usually cheaper and more honest than a window sized for the worst arrival anyone has ever seen.
A week of data proves nothing
The reason these problems surface in month five is that they were tested against a table holding one week. A week-old table has few files, small metadata, no accumulated delete files, and no partitions old enough to be inconvenient. Every merge strategy performs acceptably on it, which means the test distinguishes between none of them. The benchmark that matters is the one run against the table as it will be in a year.
- 01Build the table at its future size firstBackfill a year or two before benchmarking anything. If real history is not available, generate keys with the same cardinality and the same update distribution, because cardinality is what drives the join and the file matching.
- 02Replay many nights, not oneRun thirty consecutive daily batches against it. The cost of a single merge is not the question. The trend across thirty is, because that is the curve that bends.
- 03Record layout metrics beside runtimesCapture file count and average file size after every run. When the runtime curve bends you want to know which layout metric bent first, otherwise you are guessing at the cause during an outage.
- 04Run it once with maintenance disabledSo you know what compaction is actually buying, and how fast the table degrades on the day the maintenance job fails quietly and nobody notices.
Where this lands is unglamorous. Partitioning, file sizing, merge strategy and maintenance are not four independent settings, they are one decision about how a given table expects to be written to and read from, and the bronze staging table and the gold dimension it feeds usually want opposite answers. That is why in Antvia the merge strategy is fixed per table at the point the table is defined rather than inherited from a platform default. The mechanics are the same whether you run Iceberg on your own object storage, Delta on Databricks, or micro-partitions on Snowflake, where you have less direct control over layout and correspondingly more riding on the clustering key. And the uncomfortable version of the advice: sometimes the right answer is not an analytical table at all. If the workload is thousands of small updates a minute against rows that get read one at a time, that is an operational database question wearing analytics clothing. Keep the changes there, stream them out, and let the analytical table be append-only for real.