Fifty Thousand Orders, and Everything That Happens to Them
One table, one session: build it, evolve its schema and its layout, mutate it, wreck it with forty commits, then measure the repair — and get the same answer from three engines that never spoke to each other.
Fifty thousand bookshop orders fit in one data file. Forty more orders, delivered one commit at a time, turn that file into forty-one. The rows remain correct; the maintenance work changes.
The same table also has to survive a renamed column, a new partition spec, deletes and bad batches. Every operation below appeared in an earlier chapter. Running them together lets us follow which state changes, which files survive, and whether three engines still return the same revenue totals.
Run against Spark 4.1.3 / Iceberg 1.11.0 / PyIceberg 0.11.1 / DuckDB 1.5.5, through the REST catalog from chapter 5.
1. Build it
A partitioned, merge-on-read table, and fifty thousand orders in one commit:
CREATE TABLE ice.bookshop.capstone (
order_id BIGINT, customer_id BIGINT, book_title STRING, country STRING,
quantity BIGINT, amount DOUBLE, status STRING, ordered_at TIMESTAMP)
USING iceberg PARTITIONED BY (months(ordered_at))
TBLPROPERTIES ('write.delete.mode' = 'merge-on-read');data_files= 1 bytes= 117233 delete_files= 0 manifests= 1 snapshots= 1 | rows: 50000Fifty thousand rows in one data file of 117 KB, one manifest, one snapshot. That is what a table looks like when it is written in one commit, and it is the baseline everything below is measured against. Remember the shape: one commit, four files (chapter 3).
2. Change the schema (chapter 7)
The bookshop needs a new channel column and wants book_title renamed to title. Both changes belong in the schema:
ALTER TABLE ice.bookshop.capstone ADD COLUMN channel STRING;
ALTER TABLE ice.bookshop.capstone RENAME COLUMN book_title TO title;columns: [order_id, customer_id, title, country, quantity, amount, status, ordered_at, channel]
files rewritten: 0Zero. A rename and an addition, on a table of fifty thousand rows, and not one byte of data moved. The rename is safe because the column was never identified by name — field 3 is still field 3, and the Parquet files still carry PARQUET:field_id 3. This is the operation that, against the folder of chapter 1, silently split one logical column into two.
3. Change the layout (chapter 9)
The layout needs to move from monthly to daily partitions. Changing the spec controls where subsequent writes land:
ALTER TABLE ice.bookshop.capstone DROP PARTITION FIELD months(ordered_at);
ALTER TABLE ice.bookshop.capstone ADD PARTITION FIELD days(ordered_at);data_files= 1 bytes= 117233 delete_files= 0 manifests= 1 snapshots= 1Identical to before. Repartitioning a table wrote no data, no snapshot and no manifest — it appended a new partition spec to the metadata and moved a default. In Hive this is a migration with an outage. Here it is a metadata edit, and the existing file keeps its old spec forever. That is precisely why chapter 9 warns that evolution changes the future rather than the past.
4. Mutate it (chapter 10)
DELETE FROM ice.bookshop.capstone WHERE status='shipped' AND country='DE';data_files= 1 bytes= 117233 delete_files= 1 manifests= 2 snapshots= 2 rows: 47619The data file is byte-for-byte unchanged and a delete file appeared beside it. That is merge-on-read: the writer did almost nothing, and every reader from here on pays a little to subtract those rows. Had the table been copy-on-write, that 117 KB file would have been rewritten to remove roughly two thousand rows.
5. Wreck it (chapter 11)
Now the thing that actually happens to production tables. Forty late orders arrive one at a time, each in its own commit:
data_files= 41 bytes= 216470 delete_files= 1 manifests= 42 snapshots= 42
query: 336.1 msForty single-row inserts turned one data file into forty-one, one manifest into forty-two, and grew the table on disk from 117 KB to 216 KB — an 85% size increase to add forty rows. The rows themselves are a rounding error; what grew is the per-commit overhead of chapter 3’s four-file rule, applied forty times.
The query cost is the point. A grouped aggregate over the whole table now takes 336 ms, best of three.
Nothing is broken. Every row is correct, every commit was atomic, and nothing warned anybody. This is the shape a streaming or micro-batch pipeline reaches within hours, and it is invisible until someone complains that the dashboard got slow.
6. Repair it (chapters 12 and 13)
rewrite_data_files: {'rewritten_data_files_count': 40, 'added_data_files_count': 9,
'rewritten_bytes_count': 99237, 'failed_data_files_count': 0,
'removed_delete_files_count': 0}
rewrite_manifests: {'rewritten_manifests_count': 41, 'added_manifests_count': 1}data_files= 10 bytes= 143569 delete_files= 1 manifests= 3 snapshots= 44
query: 163.5 ms (2.1x)Forty-one data files became ten. Forty-two manifests became three. The query went from 336 ms to 163 ms.
The compaction produced nine files, not one, because the late orders crossed partition boundaries. rewrite_data_files bin-packs within partitions, and the table is partitioned by day. The forty late orders spanned nine days, so nine is the floor. Compaction cannot merge across partition boundaries, which means your partitioning decides how small your file count can ever get. That is chapter 8 and chapter 12 meeting, and it is the reason over-partitioning is so hard to recover from.
Note also what has not happened yet: the snapshot count went up, to 44. Compaction is a write like any other. Nothing has been reclaimed, and the disk still holds every superseded file.
7. The counter that lies
Look again at that first result:
'removed_delete_files_count': 0And at the state afterwards: delete_files= 1. The delete file from step 4 survived compaction, and the counter reporting on delete files reported zero either way.
The successful compaction therefore has not removed the cost of applying deletes. An ordinary rewrite_data_files does not fold position deletes back into the data files. The table is still merge-on-read, still carrying its delete file, still paying a small read cost — after a compaction that reported complete success.
Getting rid of it takes a deliberate option (use-starting-sequence-number => false), and even then the removed_delete_files_count figure cannot be trusted to tell you it happened. Check delete_files directly. The lesson generalises: in a maintenance job, verify the state, not the return value.
8. Catch a bad batch before anyone sees it (chapter 16)
A batch arrives with negative quantities. We stage it on a branch rather than on the table:
ALTER TABLE ice.bookshop.capstone CREATE BRANCH staging;
SET spark.wap.branch = staging;
INSERT INTO ice.bookshop.capstone VALUES (999999, 1, 'Corrupt', 'ZZ', -5, -1.0, …);staged on branch : 47660 rows, 1 invalid -> reject
main untouched : 47659 rows (was 47659)The write landed, was queried, was found bad, and main never moved. Drop the branch and it is as if the batch never existed.
Two things make this work, and both are easy to get wrong. The table has write.wap.enabled = true — without it, spark.wap.branch is silently ignored and the batch goes straight to main. And while the branch is set, reads redirect to it too, which is why the 47,660 above is the branch’s count and not main’s. Unset the config before you check whether you were affected.
9. Undo one that got through (chapter 6)
Not every bad batch gets staged. This one lands on main:
bad batch landed : 47660 rows, max amount 99999.0
rolled back : 47659 rows, max amount 133.5One procedure call restores both the row count and the maximum amount. The previous version was never destroyed — returning to it moves a pointer, without a backup restore or a pipeline re-run.
One trap, which caught me while writing this. .snapshots includes snapshots from every branch, so “the most recent snapshot” is not necessarily on main. My first attempt grabbed the staging branch’s snapshot and got ValidationException: Cannot roll back to snapshot, not an ancestor of the current state. Use .history, which only tracks main.
10. Then throw the history away (chapter 13)
Everything above depends on old snapshots existing. Expiry is what reclaims their storage, and the two goals are in direct conflict:
history before : 46 snapshots
expire_snapshots : {'deleted_data_files_count': 42, 'deleted_manifest_files_count': 83,
'deleted_manifest_lists_count': 45}
after expiry : data_files= 10 bytes= 143569 delete_files= 1 manifests= 3 snapshots= 1Forty-six snapshots became one. Expiry deleted 42 data files, 83 manifests and 45 manifest lists. That is more metadata files than this table has ever had data files — chapter 3’s four-file rule, paid back all at once.
And the rollback we just performed is now impossible. Every version except the current one is gone. retain_last => 1 is not a tuning parameter, it is a retention policy, and choosing it means choosing how far back your worst incident can be undone. On this table I would not run it that way; I ran it here to make the trade visible in one line.
11. The answer
Finally, the question the table exists to answer — revenue by country — asked three separate ways.
From Spark:
DE 14285 974584.5
GB 16707 1087424.1
US 16667 1112025.0
total rows: 47659From PyIceberg, through the same REST catalog, no JVM involved:
PyIceberg rows : 47659 | format_version: 2
[('DE', 14285, 974584.5), ('GB', 16707, 1087424.1), ('US', 16667, 1112025.0)]And from DuckDB, attached directly to the catalog:
DuckDB direct : [('DE', 14285, 974584.5), ('GB', 16707, 1087424.1), ('US', 16667, 1112025.0)]
DuckDB rows : (47659,)Identical to the cent, across three engines with nothing in common but a URL. Each independently read the metadata tree, applied the same merge-on-read delete file, resolved the same schema through a rename, and read files written under two different partition specs.
That is the whole argument of the format, and it is worth being precise about what made it work. Not that the engines are compatible with each other — they are not, and they share no code. They are each compatible with a written-down specification, and they agree because the table describes itself completely enough that agreement is the only available outcome.
What this book was actually about
Chapter 1 broke a folder four ways: a refresh that returned three different answers, a schema that split in two, no history, and two writers where one silently destroyed the other’s work.
Everything since has been the machinery that makes those four failures impossible, and the price of that machinery. An explicit list of files instead of a directory listing. A schema with numbered fields instead of names that files each remember differently. A pointer that swaps atomically instead of a delete-then-write. Old versions kept instead of overwritten.
And the price, which this chapter measured twice: four files per commit whether the commit carries fifty thousand rows or one, a metadata tree that grows with commits rather than data, and maintenance jobs that are not optional. A table format does not make storage free. It makes storage honest, and then hands you the tools to keep it tidy along with the responsibility to actually run them.
The run leaves three decisions that matter beyond this table.
The catalog is the transaction boundary. Everything atomic in Iceberg happens because one pointer moved. Pick that component carefully, because it is the only part of the system that cannot be replaced casually.
Commit frequency shapes your table more than data volume does. The same fifty thousand rows are one file or forty-one, depending only on how they arrived. Every downstream cost follows from that.
Verify the state, not the return value. The most expensive mistakes in this book were not failures. They were operations that reported success — a partial refresh returning a plausible number, a union_by_name that made a broken query pass, a compaction whose counter said zero, a create_table that produced a table nobody could write to. The format is honest about what it holds. It is up to you to look.
Comments