Star Schema vs. Snowflake Schema

Summary: In this article, you will see the differences between star schema and snowflake schema in various criteria.

Star schema vs. Snowflake schema

Star SchemaSnowflake Schema
Understandability Easier for business users and analysts to query data.May be more difficult for business users and analysts due to number of tables they have to deal with.
Dimension tableOnly have one dimension table for each dimension that groups related attributes. Dimension tables are not in the third normal form.May have more than 1 dimension table for each dimension due to the further normalization of each dimension table.  Dimension tables are in the third normal form (3NF).
Query complexityThe query is very simple and easy to understandMore complex query due to multiple foreign key joins between dimension tables
Query performanceHigh performance. Database engine can optimize and boost the query performance based on predictable framework.More foreign key joins therefore longer execution time of query in compare with star schema
When to useWhen dimension tables store relative small number of rows, space is not a big issue we can use star schema.When dimension tables store large number of rows with redundancy data and space is such an issue, we can choose snowflake schema to save space.
Foreign Key JoinsFewer JoinsHigher number of joins
Data warehouse systemWork best in any data warehouse / data martBetter for small data warehouse/ data mart