Why overwrite-partition was never atomic

Description

This lesson revisits, word for word, the quote from data-engineering-foundations-guide that this guide's module 1 already flagged as a pending promise: the overwrite-partition pattern — DELETE the partition, followed by INSERT of the new data — solves the duplicated-rerun problem, but leaves a real moment, between those two operations, where the partition sits empty. This lesson doesn't repeat that warning in the abstract: it builds, with real PyIceberg, a concurrent reader that tries to catch an Iceberg write "halfway," and reports, with executed evidence, that it never manages to.

Connection to the module. Lesson 1 promised this comparison as the module's starting point. Without this lesson, "an Iceberg write is atomic" would be a brochure claim, the same kind any product documentation makes about itself. With it, it's a claim you ran, with your own eyes, against the exact mechanism lessons 3 through 8 are going to use to evolve kiosko.dim_store's schema.

An analogy: the front desk that opens or updates in a single transaction

Go back to data-engineering-foundations-guide's notebook: tearing out Tuesday's page and writing a new one is better than pasting a sheet on top, but it's still two separate gestures — tearing out, then writing — and between one and the other, the page doesn't exist. Now instead imagine a government office's front desk that updates a record a different way: the clerk prepares the complete new document, on their own, at their desk, with no one else able to see it yet. Only once the new document is completely finished do they swap it for the old one in the filing cabinet — a single move, a single instant, with the cabinet never sitting empty for even a microsecond. Anyone checking the cabinet at any point, even while the clerk is preparing the new document at their desk, still sees the complete old document, right up until the exact instant of the swap — after that instant, they see the new one, complete. They never see an empty cabinet, or a half-written document.

That is, precisely, what an Iceberg write does. The equivalent of "the new document prepared at the desk" is the new metadata file, written complete to disk before anyone needs it. The equivalent of "the swap in the filing cabinet" is a single move of the catalog's pointer, from the old metadata file to the new one. No external reader — not even one querying the table at the exact instant of the write — can see an intermediate state, because that intermediate state is never published: at most, it exists on the clerk's desk, invisible to anyone checking the cabinet.

Worked example: a reader trying to catch an overwrite() in the act

Step 1 — The experiment: a concurrent reader, polling nonstop

This illustration uses its own, disposable catalog and warehouse, so as not to mix the experiment with kiosko.dim_store — that work only starts in lesson 3. The goal is purely to measure: can an external reader, querying the table as fast as possible, capture any state that isn't "before" or "after" an overwrite()?

# atomic_overwrite_demo.py -- isolated illustration, NOT part of Kiosko's model
import os
import shutil
import threading

import pyarrow as pa
from pyiceberg.catalog import load_catalog
from pyiceberg.schema import Schema
from pyiceberg.types import IntegerType, NestedField

demo_warehouse = os.path.abspath("atomic_demo_warehouse")
demo_db = os.path.abspath("atomic_demo_catalog.db")
os.makedirs(demo_warehouse, exist_ok=True)

CATALOG_KWARGS = dict(type="sql", uri=f"sqlite:///{demo_db}", warehouse=f"file://{demo_warehouse}")
writer_catalog = load_catalog("atomic_demo", **CATALOG_KWARGS)
writer_catalog.create_namespace("atomic_demo")

schema = Schema(NestedField(field_id=1, name="n", field_type=IntegerType(), required=True))
table = writer_catalog.create_table("atomic_demo.race_check", schema=schema)
pa_schema = pa.schema([pa.field("n", pa.int32(), nullable=False)])

# the concurrent reader uses the SAME catalog name -- a SqlCatalog's table
# registry is scoped by catalog_name, not just by the sqlite file
reader_catalog = load_catalog("atomic_demo", **CATALOG_KWARGS)

ROUNDS = 25
ROW_COUNTS = [3_000_000, 500]  # two very different sizes: an intermediate state would be unmistakable

table.append(pa.Table.from_pylist([{"n": i} for i in range(ROW_COUNTS[0])], schema=pa_schema))
reader_catalog.load_table("atomic_demo.race_check")  # warms up the reader's connection

all_observed = set()
total_polls = 0

for round_i in range(ROUNDS):
    target_count = ROW_COUNTS[round_i % 2]
    replacement = pa.Table.from_pylist([{"n": i} for i in range(target_count)], schema=pa_schema)

    observed_this_round = []
    stop_polling = threading.Event()

    def poll_reader():
        while not stop_polling.is_set():
            reader_table = reader_catalog.load_table("atomic_demo.race_check")
            observed_this_round.append(reader_table.scan().to_arrow().num_rows)

    poller = threading.Thread(target=poll_reader)
    poller.start()
    table.overwrite(replacement)  # the reader keeps polling WHILE this runs
    stop_polling.set()
    poller.join(timeout=5)

    total_polls += len(observed_this_round)
    all_observed.update(observed_this_round)

print(f"{ROUNDS} rounds of table.overwrite() alternating between {ROW_COUNTS[0]:,} and {ROW_COUNTS[1]} rows")
print(f"Total concurrent reads by the external reader during the writes: {total_polls}")
print(f"COMPLETE set of num_rows values observed across all rounds: {sorted(all_observed)}")
print(f"Table's final state: {table.scan().to_arrow().num_rows} rows")

What to expect (verified by running the actual script; the exact number of reads per round varies with your own machine's speed, but the set of observed values is the part that matters):

25 rounds of table.overwrite() alternating between 3,000,000 and 500 rows
Total concurrent reads by the external reader during the writes: 490
COMPLETE set of num_rows values observed across all rounds: [500, 3000000]
Table's final state: 3000000 rows

Four hundred ninety concurrent reads, while 25 writes replaced the whole table, alternating between three million rows and five hundred. Not one of those 490 reads saw 0. Not one saw any number other than exactly 3000000 or 500 — never a halfway value, never the empty state overwrite()'s internal delete (seen in module 3) does produce as a filed-away snapshot. The external reader, running on another thread, querying the catalog as fast as it can, never manages to see anything other than "the complete state before" or "the complete state after."

Step 2 — Why this isn't luck: a single confirmation to the catalog, never two

The reason isn't that the experiment got lucky 490 times in a row — it's that, by design, there's no intermediate point an external reader can observe. Table.overwrite(), internally, opens a Transaction:

# real pyiceberg 0.11.1 code -- Table.overwrite()
with self.transaction() as tx:
    tx.overwrite(df=df, overwrite_filter=overwrite_filter, ...)

transaction()'s own docstring in PyIceberg 0.11.1 says it plainly: "Create a new transaction object to first stage the changes, and then commit them to the catalog." Notice the two key words: stage, first; commit, later, as a separate, single step. Everything that happens inside the with block — including the delete snapshot and the append snapshot module 3 found inside a single overwrite() — accumulates in memory, never touching the catalog at all. Only once the with block ends does commit_transaction() package all the accumulated changes into a single call to catalog.commit_table(...), which, on this guide's SQL catalog, does exactly this:

-- the real pattern SqlCatalog.commit_table() uses in pyiceberg 0.11.1
UPDATE iceberg_tables
SET metadata_location = :new_path
WHERE catalog_name = :catalog
  AND table_namespace = :namespace
  AND table_name = :table
  AND metadata_location = :expected_old_path

A single UPDATE, with a WHERE condition requiring the old path to still be exactly the one this process saw when it started. Before running that UPDATE, the new metadata file is already complete on disk — it already has both snapshots, delete and append, already written. The UPDATE doesn't build anything: it only moves a pointer, from one already-complete row to another already-complete row, in a single SQL statement the database itself guarantees is atomic. There's never a moment where the catalog points to "halfway" through a change, because the catalog never knew any intermediate state — it only ever knew the before, and then, in a single step, the after.

Diagram: two paths toward "replacing a date's data"

flowchart TB
    subgraph fnd["overwrite-partition -- data-engineering-foundations-guide M6"]
        F1["DELETE FROM staging_demo\nWHERE dt = date"] --> F2["real moment:\nthe partition is empty,\nvisible to any reader"]
        F2 --> F3["INSERT INTO staging_demo\n... new data"]
    end
    subgraph ice["table.overwrite() -- Apache Iceberg, this lesson"]
        I1["Transaction: stage\ndelete snapshot + append snapshot\n(in memory, invisible from outside)"] --> I2["new metadata written COMPLETE\nto disk, not yet referenced"]
        I2 --> I3["A SINGLE atomic UPDATE\nof the catalog's pointer"]
        I3 --> I4["visible from outside:\nonly 'complete before'\nor 'complete after'"]
    end

Going deeper: what Iceberg's official documentation confirms

This lesson doesn't invent the term "atomic" — it takes it straight from Apache Iceberg's official documentation, "Reliability" section: "Commits replace the path of the current table metadata file using an atomic operation. This ensures that all updates to table data and metadata are atomic, and is the basis for serializable isolation." And on what happens when two writes compete for the same instant: "Iceberg supports multiple concurrent writes using optimistic concurrency. Each writer assumes that no other writers are operating and writes out new table metadata for an operation. Then, the writer attempts to commit by atomically swapping the new table metadata file for the existing metadata file. If the atomic swap fails because another writer has committed, the failed writer retries [...]" This module's lesson 7 is going to build, with real code, exactly that scenario of two competing writers — for now, hold onto the central idea: Iceberg's atomicity doesn't depend on nobody else writing at the same time; it depends on the pointer swap always being a single, indivisible operation, whoever wins the race.

It's worth saying this with the same honesty data-engineering-foundations-guide showed about its own pattern: this doesn't make overwrite-partition "wrong" — it's still, today, a valid pattern for simple batch pipelines with no transactional table format underneath. What changes is that, with Iceberg, the guarantee that guide had to name as a cost to accept — "there's a real cost to this decision, and it's worth naming honestly" — stops being a cost: the table format solves it out of the box, with nobody having to wrap a DELETE and an INSERT in a manual transaction.

Common mistakes

Thinking "atomic" means table.overwrite() can never fail. What happens: someone concludes that, because overwrite() is atomic, there's no need to handle any error when calling it. Why it happens: "atomic" sounds, by association, like "infallible" or "safe in every sense." How to spot it: if your code doesn't account for the possibility of table.overwrite() throwing an exception, you haven't internalized the difference yet. How to fix it: atomic means the operation either applies completely, or doesn't apply at all — it can fail entirely (for example, if another writer won the race on UPDATE ... WHERE metadata_location = ..., lesson 7 shows that exact case with a real exception), but it never leaves the table in a partially applied state. Failing-completely is exactly what atomicity guarantees; failing-halfway is what it prevents.

Assuming this guarantee applies across several tables at once, like a relational database transaction. What happens: someone, after seeing this lesson, expects updating kiosko.dim_store and kiosko.fact_orders in the same script to behave like a SQL transaction that can ROLLBACK both tables if one of the two fails. Why it happens: "ACID" is a term most people first learned in the context of a relational database, where it does cover multi-table transactions. How to spot it: if your code depends on an error writing the second of two Iceberg tables automatically undoing what was already written to the first, you're assuming a guarantee Iceberg doesn't give. How to fix it: Iceberg's atomicity is per table, not per multi-table transaction — every commit_table() is its own pointer move, independent of any other table's. This module's lesson 7 precisely states exactly how far this guarantee reaches, and where it ends.

Exercises

Exercise 1 — Reproduce the experiment yourself, with your own numbers. Run this lesson's full script on your machine, then modify it to use ROW_COUNTS = [1_000_000, 50] instead of [3_000_000, 500]. Confirm the set of observed values is still exactly those two numbers, never any other.

See solution

The result should reproduce with any pair of sizes you use: the all_observed set always stays limited to ROW_COUNTS's two values, no matter how many rounds you run or how fast your hardware is. The total number of concurrent reads is going to vary (it depends on your disk and CPU speed), but the conclusion — zero intermediate states observed — is the experiment's deterministic part.

Exercise 2 — Explain why this experiment uses two "very different" row sizes instead of, say, 500 and 501. In 1-2 sentences, justify the design choice of using 3_000_000 and 500 instead of two nearly equal numbers.

See solution

Using two very different numbers makes any intermediate state unmistakable: if the reader ever captured, say, 1_500_000 rows (halfway through the replacement process), it would be obvious that it's neither the "before" nor the "after" state. With 500 and 501, a real intermediate state could, by pure coincidence, match one of the two expected values and go unnoticed, weakening the evidence's strength.

Exercise 3 — Reread data-engineering-foundations-guide's quote and rewrite, in your own words, the central difference with this lesson. Without copying any sentence from this lesson, explain in 2-3 sentences what overwrite-partition and Iceberg's table.overwrite() have in common (the goal: replacing existing data with new data, safely rerunnable), and what sets them apart (where the risk window lives, if it exists at all).

See solution

Both patterns pursue the same goal: replacing a table portion's existing content with new content, safely rerunnable. The difference is where the "swap" happens: overwrite-partition runs the DELETE and the INSERT as two separate SQL statements, so any reader querying the table right between the two sees the partition empty — a real risk window, which the foundations guide itself honestly acknowledged. Iceberg's table.overwrite() prepares the complete result outside the catalog (new metadata, with however many snapshots it needs) and only afterward swaps the catalog's pointer in a single operation — there's never a window where the catalog points to an incomplete state, no matter how many internal steps the write had.

Summary and next step

In this lesson you built, with real PyIceberg, a concurrent reader that tried to catch a table.overwrite() mid-process — 490 reads, during 25 writes that replaced millions of rows with hundreds — and never captured anything other than the complete state before or after. You saw why: Transaction accumulates every change in memory and writes the complete metadata before touching the catalog at all, and the catalog itself confirms the change with a single atomic UPDATE, conditioned on nobody else having gotten there first. And you contrasted this, with the exact quote, against the real risk window data-engineering-foundations-guide acknowledged in its own overwrite-partition.

Before moving on you should be able to: explain, in your own words, why table.overwrite() is atomic even though it can produce more than one snapshot internally; and tell apart Iceberg's per-table atomicity guarantee from a traditional relational database's multi-table transaction.

With a write's atomicity already demonstrated, lesson 3 builds on the same mechanism — the catalog's single pointer swap — for a different operation: table.update_schema(), which is going to add this module's first new column with no existing Parquet file touched at all.

Resources

• Apache Iceberg — official documentation, "Reliability," the source of this lesson's two quotes about commit atomicity and optimistic concurrency. iceberg.apache.org/docs/latest/reliability. In English.
• PyIceberg — API reference and source code, Table.transaction(), Transaction.commit_transaction(), Table._do_commit(), the "stage, then commit" mechanism this lesson quotes literally. py.iceberg.apache.org/api. In English.
• data-engineering-foundations-guide DESIGN doc — source of the overwrite-partition pattern and the exact quote about the risk window, revisited word for word in this lesson. src/guides/data-engineering-foundations-guide/DISENO.md. In Spanish.
• This guide's DESIGN doc — the "Schema evolution" section (M4), the exact source of the comparison between overwrite-partition and an atomic Iceberg write. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.