Snowflake Schema

What is Snowflake Schema?

In data warehousing, Snowflake Schema is the extension to star schema such that the tables are arranged in a complex snowflake shape. The concept is similar to star schema with a center fact table and multiple dimension tables radiating from the center except that the tables described as dimensions are normalized and consist of more hierarchies.

Snowflaking is implemented to perform advanced-level queries on the normalized dimensions. The dimension tables (as in star schema diagram) are further branch to other multiple dimensions.   Sometimes, the query requirements are such that business people find snowflake diagrams easy and important to get the results of their query. For instance, the time dimension can be connected to two further hierarchies:

Year—> Month —> Day

Week —-> Day

The time dimension has a year dimensional table with a month dimensional table and day dimensional table whereas the week dimension table only connects to Day. It will provide precise data when the users calculate weekly data independently. This also avoids redundancy of data by transferring repeating data groups into new tables.

Though snowflake schema requires more storage space, they are widely used by snowflake developers for improved query performance in the data warehouse. Normalization through snowflaking results in saving storage space and the tradeoff promotes automatic underlying joins.