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

Power productive AI with reliable data

On the Databricks Data Intelligence Platform.

Read now

Big Book of Data Engineering

Fast-track your expertise with this essential guide for the AI era.

Read now

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

Resources

    Back to Glossary