Lakehouse Table Format — Understanding Apache Iceberg Through Its Metadata

The same delete and MERGE, two ways — rewrite the file, or note the rows to delete

Goal

With the same March orders, create a copy-on-write (COW) table and a merge-on-read (MOR) table, and run the same DELETE and the same MERGE on both. Confirm from the snapshot summaries and the files that COW rewrites the data files containing changed rows as a whole, while MOR only adds position delete files saying "delete row N of file X". Open a position delete file directly, and see whether other engines (DuckDB, pyiceberg) read with those deletes applied.

Why it matters

A Parquet file cannot be edited. To change one row, you either write that file anew (COW) or write down the fact that the row was deleted in another file and filter it out when reading (MOR). COW has cheap reads and expensive writes — even if you fix one row in a million-row file, you rewrite a million rows. MOR has cheap writes and expensive reads — on every read you have to match the delete files against the data files, and it gets slower as delete files pile up. A table that gets small changes often, like CDC, is usually written with MOR and compacted periodically, while a table modified heavily once a day and read a lot is better with COW. Either way, it is decided by three table properties (write.delete.mode, write.update.mode, write.merge.mode), and the basis for the judgment is not a feeling but the bytes written and the file counts left in the summary of every commit.

Steps

With /root/ice/rl/tables.py (app ice-rl-tables), create lake.rl.cow (all three modes copy-on-write) and lake.rl.mor (all three modes merge-on-read) with format-version 2, and load the whole of March into each in one go.
With /root/ice/rl/delete_cow.py (app ice-rl-delete-cow), run DELETE FROM lake.rl.cow WHERE status = 'cancelled'.
With /root/ice/rl/delete_mor.py (app ice-rl-delete-mor), run the same delete on lake.rl.mor.
With /root/ice/rl/merge.py (app ice-rl-merge), MERGE /data/ice/changes.csv (op is U, D, or I) into both tables identically.
With /root/ice/rl/posdel.py, open one position delete file of lake.rl.mor with pyarrow and write it to /root/ice/rl/out/posdel.json.
With /root/ice/rl/cost.py, write the bytes written by the MERGE commits of the two tables (added-files-size) to /root/ice/rl/out/cost.json.
With /root/ice/rl/read.py, read lake.rl.mor with DuckDB and pyiceberg and write the results to /root/ice/rl/out/read.json.
In /root/ice/rl/report.md, write three sections, ## 삭제 두 방식 ## 위치 삭제 파일 ## 쓰기와 읽기의 맞바꿈 (keep these headings as written; they stand for the two ways of deleting, position delete files, and the trade-off between writes and reads).

Notes

The columns of the change set are op, order_id, customer_id, region, amount, status, order_ts. U changes the status to refunded, D deletes, and I is a new order.
You see commit summaries with SELECT operation, summary FROM lake.rl.mor.snapshots and file kinds with SELECT content, file_path, record_count FROM lake.rl.mor.files (content 0 data · 1 position delete · 2 equality delete).
The tables in this lab are format-version 2, so position delete files are written as Parquet. In format-version 3, the same information is written as a deletion vector (Puffin).
Common mistakes: running step 2 on both tables (if the order gets mixed up with step 3, the comparison goes off), and running the MERGE twice. To undo, run DROP TABLE … PURGE on both tables and start again from step 1.
Official docs: Spark Writes — MERGE INTO · Configuration — write.delete.mode · Spec — Row-level Deletes · DuckDB — Iceberg extension

The same data, different write modes

Create /root/ice/rl/tables.py with the app name ice-rl-tables and create lake.rl.cow and lake.rl.mor. Both have six columns and 'format-version' = '2', and set write.delete.mode, write.update.mode, and write.merge.mode all to copy-on-write for cow and all to merge-on-read for mor. Load the 31 files of March into each table once.

The write mode is a table property, so the table remembers it, not the engine. The point is to make whichever engine writes follow the same approach. The grader checks the six properties and the row count of the first commit.

COW delete — rewrite the files

Create /root/ice/rl/delete_cow.py with the app name ice-rl-delete-cow and run DELETE FROM lake.rl.cow WHERE status = 'cancelled'.

The data files that contain cancelled orders are replaced by new files without the cancellations. That is the deleted-data-files and added-data-files in the summary, and there is not a single delete file. The grader looks at the summary of cow's second commit and checks that no cancelled orders remain at that point.

MOR delete — write down the rows to delete

Create /root/ice/rl/delete_mor.py with the app name ice-rl-delete-mor and run DELETE FROM lake.rl.mor WHERE status = 'cancelled'.

It leaves the data files as they are and adds a position delete file that collects the (file path, row number) of the rows to delete. added-position-delete-files appears in the summary and there is no deleted-data-files. The grader looks at the summary of mor's second commit and at the content 1 files in the manifests.

MERGE — update, delete, and add

Create /root/ice/rl/merge.py with the app name ice-rl-merge, read /data/ice/changes.csv as a temporary view, and in each of the two tables use MERGE INTO … ON t.order_id = s.order_id to DELETE for op D, UPDATE status and amount for op U, and INSERT for op I that is not in the table.

If two changes hit one source row, MERGE fails (this set has only one change per order). U and D for cancelled orders that were already deleted have no match and do nothing. The grader compares the contents of the two tables with the expected values computed from the source and the change set, and checks that only mor has position delete files.

Open a position delete file

With /root/ice/rl/posdel.py, open one file whose content is 1 in the current snapshot of lake.rl.mor with pyarrow.parquet.read_table, and write {"delete_file": 경로, "rows": 행 수, "targets": [그 파일이 가리키는 데이터 파일 경로, …]} to /root/ice/rl/out/posdel.json (the placeholders in the code stand for the path, the row count, and the data file paths that file points to).

A position delete file is a Parquet with two columns, file_path and pos. One row means "the row at position pos of this data file does not exist". The grader re-reads the row count and the target list you wrote from the file and compares them, and checks that the targets are data files that are alive now.

Bytes written by one MERGE

With /root/ice/rl/cost.py, read added-files-size from the summary of the current snapshot (= the MERGE commit) of the two tables and write it to /root/ice/rl/out/cost.json as {"cow_added_bytes": 정수, "mor_added_bytes": 정수} (the placeholders in the code stand for integers).

COW rewrites all the data files the changes touched, and MOR writes only the new or changed rows and the delete files. The difference in bytes written for the same 1,400 changes is the write amplification. The grader compares the two values with the summaries and checks that the COW side is larger.

Do other engines read with the deletes applied?

With /root/ice/rl/read.py, put the current metadata path of lake.rl.mor into iceberg_scan() and count the rows and sum(amount) with DuckDB and the rows with pyiceberg, and write them to /root/ice/rl/out/read.json as {"duckdb_rows", "duckdb_amount", "pyiceberg_rows"}.

A MOR table gives correct results only if the reader applies the delete files. An engine that does not know delete files returns even the deleted rows — this is something you must check whenever several engines read the same table. The iceberg extension of DuckDB was put into the image at build time (LOAD iceberg). The grader compares the three values with the expected values.

Which table in which mode

In /root/ice/rl/report.md, write three sections, ## 삭제 두 방식 ## 위치 삭제 파일 ## 쓰기와 읽기의 맞바꿈 (the two ways of deleting, position delete files, and the trade-off between writes and reads). In the third section, put the two byte values from step 6 as numbers.

Think of two tables in your own team: if you decided one should be COW and one MOR, what would you base it on? If you pick MOR, also write who will clear the piled-up delete files and when.