Skip to main content

Snowflake Schema

What is a snowflake schema?

A snowflake schema is a multi-dimensional data model that is an extension of a star schema, where dimension tables are broken down into subdimensions. Snowflake schemas are commonly used for business intelligence and reporting in OLAP data warehouses, data marts, and relational databases.

In a snowflake schema, engineers break down individual dimension tables into logical subdimensions. This makes the data model more complex, but it can be easier for analysts to work with, especially for certain data types.

It's called a snowflake schema because its entity-relationship diagram (ERD) looks like a snowflake, as seen below.

A snowflake schema diagram with a central fact table that connects to multiple dimensional tables and subdimensional tables via foreign keys.

Here’s more to explore

Big Book of Data Warehousing and BI

Your complete how-to guide to data warehousing with the Data Intelligence Platform — including real-world use cases.

Read now

Get started with ETL

Learn about ETL pipelines with this O’Reilly technical guide.

Download now

A Comprehensive Guide to Data and AI Governance

How to build an effective governance strategy for your lakehouse.

Get the eBook

Snowflake schemas vs. star schemas

Like star schemas, snowflake schemas have a central fact table which is connected to multiple dimension tables via foreign keys. However, the main difference is that they are more normalized than star schemas.

Snowflake schemas offer more storage efficiency, due to their tighter adherence to high normalization standards, but query performance is not as good as with more denormalized data models. Denormalized data models like star schemas have more data redundancy (duplication of data), which makes query performance faster at the cost of duplicated data.

Benefits of snowflake schemas

  • Fast data retrieval
  • Enforces data quality
  • Simple, common data model for data warehousing

Drawbacks of snowflake schemas

  • Lots of overhead upon initial setup
  • Rigid data model
  • High maintenance costs


    Back to Glossary