When one fact and two dimensions is not enough

Description

This lesson builds, with executed evidence, the module's central argument: a real warehouse almost never stays at "one fact, two dimensions" — it grows by adding new business processes, not just new rows to the original process. You're going to rebuild Kiosko's complete warehouse — three facts, four dimensions — in a single DuckDB connection, and run a query no previous module needed: counting how many distinct fact tables share each dimension, the direct proof that dim_store and dim_date are already conformed dimensions in the strictest sense — they serve more than one business process at once.

Connection to the module. This lesson picks up lesson 1's inventory and turns it into executed code: it rebuilds Kiosko's domain's seven tables and measures, with a real query, how many facts each dimension shares — the numeric foundation lesson 6 is going to build its "one conformed calendar, three facts" argument on.

An analogy: a single patient's file, and the whole company's archive

Picture the paper archive of a small clinic with a single patient: one folder, with the patient's chart up front and the lab results behind it. Easy to design, easy to maintain — one archive, two sections. Now picture a full hospital's archive: hundreds of patients, each with their own folder (a dimension: "who"), but also an appointments archive (a fact: "when each visit happened"), a lab results archive (another fact, with its own arrival rhythm), a billing archive (a third fact, with its own unit of measure), all sharing the same patient directory and the same hospital calendar. Nobody designs a hospital's system thinking about a single patient and a single visit — it's designed knowing, from the start, that several processes are going to share the same patient directory and the same calendar.

Kiosko, six modules after its first fact_orders, is already that hospital, not that single-patient clinic. This lesson measures, with a real query, how many "processes" — facts — share each "directory" — dimension — of Kiosko's domain today.

The material you need

You need, in the same folder: kiosko.py, raw_orders.py, and events.py (identical to modules 1, 2, 4, and 6). You don't need any additional file — the session-store mapping and the per-store activity window are declared directly in this lesson's script, just like in the previous projects.

Worked example: rebuilding the complete domain, and measuring what each dimension shares

First, rebuild the domain's seven tables, exactly as modules 1 through 6 left them — with no change at all:

# domain_reconstruction.py
from datetime import date, datetime, timedelta

import duckdb

from kiosko import DIM_PRODUCT, DIM_STORE, Order, transform_fact_orders
from raw_orders import RAW_ORDERS
from events import RAW_EVENTS

DAY_NAMES = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]


def generate_date_dim(start_date: str, end_date: str) -> list[dict]:
    start = date.fromisoformat(start_date)
    end = date.fromisoformat(end_date)
    rows = []
    current = start
    while current <= end:
        weekday_index = current.weekday()
        rows.append({
            "date_key": int(current.strftime("%Y%m%d")), "calendar_date": current,
            "day_of_week": DAY_NAMES[weekday_index], "month": current.month,
            "quarter": (current.month - 1) // 3 + 1, "year": current.year,
            "is_weekend": weekday_index >= 5,
        })
        current += timedelta(days=1)
    return rows


con = duckdb.connect()

# --- fact_orders (module 1) ---
orders = [
    Order(order_id=r[0], store_id=r[1], product_id=r[2], quantity=r[3],
          unit_price=r[4], order_ts=datetime.fromisoformat(r[5]))
    for r in RAW_ORDERS
]
fact_orders = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)
con.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.executemany("INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["order_id"], r["store_id"], r["product_id"], r["quantity"],
      r["unit_price"], r["revenue"], r["order_ts"]) for r in fact_orders])

# --- dim_date (module 2) ---
dim_date_rows = generate_date_dim("2026-08-01", "2026-08-31")
con.execute("""
    CREATE TABLE dim_date (
        date_key INTEGER, calendar_date DATE, day_of_week VARCHAR,
        month INTEGER, quarter INTEGER, year INTEGER, is_weekend BOOLEAN
    )
""")
con.executemany("INSERT INTO dim_date VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["date_key"], r["calendar_date"], r["day_of_week"], r["month"],
      r["quarter"], r["year"], r["is_weekend"]) for r in dim_date_rows])

# --- events + fact_sessions (module 6) ---
con.execute("CREATE TABLE events (event_id VARCHAR, event_type VARCHAR, session_id VARCHAR, event_ts TIMESTAMP)")
con.executemany("INSERT INTO events VALUES (?, ?, ?, ?)",
    [(r[0], r[1], r[2], datetime.fromisoformat(r[3])) for r in RAW_EVENTS])

STORE_ROTATION = ["S01", "S02", "S03"]


def store_for_session(session_id: str) -> str:
    session_number = int(session_id.split("-")[1])
    return STORE_ROTATION[(session_number - 1) % 3]


ALL_SESSIONS = [f"SESS-{n:02d}" for n in range(1, 18)]
con.execute("CREATE TABLE session_store_map (session_id VARCHAR, store_id VARCHAR)")
con.executemany("INSERT INTO session_store_map VALUES (?, ?)",
    [(sid, store_for_session(sid)) for sid in ALL_SESSIONS])

con.execute("""
    CREATE TABLE fact_sessions AS
    SELECT e.session_id, m.store_id, MIN(CAST(e.event_ts AS DATE)) AS session_date,
        MAX(CASE WHEN e.event_type = 'page_view'   THEN e.event_ts END) AS view_ts,
        MAX(CASE WHEN e.event_type = 'add_to_cart' THEN e.event_ts END) AS add_to_cart_ts,
        MAX(CASE WHEN e.event_type = 'purchase'    THEN e.event_ts END) AS purchase_ts,
        MAX(CASE WHEN e.event_type = 'purchase' THEN true ELSE false END) AS is_converted
    FROM events e JOIN session_store_map m ON e.session_id = m.session_id
    GROUP BY e.session_id, m.store_id
""")

print("=== Kiosko's domain, rebuilt: 2 facts already present (fact_orders, fact_sessions) ===")
for table in ["fact_orders", "dim_date", "fact_sessions"]:
    count = con.sql(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    print(f"  {table:16} {count:3} rows")

What to expect.

=== Kiosko's domain, rebuilt: 2 facts already present (fact_orders, fact_sessions) ===
  fact_orders       40 rows
  dim_date          31 rows
  fact_sessions     17 rows

Now, this lesson's central question: how many facts does each dimension share? The answer is built with an explicit mapping table — fact by fact, dimension by dimension — and a query that aggregates it:

# shared_dimensions.py -- continues on top of con from the previous section
FACT_DIMENSION_USAGE = [
    ("fact_orders",   "dim_store"),
    ("fact_orders",   "dim_date"),
    ("fact_sessions", "dim_store"),
    ("fact_sessions", "dim_date"),
]

con.execute("CREATE TABLE fact_dimension_usage (fact_table VARCHAR, dimension_table VARCHAR)")
con.executemany("INSERT INTO fact_dimension_usage VALUES (?, ?)", FACT_DIMENSION_USAGE)

print("\n=== How many facts each dimension shares ===")
print(con.sql("""
    SELECT dimension_table, COUNT(DISTINCT fact_table) AS facts_sharing_it,
           STRING_AGG(DISTINCT fact_table, ', ' ORDER BY fact_table) AS which_facts
    FROM fact_dimension_usage
    GROUP BY dimension_table
    ORDER BY facts_sharing_it DESC, dimension_table
"""))

What to expect.

=== How many facts each dimension shares ===
┌─────────────────┬──────────────────┬────────────────────────────┐
│ dimension_table │ facts_sharing_it │        which_facts         │
│     varchar     │      int64       │           varchar          │
├─────────────────┼──────────────────┼────────────────────────────┤
│ dim_date        │                2 │ fact_orders, fact_sessions │
│ dim_store       │                2 │ fact_orders, fact_sessions │
└─────────────────┴──────────────────┴────────────────────────────┘

dim_date and dim_store already serve two distinct business processes — the sale and the browsing session — not one. This isn't a design coincidence: it's the operational definition of "conformed dimension" module 2 declared in theory and this query confirms with a number. And fact_store_activity — which also uses dim_store and, in spirit, dim_date (via activity_date) — is still missing from this table; lesson 6 completes that count with all three fact tables at once.

Diagram: why "one fact, two dimensions" no longer describes Kiosko

flowchart TD
    subgraph Facts["3 fact tables"]
        FO["fact_orders\n(transactional)"]
        FS["fact_sessions\n(accumulating snapshot)"]
        FA["fact_store_activity\n(cumulative)"]
    end

    subgraph Dims["Shared dimensions"]
        DS["dim_store\n(conformed)"]
        DD["dim_date\n(conformed)"]
    end

    FO --> DS
    FO --> DD
    FS --> DS
    FS -.-> DD
    FA --> DS
    FA -.-> DD

The dotted line marks connections that exist conceptually — fact_sessions.session_date and fact_store_activity.activity_date are dates from the same calendar dim_date describes — but that no previous module joined with an explicit JOIN. That's exactly the debt this module's lesson 6 pays off.

Going deeper: the quantitative argument, not just the qualitative one

Module 3 of this guide already showed you, with EXPLAIN, that a model's shape has a measurable cost. This lesson applies the same spirit to a different question: not "which shape is faster to query," but "how shared is the business vocabulary." A facts_sharing_it = 1 (a dimension that serves only one fact) isn't an error — dim_product and dim_product_scd, for example, today only serve fact_orders, because no other Kiosko fact yet needs to know what product was sold. But a facts_sharing_it = 2 or more, like the one you just measured for dim_store and dim_date, is the quantitative signal that dimension paid off its design cost several times over: it got built once (modules 1 and 2), and served, with no change at all, a second complete business process (module 6) that didn't even exist when it was designed.

This is, in concrete numbers, the practical reason Kimball insists so much on building conformed dimensions from the start, even when only one fact uses them: dim_store was designed in module 1 thinking only about fact_orders, but its shape — one row per store, with a surrogate key, no attribute specific to sales — turned out to be exactly the right shape for fact_sessions, a completely different process, to reuse with no change at all, five modules later.

Common mistakes

Confusing "shared dimension" with "combined fact table." What happens: someone, seeing dim_store serve two facts, assumes that means fact_orders and fact_sessions should be combined into a single, bigger table. Why it happens: it's easy to think "sharing something" implies "merging," when it's actually exactly the opposite of what this module argues for. How to spot it: if your conclusion from this lesson is that Kiosko should have one giant fact table instead of three, you missed the module's central argument — lesson 1's common mistake already warned about this explicitly. How to fix it: two facts with different grains (an order line, a complete session) must always stay in separate tables — the only thing they share is the dimension, not their own structure.

Measuring "how many facts share a dimension" by counting JOINs instead of counting distinct business processes. What happens: someone writes a query that counts how many times JOIN dim_store appears in the whole guide's source code, instead of counting how many distinct facts use it. Why it happens: counting text occurrences in code is mechanically simpler than reasoning about business processes. How to spot it: if your count of "facts sharing dim_store" includes, for example, module 3's mart_daily_sales_obt — a derived table, not an independent fact — your count mixes different concepts. How to fix it: the right question is "how many distinct business processes, each with its own declared grain, query this dimension?" — fact_orders and fact_sessions are two distinct processes; mart_daily_sales_obt is a derived view of fact_orders, not a new process.

Assuming every dimension should, eventually, be conformed by every fact. What happens: someone, excited by dim_store and dim_date serving two facts, concludes dim_product "should" also serve fact_sessions and fact_store_activity, and looks for a way to force that connection. Why it happens: if sharing is good, it seems reasonable to maximize it across every possible dimension. How to spot it: if you try to write a JOIN between fact_sessions and dim_product with no real column connecting them (fact_sessions doesn't record which product was viewed in each session, in Kiosko's current dataset), you're forcing a relationship the data doesn't support. How to fix it: a dimension gets conformed when the business genuinely needs it in more than one process — dim_store and dim_date naturally satisfy that (every sale and every session happen at a store, on a date); dim_product doesn't satisfy it today because fact_sessions, as this guide built it, doesn't record which product each session viewed. Forcing a conformance the data doesn't support is worse than leaving a dimension unshared.

Exercises

Exercise 1 — Add fact_store_activity to fact_dimension_usage and recalculate the count. Using the fact_dimension_usage table already built, add the row that's missing (fact_store_activity uses dim_store) and rerun the count query.

con.execute("INSERT INTO fact_dimension_usage VALUES ('fact_store_activity', 'dim_store')")
print(con.sql("""
    SELECT dimension_table, COUNT(DISTINCT fact_table) AS facts_sharing_it
    FROM fact_dimension_usage
    GROUP BY dimension_table
    ORDER BY facts_sharing_it DESC, dimension_table
"""))

┌─────────────────┬──────────────────┐
│ dimension_table │ facts_sharing_it │
│     varchar     │      int64       │
├─────────────────┼──────────────────┤
│ dim_store       │                3 │
│ dim_date        │                2 │
└─────────────────┴──────────────────┘

Exercise 2 — Verify that dim_product doesn't appear in fact_dimension_usage for any fact beyond fact_orders. Write a query that confirms, with an assert, that dim_product isn't registered as shared by fact_sessions or fact_store_activity in the mapping table.

product_usage = con.sql("""
    SELECT fact_table FROM fact_dimension_usage WHERE dimension_table = 'dim_product'
""").fetchall()
assert product_usage == [], "dim_product should not appear as shared in this domain"
print(f"dim_product: {len(product_usage)} facts registered (expected 0)")

dim_product: 0 facts registered (expected 0)

Exercise 3 — Explain, from memory, why "conformed" is a state you measure, not a label you declare in advance. In 2-3 sentences, explain the difference between deciding "I'm going to build dim_store so it's conformed" and discovering, as this lesson did, that dim_store turned out to be conformed.

Summary and next step

This lesson measured, with a real query, the module's central argument: dim_store and dim_date already serve two distinct business processes — fact_orders and fact_sessions — the exact operational definition of a conformed dimension. "One fact, two dimensions" described Kiosko in module 1; "three facts, four dimensions, two of them already shared" describes Kiosko today — and that growth, far from being a problem, is the signal the dimensions were designed well from the start.

Before moving on you should be able to: rebuild Kiosko's domain's seven tables from memory; write the query that counts how many facts each dimension shares; and explain why dim_product correctly remains a single-fact dimension.

Lesson 3 picks up the piece of the domain this module hasn't developed in depth yet: order_id, the degenerate dimension that has lived inside fact_orders since module 1, with no table of its own — with its full justification and real use cases.

Resources

• Kimball Group — "Conformed Dimensions" — the formal definition of a conformed dimension this lesson measures with a real query over dim_store and dim_date. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/conformed-dimension. In English.
• Kimball Group — "Four-Step Dimensional Design Process" — the same process from module 1, now visible in how each new Kiosko fact (sessions, activity) repeated its four steps over dimensions that already existed. kimballgroup.com/.../four-4-step-design-process. In English.
• DuckDB — documentation on text aggregate functions (STRING_AGG), used in this lesson to list the facts sharing each dimension. duckdb.org/docs/current/sql/functions/aggregates. 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.