Recovering P002's history with zero extra columns

Description

This is the whole module's payoff lesson. You're going to join kiosko.fact_orders — Kiosko's forty real orders, unchanged since module 1 — against kiosko.dim_product, read with time travel to snap_v1, and calculate the per-category margin from the six previous guides. The number you're looking for is 10.8 for snacks — P002's correct margin — the same one you already calculated in data-modeling-for-analytics-guide with a point-in-time JOIN over valid_from/valid_to columns, and the same one dbt-analytics-engineering-guide reproduced with dbt snapshot. The difference, this time, is that kiosko.dim_product has exactly four columns.

Connection to the module. Lessons 2 through 5 built each piece separately: the table, the two writes, the capture discipline, the scan() with snapshot_id. This lesson brings them together into the real calculation that gives the whole module its meaning — not "you can travel through time" in the abstract, but "you can travel through time, and the number you get is correct, verified against two previous guides that reached the same result by different paths."

An analogy: the accountant's balance, with and without the right photo

An accountant calculating a sale's margin needs two numbers: how much was charged, and how much what was sold cost. If they use today's cost price to calculate the margin of a sale from last week, the balance comes out wrong — not because of an arithmetic error, but because they used the wrong data point for the wrong moment. This lesson is, precisely, that balance done twice: once with today's cost price (the mistake), and once with the cost price current on the day of the sale — recovered with the archive's right photo, not with a column someone had to maintain by hand.

Worked example: the same margin, two ways to calculate it

Step 1 — The BROKEN margin: fact_orders against dim_product's current state

# margin_broken_vs_correct.py
import os
from collections import defaultdict

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}",
)
dim_product = catalog.load_table("kiosko.dim_product")
fact_orders = catalog.load_table("kiosko.fact_orders")

fact_rows = fact_orders.scan().to_arrow().to_pylist()


def margin_by_category(dim_rows, fact_rows):
    dim_by_id = {r["product_id"]: r for r in dim_rows}
    revenue, margin = defaultdict(float), defaultdict(float)
    for f in fact_rows:
        d = dim_by_id[f["product_id"]]
        revenue[d["category"]] += f["revenue"]
        margin[d["category"]] += f["revenue"] - f["quantity"] * d["unit_cost"]
    return revenue, margin


current_rows = dim_product.scan().to_arrow().to_pylist()
revenue_broken, margin_broken = margin_by_category(current_rows, fact_rows)

print("=== BROKEN -- dim_product with NO time travel (P002 = health-snacks / 0.68) ===")
for cat in sorted(revenue_broken):
    print(f"  {cat:14} revenue={round(revenue_broken[cat], 2):>6}  margin={round(margin_broken[cat], 2):>6}")

What to expect (verified by running the actual script):

=== BROKEN -- dim_product with NO time travel (P002 = health-snacks / 0.68) ===
  beverages      revenue=  44.05  margin=  14.75
  electronics    revenue=   40.5  margin=   21.6
  health-snacks  revenue=   21.6  margin=   9.36

health-snacks, margin 9.36. This is exactly the broken result you already saw in data-modeling (module 5) and in dbt (module 5): the JOIN applied today's unit_cost (0.68) to ten orders that happened before that cost took effect.

Step 2 — The CORRECT margin: fact_orders against dim_product with time travel to snap_v1

history = dim_product.history()
snap_v1 = next(
    entry.snapshot_id
    for entry in history
    if any(
        r["product_id"] == "P002" and r["category"] == "snacks"
        for r in dim_product.scan(snapshot_id=entry.snapshot_id).to_arrow().to_pylist()
    )
)

v1_rows = dim_product.scan(snapshot_id=snap_v1).to_arrow().to_pylist()
revenue_correct, margin_correct = margin_by_category(v1_rows, fact_rows)

print("\n=== CORRECT -- dim_product WITH time travel (P002 = snacks / 0.60) ===")
for cat in sorted(revenue_correct):
    print(f"  {cat:14} revenue={round(revenue_correct[cat], 2):>6}  margin={round(margin_correct[cat], 2):>6}")

total_revenue = round(sum(f["revenue"] for f in fact_rows), 2)
print(f"\nTotal revenue (identical in both calculations): {total_revenue}")

What to expect:

=== CORRECT -- dim_product WITH time travel (P002 = snacks / 0.60) ===
  beverages      revenue=  44.05  margin=  14.75
  electronics    revenue=   40.5  margin=   21.6
  snacks         revenue=   21.6  margin=    10.8

Total revenue (identical in both calculations): 106.15

snacks, margin 10.8. Notice what changes and what doesn't, comparing the two blocks: beverages and electronics are identical in both calculations — P001, P003, and P004 never changed, so it makes no difference which dim_product snapshot you use — the difference is entirely in P002's category, which goes from health-snacks/9.36 to snacks/10.8 — a difference of 1.44 in margin, exactly what applying unit_cost=0.68 instead of unit_cost=0.60 to the 18 units of P002 sold produces. And the total revenue, 106.15, is identical in both calculations — the error never shows up in the number most people look at first.

The same two numbers, three different techniques

All three arrive at the same pair of numbers — 9.36 broken, 10.8 correct — with the same 106.15 total revenue in any variant. The first two techniques solve the problem by adding information to the table — columns someone has to declare, populate, and maintain on every MERGE. The third solves it without adding anything — the information was already available, filed away in the snapshot mechanism itself, with no one having had to ask Iceberg for it ahead of time.

Diagram: the JOIN, with the right photo behind it

flowchart LR
    F["kiosko.fact_orders\n40 rows, unchanged since M1"] --> J["JOIN by product_id"]
    D1["dim_product.scan()\nP002 = health-snacks/0.68"] -.->|"broken JOIN"| J
    D2["dim_product.scan(snapshot_id=snap_v1)\nP002 = snacks/0.60"] -.->|"correct JOIN"| J
    J --> M["margin by category"]
    M --> R1["health-snacks: 9.36 (BROKEN)"]
    M --> R2["snacks: 10.8 (CORRECT)"]

Going deeper: why this technique works so cleanly here

It's worth flagging, before lesson 7, why this calculation came out so clean: Kiosko's forty orders all happen between August 3 and 9, 2026, and the P002 change takes effect on August 15 — a date after every order, with no exception at all. That means there's a single snapshot — snap_v1 — that's correct for all forty rows of fact_orders at once, with no case-by-case exception. You didn't have to ask "is this specific order from before or after the change?" row by row — the answer was the same for all forty — so a single scan(snapshot_id=snap_v1) was enough for all of them. This condition — every relevant fact falls on the same side of a single dimension change — is what makes time travel, in this specific case, a perfect substitute for the point-in-time JOIN. Lesson 7 examines, with evidence, what happens once that condition stops holding.

Common mistakes

Forgetting this lesson's Python JOIN isn't the only way to do it, or the most efficient one at scale. What happens: someone, seeing this lesson's dim_by_id dict and its for f in fact_rows loop, assumes this is how a "real" JOIN against an Iceberg table is done. Why it happens: this guide, up to this point, hasn't introduced any SQL engine — DuckDB, Spark — able to run a declarative JOIN directly over table.scan()'s results. How to spot it: if you ask yourself how you'd do this same calculation with thousands of products or millions of orders, suspect this example's plain-Python loop isn't the production answer. How to fix it: in a real case, you'd take dim_product.scan(snapshot_id=snap_v1).to_arrow()'s and fact_orders.scan().to_arrow()'s results — both are already pyarrow.Tables — and join them with pyarrow.Table.join(), or load them into DuckDB (which can read a pyarrow.Table directly) to write the JOIN in declarative SQL, exactly as you already did in data-modeling. This lesson uses a plain-Python loop only to keep the example self-contained, without adding a new dependency to this guide just for a four-category calculation.

Thinking time travel "replaces" the concept of a point-in-time JOIN, instead of solving it a different way in this particular case. What happens: someone concludes, after seeing the same numbers recovered with less code, that data-modeling's JOIN BETWEEN valid_from AND valid_to no longer has any value. Why it happens: this lesson's result is, in fact, simpler to write than the full point-in-time JOIN. How to spot it: if you can't explain why this lesson's Going deeper section mentions "every order falls on the same side of a single change" as a favorable condition, you haven't seen the full limit yet. How to fix it: lesson 7, right after this one, exists exactly to close this idea — read it before drawing a general conclusion about when to use each technique.

Exercises

Exercise 1 — Reproduce both calculations yourself, and confirm the four numbers. With the complete state from lessons 2, 3, and 5 available, run this lesson's full script. Confirm you get health-snacks/9.36 in the broken calculation and snacks/10.8 in the correct one, with 106.15 total revenue in both.

Exercise 2 — Calculate Kiosko's total margin (all three categories added up), in both versions. Using the margin_broken and margin_correct results from this lesson's script, sum the margin across the three categories in each version. What's the difference between the two totals, and does it match the difference you already calculated for P002 individually?

total_margin_broken = round(sum(margin_broken.values()), 2)
total_margin_correct = round(sum(margin_correct.values()), 2)
print(total_margin_broken, total_margin_correct, round(total_margin_correct - total_margin_broken, 2))

Exercise 3 — Explain why revenue never changes between the broken and correct calculations, but margin does. In 2-3 sentences, explain why P002's revenue is 21.6 in both calculations, while the margin changes from 9.36 to 10.8.

Summary and next step

In this lesson you calculated Kiosko's three-category margin twice: once with dim_product's current state (health-snacks, margin 9.36, broken), and once with time travel to snap_v1 (snacks, margin 10.8, correct) — the same two numbers data-modeling-for-analytics-guide and dbt-analytics-engineering-guide already confirmed, now with a table of exactly four columns, none for history. You saw the three-technique comparison table — point-in-time JOIN, dbt snapshot, time travel — and a first hint at why this technique worked so cleanly in this particular case.

Before moving on you should be able to: join fact_orders against a historical version of dim_product recovered with time travel; explain the three-technique comparison table; and explain, in your own words, the condition that made time travel enough in this case — every order falls on the same side of a single change.

That condition doesn't always hold. Lesson 7 — this module's most important — shows, with a real, executed example, exactly what happens once it stops holding.

Resources

• PyIceberg — API reference, table.scan(snapshot_id=...).to_arrow(), the foundation of this lesson's JOIN. py.iceberg.apache.org/api. In English.
• data-modeling-for-analytics-guide DESIGN doc — source of the original point-in-time JOIN (f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, '9999-12-31')) and the canonical 9.36/10.8 numbers. src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.
• dbt-analytics-engineering-guide DESIGN doc — source of dbt snapshot and dbt_valid_from/dbt_valid_to, this lesson's comparison table's second technique. src/guides/dbt-analytics-engineering-guide/DISENO.md. In Spanish.
• This guide's DESIGN doc — the "Snapshots and time travel" section (M3), the source of the exact result this lesson verifies. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.