Snowflake Schema

data-warehouse

Tables

Fact Table

Fact Table in Snowflake Schema

In the snowflake schema, the fact table, similar to the star schema, contains the core data required to represent a data process, defined by the process owner.

The fact table consists of one or multiple dimension fields and one or multiple value fields. Similar to relational data modelling’s composite key, the dimension fields all together identify the value tuple (of value fields) uniquely.

See Online Analytical Processing for further insights.

Dimension Table

Dimension Table in Snowflake Schema

In the snowflake schema, the dimension table, similar to the star schema, extends the meaning of a dimension field from the fact table by deriving new fields from it.

Example:

• fact_sells (table): dim_date, …
• dim_date (table): date → month → year

The fields month and year are derived from the initial dim_date.

See OLAP data cube for further insights.

Subtable

Subtable in Snowflake Schema

The snowflake extends the star schema by introducing the concept of a subtable. Subtables add additional information to a fact table or dimension table which are not commonly used for aggregation (e.g.: name of a state).

---

Examples

Impfdaten-Dashboard

Many Subtables: The snowflake schema allows to split up data into multiple different subtables. This, however, decreases query performance.

Less Subtables: The more subtables are added to an OLAP data cube, the slower queries become. Therefore, the number of subtables should be leveraged between usage and performance.