Rolling Edition

Open Table Formats

Teaching kit

From Edition 2026.11.3, the book's current edition. It changes when the book does. Use and adapt it freely, with credit to the book.

On this page
  1. Syllabus
  2. Objectives and outlines
  3. Quiz bank

Syllabus

Reading times are for the chapter's text. Students also run its examples, in the Lab or on their own machine.

A chapter a week: 10 weeks

WeekChaptersReadingExamples
11. Build a first table, end to end17 min3
22. Read a table as it was at an earlier snapshot17 min3
33. Add and rename columns without rewriting files17 min4
44. Change partitioning without rewriting old files17 min4
55. Correct rows with deletes, updates and merges16 min5
66. Compact the small files that hourly appends leave17 min3
77. Expire snapshots and remove unreferenced files17 min3
88. Register tables in a catalog and query them from DuckDB16 min5
99. Move a table between formats and check the copy17 min4
1010. Choose a format from what your tools support16 min5

In 5 weeks

WeekChaptersReadingExamples
11. Build a first table, end to end
2. Read a table as it was at an earlier snapshot		34 min6
23. Add and rename columns without rewriting files
4. Change partitioning without rewriting old files		34 min8
35. Correct rows with deletes, updates and merges
6. Compact the small files that hourly appends leave		33 min8
47. Expire snapshots and remove unreferenced files
8. Register tables in a catalog and query them from DuckDB		33 min8
59. Move a table between formats and check the copy
10. Choose a format from what your tools support		33 min9

Objectives and outlines

Each chapter's objectives are its first sentence. Each outline follows the chapter's sections, with the terms it defines, the predictions it asks for before an example runs, and the rule each section ends on.

1. Build a first table, end to end

About 17 minutes to read, 3 examples.

By the end, students can commit batches of rows to a table, read the table back in DuckDB, and check the totals.

Lecture outline: 10 sections
  1. What a table format adds to files
  2. One load loop, three commit styles
  3. A commit names the table's files
    • Working rule Ask the table what it holds, through its current snapshot, and not by listing its folder. The folder holds every file a writer put there, and the snapshot says which of those files the table reads.
  4. Why each format is taught through a Python library
    • Working rule Write and read each table with the library whose version you pin, and say which version your reads use. Reach for an engine's own extension only when you have measured what the library cannot do for you.
  5. Land three hours in a table
  6. Create the table in a catalog
    • Working rule Write the schema out in code, with the type each column really has, before the first commit. The types are then decided on purpose, and not by whichever batch happens to arrive first.
  7. Append each hour as a commit
    • Run append-hours.py
    • Working rule Make one commit per batch you can name, and compare the rows each commit added with the count you expect from its source before you make the next commit. A wrong count is simpler to trace while its commit is the most recent one, and harder once more commits sit on top of it.
  8. Query the table with DuckDB
    • Run scan-into-duckdb.py
    • Working rule Read an Iceberg table into DuckDB as the Arrow table from a scan, and compare a total with the scan's own sum, to confirm that DuckDB received every row. Then check each query's result against a source you can compare it with.
  9. What goes wrong in production
    • Working rule Write down the row count each commit should add, next to the load that makes it, and compare that count with the commit's summary after every load. The table does not check for a repeated load. A count you wrote down in advance is the check that does.
  10. Decisions this chapter made

2. Read a table as it was at an earlier snapshot

About 17 minutes to read, 3 examples.

By the end, students can read a table as it was after an earlier commit, in each of the three formats.

Lecture outline: 7 sections
  1. Every commit is a snapshot
    • Run snapshot-history.py
    • Working rule Before you read or change a table, list its history in the format you use, and use that list as the only source of commit numbers. Sort the list by commit order, label Iceberg snapshots with snapshot_labels before you print them, and print Delta versions as Delta numbers them. Read the operation with each commit. An append only adds files, so it cannot have removed rows, while an overwrite or delete can.
  2. What a snapshot points to
    • Working rule Treat an old snapshot as readable only while its files exist. Before you remove files, decide which snapshots you keep, and expire the others first. A query over an old snapshot that fails after a cleanup is a missing file. The fix is to find that file in a copy of the table, not to read a different snapshot in its place.
  3. Read an earlier snapshot
  4. Snapshots in pyiceberg
    • Run read-as-of-snapshot.py
    • Working rule Get snapshot IDs from table.snapshots() and pass the ID you looked up, never a label's number. Map the IDs to labels in commit order before you print anything. When the question is the state after a commit, find that commit in the history first, and read the snapshot it created.
  5. Read a commit by its number in each format
    • Run three-version-numbers.py
    • Working rule Compare formats by the hour each commit loaded, never by raw number. Print Iceberg and DuckLake snapshots with their labels, print Delta versions as Delta numbers them, and bind the counts to them. Read Delta only through read_delta, and read DuckLake only with numbers that come from ducklake_snapshots. If a table in one format gets commits that the other does not, the labels stop lining up, and the history of each format becomes the only reliable record.
  6. What goes wrong in production
    • Working rule Read the error text before you change the query. It names the value, the module or the version that failed, and that is the fastest route to the fix. Compare each count in one format with the same label in the other format before you trust either one. Keep the snapshot your question needs readable until the question is answered, and expire only the snapshots that no question still needs. Record the label and the format's name with every number you report, so that a reader of the report can reproduce it.
  7. Decisions this chapter made

3. Add and rename columns without rewriting files

About 17 minutes to read, 4 examples.

By the end, students can add a column to a table that already holds data, rename a column in Iceberg without rewriting its data files, and say why a rename in Delta Lake is a rewrite of the whole table.

Lecture outline: 8 sections
  1. Columns are tracked by ID
    • Working rule Expect an Iceberg column to keep its data through a rename, because the reader matches by field ID. Expect a Delta Lake column to match by name unless column mapping is on. Test a rename on a copy of the table before you rely on it, and read the column names back afterwards.
  2. Add load_batch after three hours
    • Run add-column-to-existing-files.py
    • Working rule Add a column with the format's own call, and never by rewriting the files. Expect the rows written before the add to read null for the column, and check that they do.
  3. Old files read as null
    • Working rule After an add, count nulls in the added column by hour of the day, where each hour is one write. Rows written before the add should be null, and rows written after it should not be. Any other pattern means a writer sent a different set of columns, or sent nulls in the column, so find that writer before you trust the column.
  4. Rename a column by its ID without rewriting files
    • Run rename-by-id.py
    • Working rule Rename an Iceberg column when its name is wrong, and check the field ID before and after the call. The field ID must be the same, and the data file count must not change. Tell every reader of the table about the rename, because the current schema has only article_title, and an older snapshot still has page_title.
  5. Delta Lake has no rename at this pin
    • Run delta-rename-trap.py
    • Run delta-rename-by-rewrite.py
    • Working rule Choose Delta Lake column names when the table is created, because at this pin a later rename is a rewrite of the whole table. Do not use set_column_metadata to rename a column: it changes the version and the metadata, and the column keeps its name. After any schema change, read the column names back through the library, as the trap example does, and check them before a query depends on them.
  6. What goes wrong in production
    • Working rule After any schema change, do three things before a downstream query runs: read the column names back through the library, count nulls in each added column by hour, where each hour is one write, and compare the schema, not the snapshot count, with what the change was meant to do.
  7. Decisions this chapter made
  8. Sources

4. Change partitioning without rewriting old files

About 17 minutes to read, 4 examples.

By the end, students can change the partitioning of a table in Iceberg, Delta Lake and DuckLake, and count the files a query plans before and after the change.

Lecture outline: 7 sections
  1. Partition specs are versioned too
    • Working rule Change an Iceberg partition spec when your readers' filters change and the current spec plans too many files for them. If the current spec already plans few files for those filters, leave it alone. When you do change it, leave the old files where they are. Record the change, with the date, the old and replacement fields, and the readers' filters that prompted it, in the table's runbook. Keep the plan counts from before and after the change with it, as the section on what a query scans shows.
  2. Move from daily to hourly partitions
    • Run day-then-hour-spec.py
    • Working rule In Iceberg, make the spec change in one update_spec() block, so the old field is removed in the same change that adds the replacement. Split the two edits only if a step has to run between them; this chapter has no such step. Check the spec list after the change, and check that the field names and transforms are the ones you meant before you load any data under the second spec.
  3. Files written under each spec
    • Working rule After any spec change, count the data files under each spec and compare the counts with the loads you made. A count that moves when no rewrite was planned means the change did more than you wanted, so stop and check before the next load.
  4. Check what a query scans
    • Run plan-files-by-partition.py
    • Working rule Plan the filters your readers use, and the full scan, before and after every partition change, and keep the counts with the runbook entry. If a filter that planned one file starts to plan many, the change has broken the pruning for that filter, and you should change the spec or the filter before the next load under it.
  5. Partitioning in the other formats
    • Run delta-partition-overwrite.py
    • Run ducklake-partition-alter.py
    • Working rule In Iceberg and DuckLake, a partition change is a change to metadata. Old files stay in place and later loads use the partitioning the change set, so check the file counts after the first load. In Delta Lake, a partition change is a full overwrite that rewrites the table's current files, so plan it as a rewrite and check the row count and the file count after it.
  6. What goes wrong in production
    • Working rule Choose the partition grain from the filters your readers use and the size of the files you expect, not from how often data arrives. Before you run a partition change on a table that serves readers, run it on a copy, and compare the plan counts and the row count on both sides.
  7. Decisions this chapter made

5. Correct rows with deletes, updates and merges

About 16 minutes to read, 5 examples.

By the end, students can delete, upsert and merge rows in Iceberg, Delta Lake and DuckLake, say what each write does to the files, and count the snapshots a pyiceberg upsert adds.

Lecture outline: 9 sections
  1. Late data arrives as a correction
    • Working rule Before you write a correction, check that its batch holds each key once. pyiceberg and DuckLake refuse a batch with a repeated key, each in its own way, and a retry does not fix the data. The production section shows the failures.
  2. Delete rows, and what each format writes
  3. Copy-on-write and merge-on-read
    • Working rule Expect copy-on-write deletes to cost a file rewrite for each file that holds a match. Prefer predicates whose matches sit in few files, such as one hour, and batch the deletes that you know about into one write. Merge-on-read suits a table where writes are frequent and reads can pay for the delete files until something rewrites them.
  4. Upsert a corrected hour
  5. Upserts in pyiceberg
    • Run redeliver-hour-upsert.py
    • Working rule Use upsert with join_cols set to the key, and read its counts from the result. Count the snapshots from the snapshot list, and do not assume one commit per call. Run the same batch again when you are unsure whether the first write landed, and check that the second run reports no updates.
  6. MERGE in DuckLake
    • Run merge-correction-ducklake.py
    • Working rule Write a MERGE with a WHEN MATCHED AND condition that compares the columns you update, so that a rerun changes nothing. Make sure the source holds each key once before you run it. DuckLake refuses a merge that matches one target row to two source rows, and the production section shows the error.
  7. Merge a changed label into the domains table
    • Run merge-domains-label.py
    • Working rule Merge a dimension table with the same WHEN MATCHED AND condition as a fact table, and run the merge a second time to check that it changes nothing. A label change reaches every query that joins to the dimension, so review the label you are about to merge.
  8. What goes wrong in production
    • Run duplicate-source-keys.py
    • Working rule Check the batch for repeated keys before any write, and treat a refusal as a bug in the batch, not a transient fault. Gather deletes into one write. Count snapshots from the snapshot list when you audit a correction.
  9. Decisions this chapter made

6. Compact the small files that hourly appends leave

About 17 minutes to read, 3 examples.

By the end, students can count the data files a table holds, merge them into fewer files in Delta Lake, DuckLake and Iceberg, and say what each merge leaves on disk.

Lecture outline: 9 sections
  1. Hourly appends leave small files
    • Working rule Count the data files of each table after each load, and schedule compaction from the rate at which files arrive and the file size your readers need. A load every hour gives 24 files a day per table, so a daily compaction is a reasonable first choice. This is a rule of thumb, not a measurement. Compact more often only when your own read times or file counts show that you need to.
  2. Compact with optimize.compact()
    • Run file-counts-before-after.py
    • Working rule Compact a Delta table on the schedule from the first section (daily for hourly loads), and check the file count afterwards. Read the retention rules in chapter 7 before you remove anything: a compaction adds a commit and leaves the old files in place, so it adds storage until the vacuum runs.
  3. Compact in DuckLake
  4. Merge adjacent files
    • Run compact-ducklake.py
    • Working rule Run ducklake_merge_adjacent_files after the inserts for a period are done, and read the file count it returns. A merge does not remove deleted rows, so for a table with delete files, call ducklake_rewrite_data_files with a delete_threshold that you chose for that table, and check the result.
  5. Rewrite the table to compact it
    • Run iceberg-rewrite-by-overwrite.py
    • Working rule Rewrite an Iceberg table by overwrite only when no other writer commits to the table during the rewrite. On an unpartitioned table, stream the scan as this example does. On a partitioned table, the scan must be read with to_arrow(), so the whole scan has to fit in the memory of the job. A streamed reader can be read only once: if the commit fails, build a new reader for the next attempt. Check the file count and the row count after each rewrite, and keep the snapshot from before it until you have checked the result.
  6. Why pyiceberg has no rewrite call
    • Working rule Check the pyiceberg release notes at each upgrade for a compaction call, because a call would replace the overwrite rewrite in this chapter. Until then, treat the overwrite as a batch job, run it when no other writer is committing, and for a partitioned table budget memory for the whole scan.
  7. Check each compaction before you trust it
    • Working rule After every compaction, compare the row count with the count from before it, and read a version from before it. A compaction that changes the row count is a fault to fix before you run anything else on that table.
  8. What goes wrong in production
    • Working rule Schedule compaction next to the expiry and cleanup, not apart from them. Run the four checks from the check section after each run, and stop the schedule if the row count changes or if a concurrent writer fails.
  9. Decisions this chapter made

7. Expire snapshots and remove unreferenced files

About 17 minutes to read, 3 examples.

By the end, students can expire old snapshots in each of the three formats, remove the data files that only those snapshots still listed, and show that a read of an expired snapshot fails.

Lecture outline: 8 sections
  1. Snapshots keep files alive
    • Working rule Decide which snapshots or versions a reader may still need, and expire the rest before you remove any file. Never remove a file that a snapshot you keep still lists, and check that claim with a count before you delete.
  2. Expire snapshots with pyiceberg
    • Run expire-iceberg-snapshots.py
    • Working rule Expire Iceberg snapshots by ID, chosen from the commit order, and count the files each remaining snapshot lists before and after. Expect no disk space back from the expiry. pyiceberg removes metadata only, so the files it leaves behind stay on disk until you remove them yourself.
  3. Find orphan files after expiry
    • Working rule Count orphans by listing, and remove them only after you have confirmed that no writer can still commit them and no reader still uses a snapshot that lists them. In pyiceberg, nothing in the library makes that check for you.
  4. Vacuum a table with deltalake
    • Working rule Keep vacuum's default retention, or set a longer one in delta.deletedFileRetentionDuration, so that every version a reader may request is still readable for that long after it stops being current. Go shorter only after you have checked the longest read and the longest write in your system, and only by turning enforce_retention_duration off as a deliberate, recorded decision.
  5. Dry run first
    • Run vacuum-delta-dry-run.py
    • Working rule Run vacuum as a dry run first and read the count. Remove files with a real run only when the count matches what you expected from the table's history, and keep the default retention unless you have checked the longest read in your system.
  6. Expire and clean up in DuckLake
    • Run ducklake-expire-cleanup.py
    • Working rule Expire DuckLake snapshots first, then run each cleanup call as a dry run and then for real, in that order. Keep the default orphan check. Use cleanup_all only when one catalog owns the data folder and no reader still uses the files it removes, and use older_than otherwise.
  7. What goes wrong in production
    • Working rule Before you expire or clean up, list the readers, reports and audits that may open an older snapshot or version, and set the retention to cover the longest of them. Run each call as a dry run, compare its count with what you expected, and run the real call only when the two agree.
  8. Decisions this chapter made

8. Register tables in a catalog and query them from DuckDB

About 16 minutes to read, 5 examples.

By the end, students can register an Iceberg table in a SQL catalog, attach a DuckLake catalog, open a Delta Lake table by its path, and run one DuckDB statement that joins a table from Iceberg with a table from DuckLake.

Lecture outline: 8 sections
  1. What a catalog holds
    • Working rule Before you write to a table, find out which catalog or path names it, and back up that catalog along with the data folder. Parquet files do not name a table. The catalog does, as the failure section shows.
  2. Register a table in a SQL catalog
    • Run register-iceberg-table.py
    • Working rule Open a SQL catalog file with one name everywhere in your code, and reload a table before you read it when another writer may have committed since you loaded it. Keep SQLite for one writer at a time, and use a database built for concurrent writers before you run several.
  3. Register a table from its metadata file
    • Working rule Keep one writer name for each table. Register a metadata file under a second name only for a read you will remove afterwards, and pass overwrite=True only when you mean to replace that entry.
  4. Attach a DuckLake catalog
    • Run attach-ducklake.py
    • Working rule Back up a DuckLake catalog file with the data folder, and copy the file while no session has it attached. Give the data path when you create a catalog, and attach later with the catalog file alone.
  5. Find a table by path, with no catalog
    • Run open-delta-by-path.py
    • Working rule Keep each Delta table's path in one place your code reads, such as a configuration value, and open the table once at the start of a job to confirm the path. A path that does not exist is an error, not an empty table.
  6. Join tables from two catalogs
    • Run join-pageviews-and-domains.py
    • Working rule Join across catalogs in one session only when the side you read as Arrow fits in memory. Choose its state before you read it, and count the rows that find no match, which should be zero.
  7. What goes wrong in production
    • Run ducklake-orphan-cleanup-hazard.py
    • Working rule Run an orphan cleanup with dry_run => true first, against the catalog that owns the data folder, and read the list before any real run. If the catalog file is missing or empty, restore it before you run anything that deletes files.
  8. Decisions this chapter made

9. Move a table between formats and check the copy

About 17 minutes to read, 4 examples.

By the end, students can copy a table from one format into another, check that the copy holds the same rows, and name what the copy leaves behind.

Lecture outline: 7 sections
  1. What a copy keeps and loses
    • Working rule Before a copy, list what the source holds that the target will not keep: its history, its partition spec and any value finer than the target can store. Give each item one decision, either to accept the loss, to set the property by hand on the target, or to stop the move. The runbook should name every decision.
  2. Copy the pageviews table into DuckLake
    • Run iceberg-to-ducklake-copy.py
    • Working rule Copy through Arrow with the target's own writer. Create the target empty, set its partition spec with an ALTER, and only then insert the rows. Print the target's partition list and its files per partition. Read the copy's snapshots as the copy's own history, and keep the source until the checks in the last two sections have passed.
  3. Copy the Delta table into a pyiceberg table
    • Run delta-to-iceberg-copy.py
    • Working rule Write the target schema yourself, with the types you want, and print both schemas after the copy, as the example does. Accept a near-equal type such as int64 to long only after you have checked it in the output. Do not accept a name that differs.
  4. Check timestamp precision before the copy
    • Run timestamp-precision-check.py
    • Working rule Check timestamp precision with a value below a microsecond before the first copy. Choose the rule in advance. Cast the column to microseconds and count the values the cast changes, reject the copy, or write to a format that keeps nanoseconds. Do not leave the choice to the writer library, because a silent cut gives no error to act on.
  5. Check each copy with a sorted digest
    • Run verify-copies-by-digest.py
    • Working rule Digest both sides after the last write to the source, over the same columns, with typed values. Compare the row counts and the digests together. Do not move readers or delete the source until both match. If writers ran during the copy, repeat the copy and the digest.
  6. What goes wrong in production
  7. Decisions this chapter made

10. Choose a format from what your tools support

About 16 minutes to read, 5 examples.

By the end, students can list every tool that reads or writes your tables, measure what each one supports at its pinned version, and choose a format and its features from those measurements.

Lecture outline: 11 sections
  1. Measure support before you choose
    • Working rule Measure each reader on its own pinned version before you choose a format version or a feature. Write each result down with the version it was measured at. Run the measurement again whenever any reader's version changes, and do not reuse a support table from a vendor page.
  2. Read what the pinned tools did
  3. Writing and reading versions
  4. Changing and cleaning up
  5. Catalogs and DuckDB
    • Working rule Read each cell against the example or source it names. A cell with no example behind it is a claim to test on your own readers, not one to rely on.
  6. pyiceberg writes no version 3 table
    • Run iceberg-format-versions.py
    • Working rule Write format version 2 with pyiceberg at these pins, and do not promise a version 3 feature to any reader. Choose version 3 only when the writer you use creates it and every reader in your set opens it, and check both on their own pins.
  7. Deletion vectors need a protocol upgrade
    • Run feature-check.py
    • Working rule Enable deletion vectors only after you have checked each reader's feature support on its own version. Read the table's protocol back after each change, and keep copy-on-write for any table that a reader outside your tested set will open.
  8. A checklist for a new table
    • Working rule Keep the list with the table's design note, so the next person who adds a reader starts from the same record. A list kept up to date costs less than one rebuilt after a failed read.
  9. Pick a format from your readers
    • Working rule Pick the format that every reader in your measured set opens, then choose only the features that set supports. If no format meets every need, split the table or change the readers. Do not promise a feature to a reader that the measurements do not cover.
  10. What goes wrong in production
    • Working rule Measure every reader on its own pinned version before you write, upgrade or rely on a vendor claim. When you upgrade a writer or a catalog, keep the old release in place until every reader has opened the result.
  11. Decisions this chapter made

Quiz bank

Questions on every chapter, with answer keys, for verified teachers with the Educator plan. Each answer is tied to its section. Ask for the answer keys