Star & Snowflake Schemas

Analytics on a fully normalized (OLTP) schema

"Revenue by category, by month, by region" — on a 3NF transactional schema you chase foreign keys through half a dozen tables just to answer one question.

SELECT c.name, cal.month, r.name, SUM(oi.amount)
FROM order_items oi
JOIN orders     o   ON o.id=oi.order_id
JOIN products   p   ON p.id=oi.product_id
JOIN categories c   ON c.id=p.category_id
JOIN stores     s   ON s.id=o.store_id
JOIN regions    r   ON r.id=s.region_id
JOIN calendar   cal ON cal.date=o.order_date
-- 6 joins just to group by 3 attributes
GROUP BY c.name, cal.month, r.name

Dimensional modeling: one fact, several dimensions

A fact table holds numeric measures + foreign keys. Dimension tables hold descriptive context. Star keeps dims flat (denormalized); snowflake normalizes them further.

Toggle: Star ⇄ Snowflake

Same fact, same question — "revenue by category". In star mode every dimension is one hop from the fact — a flat graph. In snowflake mode dimensions branch into sub-dimensions — a tree — so join depth grows with every extra hop.

SELECT p.category, SUM(f.revenue)
FROM fact_sales f
JOIN dim_date    d ON d.id=f.date_id
JOIN dim_product p ON p.id=f.product_id
JOIN dim_store   s ON s.id=f.store_id
GROUP BY p.category

SELECT cat.name, SUM(f.revenue)
FROM fact_sales f
JOIN dim_date       d    ON d.id=f.date_id
JOIN dim_product    p    ON p.id=f.product_id
JOIN dim_category   cat  ON cat.id=p.category_id
JOIN dim_department dept ON dept.id=cat.department_id
JOIN dim_store      s    ON s.id=f.store_id
GROUP BY cat.name

Model for analytics, not transactions

• Facts hold measures (numbers you sum/avg/count) + foreign keys. Dimensions hold the context you slice and filter by (who, what, where, when).
• Star = flat, denormalized dims → fewer joins, faster and simpler queries, some redundancy.
• Snowflake = normalized dims → less storage, more consistency, but deeper join chains.
• Both beat a raw OLTP schema for reporting — that's what a data warehouse is built around, feeding OLAP-style queries.