Summary: in this article, you will see the differences between star schema and snowflake schema in various criteria.
| Star Schema | Snowflake Schema | |
|---|---|---|
| Understandability | Easier for business users and analysts to query data. | Maybe more difficult for business users and analysts due to the number of tables they have to deal with. |
| Dimension table | Only has 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 complexity | The query is very simple and easy to understand | More complex query due to multiple foreign keys joins between dimension tables |
| Query performance | High performance. The database engine can optimize and boost query performance based on a predictable framework. | More foreign key joins, therefore, the longer execution time of query in comparison with star schema |
| When to use | When dimension tables store a relatively small number of rows, space is not a big issue we can use star schema. | When dimension tables store a large number of rows with redundancy data and space is such an issue, we can choose snowflake schema to save space. |
| Foreign Key Joins | Fewer Joins | A higher number of joins |
| Data warehouse system | Work best in any data warehouse/data mart | Better for small data warehouse/ data mart |
