Snowflake Schema

The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy.

  Sample Snowflake schema

For example, the Time Dimension that consists of 2 different hierarchies:

1. Year → Month → Day
2. Week → Day

We will have 4 lookup tables in a snowflake schema: A lookup table for year, a lookup table for month, a lookup table for week, and a lookup table for day. Year is connected to Month, which is then connected to Day. Week is only connected to Day. A sample snowflake schema illustrating the above relationships in the Time Dimension is shown to the right.

The main advantage of the snowflake schema is the improvement in query performance due to minimized disk storage requirements and joining smaller lookup tables. The main disadvantage of the snowflake schema is the additional maintenance efforts needed due to the increase number of lookup tables.

• Data Warehouse
• Data Warehouse Definition
• Data Warehousing Concepts
  • Dimensional Data Model
  • Star Schema
  • Snowflake Schema
  • Fact Table Granularity
  • Fact And Fact Table Types
  • Slowly Changing Dimension
  • Type 1 Slowly Changing Dimension
  • Type 2 Slowly Changing Dimension
  • Type 3 Slowly Changing Dimension
  • Type 4 Slowly Changing Dimension
  • Conceptual Data Model
  • Logical Data Model
  • Physical Data Model
  • Conceptual, Logical, and Physical Data Model
  • Data Integrity
  • What Is OLAP
  • MOLAP, ROLAP, and HOLAP
  • Bill Inmon vs. Ralph Kimball
  • Factless Fact Table
  • Junk Dimension
  • Conformed Dimension
• Data Warehouse Design
• Data Warehousing Trends
• Business Intelligence
• Business Intelligence Software
• Business Intelligence Conferences
• Master Data Management
• Data Warehousing Videos
• Glossary
• Site Map
• Resources