In a columnar table whose data files are immutable, how does an UPDATE or DELETE actually work?

## Copy-on-write, not in-place edit Analytical storage is written once and never modified. There is no equivalent of an in-place page update, no free space reserved inside a file, no row-level lock on a byte range. So a statement that logically changes rows is implemented as a **rewrite**: 1. The planner resolves which files can contain matching rows, using each file's per-column statistics to skip the ones that provably cannot. 2. Each candidate file is read. 3. New files are written containing the rows that survive — unchanged rows copied verbatim, changed rows with new values, deleted rows omitted. 4. A metadata commit atomically replaces the old file identifiers with the new ones in the table's current file set. 5. The superseded files are left in place until a retention window expires, then garbage-collected. Step 4 is why the operation is atomic and why concurrent readers never see a half-applied change: a reader pinned the previous file set when it started and keeps reading it. Step 5 is why an analytical table's storage bill can be several times its logical size right after a large mutation, and why time travel and rollback are natural in these systems rather than a bolted-on feature. ## Cost scales with files touched, not rows changed This is the single most important consequence, and the thing candidates most often get wrong. Deleting one row from a table stored as 400 MB files does not do 100 bytes of work — it reads and rewrites the entire file that held the row. Updating one column of one row does the same, because the file is the unit of replacement, not the column and not the row. The multiplier is how many files the predicate reaches: ```sql -- table physically ordered by event_date DELETE FROM events WHERE event_date = DATE '2026-01-05'; -- touches a few files DELETE FROM events WHERE user_id = 7; -- may touch every file ``` The first predicate is correlated with the physical layout, so the file statistics eliminate almost everything and the rewrite is proportional to one day of data. The second is scattered across the whole table: one matching row per file means every file is rewritten, and a delete of a thousand rows can rewrite terabytes. Right-to-be-forgotten deletions are the classic production version of this problem, which is why teams often batch such deletes into a periodic job rather than executing them one subject at a time. ## Merge-on-read as the alternative Some engines and table formats avoid the immediate rewrite by recording that certain rows are logically gone or superseded — a marker file, a tombstone row, or a bitmap of deleted row positions — and applying it when the data is read. The write becomes tiny; the read pays a merge cost until a later compaction materialises the change. Engines built around continuously merged parts take a related approach: a mutation is expressed as a new part that wins over older rows, and the reconciliation happens during background merges or when the reader is explicitly asked to reconcile. The trade is symmetric and worth stating plainly: rewriting the file up front costs write amplification once and keeps reads clean; deferring costs almost nothing on write and taxes every read until compaction runs. ## Practical guidance - **Prefer set-based mutations.** One statement that deletes a month is far cheaper than thirty statements deleting a day each, because files are rewritten once instead of repeatedly. - **Align mutation predicates with the physical layout** wherever the workload allows, so file-level statistics can eliminate most files. - **Batch scattered deletes.** Accumulate the keys and run one job on a schedule rather than mutating on every request. - **Consider a rebuild.** When a mutation would touch most of the table anyway, writing a fresh table from a filtered query is often cheaper and easier to reason about than an in-place statement. - **Watch storage after big mutations.** Superseded files persist for the retention window; if your table doubled in size overnight, that is usually why. ## What interviewers are listening for The give-away weak answer is "the engine updates the row like any database, it is just slower". The strong answer names copy-on-write, ties cost to files touched rather than rows changed, explains the atomic metadata swap that gives snapshot isolation and time travel, and mentions the merge-on-read alternative and its read-side cost.