What is write amplification in a lakehouse table, and what raises it?

## Definition **Write amplification** = bytes physically written to storage ÷ bytes of data that logically changed. A single-row update of 200 bytes that causes a 512 MB file to be rewritten has an amplification factor around 2.7 million. That number sounds alarming and is entirely normal for immutable-file table formats. The question is never "is there amplification" but "is it bounded and is it worth what it buys." ## Why it exists at all Data files in these formats are immutable, and that immutability is load-bearing: it is what lets readers hold a consistent view without locking, lets snapshots and time travel work by simply retaining old files, and lets writers commit by swapping a pointer rather than mutating shared state. The price is that you cannot edit a file in place. Any change to any row in a file means producing a new file. Columnar encoding compounds this. A Parquet-style file is compressed, dictionary-encoded and organized by column; you cannot surgically patch a value inside it without decoding and re-encoding the containing structure. ## The three main drivers **1. File size.** The rewrite unit is the file, so amplification is roughly proportional to file size for scattered single-row changes. This is exactly why "just make files enormous" is the wrong answer to the small-files problem — you trade read overhead for write amplification. The target-size band exists because both ends cost something. **2. Change scatter.** Ten updates that all fall in one file cost one file rewrite. Ten updates scattered across ten files cost ten. Scatter is a function of data layout: a table clustered on the key you update by concentrates changes; an unclustered table spreads them everywhere. This is a second, less-obvious benefit of clustering — it reduces write amplification for mutations, not just scan cost for reads. **3. Repeated compaction.** Compaction is itself amplification. Every byte it processes is read and written again with no logical change whatsoever. Worse, a poorly designed schedule can rewrite the same bytes many times: a file compacted from 4 MB to 128 MB, then later merged into a 512 MB file, then re-clustered, has been written three times beyond its original write. Good compaction policy avoids re-touching already-target-sized files — only files meaningfully below target are candidates — and treats cold partitions as done. ## The main mitigation: defer the physical rewrite Rather than rewriting a file when a row changes, a table format can record the change *beside* the file and reconcile at read time: - Record which rows are logically deleted, so readers filter them out while the original file stays untouched. - Write new versions of changed rows into new small files, and have readers merge them over the base data by key. Both approaches make the write cheap — proportional to what actually changed — and push cost to the read path, which must now consult extra information for every scan. Amplification does not disappear; it is deferred until a later maintenance job materializes the changes into new base files. The tradeoff is explicit: **cheap writes and more expensive reads, versus expensive writes and clean reads.** Choosing between them is a workload decision — heavy mutation with tolerant read latency favors deferral; append-mostly data with strict query SLAs favors rewriting immediately. Note the second-order effect: deferring creates small files (the delete records or new-version files), so the mitigation for write amplification *feeds* the small-files problem. The two are coupled, which is why one maintenance policy has to address both. ## The other mitigation: batch Amplification is per rewrite, not per logical change. If you accumulate an hour of updates and apply them in one pass, every affected file is rewritten once rather than once per update. Micro-batching is the single most effective lever available to a pipeline author, and it also directly reduces the small-file count. Row-at-a-time modification of a columnar lakehouse table is the pathological case for both problems simultaneously. ## Budgeting it A useful way to reason about a table: estimate the bytes written per day from (a) ingestion, (b) mutation rewrites, and (c) maintenance passes. Object storage bills for writes and for storage of the versions you retain; compute bills for the rewriting. Teams are routinely surprised that maintenance dominates ingest on a heavily updated table — the table writes several times its own daily volume without gaining a byte of new information. When that ratio is uncomfortable, the levers in order of impact are: batch more coarsely, defer physical rewrites via delete/merge-at-read, cluster so changes concentrate, lower target file size for mutation-heavy tables, and stop compacting partitions that no longer receive writes.