What time travel does not replace

Description

This is this module's most important lesson, and it exists on purpose so you don't leave it with a wrong idea. Lesson 6 recovered P002's correct margin — 10.8 — with time travel, with no history column at all, and that's a real, verified result. But it doesn't mean time travel generally solves the problem data-modeling-for-analytics-guide solved with row-level valid_from/valid_to. This lesson builds an isolated, genuinely executed example that shows exactly where the limit sits — and why Kiosko's case fell, with nobody forcing it, on the easy side of that limit.

Connection to the module. Lessons 2 through 6 showed the favorable path: one table, a single change, every fact on the same side of that change. This lesson examines what happens when those conditions don't hold, with a real experiment — not an abstract warning — you're going to be able to run yourself.

An analogy: asking for "Tuesday's photo" when the shelf changed twice that week

Go back, one last time, to the supermarket photo archive. If the shelf got restocked only once in the whole week, "asking for the photo from before the restock" is an unambiguous question — there's a single photo that correctly answers for any day that week. But if the shelf got restocked twice — Tuesday and Thursday, for example — and you're asked "what was on the shelf Monday, and what was there Friday, in the same query?", no single photo in the archive answers both questions at once. The photo from before Tuesday is correct for Monday, but wrong for Friday; the photo from after Thursday is correct for Friday, but wrong for Monday. You'd need two different photos, one for each question — and the archive, on its own, doesn't know which one corresponds to which without you telling it, row by row.

The experiment: two sales, two correct costs, a single snapshot_id

This illustration is deliberately isolated from Kiosko's model — it uses its own, disposable catalog and warehouse, created and destroyed within this same lesson — so as not to mix hypothetical data with the real tables the rest of this guide builds. The goal is to show, with real code, time travel's structural limit, not to add a new product to Kiosko.

Step 1 — A toy product, with two price writes

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

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

demo_warehouse = os.path.abspath("demo_limit_warehouse")
demo_db = os.path.abspath("demo_limit_catalog.db")
os.makedirs(demo_warehouse, exist_ok=True)

demo_catalog = load_catalog(
    "demo", type="sql",
    uri=f"sqlite:///{demo_db}", warehouse=f"file://{demo_warehouse}",
)
demo_catalog.create_namespace("demo")

price_schema = Schema(
    NestedField(field_id=1, name="product_id", field_type=StringType(), required=True),
    NestedField(field_id=2, name="unit_cost", field_type=DoubleType(), required=True),
)
price = demo_catalog.create_table("demo.toy_price", schema=price_schema)

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

# snap_early -- T1 costs 1.00
price.append(pa.Table.from_pylist([{"product_id": "T1", "unit_cost": 1.00}], schema=pa_schema))
snap_early = price.current_snapshot().snapshot_id

# snap_late -- T1 rises to 2.00 (a normal overwrite, same as this module's lesson 3)
price.overwrite(pa.Table.from_pylist([{"product_id": "T1", "unit_cost": 2.00}], schema=pa_schema))
snap_late = price.current_snapshot().snapshot_id

Two writes, two snapshots — exactly the same mechanism from this module's lessons 2 and 3. The difference with Kiosko is that here you're going to simulate two T1 sales, one before the price change and one after — with no history column at all, just like kiosko.dim_product.

Step 2 — Two sales, each with its own correct cost

sale_before = {"order": "toy-early-sale", "correct_cost_should_be": 1.00}
sale_after = {"order": "toy-late-sale", "correct_cost_should_be": 2.00}

cost_as_of_early = price.scan(snapshot_id=snap_early).to_arrow().to_pylist()[0]["unit_cost"]
cost_as_of_late = price.scan(snapshot_id=snap_late).to_arrow().to_pylist()[0]["unit_cost"]

for sale in (sale_before, sale_after):
    ok_early = cost_as_of_early == sale["correct_cost_should_be"]
    ok_late = cost_as_of_late == sale["correct_cost_should_be"]
    print(f"{sale['order']}: actual cost={sale['correct_cost_should_be']}  "
          f"AS OF snap_early={cost_as_of_early} ({'OK' if ok_early else 'WRONG'})  |  "
          f"AS OF snap_late={cost_as_of_late} ({'OK' if ok_late else 'WRONG'})")

What to expect (verified by running the actual script; snap_early/snap_late are captured in variables, never hardcoded, following this module's lesson 4 rule):

toy-early-sale: actual cost=1.0  AS OF snap_early=1.0 (OK)  |  AS OF snap_late=2.0 (WRONG)
toy-late-sale: actual cost=2.0  AS OF snap_early=1.0 (WRONG)  |  AS OF snap_late=2.0 (OK)

There's the limit, with literal evidence. No single snapshot_id gets both sales right at once. AS OF snap_early is correct for toy-early-sale, but wrong for toy-late-sale. AS OF snap_late — the current state, no time travel even needed — is correct for toy-late-sale, but wrong for toy-early-sale. A query with a single snapshot_id fixes one single version of the whole table for every row it touches — there's no way, within the time travel mechanism itself, to tell it "for this row use this version, and for that other one, use this different version," within the same query.

shutil.rmtree(demo_warehouse)
os.remove(demo_db)

(This demo catalog and table are discarded when the lesson closes — they aren't part of kiosko, and no later module in this guide depends on them.)

Why Kiosko's real case didn't run into this limit

The reason isn't that time travel is, in Kiosko's case, smarter than in this experiment — it's that Kiosko's case satisfies, by the data's design, the one condition under which a single snapshot_id really is correct for every row at once: the forty orders happen between August 3 and 9, 2026, and P002's single change takes effect on August 15 — every order falls on the same side of that single change. There's no Kiosko order after August 15 that needed unit_cost=0.68 while another one, earlier, needed unit_cost=0.60 within the same calculation. snap_v1 is correct for all forty rows at once, with no exception — exactly the absence of the situation this lesson's experiment deliberately built with toy-early-sale and toy-late-sale.

Diagram: when a single snapshot_id is enough, and when it isn't

flowchart TB
    subgraph favorable["Favorable case -- Kiosko, this module"]
        F1["40 orders, ALL before Aug 15"] --> F2["A SINGLE snapshot (snap_v1)\ncorrect for all 40 at once"]
    end
    subgraph general["General case -- this lesson's experiment"]
        G1["sales split BEFORE and AFTER\na change (or several changes)"] --> G2["NO single snapshot_id\nis correct for all at once"]
        G2 --> G3["you need to know, PER ROW,\nwhich version applied on ITS date"]
    end

The correct answer to the general case: row-level SCD-2

The general case — facts split across both sides of one or more dimension changes, or several dimension rows changing on different, independent dates — still needs exactly what data-modeling-for-analytics-guide and dbt-analytics-engineering-guide already taught: a valid_from/valid_to column (or its dbt_valid_from/dbt_valid_to equivalent) evaluated per row, not per whole table. The structural difference is this: an Iceberg snapshot_id describes "this is what the whole table looked like at an instant" — a single coordinate, applied globally to any query that uses it. A valid_from/valid_to column describes "this specific row was current during this window" — an independent coordinate per row, which a JOIN can evaluate against each fact's own date, with no requirement that every row in the result share the same reference instant.

This isn't a limitation specific to PyIceberg, nor something a future version is going to solve differently — it's a difference in which question each mechanism answers. Time travel answers: "what did the whole table look like at instant X?" The point-in-time JOIN answers: "which version of this row was current on this fact's own date?" They're different questions, and the second is strictly more general than the first — any case where the first is enough is also a case where the second would give the same answer; the reverse isn't true.

When to use each one, with judgment

Situation	Correct tool
Audit what a whole table looked like on a specific date (what did the system report on August 10?)	Time travel — table.scan(snapshot_id=...) or AS OF
Recover a value deleted or overwritten by mistake, as a one-off	Time travel
Reproduce an exact historical report, exactly as it looked when published	Time travel
Calculate a metric that depends on the dimension version current on each individual fact's date, when the dimension changed more than once or the facts are split across both sides of a change	Point-in-time JOIN with row-level valid_from/valid_to (by hand, as in data-modeling, or automated, as in dbt snapshot)
Kiosko's favorable case in this module: a single change, every fact on the same side	Either one — time travel is simpler to write, with no extra columns

Common mistakes

Concluding Iceberg "never needs" SCD-2, after seeing lesson 6's result. What happens: someone, impressed by how simple it was to recover 10.8 with no column at all, decides it's no longer worth learning or maintaining valid_from/valid_to in any future Iceberg project. Why it happens: Kiosko's example is real, it works, and it's tempting to generalize from a favorable case into a universal rule. How to spot it: if you can't describe, from memory, a concrete scenario where time travel fails — like this lesson's toy-early-sale/toy-late-sale — you haven't internalized the limit yet. How to fix it: go back to this lesson's experiment and reproduce it yourself — the evidence that "a single snapshot_id isn't enough when there are sales on both sides of a change" is more convincing run than read.

Thinking the point is "Kiosko got lucky," instead of "Kiosko's data satisfies a verifiable condition." What happens: someone interprets lesson 6's result as having worked by coincidence, with no structural reason. Why it happens: it's easy to miss that "every order predates the change" is, in fact, a verifiable property of the data — not a stroke of luck — and that it was designed that way, on purpose, by data-modeling-for-analytics-guide from the start (the effective date 2026-08-15 was chosen, explicitly, to be after the forty orders). How to spot it: if you can't explain, with a concrete date, why snap_v1 is correct for all forty orders at once, revisit this lesson's "Why Kiosko's real case didn't run into this limit" section. How to fix it: before using time travel as a substitute for a point-in-time JOIN in a real case, explicitly verify that every relevant fact falls on the same side of every dimension change you care about — if that check fails, you need row-level SCD-2, not time travel.

Exercises

Exercise 1 — Reproduce the toy_price experiment yourself, and confirm the hit/miss table. Run this lesson's full script, in a working directory separate from the rest of this module. Confirm you get exactly the two-row table shown in "What to expect," with no snapshot_id getting both sales right.

See solution

Your output should match this lesson's: toy-early-sale correct only with snap_early, toy-late-sale correct only with snap_late, with no snapshot_id getting both rows right at once. If for some reason a single snapshot_id "got both right" — something that shouldn't happen with this experiment as built — check that you really used two different costs (1.00 and 2.00) for the two writes.

Exercise 2 — Extend the experiment with a third sale, at a hypothetical intermediate moment. Without running it yet, predict: if you added a third "sale" with correct_cost_should_be equal to a value that never existed in toy_price — for example, 1.50, a price T1 never had — could either of the two existing snapshot_ids get it right?

See solution

No — no snapshot_id can return a value that was never the table's current state at any moment. Time travel can only reconstruct states that really existed as real snapshots; it can't interpolate, average, or invent an intermediate state that was never written. This is an additional limit, different from the one this lesson's main example shows: time travel reconstructs photos that exist, not any hypothetical state someone might think to ask about.

Exercise 3 — Explain, in your own words and without looking at this lesson's table, when time travel IS the correct tool. In 2-3 sentences, describe a scenario — not necessarily Kiosko's — where this module's time travel is exactly the right tool, and explain why a point-in-time JOIN would be, in that case, unnecessary work.

See solution

There's no single correct answer, but a good example is: "I want to know exactly what the sales dashboard reported on August 1, before we fixed a load error on August 2" — here there's no fact "split across both sides" of anything; the question is, literally, "what did the whole table look like at a fixed instant," which is exactly the question time travel natively answers. Writing a point-in-time JOIN with valid_from/valid_to to answer that question would be extra work: you'd have to declare, populate, and maintain history columns just to reconstruct something Iceberg already files away automatically on every commit.

Summary and next step

In this lesson you built, with real code isolated from Kiosko's model, the proof that a single snapshot_id can't correctly serve two facts that need different versions of the same dimension at the same time. You confirmed, with the exact date of the P002 change (2026-08-15, after all forty orders), why Kiosko's case fell on the favorable side of that limit — not by chance, but through a verifiable condition of the data. And you saw, with a decision table, when time travel is the correct tool and when the general case still needs row-level SCD-2, by hand or automated.

Before moving on you should be able to: explain, with your own example, a scenario where time travel gives an incorrect result; explain the exact condition that made Kiosko's case work with time travel; and decide, faced with a new case, which of the two techniques applies.

With the previous seven lessons complete, lesson 8 brings everything together into a single project: kiosko.dim_product created, historized via time travel, verified end to end with automatic assert statements.

Resources

• Apache Iceberg — official documentation, "Table Spec," "Snapshots" section, the formal definition that a snapshot describes a table's whole state, not an individual row's. iceberg.apache.org/spec. In English.
• PyIceberg — API reference, table.scan(snapshot_id=...), the mechanism whose structural limit this lesson demonstrates. py.iceberg.apache.org/api. In English.
• data-modeling-for-analytics-guide DESIGN doc — source of the row-level point-in-time JOIN (valid_from/valid_to), the technique still needed for the general case this lesson describes. src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.
• dbt-analytics-engineering-guide DESIGN doc — source of dbt snapshot, the automated version of the same row-level technique. src/guides/dbt-analytics-engineering-guide/DISENO.md. In Spanish.
• This guide's DESIGN doc — module 3's explicit boundary: "the general case of a dimension that changes many times, with facts split across several versions, still needs row-level SCD-2." src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.