[DWH] SnowFlake Schema

 

Snowflake Schema

Snowflake schema consists of a fact table surrounded by multiple dimension tables which can be connected to other dimension tables via many-to-one relationship. Snowflake schema is a kind of star schema however it is more complex than a star schema in term of data model. This schema resembles a snowflake so it is called snowflake schema.

Snowflake schema is designed from star schema by further normalizing dimension tables to eliminate data redundancy.  Therefore in snowflake schema, instead of having a big dimension table connected to a fact table, we have a group of multiple dimension tables. In snowflake schema, tables are normally in the third normal form (3NF). This schema well helps save space however it increases the number of dimension tables.

Snowflake schema example

The figure below shows an example of snowflake schema that is a snowflaked version of a star schema in the star schema article.

Star Schema Example

Snowflake Schema Example

Let examine the snowflake schema above in a great detail:

• DIM_STORE dimension table is normalized to add one more dimension table called DIM_GEOGRAPHY
• DIM_PRODUCT dimension table is normalized to add 2 more dimension tables called DIM_BRAND and DIM_PRODUCT_CATEGORY
• DIM_DATE dimension table is now connecting with three other dimension tables: DIM_DAY_OF_WEEK, DIM_MONTH and DIM_QUARTER.
• Fact table remains the same as star schema.

Useful snowflake schema notes

• The normalization of dimension tables tends to increase number of dimension tables or sub-dimension table that require more foreign key joins when querying the data therefore reduce the query performance.
• The query of snowflake schema is more complex than query of star schema due to multiple joins from dimension table to sub-dimension tables.
• Snowflake schema help to save space by normalizing dimension tables.
• It is more difficult for business users who use data warehouse system using snowflake schema because they have to work with more tables than star schema.