Snowflake Schema - Data Warehouse

#By Wikipedia
In computing, a snowflake schema is a logical arrangement of tables in a multidimensional database such that the entity relationship diagram resembles a snowflake shape. The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions. “Snowflaking” is a method of normalizing the dimension tables in a star schema. When it is completely normalized along the dimension tables, the resultant structure resembles a snowflake with the fact table in the middle. The principle behind snowflaking is normalization of the dimension tables by removing low cardinality attributes and forming separate tables.

The snowflake schema is similar to the star schema. How ever, in the snowflake schema, dimensions are normalized into multiple related tables, whereas the star schema’s dimensions are denormalized with each dimension represented by a single table. A complex snowflake shape emerges when the dimensions of a snowflake schema are elaborate, having multiple levels of relationships, and the child tables have multiple parent tables.

Normalization splits up data to avoid redundancy (duplication) by moving commonly repeating groups of  data into new tables. Normalization therefore tend to increase the number of tables that need to be joined in order to perform a given query, but reduces the space required to hold the data and the number of places where it need to be updated if the data changes.

Benefits : The snowflake schema is in the same family as the star schema logical model. In fact, the star schema is considered a special case of the snowflake schema. The snowflake schema provides some advantages over the star schema in certain situations, including :

• Some OLAP multidimensional database modeling tools are optimized for snowflake schemas.
• Normalizing attributes results in storage savings, the tradeoff being additional complexity in source query joins.

Disadvantages : The primary disadvantage of the snowflake schema is that the additional levels of attribute normalization adds complexity to source query joins, when compared to the star schema. When compared to a highly normalized transactional schema, the snowflake schema’s denormalization removes the data integrity assurances provided by normalized schemas. Data loads into the snowflake schema must be highly controlled and managed to avoid update and insert anomalies.

Snowflake Schema Example From Wikipedia

#By Data Warehouse 4 U
What is snowflake schema? The snowflake schema architecture is a more complex variation of the star schema used in a data warehouse, because the tables which describe the dimensions are normalized.

#By 1Key Data
The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy.

The main advantage of the snowflake schema is the improvement in query performance due to minimized disk storage requirements and joining smaller lookup tables. The main disadvantage of the snowflake schema is the additional maintenance efforts needed due to the increase number of lookup tables.