Interview questions
Why tables need maintenance
1Why do tables need maintenance when nothing is broken?
Writes are made cheap by being small and unordered, so file sizes, sort order and metadata all degrade on their own as data arrives. Maintenance is the interest paid on those cheap writes.
File sizes, compaction and clustering
2What is the best file size on object storage, and why?
Large relative to F × b: the fixed cost per request F (20–50 ms) times the streaming bandwidth b. With F = 50 ms and b = 100 MB/s, 5 MB files lose 50% of the time to overhead, 50 MB about 9%, 500 MB about 1%. Hence 128 MB to 1 GB.
3Why not make files even larger, say 10 GB?
Pruning gets coarser, parallelism suffers because one huge file becomes a straggler, and under copy-on-write the file size is the amplification factor for every update or delete.
Read moreWhy not make files even larger
4What causes the small-file problem?
Not data volume, but writers × partitions × commits per interval: 50 writers × 100 partitions × one commit a minute is 5,000 files a minute.
Read moreWhy small files appear at all
5Are small files expensive mainly because of per-request GET charges?
Mostly not: 100,000 GETs to read 1 TB cost a few cents. The real costs are latency, and the time spent planning queries over all that metadata.
Read moreA correction about small files and moneyWhy small files are fatal
6Why is Z-ordering often a poor choice?
A query filtering on k of the d Z-ordered columns touches about N^(1 − k/d) of N files, so a filter on one of four columns reads about N^0.75. If most queries filter on one column, sort by that column instead.
Read moreClustering
7What is overlap depth, and why is it a good health metric for clustering?
The number of files whose min/max range contains a randomly chosen value of the clustering column. 1 is perfect; the total number of files means clustering does nothing. It measures directly how many files a point query must read.
8How can compaction make queries slower?
Packing files together because they were written at similar times merges files whose key ranges did not overlap, which increases overlap depth. Compaction must choose files by size and by key range.
Reclaiming space
9Why must vacuum keep files for a retention window?
Whether a file is still in use cannot be computed, because you cannot list every reader. So the system assumes no reader lives longer than a window T. A query running longer than T fails part-way through its scan.
Read moreVacuum: reachable is not in use
10How do compaction and time travel interact in storage cost?
Storage ≈ live size × (1 + f × R), for a fraction f of data rewritten per day and R days of retained history, and compaction raises f. At f = 30% a day and R = 7 days, storage is 3.1 times the live size.
Read moreSnapshot expiration
11Why do small files make query planning so slow?
Planning is largely serial and scales with the number of files. At 10 million files it can take minutes, and extra workers cannot help (Amdahl's Law).
Read moreMetadata has the same disease
12Why is finding orphan files different from vacuuming?
Orphans are files that no metadata references, so they cannot be found from metadata. It takes a full listing of storage and an anti-join against the metadata, which is not a consistent snapshot, so it needs a safety window of days.
Read moreOrphan files: the opposite problem
Statistics and indexes
Tiering, retention and deletion
13When should data move to a cold storage tier?
When it is expected to sit unread for longer than the retrieval fee divided by the monthly saving, about 3.5 months in a typical example, and only with large files, because tier transitions are charged per object.
Read moreLifecycle tiering
14How do you delete one person's data from an immutable table within 30 days?
Rewrite every file containing their rows, then expire every snapshot that still references the old files. Or crypto-shred: encrypt each person's data with their own key and delete the key, which must be designed in from the start.
Read moreRetention and legal hold
Running maintenance safely
15Why does a long compaction job keep failing on a hot partition, and how do you fix it?
Under optimistic concurrency, a commit's chance of conflicting grows with how long the job runs. Detect conflicts narrowly, only if the job's own input files were touched, so it can commit on top of new appends; and split it into small jobs.
16How often should compaction run?
At an interval of about T = √(2C / r), for a cost C per compaction and a rate r at which degradation accrues, so the cadence scales with the square root of the write rate. Neither "constantly" nor "nightly" is right in general.
Read moreTrigger policy
17When should you rewrite a file that contains deleted rows?
When the deleted fraction × expected future reads exceeds 1: the reads it would save outweigh the rewrite. That needs read counts per file, which most teams lack, so they fall back on fixed thresholds.
Read moreDelete compaction
18How do you know a compaction did not lose data?
Compare the row count and an order-independent hash of all rows before and after. Compaction already reads every byte, so the check is almost free.
Read moreVerification of the rewrite