Dimensional modelling — star and snowflake
Hook
You inherit a warehouse table called sales with 140 columns. Some rows are one
order, some are one order line, and a few are daily totals that a well-meaning
analyst appended. Every query starts with an argument about what a row means, and
half of them double-count. The table was never wrong on any single insert — it
just never decided what one row represents. That decision has a name, and making
it first is the whole discipline of dimensional modelling.
Concept
Dimensional modelling organizes analytics data into two kinds of table: facts, which hold the measurements of a business process, and dimensions, which hold the descriptive context you slice those measurements by. A fact table row is a measurement event — a sale, a click, a shipment — carrying numeric measures (quantity, revenue) and foreign keys to dimensions. A dimension table holds the "who, what, where, when" — customer, product, store, date — with one row per real-world thing and lots of descriptive columns.
The single most important decision is the grain: the precise meaning of one
fact-table row, stated in a sentence before any columns are chosen. "One row per
order line item." "One row per customer per day." "One row per shipment." Fix the
grain first, and everything else follows: the dimensions are whatever context is
true at that grain, and the measures are whatever is additive at that grain. Skip
it, and you get the 140-column table where nobody can trust a SUM.
Write the grain as a sentence and put it in a comment above the fact table. Every measure you add must be additive at that grain; if it is not (a ratio, a percentage, a running balance), store the additive parts and compute the ratio at query time.
A star schema is the layout that results: one fact table in the middle, dimensions around it, each joined by a single key. It is called a star because of its shape.
Dimensions in a star are denormalized on purpose: dim_product stores the
category name as a plain column, repeated on every product in that category. That
repetition is the point — a query filters by category with zero extra joins.
A snowflake schema normalizes a dimension back out into sub-tables: instead
of a category column on dim_product, you get a dim_category table that
dim_product references by key. It saves a little storage and keeps category
names in one place, at the cost of an extra join every time you slice by
category.
| Aspect | Star | Snowflake |
|---|---|---|
| Dimension shape | Denormalized, wide, flat | Normalized into sub-dimension tables |
| Joins to slice | One per dimension | One per level of the snowflake |
| Read performance | Faster — fewer joins | Slower — more joins |
| Storage/redundancy | More redundant attribute values | Less redundancy |
| When it fits | The default for query speed | Huge dimensions, or attributes reused across many dimensions |
The practitioner default is the star. Reach for a snowflake only when a dimension is genuinely enormous or a sub-hierarchy is shared across several dimensions and must stay consistent — and even then, know you are trading read speed for it.
Worked example
Let me build a tiny star and show two things: that the grain holds, and that a dimension lets me slice by an attribute the fact never stores. The grain here is one row per product per store per day.
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE dim_product (
product_key INTEGER PRIMARY KEY,
product_name TEXT,
category TEXT
);
CREATE TABLE dim_store (
store_key INTEGER PRIMARY KEY,
store_name TEXT,
region TEXT
);
-- Grain: one row per product per store per day.
CREATE TABLE fact_sales (
sale_key INTEGER PRIMARY KEY,
product_key INTEGER,
store_key INTEGER,
sale_date TEXT,
quantity INTEGER,
net_amount REAL
);
INSERT INTO dim_product VALUES
(1, 'Yirgacheffe 250g', 'Single Origin'),
(2, 'House Blend 500g', 'Blend');
INSERT INTO dim_store VALUES
(1, 'Riverside', 'West'),
(2, 'Old Town', 'East');
INSERT INTO fact_sales VALUES
(1, 1, 1, '2026-06-01', 2, 28.00),
(2, 2, 1, '2026-06-01', 1, 18.00),
(3, 1, 2, '2026-06-02', 3, 42.00),
(4, 2, 2, '2026-06-02', 1, 18.00);
""")
First, prove the grain. If one row really is one product-store-day, then no combination of those three keys appears more than once:
grain = con.execute("""
SELECT MAX(c) FROM (
SELECT COUNT(*) AS c
FROM fact_sales
GROUP BY product_key, store_key, sale_date
)
""").fetchone()[0]
print("max rows per grain key:", grain)
max rows per grain key: 1
A maximum of 1 means the declared grain holds — every measurement lands in its
own row, so a SUM cannot double-count. Now the payoff of the dimension: slice
revenue by region, a column the fact table never stores. The join to
dim_store supplies it:
rows = con.execute("""
SELECT s.region, SUM(f.net_amount) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_key = f.store_key
GROUP BY s.region
ORDER BY revenue DESC
""").fetchall()
for row in rows:
print(row)
con.close()
('East', 60.0)
('West', 46.0)
The fact holds only measures and keys; the dimension holds the descriptive
region. One join turns store-level rows into a regional total. Because
dim_store is flat, that slice cost exactly one join — the star schema's whole
value in one query. Snowflake region out into a dim_region table and the same
report would cost a second join for no analytical gain at this size.
Hands-on
Your turn, on a different process. The exercise below gives you a normalized
support-ticket source and asks you to design a star schema for it: declare the
grain as one row per ticket resolution, build a fact_resolution with the
resolution-time measure, and add dim_agent and dim_priority dimensions. Then
slice mean resolution time by priority.
Success criteria: your fact table has exactly one row per resolved ticket, the priority label comes from a dimension rather than the fact, and the by-priority query returns one row per priority level. SQL Kingdom validates the grain and the slice automatically.
Recap
- You can state the grain of a fact table as a sentence and verify it holds, so aggregations never double-count.
- You can separate facts (measures plus keys) from dimensions (descriptive context) and lay them out as a star.
- You can explain when a snowflake's extra join is worth paying for and why the star is the default for query speed.
Next up: dimensions are not frozen. A customer moves, a product is recategorized — and last year's facts must still join to the attribute that was true when they happened. That is the slowly changing dimension, and its loading patterns close out this slice.
- Declare the fact grain as a sentence first; it determines valid dimensions and which measures are additive.
- A star schema puts one fact table among flat, denormalized dimensions, each a single join away.
- Snowflaking normalizes a dimension to cut redundancy at the cost of joins — worth it only for huge or shared dimensions.