Module 2: The Star Schema And Conformed Dimensions

Anatomy of a star schema

Next →

Description

This lesson answers a question module 1 left, on purpose, unresolved: what makes a table physically a "fact" and another a "dimension," beyond the vocabulary you already learned (additive measures, descriptive context)? The answer has a behavioral part — you already know it — and a shape part: a fact is a tall, narrow table (many rows, few columns), a dimension is a short, wide table (few rows, more descriptive columns), and a star schema is exactly that: a fact at the center, with its dimensions around it, each one a single JOIN away.

Connection to the module. This lesson doesn't build any new table — it uses fact_orders, dim_store, and dim_product exactly as module 1 left them, with a natural key. What it builds is the shape vocabulary lessons 3 and 4 are going to modify (adding surrogate keys and dim_date), and that lesson 7 is going to fully assemble.

An analogy: the closet with everything within reach

Go back to this module's introduction analogy: a well-organized closet has every garment one motion away — open, see, grab — with no boxes nested inside other boxes. A star schema applies that exact idea to a warehouse: from fact_orders, any store or product attribute is a single JOIN away. Want to know which city a sale happened in: one JOIN to dim_store. Want to know the category of the product sold: one JOIN to dim_product. Never two hops, never an intermediate table you have to go through first.

The name "star" comes, literally, from the shape the diagram takes: the fact table at the center, and each dimension as a point of the star, connected directly to the center and to no other point. A snowflake schema — which module 3 is going to build, not this lesson — breaks that shape: it normalizes one dimension inside another (for example, dim_product pointing to dim_category instead of having category as its own column), and the diagram stops looking like a clean star and starts looking more like a snowflake, with branches. This lesson builds the star; module 3 explains, with JOIN-cost evidence, when it's worth breaking it.

Worked example: the physical shape of fact_orders, dim_store, and dim_product

Before looking at any diagram, verify the shape of the three tables you already have, with a real query against DuckDB. Start from module 1's kiosko.py and raw_orders.py — identical, with no change — to rebuild the three tables exactly as they stood at the end of that module.

# anatomy.py
from datetime import datetime

import duckdb

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

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 = duckdb.connect()
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],
)
con.execute("CREATE TABLE dim_store (store_id VARCHAR, store_name VARCHAR, city VARCHAR)")
con.executemany("INSERT INTO dim_store VALUES (?, ?, ?)",
                 [(s["store_id"], s["store_name"], s["city"]) for s in DIM_STORE])
con.execute("CREATE TABLE dim_product (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)")
con.executemany("INSERT INTO dim_product VALUES (?, ?, ?, ?)",
                 [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT])

print("=== Shape of each table: rows vs columns ===")
print(con.sql("""
    SELECT 'fact_orders' AS table_name,
           (SELECT COUNT(*) FROM fact_orders) AS row_count,
           (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'fact_orders') AS column_count
    UNION ALL
    SELECT 'dim_store',
           (SELECT COUNT(*) FROM dim_store),
           (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'dim_store')
    UNION ALL
    SELECT 'dim_product',
           (SELECT COUNT(*) FROM dim_product),
           (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'dim_product')
    ORDER BY table_name
"""))

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

=== Shape of each table: rows vs columns ===
┌─────────────┬───────────┬──────────────┐
│ table_name  │ row_count │ column_count │
│   varchar   │   int64   │    int64     │
├─────────────┼───────────┼──────────────┤
│ dim_product │         4 │            4 │
│ dim_store   │         3 │            3 │
│ fact_orders │        40 │            7 │
└─────────────┴───────────┴──────────────┘

There's the anatomy, in numbers: fact_orders has ten times more rows than dim_store (40 versus 3) and almost thirteen times more than dim_product (40 versus 4 rows), but fewer columns than both combined. This is, exactly, the physical signature of a well-formed star schema: facts are tall, narrow tables — they grow without limit with every new business event — and dimensions are short, wide tables — they grow slowly, almost never in rows, sometimes in columns when a new descriptive attribute gets added. dim_store is going to keep having three rows even if Kiosko processes a million orders; fact_orders is going to keep growing, one row per new order line, with no natural limit.

Diagram: the star shape

flowchart TD
    F["fact_orders\n(40 rows, 7 columns)\norder_id, store_id, product_id,\nquantity, unit_price, revenue, order_ts"]

    S["dim_store\n(3 rows, 3 columns)\nstore_id, store_name, city"]
    P["dim_product\n(4 rows, 4 columns)\nproduct_id, product_name, category, unit_cost"]
    D["dim_date\n(built in lesson 4)\ndate_key, calendar_date, day_of_week..."]

    S ---|"1 JOIN"| F
    P ---|"1 JOIN"| F
    D -.->|"1 JOIN\n(pending until L4)"| F

Notice dim_date shows up in the diagram with a dotted line — it doesn't exist yet, it gets built in lesson 4 of this module, but the diagram shows the full destination: the star schema this entire module is building, piece by piece. Every point of the star connects to the center with exactly one JOIN, and no point connects directly to another point — that is, precisely, the rule that defines the star shape.

Going deeper: the practical test for "is this a fact or a dimension?"

Beyond behavior (additive vs. descriptive, already covered in module 1), there's a quick physical test you can apply to any new table you come across in a real warehouse: does this table grow with every business event, or does it describe something relatively stable that already existed before the event? A sales table grows with every sale — it's a fact. A stores table doesn't grow with every sale — Kiosko doesn't open a new store every time someone buys a coffee — it's a dimension.

This test has an important exception worth naming here, even though this guide doesn't build it yet: some dimensions do change over time — a product's price, a product's category — and when that happens, the dimension needs a strategy for historizing that change without losing the past. That's exactly what module 4 (Slowly Changing Dimensions) solves. For now, with dim_store and dim_product completely static — three fixed stores, four fixed products, with no change during Kiosko's entire week — the simple test (does it grow with every event?) is enough.

Another useful observation, already present in this lesson's numbers: notice that dim_product, with four columns, already has more columns than dim_store, with three. This isn't a coincidence or a fixed limit — dimensions tend to accumulate columns over time, as the business needs to describe its entities in more detail (a product might eventually get brand, supplier, weight_grams...), while facts tend to keep a more stable number of columns, defined by their grain — adding a new column to a fact usually means the grain changed, something lesson 7 of module 1 already taught you to take seriously.

Common mistakes

Confusing "fewer columns" with "less important." What happens: someone, seeing that fact_orders has only seven columns against dim_product's four, assumes the dimension is "richer" or "more complete," and that the fact is a secondary table. Why it happens: in everyday language, "has more columns" sounds like "has more information," and it's easy to transfer that intuition to the dimensional model without questioning it. How to spot it: if your reasoning about which table is "the center" of the model is based on the number of columns instead of the number of rows and the business role (does this measure an event, or describe an entity?), you're using the wrong criterion. How to fix it: the fact is the star's center precisely because it's the table that grows — the one that accumulates the complete history of business events; dimensions are satellites that give context to that history, regardless of how many columns they have.

Thinking a complete star schema needs many dimensions to be "real." What happens: someone sees Kiosko's star with only three dimensions (soon four, with dim_date) and feels it's "too simple" compared to production warehouse examples with fifteen or twenty dimensions. Why it happens: industry examples, in books and conference talks, tend to show large, complex models, and it's easy to assume complexity is a requirement, not a consequence. How to spot it: if you feel the urge to "invent" extra dimensions for Kiosko that no real business process actually needs, you're optimizing for appearance, not necessity. How to fix it: the number of dimensions is determined by the business process, not an arbitrary goal — Kiosko has three (soon four) because those are exactly what its sales process needs to answer its real questions. A production warehouse with twenty dimensions probably has twenty distinct business processes behind it, not a design whim.

Believing any table with a primary key is automatically a dimension. What happens: someone sees any table with a column uniquely identifying each row and classifies it as a "dimension," without evaluating whether it grows with every business event. Why it happens: most dimensions do have a clear primary key, and it's tempting to use that trait as the definitive test. How to spot it: an audit log table, for example, can also have a unique primary key per row (a log_id), but it grows with every system event — by behavior, it's much closer to a fact than to a dimension, even though it superficially looks like one. How to fix it: always use both tests together — does it grow with every business event? and does it describe something relatively stable? — never just the shape of the primary key.

Exercises

Exercise 1 — Classify a hypothetical Kiosko table. Imagine Kiosko adds a dim_employee table (employee_id, employee_name, hire_date, store_id) to track each store's employees. Without writing code, explain in 2-3 sentences whether this table is a fact or a dimension, using this lesson's test (does it grow with every business event, or does it describe something relatively stable?).

See solution

dim_employee is a dimension: it describes a relatively stable entity (a Kiosko employee), not a business event that occurs repeatedly. Even though the table would eventually grow as Kiosko hires new employees, that growth would be much slower and less frequent than fact_orders's, which gains a new row with every order line sold — not with every hire. The name itself, with the dim_ prefix, already follows this guide's convention for naming dimensions, consistent with the behavioral classification.

Exercise 2 — Verify a new table's shape with SQL. Using the worked example's same query pattern (counting rows and columns with information_schema.columns), write the query that would confirm the shape of a hypothetical dim_employee with five employees and four columns — without running it against any real table, just write the SQL you'd use.

See solution 
SELECT 'dim_employee' AS table_name,
       (SELECT COUNT(*) FROM dim_employee) AS row_count,
       (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'dim_employee') AS column_count

With exercise 1's hypothetical data (five employees, four columns), this query should return row_count = 5 and column_count = 4 — few rows, typical dimension shape, consistent with the classification you already made in the previous exercise.

Exercise 3 — Calculate how many columns Kiosko's two current dimensions add up to, combined. Using fact_orders, dim_store, and dim_product already loaded into DuckDB by the worked example, write and run a query that sums dim_store's and dim_product's column counts together, and compares it against fact_orders's column count.

See solution 
print(con.sql("""
    SELECT
        (SELECT COUNT(*) FROM information_schema.columns WHERE table_name='dim_store') +
        (SELECT COUNT(*) FROM information_schema.columns WHERE table_name='dim_product') AS total_dim_columns,
        (SELECT COUNT(*) FROM information_schema.columns WHERE table_name='fact_orders') AS fact_columns
"""))

Expected output:

┌───────────────────┬──────────────┐
│ total_dim_columns │ fact_columns │
│       int64       │    int64     │
├───────────────────┼──────────────┤
│                 7 │            7 │
└───────────────────┴──────────────┘

dim_store (3 columns) plus dim_product (4 columns) add up to exactly 7 — the same number of columns fact_orders has on its own. It's a numeric coincidence of this specific week of Kiosko data, not a general rule of dimensional modeling — don't expect it to always match like this — but it's useful for noticing something real: today, with the dimensions still in module 1's natural shape, none of the three tables is dramatically "wider" than the others. Lesson 3 changes that number: once store_key and product_key are added, each dimension gains one more column (4 + 5 = 9 total), and the dimensions start outnumbering fact_orders in columns — the pattern this lesson's "going deeper" section already anticipated.

Summary and next step

In this lesson you verified, with a real query, a star schema's physical anatomy: fact_orders is a tall, narrow table (40 rows, 7 columns) that grows with every order line; dim_store and dim_product are short, wide tables (3 and 4 rows respectively) that describe relatively stable entities. The name "star" comes from the diagram's shape: the fact at the center, each dimension a single JOIN away, with no dimension connected directly to another.

Before moving on you should be able to: explain the physical difference between a fact and a dimension using rows and columns, not just behavior; draw Kiosko's star diagram from memory with its three (soon four) points; and describe, in one sentence, what distinguishes a star schema from a snowflake schema.

Lesson 3 tackles the first structural piece still missing: dim_store and dim_product still use a natural key. The next lesson adds surrogate keys — store_key, product_key — and explains, with a concrete analogy, why that decision matters even before module 4 makes it indispensable.

Resources

← PreviousModule introduction: the complete star schema and conformed dimensionsNext →Surrogate keys vs natural keys