90

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.

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.

Let’s see the differentiate between Star and 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 joinsHigher	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.

---