Compare and Contrast Star Schema and Snowflake Schema in Data Warehouse Design.
Compare and Contrast Star Schema and Snowflake Schema in Data Warehouse Design.
Attempted by 1 students.
Show answer & explanation
Schema design is a critical part of the logical data modeling process. The two most common structures are the Star Schema and the Snowflake Schema.
Feature | Star Schema | Snowflake Schema |
Structure | Consists of a central fact table connected to a set of non-normalized dimension tables. | An extension of the star schema where dimension tables are normalized into multiple related tables. |
Normalization | Dimension tables are not normalized (contain redundant data). | Dimension tables are fully or partially normalized. |
Query Complexity | Simple; requires fewer joins between tables. | Complex; requires multiple joins to retrieve information from normalized dimensions. |
Performance | Faster query execution due to simpler joins. | Slower query execution compared to star schema due to join overhead. |
Maintenance | Higher redundancy makes data updates more difficult. | Lower redundancy makes it easier to maintain data integrity. |
A third model, the Fact Constellation Schema (or Galaxy Schema), is used for sophisticated applications where multiple fact tables share dimension tables.