Star Schema Design
One fact, orbiting dimensions, zero surprises
A star schema arranges a single fact table surrounded by directly-joined dimension tables. It is the simplest shape a BI tool can consume: no fan-outs, no deep joins, glanceable queries.
What you will learn
- Explain why the star shape speeds up BI queries
- Contrast star with snowflake and flat schemas
- Design a star for a business question
What is this?
A star schema is a dimensional model where a single fact table sits at the center and each of its dimensions joins in a single level — no dimension joining to another dimension. That flattened shape lets BI tools generate simple, fast joins, and it explains exactly why so many analytics warehouses orbit stars.
Why do you need it?
- Simple join graph = fast and predictable queries, even in drag-and-drop BI.
- Fewer joins to write means fewer places for duplicated or wrong results.
- It mirrors how people think: 'sales (fact) by time, product, store'.
Mental model
A city's hub-and-spoke transit network
A star schema
Every line runs from the center to one district directly — fast, clear, and predictable, exactly like a fact with its dimensions.
How it works
- Pick the fact and declare its grain.
- Attach each dimension at exactly one hop from the fact.
- Flatten multi-level hierarchies into the dimension (category and subcategory in one table).
- Let BI tools join straight to the dimensions for filters and group-bys.
Central fact with single-hop dimensions in a star.
In production
- Revenue dashboards join a fact to date/customer/product dims for group-bys.
- Inventory analysts slice fact_stock by store, product, and day dims.
- Marketing joins a channel dim to the acquisition fact for CAC splits.
Best practices
- Declare the grain in the fact's name/docs and enforce it.
- Flatten used hierarchies into the dimension table.
- Add surrogate keys so dimension names can change without breaking joins.
Common mistakes
- Nesting dimensions (snowflaking) and burdening every query with extra joins.
- Multiple facts that cannot be shared by the same dimensions, forcing ad-hoc unions.
- Scatter grain: measures at order and line-item grain in one table.
Debugging tips
- If a filter on product.category does not split revenue, the join key was written wrong.
- If BI shows double-counting, two dims each joined a fan-out path — check unique keys.
Interview checkpoint
Hands-on lab
- Flatten a hierarchy into a product dimension
- Add surrogate keys to fact and dims
- Build a star and verify a grouped query
Design a Star for Revenue Reporting
40 min · intermediate
Quick quiz
In a star schema, dimensions join to the fact in
Flattening a hierarchy into a dimension means