---
title: "Open Table Formats"
chapter: "Correct rows with deletes, updates and merges"
chapter_id: corrections-and-merges
book: open-table-formats
edition: 2026.11.10
verified: 2026-11-20
edition_code: 27Y3-9R88
canonical: https://rollingedition.com/open-table-formats/corrections-and-merges
license: CC BY-NC 4.0
more_permissions: https://rollingedition.com/policies/reuse
attribution: "“Correct rows with deletes, updates and merges,” chapter 5 of Open Table Formats, Edition 2026.11.10, examples passed Nov 20, 2026. By Rolling Edition, https://rollingedition.com/open-table-formats/corrections-and-merges. Text CC BY-NC 4.0 (https://creativecommons.org/licenses/by-nc/4.0/), with added permissions (https://rollingedition.com/policies/reuse). Code MIT, copyright Rolling Edition and contributors. Written with AI. Verified by people."
---
# Correct rows with deletes, updates and merges

Rows already committed can be changed too: this chapter deletes, upserts and merges them in Iceberg, Delta Lake and DuckLake, says what each write does to the files, and counts the snapshots a pyiceberg upsert adds.

So far the table only grows. Every write in [chapter 1](#append-each-hour) appended to `wiki.pageviews_hourly`, a table the source sometimes redelivers an hour into.

## Late data arrives as a correction {#late-data-as-correction}

The first delivery of hour 02 reached the table in [chapter 1](#append-each-hour) with the counts the source sent at the time. A later delivery of the same hour carries corrected counts for some pages, and one page the first delivery left out. The pages in the correction are the same pages as before, so the table needs to change rows in place. Appending the corrected hour would store every page twice, once with each count.

In this book, a row is identified by its key: `hour_utc`, `domain_code` and `page_title` together. A correction is a set of rows with keys, and for each key the table either already holds a row, which should change, or does not, which should be added. Writing a correction is therefore a question about keys, and the write answers it with one rule: a key must identify one row in the batch you write. pyiceberg and DuckLake both enforce that rule. This chapter does not test a repeated key in a deltalake merge.

A correction usually arrives as the whole hour, re-delivered, and not as a few rows. Sending the whole hour keeps the batch self-contained, because the write decides row by row what changed. The cost is that every row of the hour is compared, not only the corrected ones. Because every row is compared, pyiceberg can count only the rows whose values differ, and a rerun of the same batch changes nothing.

Corrections here are built in code, from the real cut of [chapter 1](#land-three-hours). The helper `hour_corrections` halves the counts of 20 page titles in hour 02, rounding down. The late row is a copy of the hour's first row, with a page title that the first delivery never had. Only the correction batch is generated; the pageviews rows are the ones chapter 1 cut.

**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](#corrections-production-failures) shows the failures.

## Delete rows, and what each format writes {#delete-rows-iceberg-and-delta}

A delete removes the rows that match a predicate. The rows go away in every format, but the files that held them can be handled in two ways. In copy-on-write, the writer rewrites each data file that held a matching row and leaves out the deleted rows. In merge-on-read, the writer leaves the data file alone and writes a delete file that records which rows are gone. Readers apply the delete file when they scan.

The two libraries take the predicate in different forms. pyiceberg takes an expression object, built here from `EqualTo` and `LessThan`. deltalake takes a SQL string, which reads like the `WHERE` clause a reader already knows. The delete below uses both forms on the same rows, so you can compare them directly.

The delete removes the hour 02 rows with fewer than 180 views, a threshold this chapter chose. The cut in [chapter 1](#land-three-hours) keeps only the most-viewed rows in each hour, so a predicate for fewer than two views matches nothing. pyiceberg then warns with `UserWarning: Delete operation did not match any records` and writes no snapshot, as its [source](https://raw.githubusercontent.com/apache/iceberg-python/pyiceberg-0.12.0/pyiceberg/table/__init__.py) shows. A threshold of 180 catches a few of the least-viewed rows in hour 02, which is enough to show what a delete writes.

Example `delete-low-views` (Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0), passing in Edition 2026.11.10:

```python
"""Runs one delete through Iceberg, Delta Lake and DuckLake and prints what each format removed and rewrote. The data-file lines carry the point; the deleted-row lines beside them are for comparison."""

from datetime import datetime

from deltalake import DeltaTable
from pyiceberg.expressions import And, EqualTo, LessThan

from lib.lakehouse import DUCKLAKE_ALIAS, NAMESPACE, TABLE, ducklake_file_count, load_three_formats, new_warehouse, read_delta, safe_error, scan_arrow

HOUR_02 = datetime(2024, 6, 3, 2)
# A threshold, not a fact about the data: the cut keeps the most-viewed rows of each hour, so a small one would match nothing.
LOW_VIEWS = 180


def main():
    with new_warehouse() as wh:
        iceberg, delta_path, lake = load_three_formats(wh, range(6))  # hours 00 to 05, one commit per hour in each format
        iceberg_before = scan_arrow(iceberg).num_rows
        delta_before = read_delta(delta_path).num_rows
        table = f"{DUCKLAKE_ALIAS}.{NAMESPACE}.{TABLE}"
        lake_before = lake.execute(f"SELECT count(*) FROM {table}").fetchall()[0][0]
        files_before = ducklake_file_count(lake, DUCKLAKE_ALIAS, TABLE)  # counted before the delete, to compare with the count after

        iceberg.delete(And(EqualTo("hour_utc", HOUR_02), LessThan("views", LOW_VIEWS)))
        summary = iceberg.current_snapshot().summary  # the snapshot summary counts each data file the delete removed from the table
        print("Iceberg deleted rows:", iceberg_before - scan_arrow(iceberg).num_rows)
        print("Iceberg remaining rows:", scan_arrow(iceberg).num_rows)
        print("Iceberg data files rewritten:", summary["deleted-data-files"])

        delta = DeltaTable(delta_path)
        # the hour is written as a timestamp string inside the SQL predicate
        metrics = delta.delete(f"hour_utc = '2024-06-03T02:00:00' AND views < {LOW_VIEWS}")
        print("Delta Lake deleted rows:", delta_before - read_delta(delta_path).num_rows)
        print("Delta Lake remaining rows:", read_delta(delta_path).num_rows)
        print("Delta Lake data files rewritten:", metrics["num_removed_files"])

        lake.execute(f"DELETE FROM {table} WHERE hour_utc = TIMESTAMP '2024-06-03 02:00:00' AND views < {LOW_VIEWS}")
        lake_after = lake.execute(f"SELECT count(*) FROM {table}").fetchall()[0][0]
        print("DuckLake deleted rows:", lake_before - lake_after)
        print("DuckLake remaining rows:", lake_after)
        print("DuckLake data files before:", files_before)
        print("DuckLake data files after:", ducklake_file_count(lake, DUCKLAKE_ALIAS, TABLE))
        # a delete file is listed against the data file it applies to, so counting the listed rows that have one counts the delete files
        print("DuckLake delete files written:", lake.execute(
            f"SELECT count(*) FROM ducklake_list_files('{DUCKLAKE_ALIAS}', '{TABLE}', schema => '{NAMESPACE}') "
            "WHERE delete_file IS NOT NULL").fetchall()[0][0])


if __name__ == "__main__":
    try:
        main()
    except Exception as exc:  # the redacted error is what a reader sees
        print(safe_error(exc))
        raise SystemExit(1)
```

Output:

```text
Iceberg deleted rows: 4
Iceberg remaining rows: 2995
Iceberg data files rewritten: 1
Delta Lake deleted rows: 4
Delta Lake remaining rows: 2995
Delta Lake data files rewritten: 1
DuckLake deleted rows: 4
DuckLake remaining rows: 2995
DuckLake data files before: 6
DuckLake data files after: 6
DuckLake delete files written: 1
```

All three formats delete the same number of rows: 4 in Iceberg, 4 in Delta Lake and 4 in DuckLake, but the files they rewrite differ.

Iceberg rewrote 1 data file, the one that held hour 02, and leaves 2995 rows. Delta Lake removes the same rows, leaves 2995, and rewrote 1 file. Both formats rewrote the file for hour 02 and left the other five hours alone, because a file is the unit a copy-on-write delete replaces.

DuckLake's `DELETE` leaves 2995 rows and rewrites no data file. Its data files stay at 6 before the delete and 6 after it, and the delete writes 1 delete file.

The cost of the Iceberg and Delta Lake deletes follows the size of the files they touch, not the number of rows they remove. 4 rows went, and 1 whole file was rewritten to remove them. A table whose matches are spread across many files pays for each of those files. So the cost depends on where the matching rows sit.

Each library reports its own counts, and this example computes the deleted rows as the rows before minus the rows after, so the deleted and remaining counts agree by construction. The rewritten-file counts come from the library: the Iceberg snapshot summary's `deleted-data-files` and the `num_removed_files` metric that deltalake returns from `delete`.

### Copy-on-write and merge-on-read {#copy-on-write-and-merge-on-read}

The two write styles trade cost between the writer and the reader. Copy-on-write makes the write expensive and the read cheap: a reader gets the rows as the files hold them, with nothing to apply. Merge-on-read makes the write cheap, because it writes only the positions of the deleted rows, and it moves the cost to reads until something rewrites the data files.

The three formats in this chapter differ as follows.

| Format | Library at the pins | How the library writes a delete at the pins | Shown in this chapter |
| --- | --- | --- | --- |
| Iceberg | pyiceberg 0.12.0 | Copy-on-write. `delete` rewrites the data files that contain matching rows and writes no delete files. With `write.delete.mode` set to merge-on-read, pyiceberg warns "Merge on read is not yet supported, falling back to copy-on-write" ([source](https://raw.githubusercontent.com/apache/iceberg-python/pyiceberg-0.12.0/pyiceberg/table/__init__.py)). | Yes, in the delete example |
| Delta Lake | deltalake 1.6.6 | Copy-on-write. The file with matching rows is rewritten, and the log records the old file as removed. | Yes, in the delete example |
| DuckLake | DuckDB 2.0.0 | Merge-on-read. `DELETE` writes a delete file and leaves the data file in place. | Yes, in the delete example |

The DuckLake row is shown by the delete example, which counts the delete file and the data files that stay in place. The [DuckLake documentation on deletion vectors](https://ducklake.select/docs/stable/duckdb/advanced_features/deletion_vectors) describes an experimental option that records deleted row positions in `.puffin` files. That option is off by default, and this chapter does not turn it on. The cost of reading through delete files is not measured here. A reader of a merge-on-read table pays that cost on every scan until the data files are rewritten, so measure it on the reader's own table.

Delta Lake also has deletion vectors, which record deleted row positions in a file. That makes them Delta's merge-on-read option, not a third write style, and this book does not use them. At the pins, deltalake 1.6.6 does not write a deletion vector: `delete` on a table with the feature enabled still rewrites the file. The [Delta protocol](https://raw.githubusercontent.com/delta-io/delta/v4.4.1/PROTOCOL.md) requires the deletion-vectors feature in a table's reader and writer features when the table uses deletion vectors, and enabling the feature adds it to both. `QueryBuilder`, which this book uses for every Delta read, reads such a table. `to_pyarrow_table` and `to_pyarrow_dataset` refuse it, because the table declares the feature. So this book keeps the feature off, and its Delta deletes are copy-on-write.

**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.

## Upsert a corrected hour {#upsert-corrected-hour}

An upsert updates the row whose [key](#late-data-as-correction) matches and inserts the row whose key does not. It is the right write for a correction, because the corrected hour holds the keys that the table already has, and the late row holds a key the table does not have yet. A plain append would store the corrected rows next to the originals, so every page in the hour would appear twice.

The next two subsections show pyiceberg's method and DuckLake's SQL `MERGE` with the same batch.

The key finds the row that a change applies to, and it decides when two incoming rows are the same row. Too narrow a key collapses two real rows into one, and too wide a key inserts a second copy. The key here is `hour_utc`, `domain_code` and `page_title`, the identity the cut gives each row in [chapter 1](#land-three-hours). The batches in the examples hold each key once.

### Upserts in pyiceberg {#upserts-in-pyiceberg}

pyiceberg's `upsert` takes an Arrow table of rows and the columns that form the key, passed as `join_cols`. It returns the count of rows it updated and the count it inserted. A row counts as updated only when a non-key column changed, so a row that is identical to the table is not counted as updated. Copy-on-write still rewrites such a row when its file also holds a changed row.

Send the corrected hour and the late row through one `upsert`, and count what the call leaves in the table's history.

Example `redeliver-hour-upsert` (Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0), passing in Edition 2026.11.10:

```python
"""Upserts the corrected hour 02, with its late row, into the Iceberg table, then upserts the same batch again. The snapshot lines carry the point, in the order the commits were made."""

import pyarrow as pa

from lib.lakehouse import KEY, hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error, snapshot_labels


def main():
    with new_warehouse() as wh:
        iceberg, _, _ = load_three_formats(wh, range(6))
        before = {snapshot.snapshot_id for snapshot in iceberg.snapshots()}  # so the loop below lists only the snapshots this upsert adds
        # hour_corrections halves the views of the first 20 page titles in byte order, so the same titles change on every run
        corrected = hour_corrections(read_cut_hour(2))
        # A late row: a page the first delivery of hour 02 did not have.
        late = corrected.slice(0, 1).set_column(
            corrected.schema.get_field_index("page_title"),
            "page_title",
            pa.array(["Late_arrival_example"], type=pa.string()),
        )
        batch = pa.concat_tables([corrected, late])

        result = iceberg.upsert(batch, join_cols=list(KEY))  # KEY is the three key columns; a row matches when all three are equal
        # rows_updated counts only rows whose non-key values changed, so identical rows are not counted
        print("rows updated:", result.rows_updated)
        print("rows inserted:", result.rows_inserted)

        iceberg.refresh()
        labels = snapshot_labels([snapshot.snapshot_id for snapshot in iceberg.snapshots()])  # snapshot IDs change on every run, so the labels number commits
        added = [snapshot for snapshot in iceberg.snapshots() if snapshot.snapshot_id not in before]
        for snapshot in added:
            label = labels[snapshot.snapshot_id]
            print(label, snapshot.summary.operation.value)
            # a snapshot that adds or deletes no records has no such key, so the count is 0
            print(f"{label} records added:", snapshot.summary.get("added-records") or 0)
            print(f"{label} records deleted:", snapshot.summary.get("deleted-records") or 0)
        print("snapshots added:", len(added))

        again = iceberg.upsert(batch, join_cols=list(KEY))  # the same batch again, as a retry would send it
        print("rows updated on second upsert:", again.rows_updated)
        print("rows inserted on second upsert:", again.rows_inserted)


if __name__ == "__main__":
    try:
        main()
    except Exception as exc:  # the redacted error is what a reader sees
        print(safe_error(exc))
        raise SystemExit(1)
```

Output:

```text
rows updated: 20
rows inserted: 1
snapshot 7 overwrite
snapshot 7 records added: 480
snapshot 7 records deleted: 500
snapshot 8 append
snapshot 8 records added: 20
snapshot 8 records deleted: 0
snapshot 9 append
snapshot 9 records added: 1
snapshot 9 records deleted: 0
snapshots added: 3
rows updated on second upsert: 0
rows inserted on second upsert: 0
```

One `upsert` call is not one commit: this one wrote 3 snapshots to update 20 rows and insert 1. The snapshot list shows what each one recorded.

The first snapshot is an overwrite. It deletes 500 rows, the rows of the hour-02 file, and adds back 480 of them unchanged. The next two are appends. The second adds 20 changed rows, and the third adds 1 row, the late row. The labels come from `snapshot_labels`, so the numbers are the commit order.

In the [pyiceberg 0.12.0 source](https://raw.githubusercontent.com/apache/iceberg-python/pyiceberg-0.12.0/pyiceberg/table/__init__.py), `upsert` calls `overwrite` only when some rows changed, and `append` for the new rows only when there are some. `overwrite` writes two snapshots, a delete and an append. The library records the first as `overwrite`, because it also writes rows back. So a batch with changed and new rows adds three snapshots, one with only changed rows adds two, and one with only new rows adds one. Count them in the snapshot list, not from the library's call.

Running the same upsert again changes nothing: it reports 0 updated rows and 0 inserted. The second run finds every key already at its corrected value, so a correction sent twice does not change the table twice, which is what makes a retry safe.

**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.

### MERGE in DuckLake {#merge-in-ducklake}

DuckLake takes the same correction as a `MERGE` statement in SQL. A `MERGE` statement names the target table, the source batch and the key in its `ON` clause, and each `WHEN` clause says what to do with a matched or an unmatched row. The statement below is the one the example after it runs.

```sql
MERGE INTO lake.wiki.pageviews_hourly AS t
USING batch AS s
ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title
WHEN MATCHED AND t.views <> s.views THEN UPDATE SET views = s.views
WHEN NOT MATCHED THEN INSERT (hour_utc, domain_code, page_title, views, response_bytes)
    VALUES (s.hour_utc, s.domain_code, s.page_title, s.views, s.response_bytes)
```

The `WHEN NOT MATCHED` branch also inserts `response_bytes`, which is 0 in every row of the cut, so the late row's value is 0 too.

The `AND t.views <> s.views` condition matters. Without it, the `MERGE` would update and count every matched row, so a rerun would count them all again. With it, a matched row whose counts already agree is left alone and not counted. No example runs the statement without the condition, so the claim about a rerun without it is a reading of the statement, not a measured count.

Example `merge-correction-ducklake` (Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0), passing in Edition 2026.11.10:

```python
"""Merges the corrected hour 02 and its late row into the DuckLake copy with a SQL MERGE, then runs the same MERGE again. The matched-row line splits the changed count into inserts and updates; the second changed-row line is the rerun."""

import pyarrow as pa

from lib.lakehouse import hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error

# The WHEN MATCHED condition is what keeps a rerun from counting matched rows that already agree.
MERGE = """
MERGE INTO lake.wiki.pageviews_hourly AS t
USING batch AS s
ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title
WHEN MATCHED AND t.views <> s.views THEN UPDATE SET views = s.views
WHEN NOT MATCHED THEN INSERT (hour_utc, domain_code, page_title, views, response_bytes)
    VALUES (s.hour_utc, s.domain_code, s.page_title, s.views, s.response_bytes)
"""


def main():
    with new_warehouse() as wh:
        _, _, lake = load_three_formats(wh, range(6))
        corrected = hour_corrections(read_cut_hour(2))
        # a copy of the hour's first row, with a page title the table never held
        late = corrected.slice(0, 1).set_column(
            corrected.schema.get_field_index("page_title"),
            "page_title",
            pa.array(["Late_arrival_example"], type=pa.string()),
        )
        lake.register("batch", pa.concat_tables([corrected, late]))
        print("source rows:", lake.execute("SELECT count(*) FROM batch").fetchall()[0][0])
        # the join counts source rows whose key the table already holds, which splits the changes into inserts and updates
        matched = lake.execute(
            "SELECT count(*) FROM lake.wiki.pageviews_hourly AS t JOIN batch AS s "
            "ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title"
        ).fetchall()[0][0]
        print("source rows matched:", matched)
        # DuckDB returns one count for a MERGE, with updates and inserts added together
        print("rows changed by the merge:", lake.execute(MERGE).fetchall()[0][0])
        print("rows changed by the same merge again:", lake.execute(MERGE).fetchall()[0][0])
        print("rows in table:", lake.execute("SELECT count(*) FROM lake.wiki.pageviews_hourly").fetchall()[0][0])


if __name__ == "__main__":
    try:
        main()
    except Exception as exc:  # the redacted error is what a reader sees
        print(safe_error(exc))
        raise SystemExit(1)
```

Output:

```text
source rows: 501
source rows matched: 500
rows changed by the merge: 21
rows changed by the same merge again: 0
rows in table: 3000
```

Like the upsert, the merge is safe to rerun: it changes 21 rows the first time and 0 the second, and the table ends with 3000 rows, the six hours and the late row.

The changed count adds the two kinds of change together, so the split needs the source size. Of the 501 rows in the batch, 500 match a key already in the table, so the only row without a match is the late row. The rows inserted are the source rows minus the matched rows, here 501 minus 500. The rows updated are the changed rows minus the rows inserted. A count of matched rows alone gives neither number.

DuckLake is attached with inlining off in these examples, as [chapter 1](#files-metadata-and-commit) explains, so each load writes data files rather than rows in the catalog. The merge's counts are of rows.

**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](#corrections-production-failures) shows the error.

## Merge a changed label into the domains table {#merge-domains-table}

The second table in this book, `wiki.domains`, maps each domain code to a language. A dimension table holds labels, not counts, and a merge suits a few labels that change at once. The point is that the same statement shape works on a dimension table, and the [rule about conditions](#merge-in-ducklake) applies there too.

An `UPDATE` would change the label. The `MERGE` shape also has a `WHEN NOT MATCHED` clause, which inserts a domain code the table does not hold yet, so a feed that adds a domain can use the same statement. This table lives only in DuckLake, so the merge runs there. The pageviews table is merged in DuckLake in [MERGE in DuckLake](#merge-in-ducklake). In a pipeline, the label you merge here is the label that [chapter 8's join](#join-tables-from-two-catalogs) returns.

Example `merge-domains-label` (Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0), passing in Edition 2026.11.10:

```python
"""Merges one changed language label into the DuckLake copy of the domains table, then runs the same MERGE again. Read the second changed-row line against the sorted table, which shows the label that merged in."""

import pyarrow as pa

from lib.lakehouse import attach_ducklake, connect_ducklake, print_rows, read_domains, new_warehouse, safe_error

MERGE = """
MERGE INTO lake.wiki.domains AS t
USING labels AS s
ON t.domain_code = s.domain_code
WHEN MATCHED AND t.language <> s.language THEN UPDATE SET language = s.language
WHEN NOT MATCHED THEN INSERT (domain_code, language) VALUES (s.domain_code, s.language)
"""


def main():
    with new_warehouse() as wh:
        lake = connect_ducklake(wh.con)
        attach_ducklake(lake, "lake", wh.root, inlining_row_limit=0)  # inlining off, so the rows land in data files
        lake.execute("CREATE SCHEMA lake.wiki")
        lake.execute("CREATE TABLE lake.wiki.domains (domain_code VARCHAR, language VARCHAR)")
        lake.register("dims", read_domains())
        lake.execute("INSERT INTO lake.wiki.domains SELECT * FROM dims")

        # 'fr' is already in the table, so this label is an update and not an insert
        lake.register("labels", pa.table({"domain_code": ["fr"], "language": ["French (France)"]}))
        print("rows changed by the merge:", lake.execute(MERGE).fetchall()[0][0])
        print("rows changed by the same merge again:", lake.execute(MERGE).fetchall()[0][0])
        print_rows(lake.execute("SELECT domain_code, language FROM lake.wiki.domains").fetchall())  # sorted, so the order DuckLake returns rows in does not matter


if __name__ == "__main__":
    try:
        main()
    except Exception as exc:  # the redacted error is what a reader sees
        print(safe_error(exc))
        raise SystemExit(1)
```

Output:

```text
rows changed by the merge: 1
rows changed by the same merge again: 0
de	German
en	English
fr	French (France)
ja	Japanese
```

Merging one label, `fr` from `French` to `French (France)`, changes 1 row, and a second run of the same merge changes 0. The table starts with the four rows from the book's own data, and the sorted table at the end of the output shows each domain with its label.

**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.

## What goes wrong in production {#corrections-production-failures}

Corrections go wrong in practice through a repeated key, a loop of single-row deletes and a snapshot count that surprises whoever reads the history. Each one is cheap to check before the write and expensive to find afterwards, so the checks belong in the job that writes the batch.

**A repeated key in the batch.** A correction sent twice, or two rows with the same key from a bad join, makes the batch invalid. This example sends one corrected row twice, and pyiceberg refuses the upsert before it writes anything.

Example `duplicate-source-keys` (Python 3.12.15 · deltalake 1.6.6 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0), passing in Edition 2026.11.10:

```python
"""Sends a batch with one key twice to an Iceberg upsert and to a DuckLake MERGE, and prints each refusal with the row count after it. The two error lines carry the point: each names the cause of its refusal."""

import pyarrow as pa

from lib.lakehouse import KEY, hour_corrections, load_three_formats, new_warehouse, read_cut_hour, safe_error, scan_arrow

MERGE = """
MERGE INTO lake.wiki.pageviews_hourly AS t
USING batch AS s
ON t.hour_utc = s.hour_utc AND t.domain_code = s.domain_code AND t.page_title = s.page_title
WHEN MATCHED AND t.views <> s.views THEN UPDATE SET views = s.views
WHEN NOT MATCHED THEN INSERT (hour_utc, domain_code, page_title, views, response_bytes)
    VALUES (s.hour_utc, s.domain_code, s.page_title, s.views, s.response_bytes)
"""


def main():
    with new_warehouse() as wh:
        iceberg, _, lake = load_three_formats(wh, range(6))
        corrected = hour_corrections(read_cut_hour(2))
        # A corrected row, sent twice: the batch holds two rows with one key.
        before = read_cut_hour(2)["views"].to_pylist()  # the cut's views, to find the rows the correction changed
        changed = [i for i, after in enumerate(corrected["views"].to_pylist()) if after != before[i]]
        doubled = pa.concat_tables([corrected, corrected.slice(changed[0], 1)])

        try:
            iceberg.upsert(doubled, join_cols=list(KEY))
            print("Iceberg upsert: no error")
        except Exception as exc:
            print("Iceberg upsert:", safe_error(exc, wh))  # safe_error replaces the warehouse path, so no local path is printed
        print("Iceberg rows after the failed upsert:", scan_arrow(iceberg).num_rows)

        lake.register("batch", doubled)
        try:
            lake.execute(MERGE)
            print("DuckLake merge: no error")
        except Exception as exc:
            print("DuckLake merge:", safe_error(exc, wh))
        print("DuckLake rows after the failed merge:", lake.execute("SELECT count(*) FROM lake.wiki.pageviews_hourly").fetchall()[0][0])


if __name__ == "__main__":
    try:
        main()
    except Exception as exc:  # the redacted error is what a reader sees
        print(safe_error(exc))
        raise SystemExit(1)
```

Output:

```text
Iceberg upsert: ValueError: Duplicate rows found in source dataset based on the key columns. No upsert executed
Iceberg rows after the failed upsert: 2999
DuckLake merge: InvalidInputException: Invalid Input Error: MERGE INTO command cannot affect the same target row more than once. A target row matched more than one source row; ensure the source rows are deduplicated or the ON condition is unique.
DuckLake rows after the failed merge: 2999
```

Both refusals leave the table with every row it held before the write. The Iceberg upsert raises a `ValueError`, and the table still holds 2999 rows. The DuckLake merge raises an `InvalidInputException` that names the cause, a target row that matched more than one source row, and the table still holds 2999 rows. A pipeline that catches the error and retries the same batch fails again, because the batch is the problem. Deduplicate the source by key before the write. Delta Lake's merge with a repeated key is not tested here ([see the first section](#late-data-as-correction)).

**A loop of single-row deletes.** Copy-on-write makes each delete a commit that rewrites the files that hold its matches. Ten deletes in a loop rewrite the file that holds their matches ten times and leave ten snapshots or versions behind. The cost model, not a measurement here, is what suggests it. Collect the rows to delete, then write one delete with a predicate that covers them.

**A snapshot count that surprises the reader.** A single upsert with changed rows and new rows adds 3 snapshots in the [run above](#upserts-in-pyiceberg). A reader who expects one commit per change will find the history longer than the change log. The snapshots stay in the table's metadata until they are expired, which [chapter 7](#snapshots-keep-files-alive) covers, and each one keeps the files it refers to.

**How you'd notice.** Each of these shows up in a number you already log. The rows-updated and rows-inserted counts from pyiceberg's result, and the changed-rows count from a merge, should match the batch you sent. A rerun that reports changes means the batch differs from what you think it is. A snapshot count that grows faster than the snapshots you expect per load (up to three for an upsert) points at a non-idempotent retry, such as an append sent twice. The refusals show up as exceptions, so a job that catches them and continues will look healthy while its table stays behind.

**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.

## Decisions this chapter made {#corrections-decisions}

- Corrections are matched on the key `hour_utc`, `domain_code` and `page_title`, and a batch must hold each key once before it is written.
- The delete example uses a threshold of 180 views, because the cut keeps only the most-viewed rows in each hour, so a threshold of two matches nothing.
- Iceberg and Delta Lake deletes are copy-on-write, and their examples show the files they rewrite. DuckLake `DELETE` writes a delete file and leaves the data files in place, and the same example shows that.
- Deletion vectors are not used. At the pins, deltalake 1.6.6 does not write them, and enabling the feature makes `to_pyarrow_table` and `to_pyarrow_dataset` refuse the table.
- Upserts and merges are checked by running them twice: the second run changes no rows.
- Snapshot counts are read from the snapshot list. The chapter does not treat one call as one commit.
- The DuckLake examples turn inlining off, as [chapter 1](#what-a-table-format-adds) explains, so the counts describe data files.
- The corrections are built in code, and the late row is generated, so what these examples show is the write path, not the history of a real correction.
- The duplicate-key example shows the refusal itself, not a description of it, so the failure is in the output a reader sees.
- The chapter has no Delta Lake upsert or merge example, so it makes no claim about deltalake's merge beyond the delete.
- The late row and the correction share one batch, so one call shows both an update and an insert.
