Normalized dimensional modeling approach
A Snowflake Schema is a normalized variation of the star schema where dimension tables are broken down into multiple related tables, creating a structure that resembles a snowflake pattern. Unlike star schema's denormalized dimensions, snowflake schema normalizes dimension tables into hierarchical relationships.
In a snowflake schema, dimension attributes are organized into separate lookup tables based on their hierarchical relationships. For example, a Product dimension might be split into Product, Category, and Subcategory tables, each with their own primary and foreign keys.
All attributes in single table. Fast queries (1 join to fact) but data redundancy.
Attributes separated into hierarchical tables. Less redundancy but more joins required.
Normalized dimension hierarchies
Star Schema (2 joins):
Snowflake Schema (4 joins):
| Aspect | Star Schema | Snowflake Schema |
|---|---|---|
| Dimension Structure | Denormalized (single table) | Normalized (multiple tables) |
| Query Performance | Faster (fewer joins) | Slower (more joins) |
| Storage Space | More (redundant data) | Less (normalized) |
| Complexity | Simple, intuitive | Complex, harder to navigate |
| Maintenance | Update redundant data | Update once in lookup table |
| BI Tool Support | Excellent | Good but requires more setup |
| User Understanding | Easy for business users | Requires more training |
| ETL Complexity | Moderate | Higher (manage multiple tables) |