Module 2: The Star Schema And Conformed Dimensions

Module introduction: the complete star schema and conformed dimensions

Next →

Why this module exists

Module 1 closed with a single item resolved, and said so explicitly in its own closing checklist: the grain of fact_orders was declared and verified — GRAIN_DECLARATION, with total_rows == distinct_lines, 40 == 40 — but eleven more items were still pending. The first of those items, the one this module resolves end to end, has three parts: surrogate keys, dim_date, and conformed dimensions.

Today, as module 1 left it, dim_store and dim_product are still exactly what you inherited from foundations: catalogs with a natural key (store_id, product_id), with no column acting as the dimensional model's own identifier. There's no dim_date anywhere in the project — any question about a day, week, or weekend would have to be derived from order_ts by hand, every single time, with no reusable table. And there's still no formal vocabulary for talking about "this dimension serves more than one business process," because until now only one business process has existed: the sale.

This module builds all three pieces together, because they're really one idea seen from three angles: a complete star schema is a fact table at the center, with dimensions surrounding it, each one joined by a single key — and that key, in a well-built star schema, is almost always surrogate, not natural. dim_date is the most universal dimension of all, the one nearly every business process ends up needing. And a dimension is conformed when the same table — with the same key, the same meaning — serves more than one process, without duplicating itself. By the end of this module, Kiosko has, for the first time, a real star schema: four tables, three JOINs, and not a single row more or fewer than the forty you already know.

Connection to the module. This module doesn't touch the grain you declared in module 1 — it's still, word for word, "an order line." What it builds is the structure around that grain: the keys that identify each dimension, the calendar dimension that was missing, and the vocabulary for reasoning about shared dimensions before this project has a second business process (module 6 adds one, with fact_sessions).

An analogy: the closet with everything within reach

Imagine you're organizing a closet. There are two reasonable ways to do it. The first: every garment has a fixed, visible spot, one motion away — shirts on their rod, pants on theirs, shoes on their shelf. Finding something takes one step: you open the closet, see the garment, grab it. The second way: you pack the shirts inside a box labeled "formal wear," which sits inside another box labeled "cold season," which sits on a high shelf. Finding that same shirt now takes several steps — pull down the big box, open it, find the small box, open it — even though the clothes are technically better categorized, with less duplicated space.

A star schema is the first closet: the fact table at the center, and each dimension — dim_store, dim_product, dim_date — one JOIN away, with no intermediate layers. A snowflake schema — which this guide doesn't build yet, but which you'll see up close in module 3 — is the second closet: dimensions normalized inside other dimensions (dim_product pointing to dim_category, for example), with less data duplication but more steps to reach any question. This module builds the first closet — the star — because it's the shape Kimball recommends by default for an analytical warehouse, and because understanding a well-organized closet well is the prerequisite for appreciating, in module 3, when it's worth paying the cost of nested boxes.

Worked example: the map of the complete star, before building it

Before writing this module's first line of SQL, it's worth seeing, at a glance, the six pieces you're going to build and which lesson each one shows up in.

# star_schema_map.py
COMPONENTS = [
    ("Anatomy of a star schema", "fact_orders at the center, dim_store/dim_product/dim_date around it, one JOIN away"),
    ("Surrogate keys vs natural keys", "store_key and product_key: integers generated by the model, not by the source system"),
    ("dim_date", "The calendar dimension fact_orders never had: date_key, day_of_week, quarter, is_weekend"),
    ("Conformed dimensions", "The SAME dim_store and dim_date, ready to serve more than one business process"),
    ("Bus matrix", "The map that says which dimension serves which process, before building anything new"),
    ("The assembled star", "fact_orders + 3 JOINs, verified: 40 rows before, 40 after -- not one more, not one fewer"),
]

print("=== Kiosko's complete star schema, piece by piece (module 2) ===\n")
for i, (name, description) in enumerate(COMPONENTS, start=1):
    print(f"{i}. {name}")
    print(f"   {description}\n")

print("Each piece depends on the previous one. By the end of the module, the star's")
print("four tables -- fact_orders, dim_store, dim_product, dim_date -- all exist at once,")
print("joined, without losing or duplicating a single one of module 1's 40 rows.")

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

=== Kiosko's complete star schema, piece by piece (module 2) ===

1. Anatomy of a star schema
   fact_orders at the center, dim_store/dim_product/dim_date around it, one JOIN away

2. Surrogate keys vs natural keys
   store_key and product_key: integers generated by the model, not by the source system

3. dim_date
   The calendar dimension fact_orders never had: date_key, day_of_week, quarter, is_weekend

4. Conformed dimensions
   The SAME dim_store and dim_date, ready to serve more than one business process

5. Bus matrix
   The map that says which dimension serves which process, before building anything new

6. The assembled star
   fact_orders + 3 JOINs, verified: 40 rows before, 40 after -- not one more, not one fewer

Each piece depends on the previous one. By the end of the module, the star's
four tables -- fact_orders, dim_store, dim_product, dim_date -- all exist at once,
joined, without losing or duplicating a single one of module 1's 40 rows.

Still no real Kiosko numbers — this map is, deliberately, the blueprint before the construction, the same way module 1 opened with Kimball's four-step process map. But notice the order: shape first (anatomy), then keys, then the missing dimension, then the reuse vocabulary, then the planning map, and only at the end the real assembly. That order isn't accidental: it wouldn't make sense to build dim_date before understanding why a dimension needs its own key, nor to talk about conformed dimensions before well-formed dimensions exist to conform.

Diagram: where you were, where you're going to be

flowchart LR
    subgraph M1["Module 1 (already written)"]
        A["fact_orders: grain declared\n(an order line, 40==40)\ndim_store/dim_product: natural key"]
    end

    subgraph M2["This module (2 of 8)"]
        B["L2: Anatomy of the star\n(fact at center, dims around it)"]
        C["L3: store_key, product_key\n(surrogate keys, EXECUTED)"]
        D["L4: dim_date\n(generate_date_dim, EXECUTED)"]
        E["L5-L6: Conformed dimensions\nand bus matrix"]
        F["L7-L8: The assembled star\n3 JOINs, 40 -> 40, EXECUTED"]
    end

    subgraph Resto["Modules 3-8"]
        G["Snowflake vs OBT, SCD,\npoint-in-time joins..."]
    end

    A --> B --> C --> D --> E --> F --> G

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The map: the star's six pieces, before building them
L2        Anatomy of a complete star schema: fact at the center, dimensions around it
L3        Surrogate keys vs natural keys -- store_key, product_key, EXECUTED
L4        Building dim_date: generate_date_dim(), EXECUTED in DuckDB
L5        Conformed dimensions: the same table, more than one business process
L6        The bus matrix: the map of which dimension serves which process
L7        Assembling Kiosko's first real star -- all 3 JOINs, EXECUTED
L8        Project: Kiosko's complete star schema in DuckDB

Lessons 2, 5, and 6 are mostly conceptual — they build the vocabulary and the judgment before running new code — though each one still runs a real example over the fact_orders/dim_store/dim_product you already know. Lessons 3, 4, and 7 are the ones that run most of this module's new code: surrogate keys, dim_date, and the final assembly with the three JOINs. Lesson 8 closes with the mini-project: the complete star schema, delivered and verified against the same revenue numbers you already know from foundations.

Going deeper: why the star schema is the next logical step after the grain

It might seem that, once the grain is declared, the "obvious" next step would be to start solving more advanced problems — historizing a dimension that changes, say, or building an accumulating snapshot. This module resists that temptation for a concrete reason: everything that follows in this guide assumes a complete, well-formed star schema exists underneath it. Module 4 (SCD) historizes dim_product — but historizing a dimension only makes sense once it already has a surrogate key that can be versioned (you're going to understand this precisely in lesson 3 of this module). Module 5 (point-in-time join) joins fact_orders against historical versions of a dimension — but that join needs, as a starting point, the same clean JOIN pattern this module builds in lesson 7. Module 6 (accumulating snapshot and cumulative design) builds fact_sessions and fact_store_activity — two new fact tables that, from day one, are going to reuse dim_store and dim_date, precisely because this module leaves them conformed.

In other words: module 1 answered "what does a row represent?" This module answers "what structure does that row live in, surrounded by what context, and with what keys?" Without this answer, any more advanced technique from modules 3 through 8 would be building on a foundation that doesn't have a shape yet.

Common mistakes

Thinking "building the star schema" means just drawing the diagram. What happens: someone draws, on paper or in a diagramming tool, a fact table at the center with lines to three dimensions, and considers this module's work done. Why it happens: the diagram is the star schema's most visually satisfying part, and it's easy to confuse "I have the right drawing" with "I have the right tables, with the right keys, joined without losing rows." How to spot it: if, by the end of this module, you can't show, with a JOIN actually executed, that fact_orders joined to its three dimensions still has exactly forty rows, the diagram was a drawing exercise, not a modeling one. How to fix it: every lesson in this module that promises a "What to expect" has to be executed — the diagram is a useful map, but the evidence is always the query run against DuckDB.

Skipping dim_date because "I already have order_ts in fact_orders." What happens: someone reasons that, since fact_orders already has a date column (order_ts), no additional table is needed — any date question can be resolved with date functions directly on that column. Why it happens: order_ts technically does contain the raw information, and for simple questions (what month was this order in?) it seems like enough. How to spot it: if your only way to answer "how many orders happened on a weekend?" is writing a different date expression every time someone asks, instead of a JOIN against a table with is_weekend already calculated, you're missing dim_date — and that repeated calculation, scattered across twenty different queries, is exactly the kind of duplicated work a conformed dimension eliminates. How to fix it: lesson 4 of this module builds dim_date precisely for this — calculate once, reuse always.

Believing surrogate keys are "extra work with no immediate benefit" in this module. What happens: someone notices that, today, joining fact_orders to dim_store by store_id (natural key) works perfectly — no store has changed, no product has been renamed, nothing justifies an extra key — and concludes that adding store_key is pure formalism. Why it happens: a surrogate key's real benefit isn't felt until a dimension starts changing (module 4) — until then, the natural key "works the same." How to spot it: if your reasoning is "it makes no difference today anyway," it's the same mistake you already saw in module 1 with the grain — optimizing for the present without considering the future cost. How to fix it: lesson 3 of this module explains, with the analogy of a customer's file, why adding the surrogate key before you need it is the right decision, not an ornament.

Exercises

Exercise 1 — Recall, without looking back, the three pending pieces this module resolves. Without rereading the "Why this module exists" section, write from memory the three items from module 1's closing checklist that belong to this module 2 (check module 1's lesson 8 project if you need the exact wording of the checklist row).

See solution

Module 1's checklist row that corresponds to this module reads, literally: "Surrogate keys, dim_date, conformed dimensions." The three pieces are: (1) replacing dim_store/dim_product's natural keys with surrogate keys (store_key, product_key); (2) building dim_date, the calendar dimension foundations never needed; (3) formalizing the concept of a conformed dimension — the same dimension table, reusable by more than one business process. If your answer mentioned these three pieces, even in different words, you have this module's goal clear.

Exercise 2 — Order this lesson's six-piece map, from memory. Without looking at the worked example, write in order the six pieces this module builds (anatomy, surrogate keys, dim_date, conformed dimensions, bus matrix, the assembled star).

See solution
  1. Anatomy of a star schema
  2. Surrogate keys vs natural keys
  3. dim_date
  4. Conformed dimensions
  5. Bus matrix
  6. The assembled star

If you swapped the order of "conformed dimensions" and "bus matrix" — thinking the bus matrix comes first, as the general map — it's not a serious mistake: they're conceptually very close. But notice this guide first teaches what a conformed dimension is (lesson 5), with a concrete example (dim_store reused), before generalizing that idea into a complete map of all of Kiosko's processes (lesson 6, the bus matrix). You learn the specific case before the general map — the same pedagogical pattern you already saw in module 1, with the grain before the complete classification of facts and dimensions.

Exercise 3 — Explain the closet analogy in your own words. Using this lesson's organized-closet (star) versus nested-boxes-closet (snowflake) analogy, explain in 2-3 sentences why this guide chooses to build the closet with everything within reach first, even though module 3 shows that nested boxes also have their advantages.

See solution

The star schema (the closet with everything within reach) is the default shape Kimball recommends for an analytical warehouse because it prioritizes query simplicity: any business question needs, at most, one JOIN to get from the fact to any dimension, with no intermediate steps. The snowflake (the nested boxes) reduces data duplication — for example, not repeating a category's name on every product row — but in exchange requires more JOINs to reach the same information. This guide builds the star first because it's the foundation most use cases need, and because understanding that foundation well is what allows you, in module 3, to judge with real criteria when it's worth paying the extra cost of normalizing a dimension — not the other way around.

Summary and next step

This module builds, on top of module 1's already declared and verified grain, Kiosko's first complete star schema: surrogate keys for dim_store and dim_product, the missing dim_date dimension, the vocabulary of conformed dimensions, the bus matrix as a planning map, and the final assembly of fact_orders with its three dimensions, joined without losing or duplicating a single one of the forty rows you already know.

Before moving on you should be able to: name this module's six pieces in order; explain, in your own words, the difference between a star and a snowflake using the closet analogy; and say from memory the exact row of module 1's checklist this module resolves.

Lesson 2 starts with anatomy: what makes a table a "fact" and another a "dimension" in a star schema's physical shape, not just in the vocabulary you already learned in module 1.

Resources

← PreviousMini-project: Kiosko's grain declarationNext →Anatomy of a star schema