Four times Parquet alone wasn't enough

Description

This lesson doesn't teach anything new about Iceberg yet — it's, precisely, the evidence lesson. You're going to review, one by one, with code quoted literally from each previous guide, the four exact times the whole Kiosko ecosystem ran into the same ceiling: a Parquet file, however well written, doesn't know how to describe itself beyond its own rows. Each of the four previous guides built a real solution that works — this lesson doesn't criticize them — but each solution built that capability outside the file, with a different tool or discipline each time.

Connection to the module. This lesson is the map that connects each following module of this guide to a real, already-lived pain point. Module 3 (snapshots and time travel) directly solves problems 2 and 3 of this lesson. Module 4 (schema evolution) solves problem 1. Module 5 (hidden partitioning) solves problem 4. And module 6 (native MERGE INTO) puts problems 2 and 3 side by side again with a fourth solution. Without this lesson, each following module would feel like a new tool with no connection to what you've already learned; with it, each module is, precisely, the answer to a problem you already named here, with real code.

An analogy: the same leak, patched four times, on four different floors

Imagine a four-story building with a structural defect in the same main pipe: every time it rains hard, it leaks. The defect isn't in any particular floor — it's in the pipe itself, which has no automatic shutoff valve and no sensor to warn when the leak started. Four maintenance teams, on four different floors, solved the "floor 1 doesn't leak" problem with four different solutions: the first put out buckets and a strict "empty before it fills" protocol (it works, but demands constant manual discipline); the second installed a gauge with hand-written labels — "this drop fell on Monday," "this one on Tuesday" — to be able to reconstruct the history if they ever need it; the third bought a machine that sticks on those same labels automatically, without anyone having to write them by hand; the fourth, on the top floor, simply organized buckets by geographic zone of the floor, so anyone who knows the exact layout can find the right bucket without asking.

All four solutions work. None fixes the pipe. This lesson visits, one by one, each of the four floors — each of the four previous guides in this ecosystem — and shows, with the real code each team wrote, exactly what patch they built. The rest of this guide is, properly speaking, the repair of the pipe itself.

Problem 1 — data-engineering-foundations-guide (module 6): the overwrite-partition that was never atomic

data-engineering-foundations-guide solved, in its module 6, a real problem: if a pipeline runs twice over the same day by mistake — something that happens in production, not a hypothetical situation — a plain INSERT duplicates every row. The solution that guide built is called overwrite-partition, a term coined by Maxime Beauchemin (creator of Apache Airflow) in his essay Functional Data Engineering: before inserting a date's new data, completely delete what that date already held.

def load_overwrite_partition(con: sqlite3.Connection, rows: list[dict], partition_date: str) -> None:
    """Correct pattern: delete the partition for that date BEFORE inserting."""
    con.execute(
        "CREATE TABLE IF NOT EXISTS staging_demo (order_id TEXT, revenue REAL, dt TEXT)"
    )
    con.execute("DELETE FROM staging_demo WHERE dt = ?", (partition_date,))
    con.executemany(
        "INSERT INTO staging_demo (order_id, revenue, dt) VALUES (?, ?, ?)",
        [(r["order_id"], r["revenue"], partition_date) for r in rows],
    )
    con.commit()

This function is correct, and that same guide proved it by running it twice in a row: the first run leaves 8 rows, the second also leaves 8 — never 16. But that same guide was honest, in its own deep-dive, about the real cost of this solution:

"There's a real cost to this decision, and it's worth naming honestly: between the DELETE and the INSERT, there's a moment where the partition sits empty. If something fails exactly at that moment — the process dies halfway through — the date is left temporarily without data [...]. Wrapping the DELETE and the INSERT in a single atomic transaction [...] is a real improvement over this design."

There's the problem, named precisely: DELETE followed by INSERT are two separate operations, and between one and the other there's a real window of risk. No Parquet file, and no database without explicit transactional support for this combined operation, can guarantee that both happen as a single indivisible unit. Module 4 of this guide (schema-evolution-without-rewriting) revisits this exact quote and shows why an Iceberg write — table.overwrite() — really is atomic: there's never a moment where the table sits "halfway" between old and new.

Problem 2 — data-modeling-for-analytics-guide (modules 4-5): hand-written history columns

data-modeling-for-analytics-guide solved a different problem: when P002 Energy Bar changes category and cost (snacks/0.60 → health-snacks/0.68, effective 2026-08-15), any historical query over sales before that change must still see the old category — otherwise the calculated margin comes out wrong: 10.8 correct (with snacks) versus 9.36 broken (if health-snacks gets mistakenly assigned to sales that happened before that category even existed). That guide's solution was the SCD type 2 pattern: three new columns in dim_product_scd — valid_from, valid_to, is_current — and a hand-written MERGE INTO that closes the old version and opens the new one:

MERGE INTO dim_product_scd AS target
USING staging_product AS source
ON target.product_id = source.product_id AND target.is_current = true
WHEN MATCHED AND (
    target.unit_cost <> source.unit_cost OR
    target.category  <> source.category
) THEN UPDATE SET
    valid_to   = DATE '2026-08-15' - INTERVAL 1 DAY,
    is_current = false

followed by an INSERT of the new row with valid_from = DATE '2026-08-15', valid_to = NULL, is_current = true. It works, and that guide itself verified it with two real runs of the MERGE. But notice what it demands of whoever models the table: someone had to decide to declare those three columns, someone had to write the MERGE with the exact condition target.is_current = true, and any query that wants the correct historical state has to remember to use BETWEEN valid_from AND valid_to instead of simply reading the table. The history exists, but it lives in columns the modeler had to design — not in any capability of the storage format itself. Module 3 of this guide (snapshots-and-time-travel) reproduces this same P002 change with a plain overwrite(), without declaring a single history column, and recovers the previous state with table.scan(snapshot_id=...).

Problem 3 — dbt-analytics-engineering-guide (module 5): the same technique, automated by an external tool

dbt-analytics-engineering-guide solved the exact same problem — the same P002 change — with dbt snapshot: instead of writing the MERGE INTO by hand, you declare the mechanism once, and dbt automatically generates three equivalent columns on every run:

• dbt_valid_from — since when this version of the row is current.
• dbt_valid_to — until when it was (NULL while still current).
• dbt_scd_id — a unique key per version of each row, computed automatically.

That guide itself sums it up precisely: "a dbt snapshot does [...] every time it runs [...] it compares the source's current state [...] against the last archived frame [...] If it changed, it executes two actions in the same step: it closes the old row [...] and opens a new row [...] It's, precisely, the same 'close before opening' pattern data-modeling-for-analytics-guide already built by hand [...] and later automated with MERGE INTO — a dbt snapshot is that same automation, now behind a single terminal command."

Notice the key phrase in that quote: it's the same automation, not a different idea. dbt snapshot solves the problem of "having to write the MERGE by hand" — a real problem, of typing and of discipline — but it doesn't solve the underlying problem: the history still lives in columns (dbt_valid_from, dbt_valid_to, dbt_scd_id) that someone had to declare, and still requires that any historical query know how to filter correctly by those columns. Module 3 of this guide revisits this contrast explicitly: kiosko.dim_product on Iceberg has no column equivalent to dbt_valid_from — the engine itself keeps the full history, with no one designing it.

Problem 4 — spark-and-distributed-processing-guide (module 7): Hive folders you have to know by heart

spark-and-distributed-processing-guide solved a scale problem: with ten million rows in fact_orders_at_scale, reading the whole table to filter by a single store is a waste of work. The industry-standard solution — documented as that guide's plan for its module 7 — is to physically partition the Parquet into folders, with partitionBy:

fact_orders_at_scale.write.partitionBy("store_id").parquet("kiosko_orders_at_scale.parquet")

This produces, on disk, a folder structure where each store_id value lives in its own directory — the pattern the industry calls Hive partitioning:

kiosko_orders_at_scale.parquet/
├── store_id=S01/
│   └── part-00000-....snappy.parquet
├── store_id=S02/
│   └── part-00001-....snappy.parquet
└── store_id=S03/
    └── part-00002-....snappy.parquet

When someone filters with .filter(col("store_id") == "S01"), Spark is smart enough to read only the store_id=S01/ directory, without touching the other two — a real optimization, verifiable with .explain() showing the pruned execution plan. But notice what that optimization depends on: it depends on the query filtering by exactly the column that decided the folder structure, and on the reading engine knowing how to interpret the column=value folder-name convention. If tomorrow Kiosko decides the most frequent question is no longer "by store" but "by day," changing the partition scheme means rewriting the ten million rows from scratch — the folder structure is set in stone the day it's written. Module 5 of this guide (hidden-partitioning-and-partition-evolution) contrasts this exactly with Iceberg's hidden partitioning: the query still filters by store_id, without mentioning any folder, and the partition scheme can be evolved going forward without rewriting a single existing file.

The full map: what each guide built, and what it was missing

#	Guide	What it built	What it demanded of whoever maintains it	Where Iceberg solves it
1	data-engineering-foundations-guide (M6)	overwrite-partition: DELETE followed by INSERT	Accepting a real window of risk between both operations	Module 4 — every write is atomic
2	data-modeling-for-analytics-guide (M4-5)	valid_from/valid_to/is_current + hand-written MERGE INTO	Designing the columns, writing the MERGE, remembering the correct JOIN on every query	Module 3 — time travel with zero history columns
3	dbt-analytics-engineering-guide (M5)	dbt snapshot: dbt_valid_from/dbt_valid_to/dbt_scd_id	Installing and learning an external tool that automates the same column technique	Module 3 — the engine keeps the history, no columns
4	spark-and-distributed-processing-guide (M7)	Hive partitioning: partitionBy("store_id")	Knowing the folder structure by heart to take advantage of partition pruning	Module 5 — hidden partitioning and partition evolution

Diagram: four patches, the same ceiling underneath

flowchart TB
    A["foundations M6:\nDELETE + INSERT\n(risk window)"] --> E["The same ceiling underneath\nall four solutions:\nParquet doesn't describe itself"]
    B["data-modeling M4-5:\nvalid_from / valid_to / is_current\n(by hand)"] --> E
    C["dbt M5:\ndbt_valid_from / dbt_valid_to / dbt_scd_id\n(automated)"] --> E
    D["spark M7:\npartitionBy('store_id')\n(Hive folders)"] --> E
    E --> F["This guide: Apache Iceberg\nthe table format that solves\nall four, from the engine"]

Going deeper: why the four solutions are correct AND limited at the same time

It's worth saying with full clarity, because it's easy to read this lesson as a retroactive criticism of the six previous guides: none of the four solutions is wrong. overwrite-partition is still, today, a valid, widely used pattern in the industry for simple batch pipelines. Hand-written SCD type 2 is still, on many teams, exactly what you need to know how to do. dbt snapshot is still the standard way to historize dimensions inside a dbt project. Hive partitioning is still the foundation of nearly every data lake built in the last fifteen years, and Iceberg itself supports it as an option (explicit partitioning, no transforms) for whoever needs it for compatibility.

What the four share isn't a mistake — it's a structural limit of working directly with files: any guarantee you want (atomicity, history, an efficient layout) has to be built outside the file, with discipline, with extra columns, or with an external tool. Apache Iceberg doesn't invent a new idea for any of these four problems — it moves the responsibility of solving them off the person modeling or orchestrating, and onto the table format itself, which now knows, without anyone teaching it each time, how to be atomic, how to remember its history, and how to organize itself on disk.

Common mistakes

Concluding the six previous guides "were wrong" or "didn't know about Iceberg." What happens: someone, seeing this lesson's map, interprets each previous guide as having made a mistake this guide comes to correct. Why it happens: presenting four solutions followed by "this is solved better with Iceberg" can unintentionally sound like a retroactive correction. How to spot it: if your takeaway from this lesson is "I should have used Iceberg since foundations," you missed the point — foundations deliberately teaches the fundamentals with no table format at all, precisely because you need to understand the raw problem before you can appreciate the solution. How to fix it: reread this lesson's Going deeper section — the four solutions are correct within the scope of the guide where they appeared, and they're still legitimate tools today. This guide doesn't invalidate them: it builds on them, with the same Kiosko case, to show what changes when the storage format takes on that responsibility.

Memorizing the four problems without connecting each one to its solving module. What happens: someone reads this lesson, nods along with the four stories, and moves on without keeping the "problem → module that solves it" map. Why it happens: the lesson has four distinct stories, and it's easy to remember them as loose anecdotes instead of as a navigation map. How to spot it: if by the time you reach module 4 you don't recognize that module revisiting, by name, the exact quote from data-engineering-foundations-guide about the overwrite-partition risk window, you lost the connection this lesson built on purpose. How to fix it: use this lesson's table ("The full map") as an active reference for the rest of the guide — every time you start a new module, go back to that table and confirm which of the four problems that specific module is here to solve.

Exercises

Exercise 1 — Match the problem to Iceberg's solution. Without looking at this lesson's table, match each of the four problems to the module of this guide that solves it: (a) hand-written history columns, (b) risk window between DELETE and INSERT, (c) Hive folders you have to know by heart, (d) automated history columns via an external tool.

See solution

(a) and (d) are both solved in module 3 (snapshots and time travel) — both are, at bottom, the same problem (dimension history), solved once with no history columns. (b) is solved in module 4 (schema evolution without rewriting), which explicitly revisits the quote from data-engineering-foundations-guide about the overwrite-partition risk window and shows why an Iceberg write is atomic. (c) is solved in module 5 (hidden partitioning and partition evolution), contrasting partitionBy("store_id") with Iceberg's hidden partitioning.

Exercise 2 — Reconstruct overwrite-partition in your own words. Without looking at this lesson's code, write in pseudocode (not exact SQL or Python) the two steps of the overwrite-partition pattern, and in 1-2 sentences explain why that pattern isn't atomic.

See solution

Pseudocode: 1. Delete all data that already exists for the date being loaded. 2. Insert the new data for that same date. It isn't atomic because these are two separate operations, run in sequence: between step 1 and step 2 there's a real moment where the partition sits empty. If the process fails exactly at that moment, the partition is left without data until the next successful run — a visible problem, but a real one, that no database without explicit transactions over both operations can prevent on its own.

Exercise 3 — Explain the difference between problem 2 and problem 3 in your own words. Problems 2 (data-modeling) and 3 (dbt) solve, at bottom, the same business case — the P002 change. In 2-3 sentences, explain exactly what changes between one solution and the other, and what does not change.

See solution

What changes is who writes the close-and-open mechanism: in data-modeling, a person writes the whole MERGE INTO by hand, statement by statement; in dbt, an external tool (dbt snapshot) generates that same mechanism automatically, from a declarative configuration. What does not change is the underlying technique: both solutions add history columns to the table (valid_from/valid_to/is_current in one case, dbt_valid_from/dbt_valid_to/dbt_scd_id in the other), and both require that any historical query know how to filter correctly by those columns to get the right result. Neither one moves the responsibility for keeping history onto the storage format itself — that is, precisely, what module 3 of this guide does for the first time.

Summary and next step

In this lesson you reviewed, with code quoted literally from each previous guide, the four exact times the whole Kiosko ecosystem ran into the same ceiling: a non-atomic overwrite-partition (foundations), hand-written history columns (data-modeling), automated history columns (dbt), and Hive folders you have to know by heart (spark). You built the full map that connects each of those four problems to the exact module of this guide that solves it.

Before moving on you should be able to: name the four guides and their specific problem, from memory; and explain why the four solutions are correct within their scope, and at the same time share the same structural limitation.

Lesson 3 takes the conceptual step that makes it possible to understand why Iceberg solves all four problems from a single place: the precise distinction between a file format and a table format.

Resources

• Maxime Beauchemin — "Functional Data Engineering: a modern paradigm for batch data processing," the essay that coined the term overwrite-partition, quoted by data-engineering-foundations-guide. maximebeauchemin.medium.com/functional-data-engineering-a-modern-paradigm-for-batch-data-processing-2327ec32c42a. In English.
• data-engineering-foundations-guide DESIGN doc — source of the overwrite-partition pattern and the exact quote about the risk window. src/guides/data-engineering-foundations-guide/DISENO.md. In Spanish.
• data-modeling-for-analytics-guide DESIGN doc — source of dim_product_scd, the SCD type 2 MERGE INTO, and the margin=10.8/9.36 numbers. src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.
• dbt-analytics-engineering-guide DESIGN doc — source of dbt snapshot and the dbt_valid_from/dbt_valid_to/dbt_scd_id columns. src/guides/dbt-analytics-engineering-guide/DISENO.md. In Spanish.
• spark-and-distributed-processing-guide DESIGN doc — source of fact_orders_at_scale, partitionBy("store_id"), and the Hive partitioning from its module 7. src/guides/spark-and-distributed-processing-guide/DISENO.md. In Spanish.
• This guide's DESIGN doc — the full map of the eight modules and the market warning this lesson quotes. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.