Module 7: Messy Domains And Medallion At Depth

Module introduction: Kiosko's messy domain, named and under contract

Next →

Why this module exists

Stop for a moment on what Kiosko already has, before this module has built a single new table. fact_orders (transactional, module 1), fact_sessions (accumulating snapshot, module 6), and fact_store_activity (cumulative, module 6) are three fact tables, not one. dim_store, dim_product/dim_product_scd (historized, module 4), and dim_date (conformed, module 2) are three dimensions with different shapes — one simple, one historized, one with a smart key. And you've already seen, since module 1, that order_id lives inside fact_orders with no table of its own: this guide's first degenerate dimension, named but never developed in depth. No dimensional modeling textbook opens with this picture. Almost all of them open with the toy example: one fact, two dimensions, a clean JOIN. That example is real and necessary — that's how module 2 of this guide opened — but it isn't what a data team finds in its second month of work. It finds this: several facts, several kinds of dimension, and no automatic guarantee that the new pieces respect the shape of the old ones.

This module gives that picture a formal name — a messy domain, in Kimball's precise sense: not "badly designed," but "with more than one business process coexisting" — and builds two pieces no previous module needed. First, the junk dimension: when Kiosko starts recording payment_method and channel for each order — two low-cardinality attributes this module introduces — the question isn't just "how do I store them?" but "do I store them as two loose columns, or group them into a single small dimension, with one flag_key?" Second, the Medallion contract: with three facts and four published gold tables, how do you confirm, with evidence rather than manual review, that each one keeps exactly the columns the rest of the guide — and any BI consumer — expects? This module's answer is validate_gold_schema(), a Python function that compares the real schema against the expected one, run over this guide's four gold tables.

Connection to the module. This module doesn't modify a single row of fact_orders, dim_store, dim_product, dim_product_scd, dim_date, fact_sessions, or fact_store_activity — the seven tables modules 1 through 6 left complete and verified. What it builds is new: dim_order_flags (junk dimension), the full deep dive on the degenerate dimension already named in module 1, and validate_gold_schema(), the function that formalizes the contract between the bronze, silver, and gold layers this guide — and foundations, before it — has used from the start, without ever having put it in writing as a verifiable rule.

An analogy: a warehouse's full inventory, not one person's shopping list

Think about the difference between one person's shopping list — milk, bread, eggs — and a large supermarket's complete warehouse inventory: hundreds of SKUs, each with its own category, its own turnover rate, some arriving daily and others once a month, some sold individually and others that only make sense bundled into a combo. Nobody designs a large warehouse's inventory system thinking about a single SKU — it's designed knowing that SKUs of very different natures are going to coexist, and that the system needs explicit rules — a contract — so a new employee, or a new supplier, doesn't accidentally break the entire inventory by adding a SKU in the wrong format.

Modules 1 through 6 of this guide built, one at a time, each "SKU" of Kiosko's inventory: the grain of a sale, the calendar, the star's shape, a product's history, the correct point-in-time join, two kinds of non-transactional fact. This module is the moment to treat all of that as what it already is: a complete inventory, with explicit rules about what shape each new piece can have before it's allowed into the gold warehouse.

Worked example: Kiosko's complete inventory, before touching any new code

Before building anything, the same kind of map modules 3, 4, and 6 opened with before their central pattern — this time, an inventory of what Kiosko already has, classified by type:

# domain_inventory.py
KIOSKO_DOMAIN = [
    ("fact_orders",         "transactional fact",         "module 1", 40, "order_id (degenerate), store_id (FK), product_id (FK)"),
    ("fact_sessions",       "accumulating snapshot",      "module 6", 17, "session_id (PK), store_id (FK)"),
    ("fact_store_activity", "cumulative table design",    "module 6", 21, "store_id (FK), activity_date"),
    ("dim_store",           "simple dimension",           "module 1",  3, "store_key (surrogate PK)"),
    ("dim_product",         "simple dimension (star)",     "module 2",  4, "product_key (surrogate PK)"),
    ("dim_product_scd",     "historized SCD-2 dimension",  "module 4",  5, "product_key (surrogate PK), valid_from/valid_to"),
    ("dim_date",            "conformed dimension",         "module 2", 31, "date_key (PK, smart key)"),
]

print("=== Kiosko's domain, as module 6 left it ===\n")
print(f"{'table':22} {'type':28} {'origin':12} {'rows':>6}   {'keys'}")
for table, kind, origin, rows, keys in KIOSKO_DOMAIN:
    print(f"{table:22} {kind:28} {origin:12} {rows:6}   {keys}")

fact_tables = [t for t, kind, *_ in KIOSKO_DOMAIN if kind.startswith("transactional") or "cumulative" in kind or "accumulating" in kind]
dim_tables = [t for t, kind, *_ in KIOSKO_DOMAIN if "dimension" in kind]
print(f"\nTotal fact tables: {len(fact_tables)} -> {fact_tables}")
print(f"Total dimension tables: {len(dim_tables)} -> {dim_tables}")
print("\nWhat still doesn't exist: a junk dimension (payment_method + channel), and a function")
print("that confirms, with evidence, that the 4 gold tables maintain the schema this domain needs.")

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

=== Kiosko's domain, as module 6 left it ===

table                  type                         origin        rows   keys
fact_orders            transactional fact           module 1         40   order_id (degenerate), store_id (FK), product_id (FK)
fact_sessions          accumulating snapshot        module 6         17   session_id (PK), store_id (FK)
fact_store_activity    cumulative table design      module 6         21   store_id (FK), activity_date
dim_store              simple dimension             module 1          3   store_key (surrogate PK)
dim_product            simple dimension (star)      module 2          4   product_key (surrogate PK)
dim_product_scd        historized SCD-2 dimension   module 4          5   product_key (surrogate PK), valid_from/valid_to
dim_date               conformed dimension          module 2         31   date_key (PK, smart key)

Total fact tables: 3 -> ['fact_orders', 'fact_sessions', 'fact_store_activity']
Total dimension tables: 4 -> ['dim_store', 'dim_product', 'dim_product_scd', 'dim_date']

What still doesn't exist: a junk dimension (payment_method + channel), and a function
that confirms, with evidence, that the 4 gold tables maintain the schema this domain needs.

Three facts, four dimensions, seven tables in total — and this is, precisely, what a "one fact, two dimensions" textbook never prepares anyone to read. Notice that none of this table's seven rows is "the correct shape" for a Kiosko table — each one has the exact type its business process needed: transactional for a point-in-time sale, accumulating snapshot for a process that advances, cumulative for a metric that accumulates, historized for a catalog that changes. That is, precisely, the "messy domain" this module's title refers to — messy not because it's badly done, but because it doesn't fit in a single star's diagram.

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

flowchart LR
    subgraph M16["Modules 1-6 (already written)"]
        A["3 facts + 4 dimensions\nverified, EXECUTED"]
    end

    subgraph M7["This module (7 of 8)"]
        B["L2: Domain inventory\nEXECUTED"]
        C["L3: Degenerate dimension\nin depth, EXECUTED"]
        D["L4: dim_order_flags\njunk dimension, EXECUTED"]
        E["L5: Medallion contract\nvalidate_gold_schema(), EXECUTED"]
        F["L6: dim_date, one calendar\nfor 3 facts, EXECUTED"]
        G["L7: Schema evolution\nwithout breaking gold, EXECUTED"]
        H["L8: Integrated project\nEXECUTED"]
    end

    subgraph M8["Module 8 (capstone)"]
        I["Complete warehouse\nend to end"]
    end

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

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The domain inventory, before building anything new
L2        When one fact and two dimensions isn't enough, EXECUTED
L3        Degenerate dimensions: order_id, in depth, EXECUTED
L4        Junk dimensions: dim_order_flags, EXECUTED
L5        Medallion contracts between bronze/silver/gold, EXECUTED
L6        Multiple facts, a single conformed calendar, EXECUTED
L7        Schema evolution without breaking gold, EXECUTED
L8        Project: Kiosko's multi-fact gold layer, EXECUTED

Going deeper: why "messy domain" is a technical term, not an insult

It's worth being precise with the vocabulary, because "messy" sounds like a flaw and it isn't one. Kimball never uses that exact word, but the concept it describes — a bus matrix with multiple business processes, each with its own fact table, sharing a subset of conformed dimensions — is, literally, the description of any real production warehouse. Module 2 of this guide already built the first row of that bus matrix (the sale, with dim_store/dim_product/dim_date); module 6 added two more rows (sessions, daily activity). A bus matrix with a single row isn't "clean" — it's incomplete, because almost no real business has a single process worth measuring.

The word "messy" in this module's title precisely describes the experience of working in that domain without the right tools: loose low-cardinality columns multiplying out of control (the problem the junk dimension solves), identifiers someone tries to turn into their own tables without needing to (the problem naming the degenerate dimension well solves), and schemas that change without anyone noticing until a report breaks in production (the problem the Medallion contract solves). None of the three is a "bad design" problem — all three are normal consequences of success: more business processes to measure, more attributes to capture, more people touching the same warehouse. This module doesn't eliminate that complexity — it gives it a name, structure, and, in the contract's case, automatic verification.

Common mistakes

Thinking "messy domain" means the modules 1-6 model was badly designed. What happens: someone, reading this module's title, concludes that fact_orders, fact_sessions, and fact_store_activity should have been designed differently from the start to avoid the complexity this module names. Why it happens: the word "messy" in the title naturally invites looking for someone to blame or a prior mistake. How to spot it: if your conclusion at the end of this lesson is that some previous module "should have anticipated it," you missed the central argument — there's no way to design fact_orders in module 1 that prevents Kiosko, six modules later, from having three facts instead of one; that's simply what happens when a real business grows. How to fix it: "messy domain" is a descriptive label, not a criticism — it describes the coexistence of several business processes, exactly what Kimball predicts and what any production warehouse has. This module's goal isn't to "fix" the previous modules, but to give a name and a contract to something that was already correct.

Assuming this module is going to consolidate the three facts into one. What happens: someone, seeing the word "coexisting" in the module's description, expects lesson 6 or the final project to merge fact_orders, fact_sessions, and fact_store_activity into one bigger table. Why it happens: "making coexist" sounds, in everyday language, like "joining into a single thing." How to spot it: if you expect to find, somewhere in this module, a UNION or a JOIN that combines all three fact tables into a single row per event, you're not going to find one — each keeps its own grain, its own table, its own type. How to fix it: "coexisting" in this module means "sharing conformed dimensions" (lesson 6 demonstrates it with dim_date), not "merging into one table." Three facts with three different grains should never be combined into a single table — that would immediately break the grain definition module 1 taught you to declare so carefully.

Expecting validate_gold_schema() to also validate data, not just schema. What happens: someone expects this module's function to detect, in addition to missing or wrong-typed columns, problems like duplicate rows, unexpected nulls, or negative revenue. Why it happens: "validate" is a broad word, and the previous modules already built several forms of data validation (validate_orders() in foundations, module 1's grain assert). How to spot it: if you expect validate_gold_schema() to receive data rows as an argument, or to report something like "there are 3 rows with negative revenue," you're going to find a much narrower function than expected. How to fix it: exactly as the name states, validate_gold_schema() validates schema — column names and types — not content. That's precisely the boundary this guide's design declares: this module's "contract" is about a table's shape, not its values — full data validation, published as a system, is the territory of data-reliability-and-governance-guide.

Exercises

Exercise 1 — Classify a hypothetical new Kiosko table. If Kiosko added a fact_inventory_snapshot table tomorrow — one row per product, per store, at the close of each day, with the quantity of units in stock — what type of fact would it be, of the three you already saw in this lesson's inventory (transactional, accumulating snapshot, cumulative)? Justify in 2-3 sentences.

See solution

None of the three exactly — it would be a fourth type, the periodic snapshot fact table module 6's project already named without building: one row per fixed period (each day's close), describing a state at that instant (how many units there are), not an advancing process (like fact_sessions) nor a summary that builds on itself day by day with arrays (like fact_store_activity). The clearest clue: each row of a periodic snapshot is independent of the previous day's row — today's inventory isn't calculated by "adding" yesterday's, it gets measured again each day — unlike fact_store_activity, where today's revenue_array_7d does depend directly on yesterday's array.

Exercise 2 — Count how many tables in this lesson's inventory use a surrogate key as their primary key. Using KIOSKO_DOMAIN, without looking at the full code again, identify from memory which tables use a surrogate key (_key) as their main identifier and which don't.

See solution 
surrogate_key_tables = [t for t, kind, origin, rows, keys in KIOSKO_DOMAIN if "surrogate PK" in keys or "smart key" in keys]
print(surrogate_key_tables)

Expected output:

['dim_store', 'dim_product', 'dim_product_scd', 'dim_date']

The four dimensions use a surrogate key — store_key, product_key (twice, in its simple and historized versions), date_key — while the three fact tables don't have a single surrogate key of their own: fact_orders is identified by its grain (order_id + product_id), fact_sessions by session_id (a natural key the clickstream already provides), and fact_store_activity by the combination store_id + activity_date. This pattern — dimensions with a surrogate key, facts identified by their grain — is consistent across the seven tables of Kiosko's domain.

Exercise 3 — Explain, from memory, why fact_orders (40 rows) and fact_sessions (17 rows) can't be joined directly by any common key without losing or duplicating information. In 2-3 sentences, explain what key these two tables would need to share to join cleanly, and why that key doesn't exist today in Kiosko's domain.

See solution

fact_orders has the grain of "an order line" (identified by order_id + product_id) and fact_sessions has the grain of "a complete session" (identified by session_id) — there's no column, in Kiosko's current domain, connecting a specific order to the browsing session that originated it (something like a session_id inside fact_orders, or an order_id inside fact_sessions). Without that shared key, any attempt to join the two tables — for example, by store_id and date — would produce a many-to-many JOIN that multiplies rows with no real business meaning, exactly the kind of error module 1 taught you to detect by comparing COUNT(*) against COUNT(DISTINCT ...).

Summary and next step

This module opens with an inventory, not new code: seven tables — three facts, four dimensions — that modules 1 through 6 already built and verified, each with the exact type its business process needed. That's called, with technical precision and no negative connotation, a messy domain: several business processes coexisting, sharing conformed dimensions, not fitting into a single star's diagram. This module adds two pieces that domain still doesn't have: a junk dimension that groups low-cardinality attributes instead of multiplying loose columns, and a formal contract — validate_gold_schema() — that confirms, with executed evidence, that the guide's four gold tables maintain the schema the rest of the warehouse expects.

Before moving on you should be able to: name the seven tables of Kiosko's current domain and their exact type; explain why "messy domain" describes coexisting processes, not bad design; and anticipate the two new pieces this module is going to build (junk dimension, schema contract).

Lesson 2 deepens the central argument: why, in a real domain, "one fact and two dimensions" stops being enough as soon as the business adds a second process to measure — with this lesson's inventory as evidence that this has already happened to Kiosko.

Resources

← PreviousMini-project: Kiosko's funnel and cumulative activityNext →When one fact and two dimensions is not enough