Module 7: Messy Domains And Medallion At Depth

Multiple facts, a single conformed calendar

Description

This module's lesson 2 measured that dim_date already serves two facts — fact_orders and, in spirit, fact_sessions — but never completed the real join with an explicit JOIN beyond fact_orders. This lesson closes that debt: it joins Kiosko's three facts — fact_orders, fact_sessions, fact_store_activity — against the same dim_date, and builds a single summary table that answers, day by day, three distinct business questions at once: how much Kiosko sold, how many browsing sessions there were, and how many stores had activity — without any of the three fact tables needing to change shape to make it happen.

Connection to the module. This lesson demonstrates, with executed evidence, the module title's central piece: "multiple facts, a single conformed calendar." It picks up dim_date exactly as module 2 left it — thirty-one rows, all of August 2026, generated independently of any fact — and confirms that independence was, precisely, what let it serve three business processes without any of the three having to negotiate with the other two.

An analogy: the same wall calendar, three different agendas hanging below it

Pick back up module 2's analogy: a wall calendar, printed once, before any event that needs recording on it exists. Now imagine three different agendas hanging below that same calendar — one for sales, one for customer visits, one for each branch's operational activity — each recording its own events on the same shared calendar's dates. The sales manager can check only their own agenda; the operations manager, only theirs; but either one can, if needed, compare both agendas against the same day on the same calendar, with no date translation between them — August 8th is August 8th for all three agendas, unambiguously.

This lesson hangs, literally, Kiosko's three agendas — fact_orders, fact_sessions, fact_store_activity — below the same calendar — dim_date — and reads them together for the first time.

The material you need

You need, in the same folder: kiosko.py, raw_orders.py, and events.py (identical to the previous modules). You don't need any additional file.

Worked example: the three facts, against the same dim_date

With the warehouse rebuilt — fact_orders, dim_date, fact_sessions, fact_store_activity, exactly as modules 1 through 6 left them, no change at all — the first join: fact_orders summarized by day, against dim_date.

# shared_calendar.py -- continues on top of con, with the 4 inherited tables already rebuilt
print("=== fact_orders summarized by day, joined against dim_date ===")
print(con.sql("""
    SELECT d.calendar_date, d.day_of_week, COUNT(*) AS orders, ROUND(SUM(o.revenue), 2) AS revenue
    FROM dim_date d JOIN fact_orders o ON CAST(o.order_ts AS DATE) = d.calendar_date
    GROUP BY d.calendar_date, d.day_of_week
    ORDER BY d.calendar_date
"""))

What to expect.

=== fact_orders summarized by day, joined against dim_date ===
┌───────────────┬─────────────┬────────┬─────────┐
│ calendar_date │ day_of_week │ orders │ revenue │
│     date      │   varchar   │ int64  │ double  │
├───────────────┼─────────────┼────────┼─────────┤
│ 2026-08-03    │ Monday      │      8 │   15.85 │
│ 2026-08-04    │ Tuesday     │      6 │   15.85 │
│ 2026-08-05    │ Wednesday   │      2 │    9.55 │
│ 2026-08-06    │ Thursday    │      5 │   11.05 │
│ 2026-08-07    │ Friday      │      7 │   18.05 │
│ 2026-08-08    │ Saturday    │      9 │   31.85 │
│ 2026-08-09    │ Sunday      │      3 │    3.95 │
└───────────────┴─────────────┴────────┴─────────┘

Eight, six, two, five, seven, nine, three — the same daily counts from Kiosko's week you've known since module 1, this time grouped through dim_date instead of directly on order_ts. Now, the join no previous module made: fact_sessions.session_date against that same dim_date.calendar_date.

print("\n=== fact_sessions summarized by day, joined against dim_date ===")
print(con.sql("""
    SELECT d.calendar_date, d.day_of_week, COUNT(*) AS sessions
    FROM dim_date d JOIN fact_sessions s ON s.session_date = d.calendar_date
    GROUP BY d.calendar_date, d.day_of_week
    ORDER BY d.calendar_date
"""))

What to expect.

=== fact_sessions summarized by day, joined against dim_date ===
┌───────────────┬─────────────┬──────────┐
│ calendar_date │ day_of_week │ sessions │
│     date      │   varchar   │  int64   │
├───────────────┼─────────────┼──────────┤
│ 2026-08-03    │ Monday      │        2 │
│ 2026-08-04    │ Tuesday     │        3 │
│ 2026-08-05    │ Wednesday   │        2 │
│ 2026-08-06    │ Thursday    │        2 │
│ 2026-08-07    │ Friday      │        3 │
│ 2026-08-08    │ Saturday    │        3 │
│ 2026-08-09    │ Sunday      │        2 │
└───────────────┴─────────────┴──────────┘

Notice something important this JOIN didn't need: no type conversion, no strftime, no trick. fact_orders.order_ts is a TIMESTAMP — it needs CAST(... AS DATE) to find its date — but fact_sessions.session_date is already a DATE, ever since module 6 computed it with MIN(CAST(event_ts AS DATE)). Both ways of joining are valid; the difference is how close each fact column already is to dim_date.calendar_date's type (also DATE).

The three agendas, hanging from the same calendar

Now, the table that summarizes Kiosko's whole business day by day, with all three facts joined against dim_date at once — and, deliberately, extended one day before and one day after the week with data, to confirm dim_date stays independent of any fact, exactly as module 2 demonstrated.

# three_facts_one_calendar.py -- continues on top of con
print("\n=== The 3 facts, a single conformed calendar (includes days with no activity) ===")
print(con.sql("""
    SELECT d.calendar_date, d.day_of_week, d.is_weekend,
           COALESCE(o.orders, 0) AS orders,
           COALESCE(o.revenue, 0) AS revenue,
           COALESCE(s.sessions, 0) AS sessions,
           COALESCE(a.stores_active, 0) AS stores_active
    FROM dim_date d
    LEFT JOIN (
        SELECT CAST(order_ts AS DATE) AS d, COUNT(*) AS orders, ROUND(SUM(revenue), 2) AS revenue
        FROM fact_orders GROUP BY 1
    ) o ON o.d = d.calendar_date
    LEFT JOIN (
        SELECT session_date AS d, COUNT(*) AS sessions FROM fact_sessions GROUP BY 1
    ) s ON s.d = d.calendar_date
    LEFT JOIN (
        SELECT activity_date AS d, COUNT(DISTINCT store_id) AS stores_active
        FROM fact_store_activity WHERE daily_revenue > 0 GROUP BY 1
    ) a ON a.d = d.calendar_date
    WHERE d.calendar_date BETWEEN '2026-08-01' AND '2026-08-10'
    ORDER BY d.calendar_date
"""))

What to expect.

=== The 3 facts, a single conformed calendar (includes days with no activity) ===
┌───────────────┬─────────────┬────────────┬────────┬─────────┬──────────┬───────────────┐
│ calendar_date │ day_of_week │ is_weekend │ orders │ revenue │ sessions │ stores_active │
│     date      │   varchar   │  boolean   │ int64  │ double  │  int64   │     int64     │
├───────────────┼─────────────┼────────────┼────────┼─────────┼──────────┼───────────────┤
│ 2026-08-01    │ Saturday    │ true       │      0 │     0.0 │        0 │             0 │
│ 2026-08-02    │ Sunday      │ true       │      0 │     0.0 │        0 │             0 │
│ 2026-08-03    │ Monday      │ false      │      8 │   15.85 │        2 │             3 │
│ 2026-08-04    │ Tuesday     │ false      │      6 │   15.85 │        3 │             3 │
│ 2026-08-05    │ Wednesday   │ false      │      2 │    9.55 │        2 │             2 │
│ 2026-08-06    │ Thursday    │ false      │      5 │   11.05 │        2 │             3 │
│ 2026-08-07    │ Friday      │ false      │      7 │   18.05 │        3 │             3 │
│ 2026-08-08    │ Saturday    │ true       │      9 │   31.85 │        3 │             3 │
│ 2026-08-09    │ Sunday      │ true       │      3 │    3.95 │        2 │             3 │
│ 2026-08-10    │ Monday      │ false      │      0 │     0.0 │        0 │             0 │
└───────────────┴─────────────┴────────────┴────────┴─────────┴──────────┴───────────────┘
  10 rows                                                                      7 columns

Ten rows, three facts, one LEFT JOIN for each, and two days — 2026-08-01 and 2026-08-10 — where all three business columns consistently fall to zero: dim_date didn't need any fact to have data on that day to include it in the result, because its calendar exists completely independently, exactly as module 2 designed it. This is the final proof that dim_date is conformed in the fullest sense: not only do three facts share it — lesson 2 already measured that — but none of the three had to alter its shape, its grain, or its build logic to do so.

Cross-verification: this table's sums equal the already-known totals

totals = con.sql("""
    SELECT SUM(orders) AS total_orders, ROUND(SUM(revenue), 2) AS total_revenue, SUM(sessions) AS total_sessions
    FROM (
        SELECT COALESCE(o.orders, 0) AS orders, COALESCE(o.revenue, 0) AS revenue, COALESCE(s.sessions, 0) AS sessions
        FROM dim_date d
        LEFT JOIN (SELECT CAST(order_ts AS DATE) AS d, COUNT(*) AS orders, SUM(revenue) AS revenue FROM fact_orders GROUP BY 1) o ON o.d = d.calendar_date
        LEFT JOIN (SELECT session_date AS d, COUNT(*) AS sessions FROM fact_sessions GROUP BY 1) s ON s.d = d.calendar_date
    )
""").fetchone()

total_orders, total_revenue, total_sessions = totals
print(f"total_orders={total_orders}, total_revenue={total_revenue}, total_sessions={total_sessions}")
assert (total_orders, total_revenue, total_sessions) == (40, 106.15, 17)
print("Verification OK: totals via dim_date match what was already known (M1: 40/106.15, M6: 17)")

What to expect.

total_orders=40, total_revenue=106.15, total_sessions=17
Verification OK: totals via dim_date match what was already known (M1: 40/106.15, M6: 17)

Summing the complete thirty-one-day dim_date table — including the twenty-four days with no activity at all — produces, exactly, the same totals you already knew: forty orders and 106.15 in revenue since module 1, seventeen sessions since module 6. Joining against a complete calendar, instead of only against the days with data, doesn't change any business total — it only makes the days with no activity visible, something joining fact_orders directly against fact_sessions (without going through dim_date) could never show.

Diagram: three facts, one calendar, no negotiation between them

flowchart TD
    D["dim_date\n31 rows, all of August 2026\nGENERATED WITHOUT LOOKING AT ANY FACT (M2)"]
    D -->|"CAST(order_ts AS DATE)\n= calendar_date"| FO["fact_orders\n40 rows, unchanged"]
    D -->|"session_date\n= calendar_date"| FS["fact_sessions\n17 rows, unchanged"]
    D -->|"activity_date\n= calendar_date"| FA["fact_store_activity\n21 rows, unchanged"]

Going deeper: why this wasn't possible before this module

It's worth asking why this three-fact join lives here, in module 7, and not earlier. The answer isn't technical — this lesson's JOIN uses no new syntax, nothing module 2 hadn't already taught; it's about sequence: fact_sessions and fact_store_activity didn't exist until module 6. Joining three facts against a shared calendar only makes sense once all three facts exist, verified, with their own grain already declared and correct. This module is, literally, the guide's first point where that join is possible — which is why its title explicitly names "multiple facts, a single conformed calendar" as part of the "messy domain" that only appears once the business grows beyond one process.

Common mistakes

Joining fact_orders and fact_sessions directly against each other, instead of each one against dim_date. What happens: someone, looking to compare sales and sessions by day, writes fact_orders f JOIN fact_sessions s ON CAST(f.order_ts AS DATE) = s.session_date, joining the two facts against each other without going through dim_date. Why it happens: if both already have a date column, it seems unnecessary to introduce a third table in between. How to spot it: if your result has fewer than ten rows for the 2026-08-01 through 2026-08-10 window, or if you lose days with no sessions or no orders, your direct fact-to-fact JOIN behaves like an implicit INNER JOIN that drops any day where one of the two facts has no rows. How to fix it: each fact joins against dim_date, with a LEFT JOIN from the calendar — never facts against each other — exactly as this lesson did. That's precisely the definition of "conformed calendar": the meeting point is the shared dimension, not one fact against another.

Forgetting the LEFT JOIN and losing days with no activity. What happens: someone uses JOIN (equivalent to INNER JOIN) instead of LEFT JOIN from dim_date toward each aggregated subquery, and the final result only shows days where all three facts had activity simultaneously. Why it happens: JOIN is the shorter word and the one written out of habit most often. How to spot it: if your final ten-day table — 2026-08-01 through 2026-08-10 — shows fewer than ten rows, you lost days where some fact had no activity. How to fix it: dim_date should always be the left side of a LEFT JOIN when the goal is showing the complete calendar — including days with no event at all; use COALESCE(..., 0) so those days show up as zero, not as missing rows.

Assuming dim_date needs a different column for each fact that uses it. What happens: someone, seeing fact_orders uses order_ts (TIMESTAMP) and fact_sessions uses session_date (DATE), concludes dim_date would need separate columns — like date_key_for_orders, date_key_for_sessions — to serve each one. Why it happens: each fact reaches the date with a slightly different column type, and that seems to demand different treatment in the dimension. How to spot it: if you duplicate columns in dim_date to "better serve" each fact, you missed the central point of a conformed dimension — its shape is a single one, and it's each fact that adapts to it (with a CAST when needed, as in fact_orders), not the other way around. How to fix it: dim_date keeps its seven original columns, unchanged, no matter how many facts query it — the type adaptation, when needed, lives in the query that joins the fact against the dimension, never in the dimension itself.

Exercises

Exercise 1 — Calculate which day had the best combination of sales and sessions. Using the worked example's ten-day table, identify the day with the highest revenue and highest sessions at the same time, if one exists.

See solution

print(con.sql("""
    SELECT d.calendar_date, d.day_of_week,
           COALESCE(o.orders, 0) AS orders, COALESCE(o.revenue, 0) AS revenue, COALESCE(s.sessions, 0) AS sessions
    FROM dim_date d
    LEFT JOIN (SELECT CAST(order_ts AS DATE) AS d, COUNT(*) AS orders, ROUND(SUM(revenue), 2) AS revenue FROM fact_orders GROUP BY 1) o ON o.d = d.calendar_date
    LEFT JOIN (SELECT session_date AS d, COUNT(*) AS sessions FROM fact_sessions GROUP BY 1) s ON s.d = d.calendar_date
    WHERE d.calendar_date BETWEEN '2026-08-03' AND '2026-08-09'
    ORDER BY o.revenue DESC, s.sessions DESC
    LIMIT 1
"""))

Expected output:

┌───────────────┬─────────────┬────────┬─────────┬──────────┐
│ calendar_date │ day_of_week │ orders │ revenue │ sessions │
│     date      │   varchar   │ int64  │ double  │  int64   │
├───────────────┼─────────────┼────────┼─────────┼──────────┤
│ 2026-08-08    │ Saturday    │      9 │   31.85 │        3 │
└───────────────┴─────────────┴────────┴─────────┴──────────┘

2026-08-08 (Saturday) has the highest revenue (31.85) of the whole week and ties for the maximum number of sessions (3, along with Tuesday and Friday) — Kiosko's best combined day, according to both facts at once, something that can only be answered with both joined against the same calendar.

Exercise 2 — Confirm no day in August 2026 has more sessions than orders that week. Without using the complete ten-day table, write a query that compares, for each day with data, sessions against orders, and counts how many days had more sessions than orders.

See solution

result = con.sql("""
    SELECT COUNT(*) AS days_with_more_sessions_than_orders
    FROM (
        SELECT COALESCE(o.orders, 0) AS orders, COALESCE(s.sessions, 0) AS sessions
        FROM dim_date d
        LEFT JOIN (SELECT CAST(order_ts AS DATE) AS d, COUNT(*) AS orders FROM fact_orders GROUP BY 1) o ON o.d = d.calendar_date
        LEFT JOIN (SELECT session_date AS d, COUNT(*) AS sessions FROM fact_sessions GROUP BY 1) s ON s.d = d.calendar_date
        WHERE d.calendar_date BETWEEN '2026-08-03' AND '2026-08-09'
    )
    WHERE sessions > orders
""").fetchone()[0]
print(f"days with more sessions than orders: {result}")

Expected output:

days with more sessions than orders: 0

Zero days — in each of Kiosko's seven days that week, the number of orders was equal to or greater than the number of sessions recorded, consistent with the fact that sessions are only one among several sources of Kiosko's sales (most sales recorded in fact_orders come from the physical point of sale, not only from the digital channel events describes).

Exercise 3 — Explain, from memory, why is_weekend (a dim_date column) can answer questions about all three facts at once, without any of the three storing it. In 2-3 sentences, explain how fact_orders, fact_sessions, and fact_store_activity can be filtered or grouped by weekend without any of the three having their own is_weekend column.

See solution

is_weekend lives once, in dim_date, calculated independently of any fact — weekday_index >= 5, per module 2 — any fact that joins against dim_date by its date column automatically inherits that classification, with no need for its own copy. This is, precisely, a conformed dimension's central benefit: an attribute calculated once — is_weekend — becomes available to group or filter any number of present or future facts, with none of them having to recalculate or store it separately. If fact_store_activity needed to filter by weekend tomorrow, it would just need to join against dim_date — it wouldn't need any schema change of its own.

Summary and next step

This lesson joined, for the first time in this guide, Kiosko's three facts — fact_orders, fact_sessions, fact_store_activity — against the same dim_date, with a LEFT JOIN from the calendar toward each one, including days with no activity to demonstrate that dim_date's independence (designed since module 2) holds even while serving three business processes at once. The cross-verification confirmed that summing across the complete calendar produces exactly the same totals already known: forty orders, 106.15 in revenue, seventeen sessions.

Before moving on you should be able to: write the LEFT JOIN pattern from dim_date toward a per-fact aggregated subquery from memory; explain why facts never join directly against each other, but each one against the shared dimension; and justify why is_weekend doesn't need to exist in any fact table.

Lesson 7 closes the module's technical argument with the question still remaining: if a gold table's schema genuinely needs to change — not an accident, but a real business evolution — how do you do it without breaking the contract validate_gold_schema() already protects?

Resources

Kimball Group — "Conformed Dimensions" — the source that precisely defines what it means for a dimension to serve multiple processes without changing its shape — this lesson's central argument. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/conformed-dimension. In English.
Kimball Group — "Star Schema / OLAP Cube" — the general bus matrix framework, where several business processes share conformed dimensions, applied here with three real Kiosko facts. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. In English.
DuckDB — official documentation on date and time types (DATE, TIMESTAMP, and their conversion with CAST), used to join each fact against dim_date with the right type. duckdb.org/docs/current/sql/data_types/overview. In English.
DuckDB — official Python client documentation, the interface that runs every query in this lesson. duckdb.org/docs/current/clients/python/overview. In English.

← PreviousMedallion contracts between bronze, silver, and gold Next →Schema evolution without breaking gold