DIFFERENCE BETWEEN STAR SCHEMA AND SNOWFLAKE SCHEMA

	Snowflake Schema	Star Schema
Ease of maintenance	No 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 understand	Lower query complexity and easy to understand
Data model type	Bottom up approach	Top down approach
Normalization?	Dimension Tables are in Normalized form but Fact Table is in De-Normalized form	Both Dimension and Fact Tables are in De-Normalized form
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 choose Star schema.
Dimension tables	A snowflake schema may have more than one dimension table for each dimension.	A star schema contains only single dimension table for each dimension.
Joins count	Needed huge number of joins as dimensions are shared between facts.	Fewer join as no sharing of dimensions.
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)
Query Performance	More foreign keys and hence longer query execution time (slower)	Less number of foreign keys and hence shorter query execution time (faster)

comments

Wings Of Technology