Data modeling · Foundations

What is a snowflake schema?

Mediumsnowflake schemastar schemadimensionsnormalization

Interview question

What is a snowflake schema, and how does it differ from a star schema?

How to answer

The short answer

A snowflake schema is like a , but some dimensions are normalized into sub-dimensions. The diagram looks like a snowflake: branches off the arms.

Output
Star:     dim_product has category, subcategory on the same table

Snowflake:
  fct_orders --> dim_product --> dim_subcategory --> dim_category

Star vs snowflake

StarSnowflake
Dimension shapeWide, denormalized dimsNormalized dim hierarchies
Joins for BIFewerMore
StorageSlightly more duplicationLess duplication in dims
Ease for analystsUsually easierMore join paths

When people snowflake

  • Very large, shared hierarchies (geography: city → state → country)
  • Strict 3NF habits from thinking brought into the warehouse

Modern default

Most analytics teams prefer stars (or stars with a few carefully snowflaked pieces) because BI tools and humans prefer fewer joins. Storage is cheap; confusing join graphs are expensive.

Interview tip

> "Snowflake normalizes dimensions further than a star. I default to star for BI marts unless a hierarchy is huge and reused everywhere."