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.
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.
Get started with ETL
Learn about ETL pipelines with this O’Reilly technical guide.
A Comprehensive Guide to Data and AI Governance
How to build an effective governance strategy for your lakehouse.
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