Star Schema vs Snowflake Schema

The Star and Snowflake schemas are two types of Dimensional modelling techniques used in Data Warehousing to organize and structure data for efficient querying and analysis. How do Star and Snowflake schemas differ, and which do we recommend? Read on to find out.

Overview

Below is the model for a Sales Fact Star Schema vs. a Snowflake Schema for the same Fact.

In the Star Schema example, the Product Dimension contains an attribute for the “Brand” of the Product, whereas the Snowflake schema has a separate Brand Dimension with a “snowflake” relationship to the Product Dimension. This is the crucial difference between the Star Schema and the Snowflake Schema. In the Snowflake schema, attributes are normalised into separate Dimensions, whereas they are Denormalised in the Star Schema. The same data exists in the models; just where it is stored is different.

How do Star Schemas differ from Snowflake Dimensions?

Star Schema

1. Structure:
   • Central fact table surrounded by dimension tables.
   • Dimension tables are typically denormalized, containing all necessary attributes.
2. Relationships:
   • Only direct one-to-many relationships between the Facts and Dimensions.
3. Simplicity:
   • Easier for Users to understand and navigate when producing reports.
   • Queries are faster due to fewer joins needed between tables.
4. Normalization:
   • More redundancy because dimension tables are not normalized, which can lead to larger storage requirements.

Read a detailed description of the Star Schema.

Snowflake Schema

1. Structure:
   • Central fact table surrounded by normalized dimension tables.
   • Dimension tables are further normalized into multiple related Dimensions, resembling a snowflake.
2. Relationships:
   • More complex relationships due to the normalization of Dimension tables.
   • Dimension tables can have relationships with other Dimension tables.
3. Simplicity:
   • More difficult to design and maintain.
   • More difficult for Users to understand and navigate.
   • Potentially slower query performance due to the need for more joins.
4. Normalization:
   • Dimension tables are normalized, reducing redundancy and improving data integrity.
   • Queries may involve more joins due to the normalization.

Comparison

• Design and Maintenance: Star schema is simpler and easier to design and maintain, while the snowflake schema is more complex but can offer better data integrity.
• Simplicity: Users find the Star Schema easier to understand and navigate. The Snowflake Cchema requires the User to navigate and use multiple joins to get data from a Fact.
• Query Performance: Star schema typically offers faster query performance because it requires fewer joins, while the snowflake schema might be slower due to the need for additional joins.
• Storage Requirements: Star schema usually requires more storage space due to denormalization, whereas the snowflake schema can be more efficient in terms of storage because of normalization.

Recommendation

Our recommendation is never to use the Snowflake Schema. The only advantage is less storage, and in the modern data warehouse, storage is no longer an issue, especially considering Dimensions are usually relatively small compared to Facts. The Star schema is easier to design and maintain, easier to understand and navigate and offers faster performance.