Storage Layers: Advanced

The orders table is partitioned by order_date and sits at snapshot 41, made of 3 files, f1 to f3, where f3 holds 2026-10-23. A nightly job recomputes that date and must replace f3 with 2 corrected files. The catalog accepts a new pointer only if it still holds the value the writer last read: a compare-and-swap.

Columnar files record each column's name inside the file. Older lake engines matched file columns to the table schema by that name or by position, and both break. Rename amt to amount on a name-matching table and every file written before the rename lacks an amount column, so 3 years of orders read back as NULL. On a position-matching table, dropping the second column shifts every later column one slot. The traditional fix was to rewrite every file.

A table format assigns each column a numeric field id when the column is created and never reuses it. Writers store the id beside each column in every data file, and readers resolve columns by id, so the name becomes a label in metadata that can change freely. Here is the schema history in the root metadata of orders:

Ids also make a mistake expensive. Drop legacy_flag (id 4), regret it a week later and add legacy_flag boolean again: the new column gets id 6. Old files hold their values under id 4, readers ask for id 6, and every historical row reads NULL. The bytes still sit in the old files, but no schema maps a column to them, so recovery means a rewrite that copies id 4 values into id 6.

A file is small when its fixed costs outweigh its bytes. Every file read from object storage costs requests to open it and read its footer, plus a fetch for every column chunk, and each request pays network latency whatever its size. Writers create small files: each writer task produces its own file for every partition it touches on every commit. Take a clickstream table events that is partitioned by hour with a 256MB file target. A micro-batch job commits to it every minute, and each batch brings 30MB of new data spread across 20 writer tasks.

Compaction reads the small files of a partition, writes them back as target-size files, and commits a replace snapshot that removes the old files and adds the new ones. For 1 hour of events it swaps 1,200 files of 1.5MB for 7 files of about 256MB. The rows are identical, so no answer changes; queries already running keep their pinned snapshot, and the 1,200 files are deleted only when that snapshot expires. Table formats ship this as a built-in procedure, often exposed as an OPTIMIZE command. It takes a table and a target size, and a partition filter narrows it to the partitions you want rewritten.

Two choices decide whether compaction helps or fights the writers. First, compact closed partitions, such as the 09:00 hour once the clock is safely past 10:00. Appends only add files, so they rarely conflict with compaction. If a delete or update touches a file that compaction is replacing, though, whichever commits later has to redo its work. Second, sort while rewriting: sorting each hour by user_id gives every output file a narrow user_id range, so a filter on 1 user skips most files using their min/max stats.

Most real datasets serve several access patterns at once. Take a grocery delivery marketplace with 1 orders dataset and 3 groups of users. The shopper app reads and updates single orders at up to 3,000 writes per second and needs answers in under 20ms. Finance and data science need 4 years of history and must reproduce month-end numbers and training sets exactly. A group of 300 analysts refresh revenue dashboards by store and category and expect charts in under a second.

Each store owns a different part of the data. The operational database owns the present and keeps 90 days, so its indexes stay small. The lakehouse owns history: every order ever placed, plus each recent version within retention. The mart owns nothing: it is a derived copy that can be dropped and rebuilt from orders_curated, which is what makes it safe to tune hard for the dashboards.

Every store is another copy to keep in sync and another place to fail. With 10 analysts happy with 10-second charts, querying orders_curated directly beats building a mart. With 30 days of history and no reproduction need, the operational database plus a warehouse could cover everything. Add a store only when an access pattern needs lower latency or more concurrency than the existing stores give, or more history than they keep, and record which pattern justified it.