Dimensional Modelling
Star schemas, grain, and slowly changing dimensions — how a warehouse is shaped so questions are cheap to ask.
Shaped For Questions, Not Transactions
Dimensional modelling splits every table into one of two kinds. A fact table records events — an order, a page view, a payment — and holds measurements plus references. A dimension table holds the context you slice those events by: the customer, the product, the date, the store.
Everything else follows from that split. Facts are long and narrow and grow forever; dimensions are short and wide and change slowly. The join between them is the whole analytical query pattern.
Grain: The First Decision
The grain is what one row of a fact table means. "One row per order line" is a grain. "One row per order" is a different one. Deciding it first is the single most important step, because every other choice depends on it — and because mixing grains in one table is the defect that produces numbers nobody can reconcile.
| Grain | One row is | Watch for |
|---|---|---|
| Transaction | An event that happened | Volume — this is the big one |
| Periodic snapshot | A balance at a point in time | Must be regenerated on schedule |
| Accumulating snapshot | A process with milestones | Rows get updated, not just inserted |
State the grain in words before writing any DDL. If you cannot say it in one sentence, the table is trying to be two tables.
Star & Snowflake Schemas
A star schema puts one fact table in the middle with dimensions joined directly to it, one hop away. A snowflake normalises those dimensions further — product joins to category joins to department.
| Star | Snowflake | |
|---|---|---|
| Joins per query | One hop | Several |
| Readability | Obvious to analysts | Needs a diagram |
| Storage | Some repetition | Less repetition |
| Verdict | Default for analytics | Only when a dimension is genuinely huge |
-- star: one hop from fact to any context
SELECT d.year_month, p.category, SUM(f.net_amount) AS revenue
FROM fact_order_line f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.year_month, p.category;Storage is cheap and analyst time is not, so the repetition a star schema accepts is a deliberate trade rather than sloppiness. Use surrogate keys — a warehouse-generated integer — rather than the source system's id, so a re-keyed source or a merged company does not rewrite history.
Slowly Changing Dimensions
A customer moves city. Do last year's orders now belong to the new city or the old one? Both answers are defensible, and choosing between them is what slowly changing dimension types name.
| Type | Behaviour | Use when |
|---|---|---|
| Type 1 | Overwrite — history is lost | Correcting an error |
| Type 2 | New row, with valid-from and valid-to | History matters — the common answer |
| Type 3 | Keep a "previous value" column | Only the last change matters |
-- type 2: the fact joins to whichever version was current at the time
customer_key | customer_id | city | valid_from | valid_to | is_current
1001 | C-42 | Bristol | 2023-01-01 | 2025-06-30 | false
1002 | C-42 | Edinburgh | 2025-07-01 | 9999-12-31 | truecustomer_key, not customer_id, so it stays pointed at the version of the customer that was true when the event happened — which is what makes last year's report reproduce last year's number.Interview Questions
What is the grain of a fact table?
What a single row represents. It is decided before anything else, stated in one sentence, and never mixed — two grains in one table is how totals stop reconciling.
Why does a warehouse denormalise when an application database does not?
Different goals. Transactional systems optimise for safe cheap writes; warehouses optimise for reads and comprehensibility, so a star schema trades some repetition for one-hop joins.
What is fan-out and how do you avoid it?
Joining two fact tables at different grains multiplies the measures on one side by the row count of the other, silently inflating totals. Join facts to dimensions, and facts to each other only through a conformed dimension.
Star or snowflake?
Star by default — fewer joins, and analysts can read it without a diagram. Snowflake only where a dimension is genuinely enormous and the repetition actually costs something.
Explain SCD types 1, 2 and 3.
Type 1 overwrites and loses history — right for correcting errors. Type 2 adds a row with validity dates, preserving what was true at the time. Type 3 keeps a single previous-value column.
Why use surrogate keys?
They decouple the warehouse from source-system identifiers and make Type 2 history possible: a fact points at the version of the dimension that was current when the event happened.