snowflake schema

The star schema is considered an important special case of the snowflake schema.

The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions.

The snowflake schema is similar to the star schema.

The snowflake schema is in the same family as the star schema logical model.

The snowflake schema provides some advantages over the star schema in certain situations, including:

It is located at the center of a star schema or a snowflake schema surrounded by dimension tables.

Data loads into the snowflake schema must be highly controlled and managed to avoid update and insert anomalies.

An alternative physical implementation, called a snowflake schema, normalizes multi-level hierarchies within a dimension into multiple tables.

"Why is the Snowflake Schema a Good Data Warehouse Design?"

The cube metadata is typically created from a star schema or snowflake schema of tables in a relational database.

Some OLAP multidimensional database modeling tools are optimized for snowflake schemas.

Notice that the snowflake schema query requires many more joins than the star schema version in order to fulfill even a simple query.

The image of the schema to the right is a star schema version of the sample schema provided in the snowflake schema article.

When compared to a highly normalized transactional schema, the snowflake schema's denormalization removes the data integrity assurances provided by normalized schemas.

In computing, a snowflake schema is a logical arrangement of tables in a multidimensional database such that the entity relationship diagram resembles a snowflake in shape.

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.

However, in the snowflake schema, dimensions are normalized into multiple related tables, whereas the star schema's dimensions are normalized with each dimension represented by a single table.

Some database developers compromise by creating an underlying snowflake schema with views built on top of it that perform many of the necessary joins to simulate a star schema.

OLAP data is typically stored in a star schema or snowflake schema in a relational data warehouse or in a special-purpose data management system.

Deciding whether to employ a star schema or a snowflake schema should involve considering the relative strengths of the database platform in question and the query tool to be employed.

Snowflake schemas are often better with more sophisticated query tools that create a layer of abstraction between the users and raw table structures for environments having numerous queries with complex criteria.

The following example query is the snowflake schema equivalent of the star schema example code which returns the total number of units sold by brand and by country for 1997.

Star and snowflake schemas are most commonly found in dimensional data warehouses and data marts where speed of data retrieval is more important than the efficiency of data manipulations.

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 ("forks in the road").

The benefit of using the snowflake schema in this example is that the storage requirements are lower since the snowflake schema eliminates many duplicate values from the dimensions themselves.