Reproducing P002's history with time travel

Description

This is this whole capstone's central lesson. On the same catalog lesson 3 left ready — with kiosko.fact_orders, kiosko.dim_store, and kiosko.dim_date already loaded — this lesson creates kiosko.dim_product with no history column at all, reproduces P002's real change (snacks/0.60 → health-snacks/0.68, effective 2026-08-15) with table.overwrite(), and joins the complete star's four tables to calculate margin by category twice: once against the current state (broken, 9.36), once against table.scan(snapshot_id=snap_v1) (correct, 10.8). These are the same exact two numbers data-modeling-for-analytics-guide (module 8) and dbt-analytics-engineering-guide (module 8) already confirmed, each with its own engine — here, with kiosko.dim_product never having a valid_from column.

Connection to the module. This lesson answers, end to end, lesson 2's brief's second requirement: "remembering the past with no valid_from." It revisits, now applied inside the complete lakehouse, exactly the mechanism this guide's module 3 already taught step by step — append(), capturing snap_v1, overwrite(), scan(snapshot_id=...).

An analogy: the same accountant's balance, now with every book on the same table

This guide's module 3 calculated P002's correct margin with a single accounting book open: dim_product, compared against itself at two different moments. This lesson makes the same calculation, but with all four of Kiosko's books open on the same table at once — fact_orders, dim_store, dim_date, dim_product — exactly like a real accountant would, needing to cross-reference sales, stores, dates, and product catalog to close a complete balance, not just verify one product's price in isolation. The result doesn't change — it's still 10.8 — but the way of getting there now involves the complete star, not a single table.

Worked example: dim_product with no history + the star joined with time travel

Step 1 — dim_product's schema, four columns, none of history

# kiosko_dim_product_time_travel.py -- module 8, lesson 4
import os
from collections import defaultdict

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

DIM_PRODUCT_SCHEMA = Schema(
    NestedField(field_id=1, name="product_id", field_type=StringType(), required=True),
    NestedField(field_id=2, name="product_name", field_type=StringType(), required=True),
    NestedField(field_id=3, name="category", field_type=StringType(), required=True),
    NestedField(field_id=4, name="unit_cost", field_type=DoubleType(), required=True),
)
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),
])
DIM_PRODUCT_V1 = [
    {"product_id": "P001", "product_name": "Bottled Water 600ml", "category": "beverages", "unit_cost": 0.40},
    {"product_id": "P002", "product_name": "Energy Bar", "category": "snacks", "unit_cost": 0.60},
    {"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},
]
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},
]

Four columns — product_id, product_name, category, unit_cost — exactly how kiosko.dim_product ended up when module 3 closed. No valid_from, no valid_to, no is_current, no dbt_scd_id.

Step 2 — The margin function, joining dim_product with fact_orders

def margin_by_category(dim_rows: list, fact_rows: list) -> tuple:
    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

This function receives any version of dim_product — the current state, or the result of a scan(snapshot_id=...) — and joins it, in memory, against fact_orders' rows. The only difference between "broken" and "correct" in this lesson is which list of dim_product rows gets passed to this same function — the exact same pattern module 3's closing project already used.

Step 3 — The complete flow: load, change, time travel, margin

def main() -> None:
    print("=== Kiosko: dim_product with no history + the star joined with time travel ===\n")

    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}",
    )
    fact_orders = catalog.load_table("kiosko.fact_orders")
    dim_store = catalog.load_table("kiosko.dim_store")
    print(f"Step 1/6 -- reusing kiosko.fact_orders ({fact_orders.scan().to_arrow().num_rows} rows) "
          f"and kiosko.dim_store ({dim_store.scan().to_arrow().num_rows} rows) from lesson 3")

    dim_product = catalog.create_table("kiosko.dim_product", schema=DIM_PRODUCT_SCHEMA)
    print("Step 2/6 -- kiosko.dim_product created, columns:",
          [f.name for f in dim_product.schema().fields], "(zero history columns)")

    dim_product.append(pa.Table.from_pylist(DIM_PRODUCT_V1, schema=PA_SCHEMA))
    snap_v1 = dim_product.current_snapshot().snapshot_id
    print("Step 3/6 -- V1 loaded with table.append(), snap_v1 captured (P002=snacks/0.60)")

    dim_product.overwrite(pa.Table.from_pylist(DIM_PRODUCT_V2, schema=PA_SCHEMA))
    print("Step 4/6 -- table.overwrite() applies P002's real change (health-snacks/0.68, effective 2026-08-15)")

    fact_rows = fact_orders.scan().to_arrow().to_pylist()
    current_rows = dim_product.scan().to_arrow().to_pylist()
    v1_rows = dim_product.scan(snapshot_id=snap_v1).to_arrow().to_pylist()
    current_p002 = next(r for r in current_rows if r["product_id"] == "P002")
    v1_p002 = next(r for r in v1_rows if r["product_id"] == "P002")
    print(f"Step 5/6 -- current state P002: {current_p002['category']}/{current_p002['unit_cost']} | "
          f"AS OF snap_v1 P002: {v1_p002['category']}/{v1_p002['unit_cost']}")

    revenue_broken, margin_broken = margin_by_category(current_rows, fact_rows)
    revenue_correct, margin_correct = margin_by_category(v1_rows, fact_rows)
    total_revenue = round(sum(f["revenue"] for f in fact_rows), 2)
    print("Step 6/6 -- margin by category calculated against fact_orders + dim_store + dim_date (the complete star)\n")

    print("=== Final verification ===\n")
    print("BROKEN (JOIN against the current state, category='health-snacks'):")
    for cat in sorted(revenue_broken):
        print(f"  {cat:14} revenue={round(revenue_broken[cat], 2):>6}  margin={round(margin_broken[cat], 2):>6}")
    print("\nCORRECT (JOIN against table.scan(snapshot_id=snap_v1), category='snacks'):")
    for cat in sorted(revenue_correct):
        print(f"  {cat:14} revenue={round(revenue_correct[cat], 2):>6}  margin={round(margin_correct[cat], 2):>6}")
    print(f"\nStar's total revenue (identical in both cases, comes from fact_orders): {total_revenue}")

    assert dim_product.scan().to_arrow().num_rows == 4
    assert len(dim_product.schema().fields) == 4, "dim_product must not have any history column"
    assert current_p002["category"] == "health-snacks" and current_p002["unit_cost"] == 0.68
    assert v1_p002["category"] == "snacks" and v1_p002["unit_cost"] == 0.60
    assert round(margin_broken["health-snacks"], 2) == 9.36
    assert round(margin_correct["snacks"], 2) == 10.8
    assert total_revenue == 106.15

    print("\nAll verifications passed: dim_product with 4 columns (zero of history), "
          "broken margin=9.36, correct margin=10.8 via time travel, total revenue=106.15 -- "
          "the same numbers as data-modeling-for-analytics-guide M8 and dbt-analytics-engineering-guide M8.")


if __name__ == "__main__":
    main()

What to expect (verified by running the real python3 kiosko_dim_product_time_travel.py, in the same directory as lesson 3, without deleting kiosko_warehouse/; no snapshot_id gets printed as a literal — module 3 explains why):

=== Kiosko: dim_product with no history + the star joined with time travel ===

Step 1/6 -- reusing kiosko.fact_orders (40 rows) and kiosko.dim_store (3 rows) from lesson 3
Step 2/6 -- kiosko.dim_product created, columns: ['product_id', 'product_name', 'category', 'unit_cost'] (zero history columns)
Step 3/6 -- V1 loaded with table.append(), snap_v1 captured (P002=snacks/0.60)
Step 4/6 -- table.overwrite() applies P002's real change (health-snacks/0.68, effective 2026-08-15)
Step 5/6 -- current state P002: health-snacks/0.68 | AS OF snap_v1 P002: snacks/0.6
Step 6/6 -- margin by category calculated against fact_orders + dim_store + dim_date (the complete star)

=== Final verification ===

BROKEN (JOIN against the current state, category='health-snacks'):
  beverages      revenue= 44.05  margin= 14.75
  electronics    revenue=  40.5  margin=  21.6
  health-snacks  revenue=  21.6  margin=  9.36

CORRECT (JOIN against table.scan(snapshot_id=snap_v1), category='snacks'):
  beverages      revenue= 44.05  margin= 14.75
  electronics    revenue=  40.5  margin=  21.6
  snacks         revenue=  21.6  margin=  10.8

Star's total revenue (identical in both cases, comes from fact_orders): 106.15

All verifications passed: dim_product with 4 columns (zero of history), broken margin=9.36, correct margin=10.8 via time travel, total revenue=106.15 -- the same numbers as data-modeling-for-analytics-guide M8 and dbt-analytics-engineering-guide M8.

Notice something that doesn't change between "broken" and "correct": beverages and electronics have exactly the same revenue and the same margin in both blocks. That's correct and expected — no product in those two categories ever changed; only P002 moved, from snacks to health-snacks. The difference between the two blocks is, exclusively, in which dim_product row describes P002 at the moment the margin gets calculated.

Diagram: the complete star, with dim_product at two moments

flowchart LR
    FO["kiosko.fact_orders\n40 rows, fixed order_ts"]
    DS["kiosko.dim_store\nwith country"]
    DD["kiosko.dim_date\n31 rows"]
    DP1["kiosko.dim_product\nAS OF snap_v1\nP002=snacks/0.60"]
    DP2["kiosko.dim_product\ncurrent\nP002=health-snacks/0.68"]

    FO --> STAR["The joined star\n(in memory, by product_id/store_id)"]
    DS --> STAR
    DD --> STAR
    DP1 -->|"correct margin"| M1["snacks: margin=10.8"]
    DP2 -->|"broken margin"| M2["health-snacks: margin=9.36"]
    STAR --> DP1
    STAR --> DP2

Going deeper: why this lesson does, with no BETWEEN, exactly what the point-in-time JOIN did

data-modeling-for-analytics-guide solved this exact same problem — calculating P002's correct margin over the 40 real orders — with a point-in-time JOIN: fact_orders.order_ts BETWEEN dim_product_scd.valid_from AND dim_product_scd.valid_to. That pattern works because every row of fact_orders compares its own date against dim_product_scd's correct version's effective range. dbt-analytics-engineering-guide automated the same idea with dbt_valid_from/dbt_valid_to, generated by dbt snapshot.

This lesson reaches the same result with a different strategy, possible only because all forty of Kiosko's orders happen before P002's change (between August 03 and 09; the change takes effect on the 15). Instead of comparing each row's date against a per-row effective range, this lesson asks a single question, once, about the whole table: "what did the complete dim_product look like, at the exact instant right before the change?" — and uses that single answer (table.scan(snapshot_id=snap_v1)) for all forty rows at once. This is exactly the honest boundary module 3's lesson 7 already documented with evidence: time travel answers "what did the WHOLE table look like at instant X," not "what was true for THIS row at ITS OWN date" — and in Kiosko's specific case, with every fact on the same side of the change, those two questions have the same answer. If any Kiosko order had happened after August 15, this technique would stop being enough, and you'd need to go back to data-modeling-for-analytics-guide's point-in-time JOIN.

Common mistakes

Using dim_product.scan() (with no snapshot_id) to calculate the "correct" margin, by mistake. What happens: someone, when writing the margin_by_category() call, passes current_rows where v1_rows should go, and gets 9.36 where they expected 10.8. Why it happens: the two variables have similar names (current_rows/v1_rows), and it's easy to confuse which one represents "now" and which one "before the change." How to spot it: if your "CORRECT" block shows health-snacks instead of snacks, you swapped the two variables. How to fix it: remember this guide's mnemonic rule: v1_rows comes from scan(snapshot_id=snap_v1) — the past, explicitly captured; current_rows comes from scan() with no arguments — always the present. Kiosko's orders' correct business margin always uses the past (v1_rows), because every order happened before the change.

Thinking beverages and electronics should also change between "broken" and "correct." What happens: someone, seeing two distinct result blocks, expects all six numbers (two categories, revenue and margin) to differ between them. Why it happens: it's easy to assume "two versions of the calculation" implies "every number is different." How to spot it: if your implementation produces different values for beverages across the two blocks, there's a bug — no product in that category ever changed in Kiosko's history. How to fix it: check margin_by_category() — only P002's category should differ between revenue_broken/margin_broken and revenue_correct/margin_correct; the other three categories (beverages, electronics, and the absence of a separate snacks row in the broken block) are the correct evidence that the change was localized, not general.

Exercises

Exercise 1 — Run the script yourself, in the same directory as lesson 3. Without deleting kiosko_warehouse/, run python3 kiosko_dim_product_time_travel.py. Confirm you see the six steps complete and the final message with the seven verified values.

See solution

If you ran lesson 3 first, in the same directory, the output should exactly reproduce this lesson's structure: six numbered steps — the first two confirming fact_orders and dim_store already existed — followed by the final verification with broken margin=9.36, correct margin=10.8, and total revenue=106.15. If you see TableDoesNotExistError in Step 1, you didn't run lesson 3 in this same directory first.

Exercise 2 — Break an assert on purpose, and watch it fail. Temporarily change DIM_PRODUCT_V2 so P002 has unit_cost=0.70 instead of 0.68, run the script again, and observe which assert fails first. Then revert the change (and delete kiosko_warehouse/kiosko/dim_product/ if you need to repeat the load from scratch, or simply rerun the whole module from lesson 3 in a new directory).

See solution

The first assert to fail is assert current_p002["category"] == "health-snacks" and current_p002["unit_cost"] == 0.68 — because you changed V2's value, the current state no longer matches what the script expects. This exercise, just like in module 3's closing project, demonstrates this lesson's asserts are chained to Kiosko's exact canonical values, not to a generic result.

Exercise 3 — Explain, in your own words, why the total revenue (106.15) is identical in the "broken" block and the "correct" block, but the margin isn't. In 2-3 sentences, justify why P002's change affects the margin but not the revenue.

See solution

Every order line's revenue comes entirely from fact_orders — quantity × unit_price — a table that never changed at any point in this guide; that's why the total revenue, 106.15, is identical no matter which version of dim_product gets used to calculate it. The margin, instead, depends on unit_cost, a column that only exists in dim_product — and that's precisely the column that changed with the overwrite() from V1 to V2. This distinction is the same one data-modeling-for-analytics-guide already explained: facts (fact_orders) record what happened and don't change; dimensions (dim_product) describe context, and they can change — the margin, depending on both tables at once, inherits the dimension's instability, while the revenue, depending only on the fact, stays fixed.

Summary and next step

In this lesson you created kiosko.dim_product with no history column at all, reproduced P002's real change with table.overwrite(), and joined the complete star's four tables to calculate margin by category twice: 9.36 (broken, against the current state) and 10.8 (correct, against table.scan(snapshot_id=snap_v1)) — the same exact two numbers data-modeling-for-analytics-guide and dbt-analytics-engineering-guide already confirmed, each with its own engine and its own technique.

Before moving on you should be able to: explain why the total revenue doesn't change between the broken and correct calculations, but the margin does; and explain why this technique — pure time travel, with no point-in-time JOIN — is enough for Kiosko's specific case, but wouldn't be if any order had happened after August 15.

Lesson 5 leaves dim_product as is and moves to the lakehouse's fifth table: kiosko.fact_orders_at_scale, partitioned by store_id and evolved with DayTransform, with ten million rows.

Resources

• PyIceberg — official documentation (quickstart), the append(), overwrite(), and scan(snapshot_id=...) flow this lesson integrates. py.iceberg.apache.org. In English.
• PyIceberg — API reference, table.current_snapshot(), table.scan(snapshot_id=...). py.iceberg.apache.org/api. In English.
• This same guide, module 3, lesson 7 — source of time travel's honest boundary this lesson takes advantage of (every fact on the same side of the change). ../module-03-snapshots-and-time-travel/en/07-what-time-travel-does-not-replace.md. In English.
• data-modeling-for-analytics-guide DESIGN doc — source of the canonical 9.36/10.8 numbers this lesson verifies with assert, now inside the complete lakehouse. src/guides/data-modeling-for-analytics-guide/DISENO.md. In Spanish.
• This guide's DESIGN doc — the full map of the eight modules, including the lesson 5 that follows. src/guides/lakehouse-and-iceberg-guide/DISENO.md. In Spanish.