Time Travel, Snapshot Expiry, and the Compaction Plan
Reconstruct a table as of an earlier snapshot from metadata alone, then pick the compaction candidates that fix the small-files problem streaming writes create.
Time travel is just a WHERE clause
Once a table is a chain of snapshots, reading the past stops being a feature and becomes arithmetic. The file set as of snapshot S is every file that was added at or before S and not deleted at or before S:
WHERE added_snapshot_id <= S
AND (deleted_snapshot_id IS NULL OR deleted_snapshot_id > S)
That is the whole mechanism. No copies were kept, nothing was backed up. The old files are still in the bucket because a later commit only stopped pointing at them, and the metadata still records which snapshot did that.
Two uses pay for the feature:
- Debugging a bad deploy. A model change lands, the numbers move, and nobody can tell whether the data or the logic changed. Querying the table as of yesterday's snapshot answers it in one query, and if the data is the problem you can roll the table back to that snapshot instead of rebuilding it.
- Reproducing a number. A report published last Tuesday can be regenerated exactly, because the table as it stood last Tuesday still exists. This is what auditors and finance teams mean by reproducibility.
The tension is the bill and the law. Every retained snapshot pins the files it references, so a table that never expires snapshots keeps paying storage for data it replaced months ago. Snapshot expiry is the maintenance operation that drops old snapshots and physically deletes the files no surviving snapshot references. That also means a deleted row is not really gone until every snapshot that contained it expires, which matters when the deletion was a GDPR erasure request. The standard answer is a retention window short enough to satisfy the policy, typically days, not years.
| file added at | file deleted at | live now? | in snapshot 1004? |
|---|---|---|---|
| 1001 | 1003 | no | no, deleted before 1004 |
| 1003 | (null) | yes | yes |
| 1004 | 1005 | no | yes, deleted after 1004 |
| 1005 | (null) | yes | no, added after 1004 |
The small-files problem, and the fix that lives in the table
A streaming writer commits every few minutes. Each commit writes at least one file per partition, so a day of five-minute micro-batches leaves hundreds of tiny files in a single partition. Level 6 taught why that hurts on the read side: per-file overhead, a request to object storage for every one of them, and row groups too small for compression or statistics to do anything useful. A partition of 200 files at 3 MB each is strictly worse than the same bytes in two files.
The table format is what fixes it in place. Compaction (in Iceberg, the rewrite_data_files procedure) reads the small files in a partition, writes a few large ones, and commits the swap as one snapshot. Readers never see a partial state, and no path changes, so nothing downstream needs to know it happened. The same operation folds merge-on-read delete files into the data, which is what keeps that write mode from degrading over time.
One unit note first, because the switch is silent otherwise. Iceberg's target-file-size-bytes is a binary size, so this lesson counts 1 MB as 1048576 bytes, where Level 6's storage inventory counted it as 1,000,000. The two conventions land within 5% of each other and neither target moves because of it, but an avg_file_mb computed here is not directly comparable to one you computed a level ago. Whichever you use, say which one you used.
The number to carry into an interview is the target: 128 MB to 1 GB per file. That range is not arbitrary. It is large enough that per-file overhead disappears and row groups are big enough for statistics to prune well, and small enough that a single file does not become one enormous unsplittable task. On AWS, S3 Tables runs this compaction for you as a managed feature and bills for it, which tells you how routinely it is needed.
So a compaction plan is a metadata query. Group the live files by partition, count them, average their size, and keep the partitions where the count is high and the average is small. Both halves matter, and that is the part people get wrong.
Reading the plan honestly
A partition with many files is not automatically broken. A genuinely busy day can hold nine files of 200 MB each, which is a healthy layout and rewriting it would burn compute for nothing. The signal is many files and a small average size. Filtering on the count alone schedules pointless work; filtering on average size alone flags a quiet day that only ever held three small files, where rewriting three into one saves nothing and costs a commit. The seed you are about to query has one of each, so a plan that drops either half returns the wrong list.
The third half-filter is easy to forget: the live test. If you group before filtering out the files a later commit already replaced, a partition that was compacted months ago still looks like a swarm of small files, and you schedule a rewrite of data that no longer exists. Filter on deleted_snapshot_id IS NULL first, then group.
Common mistake: running the time-travel filter as deleted_snapshot_id IS NULL AND added_snapshot_id <= S. That answers "which files are live today and existed by S," which is not the same question. It silently drops every file that was live at S and deleted afterwards, which is usually most of what you were looking for.
Interview nuance: "you have millions of small files, what do you do?" is looking for compaction plus the numbers, not "delete it and reload." A complete answer names the cause (frequent commits or an over-granular partition key), the fix (compaction to a 128 MB to 1 GB target, scheduled as table maintenance), and the prevention (fewer, larger commits, or a coarser partition key). Mentioning that the format lets you do it without changing any downstream path is the detail that lands.
On a real platform this differs. You would time travel with
SELECT ... FOR SYSTEM_VERSION AS OF 1004orFOR SYSTEM_TIME AS OF '2026-06-16', and compact withCALL catalog.system.rewrite_data_files(table => 'analytics.events', options => map('target-file-size-bytes','536870912')), thenexpire_snapshotsto reclaim the storage. The planning query you are about to write is what the engine runs internally to decide which partitions are worth rewriting, and it is also what you write by hand when you are deciding whether the maintenance job is configured correctly.
CREATE TABLE snapshots (
snapshot_id INTEGER,
parent_id INTEGER,
committed_at TEXT,
operation TEXT,
added_files INTEGER,
deleted_files INTEGER
);
INSERT INTO snapshots (snapshot_id, parent_id, committed_at, operation, added_files, deleted_files) VALUES
(1001, NULL, '2026-05-21 02:05:00', 'append', 12, 0), -- 2 files for dt=2026-05-20 plus a 10-file backfill of dt=2026-05-19
(1002, 1001, '2026-05-22 02:05:00', 'append', 2, 0),
(1003, 1002, '2026-05-23 02:06:00', 'overwrite', 4, 12), -- rewrote dt=2026-05-20 and compacted the dt=2026-05-19 backfill
(1004, 1003, '2026-06-16 02:05:00', 'append', 2, 0),
(1005, 1004, '2026-06-17 02:05:00', 'overwrite', 1, 2),
(1006, 1005, '2026-06-21 02:05:00', 'append', 10, 0),
(1007, 1006, '2026-07-01 09:00:00', 'append', 8, 0), -- 5 files for dt=2026-07-01 plus 3 late events routed to dt=2026-06-25
(1008, 1007, '2026-07-01 09:30:00', 'append', 5, 0),
(1009, 1008, '2026-07-02 09:00:00', 'append', 12, 0),
(1010, 1009, '2026-07-02 10:15:00', 'delete', 0, 1); -- dropped one bad dt=2026-06-20 file
CREATE TABLE files (
file_path TEXT,
partition_dt TEXT,
record_count INTEGER,
file_size_in_bytes INTEGER,
added_snapshot_id INTEGER,
deleted_snapshot_id INTEGER -- NULL means the file is still live in the current snapshot
);
INSERT INTO files (file_path, partition_dt, record_count, file_size_in_bytes, added_snapshot_id, deleted_snapshot_id) VALUES
-- dt=2026-05-19: a ten-file backfill that snapshot 1003 compacted into two healthy files.
('s3://lake/warehouse/events/data/dt=2026-05-19/00001-1-1001.parquet', '2026-05-19', 16400, 4194304, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00002-1-1002.parquet', '2026-05-19', 12300, 3145728, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00003-1-1003.parquet', '2026-05-19', 20500, 5242880, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00004-1-1004.parquet', '2026-05-19', 16300, 4194304, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00005-1-1005.parquet', '2026-05-19', 14350, 3670016, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00006-1-1006.parquet', '2026-05-19', 18450, 4718592, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00007-1-1007.parquet', '2026-05-19', 16350, 4194304, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00008-1-1008.parquet', '2026-05-19', 12250, 3145728, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00009-1-1009.parquet', '2026-05-19', 20450, 5242880, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00010-1-1010.parquet', '2026-05-19', 16250, 4194304, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-19/00011-3-2001.parquet', '2026-05-19', 875000, 157286400, 1003, NULL),
('s3://lake/warehouse/events/data/dt=2026-05-19/00012-3-2002.parquet', '2026-05-19', 838000, 150994944, 1003, NULL),
('s3://lake/warehouse/events/data/dt=2026-05-20/00001-1-a1c4.parquet', '2026-05-20', 1040000, 268435456, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-20/00002-1-b7e0.parquet', '2026-05-20', 1015000, 268435456, 1001, 1003),
('s3://lake/warehouse/events/data/dt=2026-05-20/00003-3-c9d2.parquet', '2026-05-20', 1060000, 271581184, 1003, NULL),
('s3://lake/warehouse/events/data/dt=2026-05-20/00004-3-d5f8.parquet', '2026-05-20', 995000, 260046848, 1003, NULL),
('s3://lake/warehouse/events/data/dt=2026-05-21/00001-2-e2a7.parquet', '2026-05-21', 1120000, 279969792, 1002, NULL),
('s3://lake/warehouse/events/data/dt=2026-05-21/00002-2-f6b3.parquet', '2026-05-21', 1048000, 265289728, 1002, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-15/00001-4-a8c1.parquet', '2026-06-15', 640000, 167772160, 1004, 1005),
('s3://lake/warehouse/events/data/dt=2026-06-15/00002-4-b3d9.parquet', '2026-06-15', 612000, 160432128, 1004, 1005),
('s3://lake/warehouse/events/data/dt=2026-06-15/00003-5-c1e6.parquet', '2026-06-15', 1252000, 328204288, 1005, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00001-6-11aa.parquet', '2026-06-20', 815000, 209715200, 1006, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00002-6-22bb.parquet', '2026-06-20', 766000, 197132288, 1006, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00003-6-33cc.parquet', '2026-06-20', 848000, 218103808, 1006, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00004-6-44dd.parquet', '2026-06-20', 734000, 188743680, 1006, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00005-6-55ee.parquet', '2026-06-20', 880000, 226492416, 1006, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00006-6-66ff.parquet', '2026-06-20', 782000, 201326592, 1006, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00007-6-7700.parquet', '2026-06-20', 750000, 192937984, 1006, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00008-6-8811.parquet', '2026-06-20', 835000, 214958080, 1006, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-20/00009-6-9922.parquet', '2026-06-20', 799000, 205520896, 1006, NULL),
-- Added after snapshot 1004 and dropped after it too, by the delete commit 1010.
('s3://lake/warehouse/events/data/dt=2026-06-20/00010-6-aa33.parquet', '2026-06-20', 748000, 192937984, 1006, 1010),
-- dt=2026-06-25: a quiet day whose three small files are not worth rewriting.
('s3://lake/warehouse/events/data/dt=2026-06-25/00001-7-c101.parquet', '2026-06-25', 19600, 5242880, 1007, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-25/00002-7-c202.parquet', '2026-06-25', 15700, 4194304, 1007, NULL),
('s3://lake/warehouse/events/data/dt=2026-06-25/00003-7-c303.parquet', '2026-06-25', 23500, 6291456, 1007, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00001-7-1a2b.parquet', '2026-07-01', 11800, 3145728, 1007, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00002-7-2c3d.parquet', '2026-07-01', 19600, 5242880, 1007, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00003-7-3e4f.parquet', '2026-07-01', 7900, 2097152, 1007, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00004-7-4a5b.parquet', '2026-07-01', 27400, 7340032, 1007, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00005-7-5c6d.parquet', '2026-07-01', 15700, 4194304, 1007, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00006-8-6e7f.parquet', '2026-07-01', 23500, 6291456, 1008, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00007-8-7a8b.parquet', '2026-07-01', 11200, 3145728, 1008, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00008-8-8c9d.parquet', '2026-07-01', 19100, 5242880, 1008, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00009-8-9e0f.parquet', '2026-07-01', 31300, 8388608, 1008, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-01/00010-8-0a1b.parquet', '2026-07-01', 15400, 4194304, 1008, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00001-9-a100.parquet', '2026-07-02', 7800, 2097152, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00002-9-a200.parquet', '2026-07-02', 15500, 4194304, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00003-9-a300.parquet', '2026-07-02', 23200, 6291456, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00004-9-a400.parquet', '2026-07-02', 11500, 3145728, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00005-9-a500.parquet', '2026-07-02', 19400, 5242880, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00006-9-a600.parquet', '2026-07-02', 27100, 7340032, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00007-9-a700.parquet', '2026-07-02', 7600, 2097152, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00008-9-a800.parquet', '2026-07-02', 31000, 8388608, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00009-9-a900.parquet', '2026-07-02', 15300, 4194304, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00010-9-b000.parquet', '2026-07-02', 23000, 6291456, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00011-9-b100.parquet', '2026-07-02', 11300, 3145728, 1009, NULL),
('s3://lake/warehouse/events/data/dt=2026-07-02/00012-9-b200.parquet', '2026-07-02', 19200, 5242880, 1009, NULL);-- The layout the table is actually in right now: live files per partition and their average size.
-- The streaming partitions stand out immediately.
SELECT partition_dt,
COUNT(*) AS live_files,
ROUND(AVG(file_size_in_bytes) / 1048576.0, 1) AS avg_file_mb
FROM files
WHERE deleted_snapshot_id IS NULL
GROUP BY partition_dt
ORDER BY partition_dt;Apply
Your turn
The task this lesson builds to.
Write a query that returns the table's file set as of snapshot 1004, as (file_path, record_count), in file_path order, over files(file_path, partition_dt, record_count, file_size_in_bytes, added_snapshot_id, deleted_snapshot_id).
A file belonged to snapshot 1004 when it was added at or before 1004 and was not yet deleted at 1004. A file deleted by a later snapshot still counts, and a file added by a later snapshot does not.
4 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 1 bonus drill.
Write a query that returns the compaction plan as (partition_dt, file_count, avg_file_mb, total_mb) over files: among live files only, keep the partitions holding more than 8 files whose average file is under 32 MB, worst first.
Treat 1 MB as 1048576 bytes, and round avg_file_mb and total_mb to 2 decimals. Worst first means the most files first, breaking ties by the smaller average file. Alias the columns exactly as listed.
5 hints and 1 automated check are waiting in the workspace.