Snowflake schema

Short description: Logical arrangement of computing tables in a multidimensional database

Not to be confused with Snowflake Inc. or Snowflake ID.

The snowflake schema is a variation of the star schema, featuring normalization of dimension tables.

In computing, a snowflake schema or snowflake model 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 all 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.[1]

The snowflake schema is similar to the star schema. However, 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 ("forks in the road").

## Contents

* 1 Common uses
* 2 Data normalization and storage
* 2.1 Benefits
* 3 Disadvantages
* 4 Examples
* 5 References
* 6 Bibliography
* 7 External links

## Common uses

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. As such, the tables in these schemas are not normalized much, and are frequently designed at a level of normalization short of third normal form.[2]

## Data normalization and storage

Normalization splits up data to avoid redundancy (duplication) by moving commonly repeating groups of data into new tables. Normalization therefore tends 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 needs to be updated if the data changes.

From a space storage point of view, dimensional tables are typically small compared to fact tables. This often negates the potential storage-space benefits of the snowflake schema as compared to the star schema. Example: One million sales transactions in 300 shops in 220 countries would result in 1,000,300 records in a star schema (1,000,000 records in the fact table and 300 records in the dimensional table where each country would be listed explicitly for each shop in that country). A more normalized snowflake schema with country keys referring to a country table would consist of the same 1,000,000 record fact table, a 300 record shop table with references to a country table with 220 records. In this case, the star schema, although further denormalized, would only reduce the number or records by a (negligible) ~0.02% (=[1,000,000+300] instead of [1,000,000+300+220])

### Benefits

In dimensional modeling, a snowflake schema normalizes dimension hierarchies into multiple related tables. In some situations it can offer benefits; however, they come with notable trade-offs.

Potential benefits

* Storage efficiency for very large hierarchies. Normalizing low-cardinality rollups (e.g., product category/brand, geography) removes repeated text and can reduce redundancy and update effort in large dimensions.[3]
* Accurate representation of hierarchical relationships. A snowflake reflects the hierarchy explicitly by linking secondary tables to the base dimension.[4]

Trade-offs and limitations

* Ease-of-use and query performance. Snowflaked designs are harder for business users to understand and navigate and can negatively impact query performance due to additional joins.[4][5]
* ETL and maintenance complexity. Managing keys and slowly changing hierarchies across multiple linked dimension tables increases ETL complexity; any space savings are often negligible relative to the added complexity.[5]
* More joins required. While normalization reduces redundancy, it typically increases the number of joins needed at query time.[3]

## 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.

Snowflake schemas, in contrast to flat single table dimensions, have been heavily criticised. Their goal is assumed to be an efficient and compact storage of normalised data but this is at the significant cost of poor performance when browsing the joins required in this dimension.[6] This disadvantage may have reduced in the years since it was first recognized, owing to better query performance within the browsing tools.

## Examples

Snowflake schema used by example query.

The example schema shown to the right is a snowflaked version of the star schema example provided in the star schema article.

The following example query is the snowflake schema equivalent of the star schema example code which returns the total number of television units sold by brand and by country for 1997. Notice that the snowflake schema query requires many more joins than the star schema version in order to fulfill even a simple query. 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.

SELECT
B.Brand,
G.Country,
SUM(F.Units_Sold)
FROM Fact_Sales F
INNER JOIN Dim_Date D             ON F.Date_Id = D.Id
INNER JOIN Dim_Store S            ON F.Store_Id = S.Id
INNER JOIN Dim_Geography G        ON S.Geography_Id = G.Id
INNER JOIN Dim_Product P          ON F.Product_Id = P.Id
INNER JOIN Dim_Brand B            ON P.Brand_Id = B.Id
INNER JOIN Dim_Product_Category C ON P.Product_Category_Id = C.Id
WHERE
D.Year = 1997 AND
C.Product_Category = 'tv'
GROUP BY
B.Brand,
G.Country

## References

1. ↑ Paulraj Ponniah. Data Warehousing Fundamentals for IT Professionals. Wiley, 2010, pp. 29–32. ISBN 0470462078.
2. ↑ Han, Jiawei (2012). Data Mining - Concepts and Techniques. Massachusetts, USA: Morgan Kauffmann Publishers. ISBN 9780123814791.
3. ↑ 3.0 3.1 "Star and Snowflake Schemas". Oracle. https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/owb/owb10gr2_gs/owb/lesson3/starandsnowflake.htm.
4. ↑ 4.0 4.1 "Snowflaked Dimensions". https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/snowflake-dimension/.
5. ↑ 5.0 5.1 Ross, Margy (2008-09-03). "Design Tip #105: Snowflakes, Outriggers, and Bridges". https://www.kimballgroup.com/2008/09/design-tip-105-snowflakes-outriggers-and-bridges/.
6. ↑ Kimball, Ralph (1996). "6: The Big Dimensions". The Data Warehouse Toolkit (1st ed.). Wiley. pp. 95–98. ISBN 0-471-15337-0. https://archive.org/details/datawarehousetoo00kimb_0/page/95. "Do not snowflake your dimensions, even if they are large"

## Bibliography

* Anahory, S.; D. Murray. Data Warehousing in the Real World: A Practical Guide for Building Decision Support Systems. Addison Wesley Professional.
* Kimball, Ralph (1996). The Data Warehousing Toolkit. John Wiley. ISBN 978-0-471-15337-5. https://archive.org/details/datawarehousetoo00kimb_0.

## External links

* "Why is the Snowflake Schema a Good Data Warehouse Design?" by Mark Levene and George Loizou
* Reverse Snowflake Joins

* v
* t
* e

Data warehouses

Creating a data warehouse

| Concepts|

* Database
* Dimension
* Dimensional modeling
* Fact
* OLAP
* Star schema
* Snowflake schema
* Reverse star schema
* Aggregate

|

Variants|

* Anchor modeling
* Column-oriented DBMS
* Data vault modeling
* HOLAP
* MOLAP
* ROLAP
* Operational data store

Elements|

* Data dictionary/Metadata
* Data mart
* Sixth normal form
* Surrogate key

Fact|

* Fact table
* Early-arriving fact
* Measure

Dimension|

* Dimension table
* Degenerate
* Slowly changing

Filling|

* Extract-Transform-Load (ETL)
* Extract
* Transform
* Load

Using a data warehouse

| Concepts|

* Business intelligence
* Dashboard
* Data mining
* Decision support system (DSS)
* OLAP cube
* Data warehouse automation

|

Languages|

* Data Mining Extensions (DMX)
* MultiDimensional eXpressions (MDX)
* XML for Analysis (XMLA)

Tools|

* Business intelligence software
* Reporting software
* Spreadsheet

Related

| People|

* Bill Inmon
* Ralph Kimball

|

Products|

* Comparison of OLAP servers
* Data warehousing products and their producers

0.00

(0 votes)

Original source: https://en.wikipedia.org/wiki/Snowflake schema. Read more

|

Retrieved from "https://handwiki.org/wiki/index.php?title=Snowflake_schema&oldid=3982755"
*[v]: View this template
*[t]: Discuss this template
*[e]: Edit this template