Summary: in this tutorial, we take a look the snowflake schema that is a variation of star schema using by data warehouse systems.
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. the snowflake schema is a kind of star schema however it is more complex than a star schema in terms of the data model. This schema resembles a snowflake, therefore, it is called the snowflake schema.
A snowflake schema is designed from the star schema by further normalizing dimension tables to eliminate data redundancy. Therefore in the snowflake schema, instead of having big dimension tables connected to a fact table, we have a group of multiple dimension tables. In the snowflake schema, dimension tables are normally in the third normal form (3NF). The snowflake schema helps save storage however it increases the number of dimension tables.
Snowflake schema example
The figure below shows an example of a snowflake schema that is a snowflaked version of a star schema demonstrated in the star schema article.
Let’s examine the snowflake schema above in greater 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.
- The fact table remains the same as the star schema.
Useful snowflake schema notes
- The normalization of dimension tables tends to increase the number of dimension tables or sub-dimension tables that require more foreign key joins when querying the data, therefore, reduce the query performance.
- The query of snowflake schema is more complex than the query of star schema due to multiple joins from the dimension table to sub-dimension tables.
- Snowflake schema helps to save space by normalizing dimension tables.
- It is more difficult for business users who use a data warehouse system using snowflake schema because they have to work with more tables than star schema.
In this tutorial, we’ve examined a variety of star schema called snowflake schema with useful notes about using it.
