| 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. |
For Videos Join Our Youtube Channel: Join Now
