Snowflake Schema

A snowflake schema is the same as a star schema except that a dimension table can be normalized (hierarchically decomposed) into additional dimension tables. Every dimension table participates in a 1::n join with the fact table or another dimension table.

In a snowflake schema, the

fact table used in a query or projection is called the

Anchor Table. A query or projection based on that anchor table can include columns from any table in the schema, as shown below.

The anchor table of a join can be:

the fact table in a star schema
the central fact table in a snowflake schema
a dimension table in a snowflake schema that functions as a fact table (with a restriction)

A fact table is a table that represents quantitative or factual data. For example, in a business, a fact table might represent orders.

A fact table is often located at the center of a star schema or snowflake schema. It typically has a large number of tuples and is surrounded by a collection of dimension tables, each with a lesser number of tuples. The fact table participates in a join with every dimension table. In other words, a fact table can contain data but generally contains many join columns (with optional FOREIGN KEY constraints), each of which corresponds to the primary key column of a dimension table.

However, you also can use a dimension table as an anchor table. There is one restriction: a query or projection based on that table can include only its own columns and those of its dimension tables. It cannot include another fact table. In the diagram below, a query or projection based on the indicated anchor table is limited to the tables inside the box.