Data Modeling/Dimensional Modeling
Star Schema vs Snowflake Schema
The two most common ways to organize a dimensional model for analytics — one favors fewer joins and simplicity, the other favors less redundancy — and how to decide between them.
Once you've decided to build a dimensional model for analytics (fact tables + dimension tables — see the next article if this is new), you still choose how normalized the dimension tables themselves should be. The two dominant patterns are the star schema and the snowflake schema.
Star schema
In a star schema, a central fact table connects directly to fully denormalized dimension tables — each dimension is a single, flat table with no further joins needed. Drawn out, the fact table sits in the middle with dimensions radiating outward, resembling a star. A dim_product table, for example, would include product name, category, and category description all in one table, even though category description technically 'belongs' to a category, not a product.
Snowflake schema
A snowflake schema normalizes the dimension tables further, splitting them into multiple related tables (dim_product references dim_category, which references dim_department). This reduces redundancy — a category's description is stored once, not repeated on every product — at the cost of needing extra joins to fully denormalize a dimension when querying.
- Star: dimensions fully flattened, fewer joins, some redundancy, easier for BI tools and analysts to query
- Snowflake: dimensions normalized further, more joins, less redundancy, marginally more complex queries
- Star schema is the more common default in modern cloud warehouses, where storage is cheap and join performance is the bigger concern
-- Star schema: one join gets you everything about the product dimension
select f.order_id, f.amount, d.product_name, d.category_name
from fact_orders f
join dim_product d on f.product_id = d.product_id;
-- Snowflake schema: category detail lives one hop further away
select f.order_id, f.amount, p.product_name, c.category_name
from fact_orders f
join dim_product p on f.product_id = p.product_id
join dim_category c on p.category_id = c.category_id;Tip — Default to star unless you have a reason not to
Storage costs that once justified snowflaking dimensions have mostly disappeared in cloud warehouses. Most teams default to star schemas for simplicity and query speed, and only normalize a dimension further when it changes independently at a very different rate or size than the rest of the dimension.
Both patterns are variations on the same underlying idea — a fact table surrounded by descriptive context — which is worth understanding on its own terms, covered next.