Snowflake Schema and Normalization Trade-offs
You'll learn how snowflake schemas reduce data redundancy but complicate queries compared to star schemas.
What is a Snowflake Schema?
A snowflake schema extends the star schema by normalizing dimension tables into multiple related sub-tables. Instead of keeping all dimension attributes in one flat table, you split them into hierarchies connected by foreign keys.
Imagine a Product dimension in a star schema with columns: product_id, product_name, category_name, category_group. In a snowflake schema, you'd split this into:
- A
Producttable (product_id, product_name, category_id) - A
Categorytable (category_id, category_name, category_group_id) - A
Category_Grouptable (category_group_id, group_name)
This is like organizing files into nested folders instead of keeping everything in one directory.
Normalization Benefits
- Reduced data redundancy: Category names aren't repeated for every product
- Smaller storage footprint: Less duplication means less disk space
- Easier dimension updates: Changing a category name happens in one place
- Data integrity: Foreign key constraints maintain consistency
The Query Performance Cost
Here's the trade-off: queries now require more joins to reconstruct full dimension data. A simple "show me sales by category" query that touches one dimension table in star schema now needs two or three joins in snowflake schema.
More joins mean:
- Longer query execution time
- More complex SQL statements
- Higher CPU and memory overhead
- Potentially more opportunities for query optimization errors
Data warehouses are optimized for read-heavy analytical queries, so the star schema's denormalized approach often wins despite redundancy.
Key Takeaway: Snowflake schemas trade query simplicity and speed for storage efficiency and normalization benefits—usually favoring star schemas for analytics unless storage costs dominate or dimension updates are frequent.