Snowflake Schema Star Schema
Ease of maintenanceNo redundancy, so snowflake schemas are easier 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 understandLower query complexity and easy to understand
Data model typeBottom up approachTop down approach
Normalization?Dimension Tables are in Normalized form but Fact Table is in De-Normalized formBoth Dimension and Fact Tables are in De-Normalized form
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 choose Star schema.
Dimension tablesA snowflake schema may have more than one dimension table for each dimension.A star schema contains only single dimension table for each dimension.
Joins countNeeded huge number of joins as dimensions are shared between facts.Fewer join as no sharing of dimensions.
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)
Query PerformanceMore foreign keys and hence longer query execution time (slower)Less number of foreign keys and hence shorter query execution time (faster)



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: