What is Snowflake Schema?
Warehouse design extending star schema by normalizing dimension tables into multiple related tables, reducing redundancy while adding query complexity
- A snowflake schema is a data warehouse design where dimension tables are broken into related subdimensions, creating a more normalized, snowflake shaped model.
- This structure can reduce data redundancy and storage by organizing attributes such as product, category and department into separate linked tables.
- Compared with a star schema, a snowflake schema saves space but usually requires more joins, which can make queries more complex and slower for read heavy analytics.
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.
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.
The agentic AI playbook for the enterprise
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
Get the latest posts in your inbox
Subscribe to our blog and get the latest posts delivered to your inbox.