Mini-project: Kiosko's correct historical revenue

Description

This project closes the module by integrating the six previous pieces: the fan-out of an unfiltered JOIN (lesson 2), the contrast between the JOIN broken by is_current and the correct point-in-time one (lesson 3), the risk of a late-arriving dimension (lesson 4), diagnosing a batch with duplicate rows (lesson 5), deduplicating it with ROW_NUMBER()/QUALIFY (lesson 6), and ANTI JOIN/SEMI JOIN for detecting orphans and changes (lesson 7). What's left is bringing it all together into a single formal delivery, verified end to end with assert, over the same fact_orders (40 rows) and dim_product_scd (5 rows) this module has used since lesson 2.

The project has seven parts. First, you rebuild fact_orders and dim_product_scd, inherited unchanged. Second, you formally reject lesson 2's naive JOIN (fan-out, inflated revenue). Third, you run the broken JOIN (is_current) and measure its misattribution. Fourth, you run the correct JOIN (point-in-time) and confirm total revenue is identical, but category and margin aren't. Fifth, you deduplicate a batch with three resent rows. Sixth, you run lesson 7's ANTI JOINs over two scenarios. Seventh, you document everything in POINT_IN_TIME_SUMMARY, the formal structure that closes the module.

Connection to the module. This project introduces no new concept — it's the final integration of the seven previous lessons, packaged as POINT_IN_TIME_SUMMARY, the structure this guide's module 6 can cite without rebuilding the evidence from scratch.

An analogy: the complete audit, before publishing the report

Each lesson in this module solved one piece of the problem separately: the way of joining that breaks the count, the way that fixes the count but misattributes, the one that attributes correctly but depends on the dimension being current, and cleaning up repeated rows before any of those ways of joining even matters. This project is the moment to run the complete audit: all those pieces, assembled into a single flow, with every step verified by an assert before moving to the next one — exactly the rigor anyone would expect before publishing a historical revenue report Kiosko's finance team is going to use to make real decisions.

The material: everything this module built, in a single flow

You need, in the same folder: kiosko.py, raw_orders.py (identical to the previous modules) and dim_product_scd.py (this module's new file, introduced in lesson 2, with DIM_PRODUCT_SCD_ROWS). You don't need any additional file — the resent batch and the demo orders are defined directly in this project's script, same as in the previous projects.

The reference solution, verified

Part 1 — Rebuild fact_orders and dim_product_scd, inherited unchanged

# kiosko_pit_project.py -- correct historical revenue, module 5's closing mini-project
from datetime import datetime, timedelta
import duckdb

from kiosko import DIM_PRODUCT, DIM_STORE, Order, transform_fact_orders
from raw_orders import RAW_ORDERS
from dim_product_scd import DIM_PRODUCT_SCD_ROWS

print("=== Kiosko: correct historical revenue, module 5 final delivery ===\n")

orders = [
    Order(order_id=r[0], store_id=r[1], product_id=r[2], quantity=r[3],
          unit_price=r[4], order_ts=datetime.fromisoformat(r[5]))
    for r in RAW_ORDERS
]
fact_orders = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)

con = duckdb.connect()
con.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.executemany(
    "INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["order_id"], r["store_id"], r["product_id"], r["quantity"],
      r["unit_price"], r["revenue"], r["order_ts"]) for r in fact_orders],
)

con.execute("""
    CREATE TABLE dim_product_scd (
        product_key  INTEGER PRIMARY KEY,
        product_id   VARCHAR NOT NULL,
        product_name VARCHAR,
        category     VARCHAR,
        unit_cost    DOUBLE,
        valid_from   DATE NOT NULL,
        valid_to     DATE,
        is_current   BOOLEAN NOT NULL DEFAULT true
    )
""")
con.executemany("INSERT INTO dim_product_scd VALUES (?, ?, ?, ?, ?, ?, ?, ?)", DIM_PRODUCT_SCD_ROWS)

total_orders = con.sql("SELECT COUNT(*) FROM fact_orders").fetchone()[0]
total_dim_rows = con.sql("SELECT COUNT(*) FROM dim_product_scd").fetchone()[0]
total_dim_products = con.sql("SELECT COUNT(DISTINCT product_id) FROM dim_product_scd").fetchone()[0]

print("Part 1 -- fact_orders and dim_product_scd, inherited unchanged")
print(f"  fact_orders       {total_orders:3} rows")
print(f"  dim_product_scd   {total_dim_rows:3} rows ({total_dim_products} products, 1 historized)")

This first part builds nothing new — it rebuilds, exactly as in every lesson in this module, the two input tables modules 1 and 4 left ready.

Part 2 — The naive JOIN (lesson 2): rejected, with the number that proves it

naive = con.sql("""
    SELECT COUNT(*) AS rows_, ROUND(SUM(f.revenue), 2) AS inflated_revenue
    FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id
""").fetchone()

print("\nPart 2 -- naive JOIN (product_id only): the fan-out, REJECTED")
print(f"  resulting rows: {naive[0]} (should be {total_orders}) -- inflated revenue: {naive[1]}")
assert naive[0] == 50 and naive[1] == 127.75, "the fan-out did not behave as expected"

Exactly lesson 2's result: 50 rows, revenue inflated to 127.75. This project doesn't use this way of joining for anything else — it stays documented as the evidence for why it's rejected, not as a viable option.

Part 3 — The broken JOIN (lesson 3): no fan-out, misattributed

broken = con.sql("""
    SELECT d.category, ROUND(SUM(f.revenue), 2) AS revenue, ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
    GROUP BY d.category ORDER BY d.category
""").fetchall()

print("\nPart 3 -- broken JOIN (is_current = true): no fan-out, but misattributed")
for category, revenue, margin in broken:
    print(f"  {category:14} revenue={revenue:7}  margin={margin:6}")

Part 3 -- broken JOIN (is_current = true): no fan-out, but misattributed
  beverages      revenue=  44.05  margin= 14.75
  electronics    revenue=   40.5  margin=  21.6
  health-snacks  revenue=   21.6  margin=  9.36

Part 4 — The correct JOIN (lesson 3): point-in-time

correct = con.sql("""
    SELECT d.category, ROUND(SUM(f.revenue), 2) AS revenue, ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    GROUP BY d.category ORDER BY d.category
""").fetchall()

print("\nPart 4 -- correct JOIN (point-in-time)")
for category, revenue, margin in correct:
    print(f"  {category:14} revenue={revenue:7}  margin={margin:6}")

broken_categories = {c for c, _, _ in broken}
correct_categories = {c for c, _, _ in correct}
assert "health-snacks" in broken_categories and "health-snacks" not in correct_categories
assert "snacks" in correct_categories and "snacks" not in broken_categories
print("  Check: 'health-snacks' (broken) vs 'snacks' (correct) -- same money, different category")

total_broken = con.sql("""
    SELECT ROUND(SUM(f.revenue), 2)
    FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
""").fetchone()[0]
total_correct = con.sql("""
    SELECT ROUND(SUM(f.revenue), 2)
    FROM fact_orders f JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
""").fetchone()[0]
assert total_broken == total_correct == 106.15
print(f"  Total revenue: identical in both cases -- {total_correct}")

Part 4 -- correct JOIN (point-in-time)
  beverages      revenue=  44.05  margin= 14.75
  electronics    revenue=   40.5  margin=  21.6
  snacks         revenue=   21.6  margin=  10.8
  Check: 'health-snacks' (broken) vs 'snacks' (correct) -- same money, different category
  Total revenue: identical in both cases -- 106.15

This part's two asserts are the project's heart: they confirm, with evidence and not on anyone's word, that health-snacks only shows up on the broken side, snacks only on the correct side, and that total revenue — 106.15 — never moved, in either case.

Part 5 — Deduplicating a batch with 3 resent rows (lessons 5 and 6)

con.execute("""
    CREATE TABLE raw_orders_batch (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE,
        order_ts TIMESTAMP, ingested_at TIMESTAMP
    )
""")
rows = []
for r in RAW_ORDERS:
    order_id, store_id, product_id, quantity, unit_price, order_ts_str = r
    order_ts = datetime.fromisoformat(order_ts_str)
    ingested_at = order_ts + timedelta(minutes=1)
    rows.append((order_id, store_id, product_id, quantity, unit_price, quantity * unit_price, order_ts, ingested_at))

RESEND_DELAY = {"ORD-1001": timedelta(minutes=35), "ORD-3001": timedelta(minutes=54), "ORD-6005": timedelta(minutes=49)}
for r in RAW_ORDERS:
    order_id, store_id, product_id, quantity, unit_price, order_ts_str = r
    if order_id in RESEND_DELAY:
        order_ts = datetime.fromisoformat(order_ts_str)
        resend_ingest = order_ts + timedelta(minutes=1) + RESEND_DELAY[order_id]
        rows.append((order_id, store_id, product_id, quantity, unit_price, quantity * unit_price, order_ts, resend_ingest))
con.executemany("INSERT INTO raw_orders_batch VALUES (?, ?, ?, ?, ?, ?, ?, ?)", rows)

before = con.sql("SELECT COUNT(*) FROM raw_orders_batch").fetchone()[0]
after = con.sql("""
    WITH deduped AS (
        SELECT * FROM raw_orders_batch
        QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) = 1
    )
    SELECT COUNT(*) FROM deduped
""").fetchone()[0]

print("\nPart 5 -- Deduplicating a batch with 3 resent rows")
print(f"  rows before deduplicating: {before}")
print(f"  rows after QUALIFY ROW_NUMBER() = 1: {after}")
assert before == 43 and after == 40
print("  Check OK: the grain matches fact_orders again (40)")

Part 5 -- Deduplicating a batch with 3 resent rows
  rows before deduplicating: 43
  rows after QUALIFY ROW_NUMBER() = 1: 40
  Check OK: the grain matches fact_orders again (40)

Part 6 — Anti-joins: orphans and what changed (lesson 7)

con.execute("""
    CREATE TABLE orphan_order (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.execute("INSERT INTO orphan_order VALUES ('ORD-9001', 'S02', 'P002', 1, 1.20, 1.20, '2026-07-30T09:00:00')")

orphans_in_week = con.sql("""
    SELECT COUNT(*) FROM fact_orders f
    ANTI JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
""").fetchone()[0]
orphans_demo = con.sql("""
    SELECT COUNT(*) FROM orphan_order o
    ANTI JOIN dim_product_scd d
        ON o.product_id = d.product_id
       AND o.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
""").fetchone()[0]

print("\nPart 6 -- Anti-joins: orphans and what changed")
print(f"  orphans within Kiosko's real week (40 orders): {orphans_in_week}")
print(f"  orphans in the demo batch (ORD-9001, before 2026-08-01): {orphans_demo}")
assert orphans_in_week == 0 and orphans_demo == 1

con.execute("""
    CREATE TABLE staging_product_v3 (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)
""")
con.executemany(
    "INSERT INTO staging_product_v3 VALUES (?, ?, ?, ?)",
    [
        ("P001", "Bottled Water 600ml", "beverages", 0.40),
        ("P002", "Energy Bar", "health-snacks", 0.72),
        ("P003", "Instant Coffee Sachet", "beverages", 0.35),
        ("P004", "Phone Charger Cable", "electronics", 2.10),
    ],
)
changed = con.sql("""
    SELECT s.product_id FROM staging_product_v3 s
    ANTI JOIN dim_product_scd d
        ON s.product_id = d.product_id AND d.is_current = true
       AND s.category = d.category AND s.unit_cost = d.unit_cost
""").fetchall()
print(f"  products that changed in staging_product_v3 (before running the MERGE): {[c[0] for c in changed]}")
assert [c[0] for c in changed] == ["P002"]

Part 6 -- Anti-joins: orphans and what changed
  orphans within Kiosko's real week (40 orders): 0
  orphans in the demo batch (ORD-9001, before 2026-08-01): 1
  products that changed in staging_product_v3 (before running the MERGE): ['P002']

Part 7 — Document it as a formal structure

POINT_IN_TIME_SUMMARY = {
    "fact_orders_rows": total_orders,
    "dim_product_scd_rows": total_dim_rows,
    "naive_join_product_id_only_rows": naive[0],
    "naive_join_revenue_inflated": naive[1],
    "broken_join_is_current_only_category_for_p002": "health-snacks",
    "correct_join_point_in_time_category_for_p002": "snacks",
    "total_revenue_broken_vs_correct": [total_broken, total_correct],
    "margin_p002_broken_vs_correct": [9.36, 10.8],
    "dedup_batch_rows_before_after": [before, after],
    "orphan_orders_in_kiosko_week": orphans_in_week,
    "orphan_orders_in_demo_batch": orphans_demo,
    "antijoin_detected_changes": [c[0] for c in changed],
}
print("\nPart 7 -- the formal declaration: POINT_IN_TIME_SUMMARY")
for key, value in POINT_IN_TIME_SUMMARY.items():
    print(f"  {key}: {value}")

What to expect. Running the complete python3 kiosko_pit_project.py (all seven parts together), the output is exactly this:

=== Kiosko: correct historical revenue, module 5 final delivery ===

Part 1 -- fact_orders and dim_product_scd, inherited unchanged
  fact_orders        40 rows
  dim_product_scd     5 rows (4 products, 1 historized)

Part 2 -- naive JOIN (product_id only): the fan-out, REJECTED
  resulting rows: 50 (should be 40) -- inflated revenue: 127.75

Part 3 -- broken JOIN (is_current = true): no fan-out, but misattributed
  beverages      revenue=  44.05  margin= 14.75
  electronics    revenue=   40.5  margin=  21.6
  health-snacks  revenue=   21.6  margin=  9.36

Part 4 -- correct JOIN (point-in-time)
  beverages      revenue=  44.05  margin= 14.75
  electronics    revenue=   40.5  margin=  21.6
  snacks         revenue=   21.6  margin=  10.8
  Check: 'health-snacks' (broken) vs 'snacks' (correct) -- same money, different category
  Total revenue: identical in both cases -- 106.15

Part 5 -- Deduplicating a batch with 3 resent rows
  rows before deduplicating: 43
  rows after QUALIFY ROW_NUMBER() = 1: 40
  Check OK: the grain matches fact_orders again (40)

Part 6 -- Anti-joins: orphans and what changed
  orphans within Kiosko's real week (40 orders): 0
  orphans in the demo batch (ORD-9001, before 2026-08-01): 1
  products that changed in staging_product_v3 (before running the MERGE): ['P002']

Part 7 -- the formal declaration: POINT_IN_TIME_SUMMARY
  fact_orders_rows: 40
  dim_product_scd_rows: 5
  naive_join_product_id_only_rows: 50
  naive_join_revenue_inflated: 127.75
  broken_join_is_current_only_category_for_p002: health-snacks
  correct_join_point_in_time_category_for_p002: snacks
  total_revenue_broken_vs_correct: [106.15, 106.15]
  margin_p002_broken_vs_correct: [9.36, 10.8]
  dedup_batch_rows_before_after: [43, 40]
  orphan_orders_in_kiosko_week: 0
  orphan_orders_in_demo_batch: 1
  antijoin_detected_changes: ['P002']

Stop at Part 4 and Part 7 together, because they're the ones that summarize the whole module in a single picture. This part's two asserts confirm, with executed evidence, this module's central claim: total revenue never lies (106.15 in both cases), but category and margin do, if the JOIN is written wrong. And POINT_IN_TIME_SUMMARY gathers, into a single structure, every number the six previous lessons measured separately — from lesson 2's fan-out to lesson 7's ANTI JOINs.

Diagram: the module's six pieces, closed with evidence

flowchart TD
    A["L2: unfiltered JOIN\nVERIFIED -- fan-out, 50 rows"] --> B
    B["L3: is_current vs point-in-time\nVERIFIED -- health-snacks vs snacks"] --> C
    C["L4: late arrival\nVERIFIED -- same JOIN, different dimension"] --> D
    D["L5: where the duplicates come from\nVERIFIED -- 40 -> 43 rows"] --> E
    E["L6: QUALIFY + ROW_NUMBER\nVERIFIED -- 43 -> 40 rows"] --> F
    F["L7: ANTI JOIN / SEMI JOIN\nVERIFIED -- orphans and changes detected"] --> G
    G["POINT_IN_TIME_SUMMARY\nthe formal contract this project delivers"]
    G --> H["Module 6: accumulating snapshot\nover fact_sessions"]

Closing module 1's checklist, piece by piece

Six of the eight rows are already resolved. Module 6, next on the list, needs fact_orders and dim_product_scd exactly as they were left — unchanged — plus the events foundations already generated, to build fact_sessions, the accumulating snapshot for Kiosko's session funnel. None of this module's techniques (point-in-time join, deduplication, ANTI JOIN) get discarded going forward — any fact this warehouse adds from here on may need to join against dim_product_scd with the same pattern, or arrive with its own duplicates to deduplicate the same way.

Common mistakes

Delivering POINT_IN_TIME_SUMMARY without Parts 4, 5, and 6's asserts. What happens: someone, in a hurry to show off the summary structure as the final result, builds POINT_IN_TIME_SUMMARY right after running the queries, without going through the asserts that confirm each number. Why it happens: the summary structure looks more presentable as "the deliverable," and the asserts feel like disposable preliminary steps. How to spot it: if your final delivery includes no executed evidence that health-snacks only shows up on the broken side, that the deduplicated batch is back to 40 rows, and that the ANTI JOIN found exactly the expected orphans, you're documenting a process without having confirmed it worked. How to fix it: this project's asserts aren't optional — they're the guarantee that makes everything POINT_IN_TIME_SUMMARY documents trustworthy.

Assuming this project replaces the JOIN the rest of the guide uses for reports that don't need history. What happens: someone, finishing this project, assumes that from here on the whole guide should use the point-in-time join against dim_product_scd, even for reports that only describe the catalog's current state, where dim_product (with no history) or is_current = true are still perfectly correct. Why it happens: after an entire module dedicated to showing that is_current can be wrong, it's easy to overgeneralize to "never use is_current." How to spot it: if you expect a "Kiosko's current catalog today" report — with no relation to any dated sale — to use the BETWEEN valid_from AND valid_to pattern, you lost sight of lesson 3's distinction: that pattern is for facts with their own date, is_current is still correct for queries about the present with no relation to a historical event. How to fix it: the right question is still "what does each row of the report describe?" — a past event needs the point-in-time join; the catalog's present state doesn't.

Thinking this project exhausted every possible duplication or late-arrival scenario. What happens: someone finishes this project thinking they've seen "every case" of deduplication or outdated dimensions, without considering scenarios Kiosko didn't have in this module — duplicates with different values between copies (corrections, not exact resends), a dimension with more than one product changing on the same day, or a MERGE that runs partway and fails halfway through. Why it happens: a complete, well-verified example can feel like "the general case" when it's actually a specific and deliberately simple one. How to spot it: if you can't explain how your deduplication criterion would change if two copies of the same order had different values (not identical ones), or how lesson 7's ANTI JOIN would detect two products changing on the same day instead of one, you're missing variations this project deliberately didn't cover. How to fix it: this project solves, with complete evidence, the case Kiosko needed — a dimension with one historized product, a batch with three exact resends; more complex scenarios are extensions of the same pattern, not different techniques.

Exercises

Exercise 1 — Verify Kiosko's total margin (all three categories summed) also differs between the broken and correct JOIN. Using the already-built fact_orders and dim_product_scd, write a query that sums the margin across the three categories in each scenario and confirms the exact difference.

print(con.sql("""
    SELECT
        (SELECT ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2)
         FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true) AS broken_total_margin,
        (SELECT ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2)
         FROM fact_orders f JOIN dim_product_scd d
            ON f.product_id = d.product_id
           AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')) AS correct_total_margin
"""))

┌──────────────────────┬───────────────────────┐
│ broken_total_margin  │ correct_total_margin  │
│        double        │         double        │
├──────────────────────┼────────────────────────┤
│                 45.71 │                 47.15 │
└──────────────────────┴────────────────────────┘

Exercise 2 — Extend POINT_IN_TIME_SUMMARY with a field that explicitly confirms total revenue never changed in any scenario in this project. Add a revenue_invariant_across_all_scenarios field that compares total revenue across the four scenarios this project touched: the broken JOIN, the correct one, the deduplicated batch, and fact_orders with no JOIN at all.

revenue_no_join = con.sql("SELECT ROUND(SUM(revenue), 2) FROM fact_orders").fetchone()[0]
revenue_deduped = con.sql("""
    WITH deduped AS (
        SELECT * FROM raw_orders_batch
        QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) = 1
    )
    SELECT ROUND(SUM(revenue), 2) FROM deduped
""").fetchone()[0]

POINT_IN_TIME_SUMMARY["revenue_invariant_across_all_scenarios"] = {
    "fact_orders_no_join": revenue_no_join,
    "broken_join_is_current": total_broken,
    "correct_join_point_in_time": total_correct,
    "deduped_batch": revenue_deduped,
}
print(f"revenue_invariant_across_all_scenarios: {POINT_IN_TIME_SUMMARY['revenue_invariant_across_all_scenarios']}")

revenue_invariant_across_all_scenarios: {'fact_orders_no_join': 106.15, 'broken_join_is_current': 106.15, 'correct_join_point_in_time': 106.15, 'deduped_batch': 106.15}

Exercise 3 — Explain, from memory, what module 6 needs from this project to be able to start. Without looking at the guide's design, describe in a 4-6 sentence paragraph what pieces of fact_orders, dim_product_scd, or POINT_IN_TIME_SUMMARY module 6 is going to need to build fact_sessions, the accumulating snapshot for Kiosko's session funnel.

Summary and next step: the end of module 5

With this mini-project you close module 5 completely. You rejected, with evidence, the unfiltered JOIN (fan-out, 50 rows); contrasted the JOIN broken by is_current against the correct point-in-time one, confirming total revenue never changes but category and margin do (health-snacks/9.36 versus snacks/10.8); deduplicated a real batch with three resends, restoring the forty-row grain; and ran ANTI JOIN/SEMI JOIN over two scenarios — temporal orphans and change detection. You documented everything in POINT_IN_TIME_SUMMARY, verified with assert at every step.

You took the fifth step of an eight-module path: fact_orders and dim_product_scd now join with the correct, point-in-time pattern, with deduplication and change detection already solved — the foundation any future fact in this warehouse can build on with confidence.

Where you go next. Module 6 — accumulating-and-cumulative-patterns — changes topic completely: instead of joining facts against dimensions, it builds two different fact table patterns — Kimball's accumulating snapshot, applied to Kiosko's session funnel over foundations's events, and Zach Wilson/DataExpert's cumulative table design, applied to daily per-store activity with array-typed columns and rolling 7- and 30-day windows.

Resources

• Kimball Group — "Slowly Changing Dimension Type 2" — the formal definition behind dim_product_scd, verified in each of this project's seven parts. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English.
• Kimball Group — "Late Arriving Dimension" — the technique behind this project's Part 6, the orphan-orders and change-detection scenario. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/late-arriving-dimension. In English.
• DuckDB — FROM and JOIN clause documentation (including SEMI JOIN/ANTI JOIN) — the syntax reference for this project's Part 6. duckdb.org/docs/current/sql/query_syntax/from. In English.
• DuckDB — QUALIFY clause and window functions documentation — the syntax reference for this project's Part 5. duckdb.org/docs/current/sql/query_syntax/qualify and duckdb.org/docs/current/sql/functions/window_functions. In English.
• DuckDB — official Python client documentation, the interface that ran every check in this project. duckdb.org/docs/current/clients/python/overview. In English.