1. Normalization and Redundancy
As mentioned, the dimensions on the star schema are denormalized and the dimensions on the flake schema are normalized.
Due to the denormalization, the star schemas tend to have better query performance compared to the flake schema.
2. Query complexity
Star schemas require less complex queries because the fact table is linked to each dimension by a single join, which also speeds up query performance.
In snowflake schemas, multiple joins are required between the fact table and the dimension tables, which makes the queries more complex and increases the query execution time.
3. Implementation and Maintenance
Data warehouse architecture and implementation is generally easier for star schemas due to its simplicity, however, maintenance is highly related to the volume of redundant data. Fewer maintenance points are required on flake schemas because the dimensions do not have redundant data.
4. Disk Storage and Data Integrity
Because the dimensions in the snowflake schema do not have redundant data, this architecture requires less disk storage than star schemas.
Another advantage of non-redundant data is that it is easier to maintain data integrity because inserts and updates only affect one data entry instead of several in star schemas.
It’s important to mention that with the growth of cloud computing, disk storage is becoming less expensive and scalable.
Which scheme to choose?
It’s up to you to decide which architecture is best for your business case… We hope this article has helped you understand the main differences between the architectures and the advantages and disadvantages of each model
Kimball, Ralph – The Data Warehouse Toolkit.