The snow flake schema is a specific type of a dimensional data model used in data warehouses. The model is a normalized structure, which means that redundant data is not stored in the dimension table, but is stored in more tables – in the snowflake to help with performance¹⁾.

The characteristic of a dimensional data model is a set of detailed business facts surrounded by multiple dimensions that describe those facts²⁾. Dimensional data models are consist of one table with a multipart key, called the fact table, and a set of smaller tables called dimension tables. Each dimension table has a single part primary key that corresponds exactly to one of the components of the multipart key in the fact table. This standard structure of a dimensional data model is often called a Star Schema³⁾. The snowflake schema is a specific dimensional data model which has been developed by normalizing dimension tables in the star schema. The method of normalizing dimension tables is often called “snowflaking”⁴⁾. While in the star schema the dimension tables are directly connected to the fact table, in the snowflake schema one or more dimension tables do not join directly to the fact table but must be joined through other dimension tables⁵⁾. A dimension is “snowflaked” when the low cardinality fields in the dimension have been removed to separate tables and linked back into the original table with artificial keys⁶⁾. An example of a “snowflaked” product dimension table is shown in following picture⁷⁾:

“Snowflaking” or normalization of the dimension tables can be achieved in a few different ways. By “snowflaking” the contents and the normal usage of each dimension table has to be examine. The following options indicate the different methods which may be considered for normalization of the dimension tables:

• Partially normalize only a few dimension tables, the other dimension tables are leaving intact.
• Partially or fully normalize only a few dimension tables, leaving the rest of the dimension tables intact.
• Partially normalize every dimension table.
• Fully normalize every dimension table.

When all the dimension tables have been completely normalized, the resultant structure resembles a snowflake with the fact table in the middle⁸⁾.

Advantages of the snow flake schema:

• small savings in storage space,
• normalized structures are easier to update and maintain.

Disadvantages of the snow flake schema:

• schema less intuitive and end-users are put off by the complexity,
• ability to browse through the contents difficult,
• degraded query performance because of additional joint⁹⁾.

“Snowflaking” is not generally recommended in a data warehouse environment. Query performance takes the highest significance in a data warehouse and “snowflaking” makes the performance worse¹⁰⁾. In some situations when the technique of “snowflaking” is properly used, the performance and user understandability can be improved¹¹⁾. For example improvement of reporting performance for some business intelligence query tools, which prefer snowflake designs.
“Snowflaking”provide opportunities to separate out a set of attributes and form a subdimension in a database¹²⁾.
Snowflake schemas support ease of dimension maintenance because they are partially or fully normalized. They reduce storage requirements, since dimensions have high volumes of data¹³⁾.