Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Difference between Star Schema & Snow Flake Schema?

Difference between Star Schema & Snow Flake Schema?

3 Replies
its_anandrjs

In general way if we say

1.When you have one Fact Table and Multiple Dimension Tables is a Star schema.

2.When You have a Fact Table it is connected to dimension table and then sub dimension table is a snow flake schema.

its_anandrjs

Here are the main difference from the learning website

Snowflake Schema

Star Schema

Ease of maintenance / changeNo redundancy and hence more easy to maintain and changeHas redundant data and hence less easy to maintain/change
Ease of UseMore complex queries and hence less easy to understandLess complex queries and easy to understand
Query PerformanceMore foreign keys-and hence more query execution timeLess no. of foreign keys and hence lesser query execution time
Type of DatawarehouseGood to use for datawarehouse core to simplify complex relationships (many:many)Good for datamarts with simple relationships (1:1 or 1:many)
JoinsHigher number of JoinsFewer Joins
Dimension tableIt may have more than one dimension table for each dimensionContains only single dimension table for each dimension
When to useWhen dimension table is relatively big in size, snowflaking is better as it reduces space.When dimension table contains less number of rows, we can go for Star schema.
Normalization/ De-NormalizationDimension Tables are in Normalized form but Fact Table is still in De-Normalized formBoth Dimension and Fact Tables are in De-Normalized form
Data modelBottom up approachTop down approach
Not applicable
Author

thankyou