Table & Storage Maintenance
Who pays for the gap between the order data arrives in and the order it is read in: the writer, every reader, or a background job, and what is the break-even?
Who this mechanism is for: a developer who writes code, calls APIs and knows basic SQL, but has never operated a data lake or studied how table formats, object storage or databases clean up after themselves. You do not need any database knowledge. Every technical term is explained the first time it appears, and again in the glossary at the end.
The story we will follow
Nadia is the only data engineer at Stackwise, a small company that tracks parcels for online shops. Every scan of a parcel (picked up, in a van, delivered) is an event. Stackwise stores these events in one big table in cloud object storage, using a table format (a layer of metadata files that turns a pile of data files into one logical table with commits, snapshots and time travel). Her manager, Kofi, runs the customer dashboards on top of this table and promises customers that a dashboard opens in a few seconds.
Nothing in Stackwise's system is wrong. No code has a bug. Yet over six months the table slowly becomes slower, bigger and more expensive. In each article below Nadia meets a problem that looks like a bug. Each one is actually a consequence of a simple rule about maintenance: the background work that keeps a table healthy, that nobody asked for, and that changes no answer. At the end of each article we go back and solve her problem.
This is the main idea of this whole mechanism:
How this mechanism is organized
- 1
Work nobody asked for
Why does a correct, bug-free table get worse by itself, and who should pay to fix it?
Case4 seconds in April, 90 in September
- 2
Layout restoration
How big should files be, why do small files appear, and how do compaction and clustering repair a table?
Case2.1 million files for 1.2 TB
- 3
Reclamation
How can we delete old files safely when we cannot know who is still reading them?
CaseA bucket three times the size of the table
- 4
Semantic migration
Which "maintenance" jobs change the data model or the answers: partition changes, cold storage, deletion by law, format changes?
CaseDelete one customer from an immutable table
- 5
Running maintenance safely
How do maintenance jobs coexist with writers, when should they run, and how do we know they did not corrupt anything?
CaseA compaction that fails every night
- 6
What maintenance forbids
What does this mechanism forbid, what are the escapes, and what laws does it add?
After that: a summary, hands-on labs, interview questions and a glossary.
What do I do, and what does each option cost?
Can I see it myself?
Measure the per-file overhead
The same data as 800 files of 0.5 MB, 8 files and 1 file, scanned by DuckDB from local disk and from an S3-compatible store; F and b fitted.
Real systemDocker, Python with DuckDBZ-order versus sort: count the files touched
10,000 files over four columns, clustered by a sort on A and by a Z-order on A–D: files read by a filter on A and on D.
SimulatePython with NumPyOverlap depth before and after a blind compaction
200 small files with narrow key ranges in arrival order; overlap depth before and after compacting by time and by key range.
SimulatePython with NumPyRetention window versus storage
A model of a table format: 10% rewritten daily for 10 days, storage after expiry and vacuum for 1-, 3- and 7-day windows; then a long query after a short vacuum.
SimulatePythonFind the square-root interval by simulation
A read penalty that grows by r per hour since the last compaction and a compaction cost C: the cheapest interval against √(2C/r), then with 4r.
SimulatePythonFind the compaction interval
Measure the scan cost per unmerged delta file and the cost of a rewrite, compute the compaction interval τ*, and compare a day at τ*/4, τ* and 4τ*.
MeasurePython with DuckDB, PyArrowVerify a rewrite
Compact 50 Parquet files into one sorted file; compare count and an order-independent fingerprint; then drop a row and replace a row with a duplicate.
Break itPython with DuckDB
Can I reason about it under questioning?
Interview questions
Short questions on maintaining lakehouse tables, such as file sizes and compaction, clustering, vacuum and snapshot expiry, statistics, tiering and deletion, and running maintenance safely, each with an answer you could give aloud and a link to the full explanation.
Scenarios
Longer problems on maintaining lakehouse tables, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.
The tensions this mechanism takes a position on. Each recurs across mechanisms.
Ideas this mechanism meets that keep coming back, mechanism after mechanism.
Granularity mismatch
Granularity mismatch creates amplification.
Fixed cost per operation
Fixed cost per operation implies a natural batch size.
Costs are conserved
Costs are conserved, never removed.
Which way the error points
Designate one write
Atomicity over n items is atomicity over one designated item.