Dimensional Modeling
Facts to measure, dimensions to slice
Dimensional modeling structures warehouse data into facts and dimensions: numbers you measure (facts) and the things you slice them by (dimensions). It is the most proven way to make a warehouse understandable.
What you will learn
- Define fact and dimension tables and their grains
- Pick the grain of a fact table
- Decide what belongs in a dimension vs a fact
What is this?
Dimensional modeling separates measures from context. Fact tables hold numeric measures at a stated grain (one row per order, per event), with foreign keys to dimensions. Dimension tables hold the descriptive context — who, what, when, where — that analysts group and filter by. It is the Kimball-style backbone of most analytics warehouses.
Why do you need it?
- Analysts instantly understand 'orders by customer by month' when the model has facts and dimensions.
- Slow-changing context (customer name, address) is stored once in dimensions, not repeated.
- The pattern is proven across decades and tools.
Mental model
A concert ticket
A fact row
The seats-sold number is fixed and factual, with references to the artist, venue, and date — facts point at dimensions, exactly like a ticket references a show.
How it works
- Choose the fact grain: the smallest meaningful unit (order, session, event).
- Define numeric measures at that grain and foreign keys to dimensions.
- Build dimension tables for the context: customers, products, dates, channels.
- Store facts append-only; keep history-changing attributes in dimensions.
A fact in the middle, dimensions around it for slicing and dicing.
In production
- An orders fact with date/customer/product dimensions powers revenue dashboards.
- An events fact at session grain supports funnel and retention queries.
- Sales-reporting marts restructure monthly aggregates on top of daily facts.
Best practices
- State the grain in the model name and docs — it is the model's contract.
- Keep facts append-only and immutable where possible.
- Let dimensions be slowly changing where history matters (see SCD).
Common mistakes
- Mixing measures and qualifiers: 'order count' in the customer dimension instead of the fact.
- Choosing a grain that is not the natural unit, forcing dedup logic everywhere.
- Duplicating attribute lists into every fact instead of sharing dimensions.
Debugging tips
- If a dashboard multiplies counts, the fact's grain changed — check the grain against the dimension keys.
- If a dimension attribute 'changes history', the table is missing SCD handling.
Interview checkpoint
Hands-on lab
- Define a fact table at order grain with measure columns
- Build date, customer, and product dimension tables
- Join the star and verify aggregation correctness
Model Orders into Facts and Dimensions
45 min · intermediate
Quick quiz
A fact table usually stores
The grain of a fact table is