Module 8: Project Kioskos Analytics Warehouse

The brief: Kiosko needs a real warehouse, not seven demos

Next →

Description

Kiosko's management — the same operations manager who, in foundations, used to ask for a weekly report by hand — now has a different problem. The seven previous modules each gave her, separately, a flawless piece: a declared grain, a star schema, a historized dimension, a corrected join, a session funnel, accumulated activity. But when she asked the data team for "a report that combines all of that," the answer was "not yet possible — each piece lives in its own script, with its own DuckDB connection, and none knows the others exist." This lesson turns that frustration into a concrete brief: exactly what questions the integrated warehouse has to answer, with what evidence, and who's going to query it.

Connection to the module. Just like in foundations, this lesson builds no layer yet — that starts in lesson 3. Its job is to translate a business frustration into a list of verifiable requirements, each one mapped to the lesson in this module that solves it.

An analogy: the architect with seven loose blueprints, no master plan

Imagine an architect who designed, separately and with real technical excellence, a building's foundation, each floor's structure, the electrical system, the plumbing, and the facade design — five blueprints, each perfect on its own sheet of paper. The client, seeing the five blueprints on the table, asks something reasonable: "where's the blueprint showing how they all fit together, in the same building, without the plumbing running through a structural column?" That question doesn't question any individual blueprint's quality — it questions whether anyone already verified that, together, they form a buildable structure.

This lesson's "master plan" is exactly that document Kiosko is missing: not a new blueprint, but confirmation that the seven already-drawn blueprints fit together, in the correct order, with no piece breaking another when built next to it.

The brief, in management's words

"Each module showed me something different, and each one convinced me separately. But I need one single thing: to open a warehouse, run a few queries, and answer three questions my team asks me every week. First, how much did we really sell, by product category — including what happened when we renamed the energy bars in August? Second, how well do our app's sessions convert, from someone looking at a product to buying it? Third, how active was each store this week and over the last month? And I need the BI team to be able to query all of this without having to understand SCD, or point-in-time JOIN, or any of the technical words you all use."

Four concrete requirements come out of that paragraph, and each one maps to a piece you already built in a previous module:

Management requirementPiece you already builtModule
"revenue by category, including the August change"dim_product_scd + point-in-time joinModules 4 and 5
"how well do sessions convert"fact_sessions, the accumulating snapshotModule 6
"how active each store was"fact_store_activity, the cumulative designModule 6
"without having to understand SCD or point-in-time JOIN"an already-resolved wide table, without BI writing the BETWEENModule 3, rebuilt in this module

The last row is the one this module truly adds: no previous lesson served the point-in-time join's result already resolved in a wide table, ready for someone with no SCD knowledge to query with a simple GROUP BY. That's exactly what this module's lesson 6 builds.

Worked example: confirming what material you have before integrating

Before promising anything to management, confirm the seven pieces from the previous modules are available, with the exact numbers you already know, so this module's integration doesn't depend on memory but on evidence.

# confirm_material.py
MODULE_DELIVERABLES = [
    ("Module 1", "fact_orders declared",        "40 rows, revenue 106.15"),
    ("Module 2", "complete star schema",         "dim_store (3), dim_date (31), 3 JOINs, 40==40"),
    ("Module 3", "star vs snowflake vs OBT",     "106.15 identical across the 3 shapes"),
    ("Module 4", "historized dim_product_scd",   "5 rows, P002 in 2 versions (snacks->health-snacks)"),
    ("Module 5", "correct historical revenue",   "snacks/10.8 margin (correct) vs health-snacks/9.36 (broken)"),
    ("Module 6", "fact_sessions + fact_store_activity", "funnel 17->9->6 (35.3%), 7d/30d actives"),
    ("Module 7", "dim_order_flags + Medallion contract", "6 rows, 4 gold tables, 0 discrepancies"),
]

print("=== Kiosko: confirming the material from the 7 modules, before integrating ===\n")
for module, deliverable, evidence in MODULE_DELIVERABLES:
    print(f"{module:10} {deliverable:38} {evidence}")

print("\nWhat's really missing: NO new modeling piece -- they just need assembling into one warehouse.")
print("What this module adds: mart_daily_sales_obt with the point-in-time join already resolved.")

What to expect. Running python3 confirm_material.py, the output is exactly this:

=== Kiosko: confirming the material from the 7 modules, before integrating ===

Module 1   fact_orders declared                  40 rows, revenue 106.15
Module 2   complete star schema                  dim_store (3), dim_date (31), 3 JOINs, 40==40
Module 3   star vs snowflake vs OBT               106.15 identical across the 3 shapes
Module 4   historized dim_product_scd             5 rows, P002 in 2 versions (snacks->health-snacks)
Module 5   correct historical revenue             snacks/10.8 margin (correct) vs health-snacks/9.36 (broken)
Module 6   fact_sessions + fact_store_activity     funnel 17->9->6 (35.3%), 7d/30d actives
Module 7   dim_order_flags + Medallion contract    6 rows, 4 gold tables, 0 discrepancies

What's really missing: NO new modeling piece -- they just need assembling into one warehouse.
What this module adds: mart_daily_sales_obt with the point-in-time join already resolved.

Seven rows, seven concrete numeric pieces of evidence, none invented anew here — each one is, literally, the already-verified result from its own module's closing project. Notice the pattern: this brief doesn't ask to "build something new" in the sense of a concept — it asks to bring together what already exists, in a way that answers three concrete business questions without anyone on the BI side having to write a MERGE INTO or a BETWEEN valid_from AND valid_to on their own.

Diagram: from business frustration to technical brief

flowchart TD
    A["'Each module convinced me\nseparately, but I need\nONE warehouse, not seven demos'"] --> B["Technical brief (this lesson)"]
    B --> C["revenue by category, with the August\nchange -> dim_product_scd + PIT join\n(modules 4-5)"]
    B --> D["funnel conversion\n-> fact_sessions (module 6)"]
    B --> E["store activity\n-> fact_store_activity (module 6)"]
    B --> F["queryable without SCD or PIT JOIN\n-> mart_daily_sales_obt (NEW, lesson 6)"]
    C --> G["Integrated warehouse, one single flow"]
    D --> G
    E --> G
    F --> G

Going deeper: why "without having to understand SCD" is the brief's most important requirement

Notice the last sentence in management's quote: "without having to understand SCD, or point-in-time JOIN." That sentence, almost in passing, is the one that fully justifies this module's lesson 6. Everything you built in modules 4 and 5 — dim_product_scd, the BETWEEN valid_from AND valid_to — is exactly correct, but it requires whoever writes the query to know a historized dimension exists and to know how to avoid the mistake of joining by is_current alone. That's reasonable to ask of someone on the data team, who already went through this guide's modules 4 and 5. It isn't reasonable to ask of the BI team, who just wants to open a dashboard and drag category into a column.

This is the underlying reason a wide table (OBT) has real value, beyond the performance comparison you already saw in module 3: the OBT isn't only faster to query — it also encapsulates a correct modeling decision (the point-in-time join) so whoever queries it doesn't have to make it again, nor risk making it wrong. mart_daily_sales_obt, published with the correct join already resolved, is, in a very concrete sense, a way for the BI team to benefit from everything you learned in module 5 without having to learn it themselves.

Common mistakes

Interpreting the brief as a request for new modeling code. What happens: someone, reading "I need a real warehouse," assumes the brief asks to invent an additional modeling technique the seven previous modules didn't cover. Why it happens: "real warehouse" sounds like something bigger than what's already built, and it's easy to confuse "bigger" with "more techniques." How to spot it: if your plan for this module includes researching a dimensional-modeling technique that doesn't appear in any previous module, you drifted from the brief — reread this lesson's table, the seven rows already cover everything the business asked for. How to fix it: the brief asks for integration, not invention — management's four requirements get solved by combining already-built pieces, not by designing an eighth one.

Assuming "without SCD or point-in-time JOIN" means the OBT can ignore historization. What happens: someone, rushing to simplify, builds mart_daily_sales_obt by joining against dim_product (module 2's static version), reasoning that this way "there's no need to deal with SCD." Why it happens: the static version is, literally, simpler to join — a single JOIN by product_id, no BETWEEN at all. How to spot it: if your OBT shows P002 always as health-snacks (today's current category), regardless of the sale's date, you made exactly the mistake module 5 taught you to avoid — only now hidden inside a wide table instead of a direct report. How to fix it: "without the BI team having to understand SCD" doesn't mean "without applying SCD" — it means whoever builds the OBT (you, in lesson 6) applies the point-in-time join for them, so the table they query already has the correct category without them having to write the JOIN themselves.

Treating the brief's three questions as independent of each other. What happens: someone builds three completely separate reports — one for revenue, one for the funnel, one for activity — with no relationship between them, missing the opportunity to show how they connect within the same warehouse. Why it happens: each brief question mentions a different table (mart_daily_sales_obt, fact_sessions, fact_store_activity), so treating them as three separate deliverables feels natural. How to spot it: if your final warehouse has no dimension shared across the three answers — for example, if fact_sessions and mart_daily_sales_obt don't share store_id from the same dim_store — you lost the most valuable property of a real dimensional warehouse: the conformed dimensions module 2 taught you to build. How to fix it: the brief's three answers share dim_store and, in spirit, the same dim_date calendar — build them over the same conformed dimensions, not as three isolated silos.

Exercises

Exercise 1 — Translate a fourth hypothetical question. Imagine management adds a fifth requirement: "I also want to know if the stores with the best session conversion are also the highest-revenue ones." Without writing code, say which two tables of this warehouse (already built in previous modules) you'd need to combine to answer that question, and by what key you'd connect them.

See solution

You'd combine fact_sessions (which has store_id and is_converted, to calculate the conversion rate by store) and mart_daily_sales_obt or fact_orders (which has store_id and revenue, to calculate revenue by store). The connecting key would be store_id, aggregating both tables separately at the store level — GROUP BY store_id — and then comparing the two results side by side, with no need for a row-by-row JOIN between them, because fact_sessions (grain: session) and fact_orders/mart_daily_sales_obt (grain: order line or day+store+product) have different grains and shouldn't be joined directly without aggregating first, exactly as module 7's lesson 1 exercise 3 already warned.

Exercise 2 — Find the implicit reproducibility requirement. Reread management's complete quote. Without looking at the "The brief" table, identify an implicit requirement not in the table, related to numbers' reliability over time, and which principle of this guide it corresponds to.

See solution

A reasonable answer: if management is going to query this warehouse "every week," as the quote's tone suggests, they need the same data to always produce the same numbers — the same reproducibility discipline this guide required since its design (no random, no datetime.now(), always fixed dates). That implicit requirement isn't explicit in management's words, but it's what justifies why this module, like the previous seven, never uses a dynamically calculated date — every assert in this warehouse has to be repeatable, byte for byte, the next time someone runs it.

Exercise 3 — Argue why an OBT solves "without having to understand SCD" better than training the BI team in advanced SQL. In 2-3 sentences, compare the two alternatives — publishing an OBT with the join already resolved, vs. teaching the whole BI team to write the BETWEEN valid_from AND valid_to themselves — and explain why the first is the right choice for this specific brief.

See solution

Training the whole BI team in SCD and point-in-time joins would solve the problem once per person, but would leave the report's correctness depending on every analyst remembering to apply the correct pattern every time they write a new query — exactly the kind of fragile dependency human error breaks sooner or later. Publishing the OBT with the join already resolved moves that responsibility, once, to the team building the warehouse — who already went through this guide's modules 4 and 5 — and leaves the BI team with a table where the question "is the price or the category correct?" no longer depends on them remembering a specific SQL pattern. It's the same logic behind any well-designed abstraction layer: move the complexity to where the knowledge lives, don't distribute it to whoever doesn't need it.

Summary and next step

In this lesson you translated a business frustration — "each module convinced me separately, but I need one single warehouse" — into a brief with four concrete requirements, each mapped to a piece you already built in the seven previous modules. You confirmed, with confirm_material.py, that the seven pieces are ready, with their exact numbers, before starting to integrate them. And you saw why this module's only genuinely new piece — mart_daily_sales_obt with the correct join already resolved — exists specifically so the BI team doesn't have to learn SCD to trust the report.

Before moving on you should be able to: repeat, from memory, the brief's four requirements and which module each corresponds to; explain why an OBT with the join already resolved is a better solution than training the whole BI team; and name this module's only piece that didn't exist, as such, in any previous module.

Lesson 3 starts building: it rebuilds bronze and silver from foundations — the same fact_orders as always, 40 rows, revenue 106.15 — now as this integrated warehouse's first formal step, not as an isolated exercise.

Resources

← PreviousModule introduction: the whole guide's capstoneNext →Rebuilding bronze and silver from foundations