Module 2: The Star Schema And Conformed Dimensions

Conformed dimensions across processes

Next →

Description

Up to this lesson, Kiosko has had a single business process: the sale (fact_orders). With only one process, the question "does this dimension serve more than one fact?" never became relevant — dim_store and dim_product only had one consumer. This lesson previews, in a controlled way, what's going to happen in module 6, when Kiosko gains a second business process (fact_sessions, the app's session funnel): and it shows why dim_store and dim_date, as built in this module, are already ready for that moment, with no change needed.

Connection to the module. This lesson doesn't build any new table — it reuses dim_store from lesson 3, with no modification. What it builds is the precise vocabulary of conformed dimension, the concept lesson 6 (bus matrix) is going to generalize into a complete map of all of Kiosko's processes.

An analogy: the company's single directory

Think of a mid-sized company, with several departments: sales, logistics, accounting. Each of those departments, at some point, needs to know "which branch did this happen at?" — sales to know where a contract was closed, logistics to know where to ship an order, accounting to know which cost center to charge an expense to. A poorly organized company would have three branch directories, one per department, each maintained separately — and the day a branch changes address, someone would have to update all three directories, with a real risk that one gets updated and the other two go stale, silently, until someone notices the discrepancy.

A well-organized company has a single branch directory, shared by all three departments — it gets updated once, in one place, and all three departments always see the same truth. That's exactly what Kimball calls a conformed dimension: the same dimension table — with the same key, the same meaning, the same values — reused by more than one business process, without duplicating itself. dim_store, as you built it in lesson 3, is that single directory: when module 6 builds fact_sessions, it's going to join against the same dim_store that fact_orders already uses today — not a copy, not a new version under a different name.

Worked example: dim_store, ready for a second process that doesn't exist yet

Rebuild dim_store exactly as in lesson 3, and verify something you can't yet prove with a real table — because fact_sessions doesn't exist until module 6 — but that you can reason about precisely using a data structure documenting which dimensions each process needs.

# conformed_dimensions.py
import duckdb

con = duckdb.connect()
con.execute("CREATE TABLE dim_store_natural (store_id VARCHAR, store_name VARCHAR, city VARCHAR)")
con.executemany("INSERT INTO dim_store_natural VALUES (?, ?, ?)", [
    ("S01", "Kiosko Centro", "Bogota"),
    ("S02", "Kiosko Norte", "Lima"),
    ("S03", "Kiosko Sur", "Santiago"),
])
con.execute("""
    CREATE TABLE dim_store AS
    SELECT ROW_NUMBER() OVER (ORDER BY store_id) AS store_key, store_id, store_name, city
    FROM dim_store_natural
""")

print("=== dim_store, exactly as lesson 3 built it -- a single table, built once ===")
print(con.sql("SELECT * FROM dim_store ORDER BY store_key"))

# Two different business processes that, at some point in this guide, are going to need
# "which store did this happen at." orders already exists (fact_orders). sessions is the
# clickstream funnel module 6 is going to build as fact_sessions -- it isn't built here,
# it's only named to illustrate this lesson's point.
BUS_MATRIX_PROCESSES = {
    "orders": {"dimensions": ["dim_store", "dim_product", "dim_date"], "fact_table": "fact_orders", "status": "built (M1-M2)"},
    "sessions": {"dimensions": ["dim_store", "dim_date"], "fact_table": "fact_sessions", "status": "planned (M6)"},
}

print("\n=== Which dimensions each Kiosko business process needs ===")
for process, info in BUS_MATRIX_PROCESSES.items():
    print(f"{process:10} -> {info['fact_table']:14} uses {info['dimensions']} [{info['status']}]")

conformed = set(BUS_MATRIX_PROCESSES["orders"]["dimensions"]) & set(BUS_MATRIX_PROCESSES["sessions"]["dimensions"])
print(f"\nCONFORMED dimensions between orders and sessions: {sorted(conformed)}")
print("These are the dimensions that fact_sessions, once module 6 builds it, is going to join")
print("against the SAME dim_store table that already exists -- not a copy, not a new version.")

# Concrete illustration: even though fact_sessions doesn't exist yet, dim_store ALREADY has
# everything it would need to join against it, without adding a single new row.
hypothetical_session_store_ids = {"S01", "S02"}
placeholders = ", ".join(f"'{s}'" for s in sorted(hypothetical_session_store_ids))
print(f"\n=== dim_store already covers the stores a hypothetical fact_sessions would use today ===")
print(con.sql(f"SELECT store_key, store_id, store_name FROM dim_store WHERE store_id IN ({placeholders}) ORDER BY store_key"))

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

=== dim_store, exactly as lesson 3 built it -- a single table, built once ===
┌───────────┬──────────┬───────────────┬──────────┐
│ store_key │ store_id │  store_name   │   city   │
│   int64   │ varchar  │    varchar    │ varchar  │
├───────────┼──────────┼───────────────┼──────────┤
│         1 │ S01      │ Kiosko Centro │ Bogota   │
│         2 │ S02      │ Kiosko Norte  │ Lima     │
│         3 │ S03      │ Kiosko Sur    │ Santiago │
└───────────┴──────────┴───────────────┴──────────┘

=== Which dimensions each Kiosko business process needs ===
orders     -> fact_orders    uses ['dim_store', 'dim_product', 'dim_date'] [built (M1-M2)]
sessions   -> fact_sessions  uses ['dim_store', 'dim_date'] [planned (M6)]

CONFORMED dimensions between orders and sessions: ['dim_date', 'dim_store']
These are the dimensions that fact_sessions, once module 6 builds it, is going to join
against the SAME dim_store table that already exists -- not a copy, not a new version.

=== dim_store already covers the stores a hypothetical fact_sessions would use today ===
┌───────────┬──────────┬───────────────┐
│ store_key │ store_id │  store_name   │
│   int64   │ varchar  │    varchar    │
├───────────┼──────────┼───────────────┤
│         1 │ S01      │ Kiosko Centro │
│         2 │ S02      │ Kiosko Norte  │
└───────────┴──────────┴───────────────┘

The set intersection (set & set) confirms, with code, what the single-directory analogy already anticipated: dim_store and dim_date are the two dimensions both processes — the one that already exists (orders) and the one that's going to exist (sessions) — need. dim_product, on the other hand, doesn't show up in the intersection: today only orders uses it, because fact_sessions's grain (one row per browsing session, not per product viewed) doesn't include any specific product as part of its structure. That doesn't mean dim_product is less important — it simply means it isn't a conformed dimension yet, because it only has one consumer.

Diagram: the same dimension, two processes

flowchart TD
    DS["dim_store\n(ONE single table,\nstore_key 1, 2, 3)"]
    DD["dim_date\n(ONE single table,\ndate_key per day of August)"]
    DP["dim_product\n(a single table,\nonly one consumer today)"]

    FO["fact_orders\n(built, M1-M2)"]
    FS["fact_sessions\n(planned, M6)"]

    DS --> FO
    DS -.->|"conformed:\nthe same store_key"| FS
    DD --> FO
    DD -.->|"conformed:\nthe same date_key"| FS
    DP --> FO

Going deeper: Kimball's precise definition, and why "conformed" doesn't mean "identical"

Kimball defines a conformed dimension with two conditions, both required: the keys must be the same (the same store_key = 2 means "Kiosko Norte, Lima" in any fact that uses it), and the meaning of the attributes must be identical (city means the same thing, with the same possible values, no matter which fact you query it from). It's not enough for two tables to look alike — they have to be, literally, the same table, or two tables guaranteed to stay in sync through the same loading process.

It's worth clearing up a common misunderstanding: "conformed dimension" doesn't mean every fact using it has to look like every other, or share the same grain. fact_orders has a grain of "an order line"; fact_sessions, once it exists, is going to have a grain of "a complete browsing session" — two completely different grains, two different business processes, two fact tables with entirely different columns. The only thing that gets conformed is the shared dimension — dim_store, with its store_key — not the facts that consume it. This distinction matters because it's easy, when you start modeling a warehouse with several processes, to assume "conforming" means unifying everything — and that's not the case: each fact keeps its own grain and its own shape; only the dimensions they share stay as a single source of truth.

This is also the practical reason adding the surrogate key in lesson 3, before any second process existed, turned out to be the right decision: store_key is already stable, already generated deterministically, and when module 6 builds fact_sessions, that new table is simply going to point to the same store_key fact_orders already uses — no work "synchronizing" two different dimensions, because there were never two dimensions to begin with.

Common mistakes

Creating a copy of dim_store specific to a new process, "so as not to touch the original." What happens: someone, building a new fact that needs store information, creates dim_store_sessions instead of reusing dim_store — with the idea that this avoids the risk of breaking something that already works. Why it happens: touching (or depending on) a table another process already uses feels risky, and duplicating it seems like a safe way to isolate the new work. How to spot it: if your warehouse has two tables with the same store information, under different names, you have exactly the "three branch directories" problem from this lesson's analogy — the day a store changes name, someone has to remember to update both copies, and the risk of them going out of sync is real, not theoretical. How to fix it: any new fact that needs "which store" reuses the dim_store that already exists, joining by store_key — never duplicate it.

Thinking two dimensions are conformed just because they have the same columns. What happens: someone sees two tables with identically named columns (store_id, store_name, city) and declares them conformed, without verifying both contain exactly the same values for the same keys. Why it happens: matching column names feel like sufficient evidence. How to spot it: if two tables with same-named columns could, at some point, hold different values for the same key — for example, an "old" dim_store that wasn't updated when the other was — they aren't conformed, they're two similar tables that happen to share a schema. How to fix it: the real test for conformance isn't the column names — it's that both processes query, literally, the same physical table (or a view over it), guaranteeing they can never diverge.

Assuming dim_product is also conformed, because "eventually everything connects." What happens: someone, seeing that dim_store and dim_date are conformed between orders and sessions, assumes by extension that dim_product is too, because intuitively "products matter for sessions too" (someone browses product pages, after all). Why it happens: it seems reasonable that, if two dimensions are shared, the third should be too. How to spot it: check the exact grain this guide's design declares for fact_sessions — one row per complete session (session_id, store_id, session_date, view/cart/purchase milestones) — no specific product is part of that grain. How to fix it: a dimension is conformed only if the fact's grain genuinely needs it as part of its structure — not by business intuition. dim_product remains, for now, a single-process dimension; that could change in the future if Kiosko decided to model the session grain at the level of viewed products, but that's a different design decision, outside this guide's scope.

Exercises

Exercise 1 — Add a hypothetical third process to BUS_MATRIX_PROCESSES. Module 6 is also going to build fact_store_activity (daily activity per store, with 7- and 30-day rolling revenue arrays). Add that entry to the worked example's BUS_MATRIX_PROCESSES dictionary, with dimensions dim_store and dim_date (no dim_product), and recalculate the intersection of conformed dimensions across all three processes.

See solution 
BUS_MATRIX_PROCESSES["store_activity"] = {
    "dimensions": ["dim_store", "dim_date"],
    "fact_table": "fact_store_activity",
    "status": "planned (M6)",
}

conformed_all = (
    set(BUS_MATRIX_PROCESSES["orders"]["dimensions"])
    & set(BUS_MATRIX_PROCESSES["sessions"]["dimensions"])
    & set(BUS_MATRIX_PROCESSES["store_activity"]["dimensions"])
)
print(f"Conformed dimensions across the 3 processes: {sorted(conformed_all)}")

Expected output:

Conformed dimensions across the 3 processes: ['dim_date', 'dim_store']

The result doesn't change compared to comparing just two processes — dim_store and dim_date are still the only two dimensions shared by all three. This is exactly what lesson 6 (bus matrix) is going to formalize into a complete map: dim_store and dim_date are Kiosko's "universal" dimensions — the ones almost any business process ends up needing — while dim_product remains specific to the sale.

Exercise 2 — Verify dim_store didn't change while "getting ready" for a second process. Using the worked example's dim_store, write a query confirming it still has exactly 3 rows and the same keys (store_key 1, 2, 3) it had in lesson 3 — a check that "making it conformed" involved no change of structure or data at all.

See solution 
print(con.sql("""
    SELECT COUNT(*) AS total_rows, MIN(store_key) AS min_key, MAX(store_key) AS max_key
    FROM dim_store
"""))

Expected output:

┌────────────┬─────────┬─────────┐
│ total_rows │ min_key │ max_key │
│   int64    │  int64  │  int64  │
├────────────┼─────────┼─────────┤
│          3 │       1 │       3 │
└────────────┴─────────┴─────────┘

The exact same numbers from lesson 3 — because, precisely, nothing changed. The dimension was already ready to be conformed from the moment it was well built, in lesson 3 — "conforming" isn't an operation you perform on a dimension, it's a property a well-built dimension already has, available for whenever a second process needs it.

Exercise 3 — Explain, without code, what would happen if dim_store currently had two different keys for "Kiosko Norte." Imagine, hypothetically, that due to a loading error dim_store had two rows for the same Lima store — store_key = 2 and store_key = 5, both with store_id = "S02". In 2-3 sentences, explain why this would break the conformed-dimension property, even if both processes keep using "the same physical table."

See solution

Even though orders and sessions would keep querying the same physical dim_store table, the conformance property doesn't depend solely on "being the same table" — it depends on the key uniquely and consistently identifying each entity. If fact_orders had, at some point, joined using store_key = 2 for Lima's sales, and fact_sessions joined using store_key = 5 for that same store's sessions, both processes would "agree" that a Lima store exists, but completely disagree about which key represents it — any report trying to compare Lima's sales and sessions by store_key would fail silently, with no visible error. This is exactly the kind of problem key-integrity checks (like lesson 3's exercise 2) exist to prevent — a conformed dimension requires, beyond being a single table, that its key never carry ambiguity or duplicates over the same business entity.

Summary and next step

In this lesson you learned the precise vocabulary of a conformed dimension: the same table, with the same key and the same meaning, reused by more than one business process, instead of being duplicated. With BUS_MATRIX_PROCESSES and a set intersection, you confirmed that dim_store and dim_date — as built in lessons 3 and 4 — are already ready to serve fact_sessions, the process module 6 is going to build, needing no change today. dim_product, on the other hand, remains specific to a single process, because fact_sessions's grain doesn't need it.

Before moving on you should be able to: define "conformed dimension" using Kimball's two precise conditions (same key, same meaning); explain why "conformed" doesn't imply the facts that use it share a grain or structure; and name, from memory, which of Kiosko's three dimensions are conformed today and which isn't.

Lesson 6 generalizes this idea into a complete planning tool: the bus matrix, the map that shows, at a glance, which dimension serves which process — for all of Kiosko's processes at once, not just the pair you compared in this lesson.

Resources

← PreviousBuilding dim_date: Kiosko's calendarNext →The bus matrix: mapping Kiosko's processes