|Basis for Comparison
|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
||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
||Less number of foreign keys and hence lesser query execution time
||More foreign keys and thus more query execution time
||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)
||Fewer joins Higher
||number of joins
||It contains only a single dimension table for each dimension
||It may have more than one dimension table for each dimension
||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.