Opinion — Are Classical OLAP Structures still relevant?

What is a Snowflake Schema?

And What are the Differences to a Star Schema?

In this article, you will get to know what a Snowflake Schema is, what the difference to a Star Schema[1] is and the most important facts.

Snowflake Schema in a Nutshell

The snowflake schema is an extension of the star schema used in OLAP and Data Warehouse Architectures. In the star schema, the dimension tables are denormalized, which results in better processing speed at the expense of data integrity and storage space. In contrast, the Snowflake Schema refines the individual dimension tables by classifying or normalizing them. This further branching of the data model creates the shape of a snowflake, which is where the name of this design pattern comes from[2].

Example of a Snowflake Schema in a Database Model — Source: Stackoverflow [3]

An example for that can be seen above. Here for example, the table address is normalized into two dimension tables namely address and country while working in a Star Schema you would probably put address and county data into one dimension table.

Pros and Cons

Due to this finer structuring, the data is less redundant than in a star schema, but additional join operations may be required for queries. A snowflake schema thus leads to smaller and better structured data sets, which, however, have more complex interrelationships and thus may lead to longer loading or query times.

Summary & Further Outlook

Once in short what you should know in theory about the Snowflake Schema. In the area of OLAP and data warehousing it is the extension of the Star Schema. Its biggest pro is that it goes further in the normalization of the data and therefore often result in a better structure but that also brings it’s biggest problems less speed and more complexity. That’s why developers and architects often rather stick to a star schema. Due to newer technologies this approach will lose its relevance in the future. NoSQL databases and Data Lakes with new approaches are on the rise. Nevertheless, as a data engineer or scientist, you should be aware of this topic, since you will still often find databases with Star or Snowflake Schema as source systems.

Sources and Further Readings

[1] Christian Lauer, What is a Star Schema? (2021)

[2] Wikipedia, Snowflake schema (2021)

[3] Stackoverflow, how to create snowflake schema from this database? (2020)