Home » Interviews Q & A » Star Schema vs Snowflake Schema

Star Schema vs Snowflake Schema

Star vs Snowflake schema

Star and snowflake schemas are both data modeling techniques used to structure data in a relational database. However, there are key differences between them:

Star Schema:

  • A star schema consists of a single fact table that is surrounded by dimension tables.
  • The fact table contains the measures (numeric values) that we want to analyze, while the dimension tables contain descriptive attributes of the measures.
  • In a star schema, the dimension tables are connected to the fact table through a series of relationships represented by foreign keys.
  • Star schemas are optimized for queries that aggregate data along several dimensions, making them ideal for use in business intelligence and data warehousing systems.
Star Schema

Star Schema

Snowflake Schema:

  • A snowflake schema is a type of star schema, but with more normalized dimension tables.
  • In a snowflake schema, the dimension tables are not only connected to the fact table but also to other dimension tables. This normalization helps to reduce data redundancy and maintain data integrity.
  • The normalization process makes snowflake schemas more complex than star schemas, but it also allows for more flexible data modeling.
  • Snowflake schemas are well-suited for transactional databases and data marts where detailed data analysis is not a priority.
Snowflake Schema

Snowflake Schema

Leave a Reply