Snowflake Schema
A snowflake schema is a dimensional data modeling technique that normalizes dimension tables of a star schema into multiple related sub-tables, reducing data redundancy at the cost of additional joins.
Definition
A snowflake schema is a dimensional data modeling technique that normalizes dimension tables of a star schema into multiple related sub-tables, reducing data redundancy at the cost of additional joins.
Overview
A star schema keeps every dimension flat and denormalized — a Product dimension, for instance, might repeat category and brand names on every row. A snowflake schema instead splits that Product dimension into separate normalized tables (Product, Category, Brand) linked by foreign keys, so each category or brand name is stored exactly once. Diagrammed, the branching sub-dimensions around the fact table resemble a snowflake, which gives the pattern its name. This extra normalization reduces storage redundancy and can make maintaining reference data (like updating a category name in one place) simpler and less error-prone. The tradeoff is query complexity: analytical queries need more joins to reassemble the full dimensional context, which can be slower than an equivalent star-schema query, especially on engines that are not optimized for many small joins. In practice, most modern analytics-engineering teams favor the star schema for its simplicity and query performance, reserving snowflaking for dimensions that are large, frequently updated, or shared across many fact tables where redundancy would otherwise be costly. This is a standard design tradeoff taught alongside database normalization principles in data warehousing courses. Both patterns coexist under the umbrella of dimensional modeling for a data warehouse or data mart, and the choice between them is typically driven by query engine characteristics and how often shared dimension data changes.
Key Concepts
- Normalizes dimension tables into related sub-tables, unlike the flat dimensions of a star schema
- Reduces data redundancy and can simplify updates to shared reference data
- Requires more joins for analytical queries than an equivalent star schema
- Named for the branching, snowflake-like diagram shape of normalized dimensions
- Useful when dimensions are large, shared across many fact tables, or updated frequently