🌟Star and snowflake schemas
Facts in the middle, dimensions around
A fact table holds measurements and foreign keys to dimensions (who, what, when, where). A star keeps dimensions denormalized for fewer joins. A snowflake normalizes dimensions (e.g., brand → category hierarchies) to save space at the cost of extra joins at query time.
Star join pattern
SELECT p.product_name, d.month, SUM(f.units) AS units
FROM fact_shipments f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_date d ON d.date_key = f.date_key
GROUP BY p.product_name, d.month;| product_name | month | units |
|---|---|---|
| Pipette tips | 2026-03 | 12000 |
| Gloves | 2026-03 | 9000 |
⚖️Trade-offs
Columnar warehouses still care about join count.
| Schema | Pros | Cons |
|---|---|---|
| Star | Fewer joins, simpler BI models, friendly to wide scans | More denormalized storage; duplicate text in dimensions |
| Snowflake | Less duplication in hierarchies when normalized | More joins for every query; planner must still be happy |
Dimensions that diverge
- Multiple incompatible “customer” dimensions across facts, KPI wars.
- Fat degenerate dimensions bloating the fact when keys belong in a junk dimension.
- Ignoring late-arriving facts and SCD2 when history matters.
Conform and document grain
- Conform dimensions across facts; name grain explicitly (per line vs per order).
- Prefer star ergonomics unless snowflake saves measurable space after compression.
- First-class date dimensions; plan SCD strategy before dashboards ship.
Physical modeling
Columnar engines love narrow facts with dictionary-encoded dimensions, heavy snowflaking can still be fine if joins stay selective; validate with realistic cardinality, not toy star schemas.
TL;DR
A star schema centers a fact table on surrogate keys pointing to denormalized dimension tables (one join hop per dimension): great for OLAP scans. A snowflake normalizes dimensions further (smaller storage, more joins). Both are relational; the trade-off is join count vs redundancy. Choose based on typical query patterns and columnar engine behavior, not aesthetics alone.