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.
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
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)
Higher number of Joins
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.
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