On an Iceberg or Delta table, what's the difference between copy-on-write and merge-on-read for updates and deletes, and why does the table still get slow over time if you only ever write to it?

technical-conceptual · Mid level · data-ml

What the interviewer is really asking

Tests whether the candidate understands the copy-on-write versus merge-on-read trade-off for mutations and knows that open table formats need ongoing maintenance — compaction of small files, expiring old snapshots, and removing orphan files — rather than assuming the format manages itself.

What to say

• Explain the mutation trade-off: copy-on-write rewrites whole data files when rows change, so writes cost more but reads stay fast with no delete files; merge-on-read writes small delete or change files instead, so writes are cheap but readers pay to merge them at query time.
• Connect it to who should pay the cost: choose copy-on-write for read-heavy tables with occasional updates, merge-on-read for high-frequency writes or streaming ingest where you can compact later.
• Explain why a write-only table still degrades: frequent commits and streaming produce many small files and accumulating snapshots, so you schedule maintenance — compaction to rewrite small files into larger ones, expiring old snapshots to free storage and bound metadata, and removing orphan files.

What to avoid

• Mixing up the two: claiming merge-on-read is faster to read or that copy-on-write avoids rewriting files.
• Assuming the table format auto-tunes itself and never needs compaction, snapshot expiration, or cleanup.
• Treating slow reads as purely a cluster-size problem and reaching for more compute instead of fixing the small-files and metadata buildup.

Example answers

Strong: Copy-on-write rewrites the whole data file on an update, so writes are heavier but reads are clean with no delete files to merge. Merge-on-read writes small delete or change files, so writes are cheap but readers reconcile them, which slows queries. Even a write-only table degrades because streaming or frequent commits leave thousands of small files and a growing snapshot history, so I'd schedule compaction to rewrite small files, expire old snapshots, and remove orphan files.

Weak: Merge-on-read is the faster, more modern option so I'd default to that. The table shouldn't really slow down if you're only appending — if it does, I'd just scale the cluster up since the format handles all the file management automatically.