Snowflake Schema vs Star Schema

Snowflake Schema
Star Schema

When choosing a database schema for a data warehouse, snowflake and star schemas tend to be popular choices. This comparison discusses suitability of star vs. snowflake schemas in different scenarios and their characteristics.


Comparison chart

Edit this comparison chart

Snowflake Schema

Star Schema

Ease of maintenance / change No redundancy and hence more easy to maintain and change Has redundant data and hence less easy to maintain/change
Ease of Use More complex queries and hence less easy to understand Less complex queries and easy to understand
Query Performance More foreign keys-and hence more query execution time Less no. of foreign keys and hence lesser query execution time
Type of Datawarehouse Good to use for datawarehouse core to simplify complex relationships (many:many) Good for datamarts with simple relationships (1:1 or 1:many)
Joins Higher number of Joins Fewer Joins
Dimension table It may have more than one dimension table for each dimension Contains only single dimension table for each dimension
When to use When 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-Normalization Dimension Tables are in Normalized form but Fact Table is still in De-Normalized form Both Dimension and Fact Tables are in De-Normalized form
Data model Bottom up approach Top down approach

edit References

Comments: Snowflake Schema vs Star Schema

Your IP address will be logged and parts of it shown publicly.
Write <youtube v=YouTubeVideoID /> to embed a YouTube video in your comment.
Comments with links (URLs) are not allowed because we get overrun by spam.

February 6, 2014, 5:48pm

Awesome :)

— 122.✗.✗.91
0

July 10, 2013, 6:09am

precise differences.

— 144.✗.✗.130
0

February 18, 2013, 11:24am

thanx for short & simple info.

— 202.✗.✗.43
0

January 7, 2013, 9:47am

Nice one... it's very useful

— 203.✗.✗.103
0

October 25, 2011, 6:57pm

Very simple and short.

— 204.✗.✗.4
0

May 28, 2010, 3:08am

short and simple.
Thankss

— 120.✗.✗.118
0

February 8, 2010, 5:15pm

Good and simple differences

— 122.✗.✗.18
0
Stay informed Related Comparisons
Follow Diffen
Make Diffen Smarter.

Log in to edit comparisons or create new comparisons in your area of expertise!

Sign up »
Top 5 Comparisons Recently Compared

share

Up next

Calloc vs. Malloc