DWH - Snowflake Schema

Snowflake schema

The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions. "Snow flaking" is a method of normalizing the dimension tables in a STAR schema. The principle behind snow flaking is normalization of the dimension tables by removing low cardinality attributes and forming separate tables.

Snowflake schemas are generally used when a dimensional table becomes very big and when a star schema can’t represent the complexity of a data structure. For example if a PRODUCT dimension table contains millions of rows, the use of snowflake schemas should significantly improve performance by moving out some data to other table (with BRANDS for instance). 


The problem is that the more normalized the dimension table is, the more complicated SQL joins must be issued to query them. This is because in order for a query to be answered, many tables need to be joined and aggregates generated.