Javatpoint Logo
Javatpoint Logo

Difference between Star and Snowflake Schemas

Star Schema

  • In a star schema, the fact table will be at the center and is connected to the dimension tables.
  • The tables are completely in a denormalized structure.
  • SQL queries performance is good as there is less number of joins involved.
  • Data redundancy is high and occupies more disk space.
Star Schema vs Snowflake Schema

Snowflake Schema

  • A snowflake schema is an extension of star schema where the dimension tables are connected to one or more dimensions.
  • The tables are partially denormalized in structure.
  • The performance of SQL queries is a bit less when compared to star schema as more number of joins are involved.
  • Data redundancy is low and occupies less disk space when compared to star schema.
Star Schema vs Snowflake Schema

Let's see the differentiate between Star and Snowflake Schema.

Star Schema vs Snowflake Schema
Basis for Comparison Star Schema Snowflake Schema
Ease of Maintenance/change It has redundant data and hence less easy to maintain/change No redundancy and therefore more easy to maintain and change
Ease of Use Less complex queries and simple to understand More complex queries and therefore less easy to understand
Parent table In a star schema, a dimension table will not have any parent table In a snowflake schema, a dimension table will have one or more parent tables
Query Performance Less number of foreign keys and hence lesser query execution time More foreign keys and thus more query execution time
Normalization It has De-normalized tables It has normalized tables
Type of Data Warehouse Good for data marts with simple relationships (one to one or one to many) Good to use for data warehouse core to simplify complex relationships (many to many)
Joins Fewer joins Higher number of joins
Dimension Table It contains only a single dimension table for each dimension It may have more than one dimension table for each dimension
Hierarchies Hierarchies for the dimension are stored in the dimensional table itself in a star schema Hierarchies are broken into separate tables in a snowflake schema. These hierarchies help to drill down the information from topmost hierarchies to the lowermost hierarchies.
When to use When the dimensional table contains less number of rows, we can go for Star schema. When dimensional table store a huge number of rows with redundancy information and space is such an issue, we can choose snowflake schema to store space.
Data Warehouse system Work best in any data warehouse/ data mart Better for small data warehouse/data mart.





Help Others, Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA