Changing P002 with a plain overwrite

Description

This is the lesson where the change happens. Since lesson 2, kiosko.dim_product has had one row per product with the V1 values — P002 at category='snacks', unit_cost=0.60. This lesson overwrites the whole table with the V2 values: the same four products, but P002 now at category='health-snacks', unit_cost=0.68. The tool is table.overwrite() — a normal Iceberg operation, with no special "history mode" parameter. And you're going to discover, with real evidence, that "one overwrite" doesn't always mean "one snapshot."

Connection to the module. Lesson 2 left snap_v1 captured: the photo of dim_product with P002 still in its original state. This lesson is the second half of the experiment — the write that makes the table's current state change. Without this lesson, there would be no "before" and "after" to travel between; with it, lessons 4 through 6 have something real to recover.

An analogy: fully restocking the shelf

Back to the supermarket clerk. Restocking the shelf doesn't mean adding new merchandise next to the old — that would be append(), and it would produce a shelf with twice as many products, most of them duplicates. Restocking means pulling out everything that was there and putting the new merchandise in its place: the shelf, after restocking, has the same number of slots as before, but with updated content. This lesson does exactly that with kiosko.dim_product: it pulls out V1's four rows and puts V2's four rows in their place — the same number of products, one of them with different data.

Worked example: the overwrite, and what table.history() reveals

Step 1 — The V2 values: P002 changes to health-snacks/0.68

# overwrite_v2.py -- kiosko.dim_product, the P002 change
import os

import pyarrow as pa
from pyiceberg.catalog import load_catalog

warehouse_path = os.path.abspath("kiosko_warehouse")
catalog_db_path = os.path.abspath("kiosko_catalog.db")

catalog = load_catalog(
    "kiosko", type="sql",
    uri=f"sqlite:///{catalog_db_path}", warehouse=f"file://{warehouse_path}",
)
table = catalog.load_table("kiosko.dim_product")

dim_product_pa_schema = pa.schema([
    pa.field("product_id", pa.string(), nullable=False),
    pa.field("product_name", pa.string(), nullable=False),
    pa.field("category", pa.string(), nullable=False),
    pa.field("unit_cost", pa.float64(), nullable=False),
])

# V2 -- effective since 2026-08-15; the only change is P002. P001/P003/P004 identical to V1
DIM_PRODUCT_V2 = [
    {"product_id": "P001", "product_name": "Bottled Water 600ml", "category": "beverages", "unit_cost": 0.40},
    {"product_id": "P002", "product_name": "Energy Bar", "category": "health-snacks", "unit_cost": 0.68},
    {"product_id": "P003", "product_name": "Instant Coffee Sachet", "category": "beverages", "unit_cost": 0.35},
    {"product_id": "P004", "product_name": "Phone Charger Cable", "category": "electronics", "unit_cost": 2.10},
]

pa_table_v2 = pa.Table.from_pylist(DIM_PRODUCT_V2, schema=dim_product_pa_schema)
table.overwrite(pa_table_v2)

print("table.scan().to_arrow() after the overwrite:")
for row in table.scan().to_arrow().to_pylist():
    print(f"  {row['product_id']}  {row['product_name']:<22} {row['category']:<14} unit_cost={row['unit_cost']}")

What to expect (verified by running the actual script, against the table lesson 2 left):

table.scan().to_arrow() after the overwrite:
  P001  Bottled Water 600ml    beverages      unit_cost=0.4
  P002  Energy Bar             health-snacks  unit_cost=0.68
  P003  Instant Coffee Sachet  beverages      unit_cost=0.35
  P004  Phone Charger Cable    electronics    unit_cost=2.1

Four rows, exactly as before the overwrite() — the grain didn't change — but P002 now has category='health-snacks', unit_cost=0.68. P001, P003, and P004 are identical to V1: the overwrite() replaced the whole table, not just the row that changed, so the three unchanged rows had to be included in DIM_PRODUCT_V2 for them to survive the operation.

Step 2 — table.history() reveals something lesson 1 didn't fully preview

history = table.history()
print(f"\ntable.history() has {len(history)} entries")

snaps = table.inspect.snapshots().select(["snapshot_id", "parent_id", "operation", "summary"])
print("\ntable.inspect.snapshots():")
for row in snaps.to_pylist():
    summary = dict(row["summary"])
    print(f"  operation={row['operation']:<8}  "
          f"added-records={summary.get('added-records', '-')}  "
          f"deleted-records={summary.get('deleted-records', '-')}  "
          f"total-records={summary['total-records']}")

What to expect (verified by running the actual script; the exact snapshot_id, parent_id, and committed_at values are your own run's, different each time — the number of entries and the operation/summary values are deterministic):

table.history() has 3 entries

table.inspect.snapshots():
  operation=append    added-records=4  deleted-records=-  total-records=4
  operation=delete    added-records=-  deleted-records=4  total-records=0
  operation=append    added-records=4  deleted-records=-  total-records=4

Three entries, not two. This is the part of this lesson lesson 1's map deliberately left not fully detailed. The first entry is snap_v1 — lesson 2's append(), with V1's four rows. The third is the currently active snapshot, with V2's four rows. But in between there's a third entry: a delete operation that removed V1's four rows (deleted-records=4, total-records=0) before the following entry replaced them with V2's.

Why table.overwrite() can produce more than one snapshot

This isn't a mistake in this guide or unexpected behavior — it's documented in PyIceberg's own API. Table.overwrite()'s docstring, in version 0.11.1, says exactly this:

"An overwrite may produce zero or more snapshots based on the operation: DELETE (existing Parquet files can be dropped completely), OVERWRITE (existing Parquet files need to be rewritten to drop rows that match the overwrite filter), APPEND (new data is being inserted into the table)."

In this lesson's case — an overwrite() with no overwrite_filter, which by default replaces 100% of the existing rows — Iceberg resolved the operation as two internal steps: a delete that drops every old data file completely (cheaper than rewriting them row by row, because no file survives partially), followed by an append that adds the new files with V2. Each of those two steps is, in its own right, a snapshot — exactly this module's lesson 2 rule ("every data write is a new snapshot"), applied here twice within a single overwrite() call.

Notice one more detail, confirmed in that same table.inspect.snapshots(): the intermediate snapshot — the delete — has total-records=0. For a fraction of that commit, kiosko.dim_product literally had no row at all. Nobody querying the table from outside ever sees that empty state as "current" — table.overwrite() runs as a single atomic transaction, and the catalog only updates its pointer once, at the end, toward the snapshot with V2 — but that intermediate snapshot still stays filed in the history, available to anyone who explicitly looks for it by its own snapshot_id. It's, in the most literal practical sense, a real photo of the completely empty shelf, taken and filed away, even though nobody saw it at the front desk while it happened.

Diagram: two writes, three snapshots

flowchart LR
    S1["snap_v1\noperation: append\nP001..P004, V1\n(P002 = snacks/0.60)"] -->|"table.overwrite(V2)\npart 1: deletes EVERYTHING"| SD["intermediate snapshot\noperation: delete\n0 rows -- never 'current'\nfor anyone outside"]
    SD -->|"part 2: adds V2"| S2["snap_v2\noperation: append\nP001..P004, V2\n(P002 = health-snacks/0.68)\n= current_snapshot()"]

    S1 -.->|"snap_v1 stays available"| T["time travel\n(lesson 5)"]

Common mistakes

Assuming table.history() is going to have exactly one new entry per call to a write method. What happens: someone, after having run two operations — append(V1) in lesson 2, overwrite(V2) in this lesson — expects to see len(table.history()) == 2, and is surprised to see 3. Why it happens: it's a reasonable generalization from lesson 2, where "one write, one snapshot" was literally true — but that lesson didn't cover the case of a full overwrite(), which this lesson's Going deeper section explains can internally require more than one snapshot. How to spot it: if your snapshot count doesn't match your count of calls to write methods, check table.inspect.snapshots() with the operation column — you're going to find the extra operation there, documented, not lost. How to fix it: count snapshots by querying table.history() or table.inspect.snapshots() directly, never by assuming a number from how many times you called a method — the real number of snapshots depends on how Iceberg decides to resolve that operation internally, not just on your code.

Worrying about the intermediate snapshot with total-records=0, thinking the table "broke halfway." What happens: someone sees the operation=delete ... total-records=0 row in table.inspect.snapshots() and gets alarmed, thinking there was a real moment kiosko.dim_product sat empty and someone could have queried it that way. Why it happens: seeing "0 rows" in the middle of an operation instinctively sounds like an atomicity failure — exactly the problem data-engineering-foundations-guide's overwrite-partition (module 6) actually had, from not being transactional. How to spot it: if you're worried an external reader could have seen the table empty during this overwrite(), revisit what "ACID" guarantees in this context — a topic module 4 of this guide develops in depth. How to fix it: nothing to fix — table.overwrite() runs as a single transaction; the catalog never points to the intermediate snapshot as current, so no external reader could have seen it. The snapshot with total-records=0 stays filed in the history for completeness — it's a real photo, taken and saved — but it was never the photo shown at the front desk.

Exercises

Exercise 1 — Reproduce the overwrite yourself, and confirm the three entries. With lesson 2's state available (snap_v1 captured), run this lesson's overwrite_v2.py. Confirm table.scan().to_arrow() shows P002 as health-snacks/0.68, and that table.history() reports exactly three entries.

See solution

If your table started from lesson 2's exact state — four rows with V1, a single snapshot — your output should match this lesson's: V2's four rows in the scan(), and three entries in table.history(), with operation in the order append, delete, append. The snapshot_ids are going to be different from the ones shown here — that's exactly expected.

Exercise 2 — Calculate how many Parquet data files physically exist in kiosko_warehouse/kiosko/dim_product/data/ after this lesson. Using table.inspect.files() or checking the data/ folder directly from the terminal, count how many distinct .parquet files there are. Justify your answer by thinking about which files survived the delete and which are new from append.

See solution

Two files: the one containing V1's four rows (created in lesson 2) and the one containing V2's four rows (created by this lesson's append part). The delete snapshot in between doesn't delete any physical file — Iceberg never deletes a data file through a normal write operation; what delete does is stop referencing that file from the current snapshot. V1's Parquet file still exists on disk, intact, because snap_v1 still needs it to correctly answer a table.scan(snapshot_id=snap_v1) — you're going to confirm this in lesson 5. Only an explicit maintenance operation (remove_orphan_files, which module 7 of this guide teaches) would delete that file, and only after no current snapshot needs it anymore.

Exercise 3 — Prediction: what would happen if you used table.append() instead of table.overwrite() for this same P002 change? Without running it, predict: if instead of this lesson's overwrite_v2.py you had run table.append(pa_table_v2) — adding V2's four rows without removing V1's — how many total rows would kiosko.dim_product have, and how many rows with product_id='P002' would exist?

See solution

Eight total rows, two of them with product_id='P002' — one with category='snacks'/unit_cost=0.60 (from V1) and another with category='health-snacks'/unit_cost=0.68 (from V2) — coexisting in the same current snapshot. This would break dim_product's grain — one row per product — and any later JOIN against fact_orders would produce a fan-out: every P002 order would multiply into two dimension rows, doubling the reported revenue. This is exactly the mistake this module's lesson 2 "Common mistakes" section already warned about, and the concrete reason this lesson uses table.overwrite() — replacing the whole content, not adding one more version on top.

Summary and next step

In this lesson you changed P002 from snacks/0.60 to health-snacks/0.68 with table.overwrite(), a normal Iceberg overwrite(), with no "history mode" parameter at all. You confirmed, with table.scan().to_arrow(), that kiosko.dim_product's current state reflects V2. And you discovered, with table.inspect.snapshots(), that that single overwrite() call produced three entries in the history — append, delete, append — not two, because Iceberg resolved the full replacement as a removal followed by a load.

Before moving on you should be able to: explain the difference between table.append() and table.overwrite() in the context of a dimension table; and explain why table.history() can have more entries than write-method calls you made, citing overwrite()'s official docstring as evidence.

kiosko.dim_product now has three snapshots filed away, and its current state no longer matches V1. Lesson 4 pauses on the snapshot_id captured in lesson 2 — snap_v1 — and on this guide's hard rule about never hardcoding it, before using it to travel through time in lesson 5.

Resources

• PyIceberg — API reference, table.overwrite(), including the docstring quoted in this lesson about the combinations of operations it can produce. py.iceberg.apache.org/api. In English.
• PyIceberg — API reference, table.inspect.snapshots(), with the operation and summary columns this lesson used to tell append apart from delete. py.iceberg.apache.org/api. In English.
• Apache Iceberg — official documentation, "Table Spec," "Snapshots" section, where operation is formally defined as part of every snapshot's summary. iceberg.apache.org/spec. In English.
• data-modeling-for-analytics-guide DESIGN doc — source of the canonical P002 change (snacks/0.60 → health-snacks/0.68, 2026-08-15) this lesson reproduces with table.overwrite(). src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.
• This guide's DESIGN doc — the "Snapshots and time travel" section (M3), the exact source of this step in the experiment. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.