The snowflake schema is nothing but normalized star schema; some dimensions of star schema are normalized and extended by creating associated details table. As opposed to star schema where each dimension is represented by single dimension table in snowflake schema dimension table is normalized by associating detail or lookup table to dimension table which also helps to represent dimensional hierarchy.
e.g. in Snowflake diagram as you can Customer Dimension table has detail Address table associated with one-to-many relationship
This will help to get the customer address data when report required it avoiding un-necessary attributes in query and making Customer Dimension table lightweight. Also with this design we can also track the customer address history as Address table is a SCD.
Advantages of Snowflake Schema:
1. The biggest advantage of snowflake schema is improved query performance.
2. Reduces dimension table size.
Disadvantage of Snowflake Schema is additional maintenance efforts needed due to the increase number of lookup or detail tables.
Did you enjoy this article? Please subscribe to Email or RSS to receive all the FREE updates!
Related posts: