Module introduction: the whole guide's capstone

Why this module exists

Stop for a moment and look back. In module 1 you declared fact_orders's grain with a query — COUNT(*) against COUNT(DISTINCT order_id || '-' || product_id) — and confirmed each row represents an order line. In module 2 you built the complete star schema: surrogate keys, dim_date, the three JOINs. In module 3 you compared star, snowflake, and wide table with evidence, not fashion. In module 4 you historized dim_product with SCD type 2, using a real MERGE INTO. In module 5 you discovered a badly written JOIN against that historized dimension corrupts a product's category, even though total revenue never lies. In module 6 you built two fact-table patterns no transactional fact can solve: the accumulating snapshot of the session funnel, and the cumulative table design of daily per-store activity. In module 7 you gave a name to a domain with three facts and four dimensions, and wrote this guide's first verifiable schema contract.

Seven modules, seven pieces, each built and verified separately, in its own script, with its own assert. What's left — and it's exactly what this module, the guide's last one, does — is to stop treating them as seven pieces. This is the capstone: a single warehouse, built end to end in one flow, from foundations's raw data to the report Kiosko's management can read directly. There's no new concept underneath this module — there's no eighth hidden idea you haven't already seen; there's one new idea, and it's about integration: bronze, silver, and the four gold layers you built (historized star, accumulating snapshot, cumulative, wide table) have to coexist in the same DuckDB connection, in the same dependency order, producing the same numbers you already know, but now all at once.

Connection to the module. Each lesson in this module reuses, without changing a single line of its internal logic, code you already built: transform_fact_orders() (module 1), generate_date_dim() (module 2), MERGE INTO over dim_product_scd (module 4), the point-in-time join BETWEEN valid_from AND valid_to (module 5), fact_sessions and fact_store_activity (module 6), and validate_gold_schema() (module 7). This module calls them in the correct order, inside a single script, and adds the final piece no previous module needed yet: publishing mart_daily_sales_obt — module 3's wide table — using the correct join against the historized dimension, not the static version its own origin module used.

An analogy: the complete orchestra, not seven soloists separately

Think about seven talented musicians who, each on their own, rehearsed their part of a symphony in their own practice room: the violinist played their part perfectly, the cellist theirs, the pianist theirs. Each individual rehearsal sounded flawless. But a symphony isn't the sum of seven separate recordings played back at the same time from different rooms — it needs all seven musicians playing together, on the same stage, at the same tempo, responding in real time to what the others do. The conductor doesn't teach anyone a new note at the dress rehearsal: their job is to make the seven parts, already mastered separately, sound like a single coherent piece.

This module is Kiosko's dress rehearsal. fact_orders, dim_product_scd, fact_sessions, fact_store_activity, mart_daily_sales_obt — each sounded perfect in its own module. Here you learn no new note; you learn to make the five sound together, in the same warehouse, with none stepping on the other.

Worked example: the complete warehouse's map, before building anything

Before writing this module's first line, it's worth seeing the complete destination: which table each layer builds, and in what order one depends on the other.

# warehouse_preview.py
WAREHOUSE_LAYERS = [
    ("bronze",  "bronze_orders, bronze_events",
     "raw, untransformed (equivalent to foundations's CSVs)"),
    ("silver",  "fact_orders (via validate_orders + transform_fact_orders)",
     "validated + modeled, 40 rows, revenue 106.15"),
    ("gold: star",
     "dim_store, dim_date, dim_product_scd (historized, MERGE INTO x2)",
     "modules 2 and 4, the star with real history"),
    ("gold: funnel + cumulative",
     "fact_sessions, fact_store_activity",
     "module 6, two non-transactional fact patterns"),
    ("gold: BI",
     "mart_daily_sales_obt",
     "module 3 + 5 integrated: wide table with point-in-time join"),
]

print("=== Kiosko: the warehouse this module integrates ===\n")
for layer, tables, description in WAREHOUSE_LAYERS:
    print(f"{layer:24} -> {tables}")
    print(f"{'':24}    {description}\n")

print("Input: 40 orders + 32 events (fixed, from foundations and module 6).")
print("Output: correct historical revenue, funnel conversion, 7/30d actives, OBT for BI.")

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

=== Kiosko: the warehouse this module integrates ===

bronze                   -> bronze_orders, bronze_events
                            raw, untransformed (equivalent to foundations's CSVs)

silver                   -> fact_orders (via validate_orders + transform_fact_orders)
                            validated + modeled, 40 rows, revenue 106.15

gold: star               -> dim_store, dim_date, dim_product_scd (historized, MERGE INTO x2)
                            modules 2 and 4, the star with real history

gold: funnel + cumulative -> fact_sessions, fact_store_activity
                            module 6, two non-transactional fact patterns

gold: BI                 -> mart_daily_sales_obt
                            module 3 + 5 integrated: wide table with point-in-time join

Input: 40 orders + 32 events (fixed, from foundations and module 6).
Output: correct historical revenue, funnel conversion, 7/30d actives, OBT for BI.

No business numbers yet — this lesson is, deliberately, the map before the territory — but notice the order of the five rows: it isn't alphabetical, it's the real dependency order. silver needs bronze; the star needs silver (fact_orders already built); mart_daily_sales_obt, the last row, needs three things at once — fact_orders, dim_store/dim_date, and dim_product_scd — because it's this guide's first table to integrate module 5's point-in-time join inside module 3's wide table, something no previous module did separately.

Diagram: Kiosko's complete warehouse architecture

flowchart TD
    subgraph Bronze["BRONZE (lesson 3)"]
        A["bronze_orders (40)\nbronze_events (32)"]
    end

    subgraph Silver["SILVER (lesson 3)"]
        B["validate_orders()\nquality gate"]
        C["transform_fact_orders()\nfact_orders, 40 rows, 106.15"]
    end

    subgraph GoldStar["GOLD: STAR (lesson 4)"]
        D["dim_store (3)\ndim_date (31)"]
        E["dim_product_scd (5)\nMERGE INTO x2, P002 historized"]
        F["Point-in-time JOIN\nCORRECT historical revenue"]
    end

    subgraph GoldFunnel["GOLD: FUNNEL + CUMULATIVE (lesson 5)"]
        G["fact_sessions (17)\nfunnel 17->9->6, 35.3%"]
        H["fact_store_activity (21)\n7d/30d actives by store"]
    end

    subgraph GoldBI["GOLD: BI (lesson 6)"]
        I["mart_daily_sales_obt\npoint-in-time join, 39 rows"]
    end

    A --> B --> C
    C --> D
    C --> E
    D --> F
    E --> F
    C --> G
    C --> H
    C --> I
    D --> I
    E --> I
    F -.confirms the correct category.-> I

Notice the dotted arrow: it isn't a data dependency, it's a warning. Lesson 4 demonstrates, with numbers, that the point-in-time join is the only correct one against dim_product_scd; lesson 6 doesn't demonstrate it again — it simply applies it, trusting the evidence lesson 4 already built. That's, precisely, this whole module's spirit: nothing gets re-explained from scratch, everything gets integrated over what's already verified.

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The complete warehouse's map, before building anything
L2        The brief: why 7 loose pieces aren't a warehouse
L3        Bronze and silver rebuilt: fact_orders, 40 rows, 106.15
L4        The star with historized dim_product_scd + point-in-time join
L5        fact_sessions and fact_store_activity: funnel and 7/30d activity
L6        mart_daily_sales_obt: the published OBT, with the correct join
L7        What Kiosko still needs -- the map of sibling guides
L8        Project: Kiosko's first complete analytical warehouse

Lessons 3, 4, 5, and 6 build the warehouse by layers, in the exact order of the diagram above — each with its own verification, just as you already did in the previous seven modules. Lesson 7 takes a step back, with the warehouse already finished: it names, one by one, the data-engineering-ecosystem sibling guides and what each one solves about what this warehouse leaves pending. And lesson 8 — the final mini-project, not just of this module but of the whole guide — rebuilds the five layers one last time, in a single script, with the final report that closes data-modeling-for-analytics-guide completely.

Going deeper: why integrating is different work than building

It's worth being explicit about something this module doesn't repeat from the previous seven: building an isolated piece and verifying it with its own assert is easier than integrating it with the others, even though both jobs use exactly the same code. The reason is that integration exposes dependencies an isolated test never sees. dim_product_scd, built alone in module 4, doesn't need to know anything about fact_sessions. But in this module, both live in the same DuckDB connection that also has fact_orders, dim_store, dim_date, and mart_daily_sales_obt — and the order they're created in does matter: mart_daily_sales_obt can't be built before dim_product_scd has its five final rows, because a MERGE INTO run halfway through would leave the OBT with an incomplete version of P002's history.

This is, at bottom, the same lesson module 7 already named about Medallion contracts: silver depends on bronze, gold depends on silver, and no layer can be built outside that order without risking a silently incorrect result. This module doesn't invent that rule — it applies it, for the first time, over all five complete layers at once, not over an isolated table.

Common mistakes

Thinking "integrating" means "copy-pasting the seven previous scripts, one after another." What happens: someone, starting this module, takes the eight .py files from modules 1 through 7's mini-projects and concatenates them into one giant file, expecting the result to be the integrated warehouse. Why it happens: each previous mini-project is already a complete, verified script, so pasting them one after another seems like the shortest path. How to spot it: if your resulting script defines con = duckdb.connect() seven times, or rebuilds fact_orders five times with slightly different data, you didn't integrate anything — you just concatenated. How to fix it: an integrated warehouse uses a single connection, builds each table once, and respects this lesson's diagram's real dependency order — not the order the modules appeared in the guide.

Expecting a new dimensional-modeling concept in this module. What happens: someone comes to this module looking for the "eighth technique" of dimensional modeling they haven't seen yet, assuming a guide's capstone always adds something new underneath. Why it happens: the eight previous modules each introduced at least one new idea — grain, star, snowflake/OBT, SCD, point-in-time, accumulating/cumulative, junk/degenerate — so it seems reasonable to expect one more. How to spot it: if you look in this module for a modeling technique you can't name from the previous seven, you're not going to find it — it doesn't exist. How to fix it: this module integrates, it doesn't teach again. The only genuinely new piece is publishing mart_daily_sales_obt with the correct join — a combination of two already-known techniques (modules 3 and 5), not a third.

Underestimating how much can break just from build order. What happens: someone builds mart_daily_sales_obt before running the second MERGE INTO over dim_product_scd, and is surprised when the OBT doesn't reflect P002's change. Why it happens: in modules 4 and 5, dim_product_scd always appeared already complete — five rows, P002 historized — so it's easy to forget that table gets built in two steps (MERGE #1, MERGE #2) and that any derived table built between those two steps ends up with a halfway version. How to spot it: if your mart_daily_sales_obt has unexpected categories, or is missing one, check exactly at what point in the script you built it relative to the two MERGEs. How to fix it: this module's lesson 4 runs the two complete MERGEs, with their verification, before lesson 6 builds the OBT — that order isn't arbitrary, it's the guarantee that the dimension is in its final state before any derived table queries it.

Exercises

Exercise 1 — Order the five layers from memory. Without looking at this lesson's diagram, write in order Kiosko's warehouse's five layers (bronze, silver, gold: star, gold: funnel + cumulative, gold: BI) and, for each one, name at least one table it builds.

See solution

1. Bronze: bronze_orders, bronze_events — raw, untransformed.
2. Silver: fact_orders — validated with validate_orders(), modeled with transform_fact_orders().
3. Gold: star: dim_store, dim_date, dim_product_scd — the star with the historized dimension.
4. Gold: funnel + cumulative: fact_sessions, fact_store_activity — accumulating snapshot and cumulative design.
5. Gold: BI: mart_daily_sales_obt — the published wide table, with the point-in-time join applied.

The order matters because each layer literally depends, in the code, on the previous one already existing and being complete — silver can't be built without bronze, and item 5's OBT needs both item 3's star and item 2's fact_orders.

Exercise 2 — Identify this module's only genuinely new piece. Of the five layers in the diagram, four reuse already-built code with no change underneath. Identify which specific combination is new in this module, and why no previous module could build it.

See solution

The new piece is mart_daily_sales_obt built with the point-in-time join against dim_product_scd, instead of the simple join against dim_product (static) module 3 used. No previous module could build this version because dim_product_scd didn't exist yet when module 3 built its own OBT — that historized dimension only got built in module 4 — and module 5, which did build the point-in-time join, never applied it to a wide table, only to reports aggregated by category. This module is the first to have, at the same time, the OBT (module 3) and the historized dimension with its correct join (modules 4 and 5) available to combine.

Exercise 3 — Explain, from memory, why this lesson's diagram's dotted arrow doesn't represent a data dependency. In 2-3 sentences, explain what kind of relationship that arrow represents between "point-in-time JOIN" and "mart_daily_sales_obt," if it isn't a technical build dependency.

See solution

The dotted arrow represents an evidence dependency, not a data one: lesson 4 demonstrates, with an assert comparing the broken join (is_current = true) against the correct one (BETWEEN valid_from AND valid_to), that only the second produces the correct category for P002. Lesson 6 doesn't repeat that demonstration — it builds mart_daily_sales_obt directly with the already-verified pattern, trusting that lesson 4 already proved it's the correct one. If the order were reversed — building the OBT before having demonstrated which join is correct — lesson 6 would be making a decision without the evidence justifying it, exactly the mistake this guide warned about since module 1: never declare something without verifying it first.

Summary and next step

In this lesson you saw the guide's last module's complete map: five layers — bronze, silver, gold: star (with historized dim_product_scd), gold: funnel + cumulative, gold: BI — each already built in a previous module, now integrated into a single warehouse, in the exact order their real dependencies demand. No new dimensional-modeling concept appears here — the only new piece is combining module 3's wide table with module 5's point-in-time join, something no previous module could build separately.

Before moving on you should be able to: name the warehouse's five layers and at least one table from each; explain why build order matters, not just each table's code; and anticipate that this module's only genuinely new piece is mart_daily_sales_obt with the correct join.

Lesson 2 turns this map into a concrete brief: what, in their own words, Kiosko's management would ask of a real analytical warehouse — and why seven loose pieces, each perfect in its own module, still aren't that warehouse.

Resources

• Kimball Group — "Star Schema / OLAP Cube" — the complete dimensional vocabulary this module integrates end to end: grain, star, SCD, conformed dimensions, accumulating snapshot. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. In English.
• Databricks — "What is the medallion lakehouse architecture?" — the bronze/silver/gold framework organizing this whole module's dependency order. docs.databricks.com/aws/en/lakehouse/medallion. In English.
• "The Data Warehouse Toolkit," 3rd edition (Kimball & Ross, Wiley) — the canonical reference backing, from beginning to end, the dimensional model this capstone integrates. wiley.com/en-jp/The+Data+Warehouse+Toolkit. In English.
• DuckDB — official Python client documentation, the interface that runs every query in this module. duckdb.org/docs/current/clients/python/overview. In English.