Course contentsShow
← Data Science
Lesson 1810 of 2,14542. Working with Big DataFree lesson

Snowflake Schema and Normalization Trade-offs

Compare snowflake schema's normalized dimensions to star schema and understand query performance implications.

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 Product table (product_id, product_name, category_id)
  • A Category table (category_id, category_name, category_group_id)
  • A Category_Group table (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.