Snowflake Schema

Normalized dimensional modeling approach

What is a Snowflake Schema?

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.

Star Schema vs Snowflake Schema

Star Schema Structure
Dim_Product (Denormalized)
product_key (PK)
product_name
product_description
category_name
category_description
subcategory_name
subcategory_description
brand
supplier_name
supplier_country

All attributes in single table. Fast queries (1 join to fact) but data redundancy.

Snowflake Schema Structure
Dim_Product
product_key (PK), product_name, subcategory_key (FK)
Dim_Subcategory
subcategory_key (PK), subcategory_name, category_key (FK)
Dim_Category
category_key (PK), category_name

Attributes separated into hierarchical tables. Less redundancy but more joins required.

Snowflake Schema Example

Sales Snowflake Schema

Normalized dimension hierarchies

Fact_Sales
date_key (FK)
product_key (FK)
customer_key (FK)
store_key (FK)
quantity_sold
sales_amount
cost_amount

Product Dimension (Normalized)

Dim_Product
product_key, product_name, SKU, subcategory_key (FK), brand_key (FK)
→
Dim_Subcategory
subcategory_key, subcategory_name, category_key (FK)
→
Dim_Category
category_key, category_name, department
Dim_Brand
brand_key, brand_name, manufacturer

Customer Dimension (Normalized)

Dim_Customer
customer_key, customer_name, email, city_key (FK)
→
Dim_City
city_key, city_name, state_key (FK)
→
Dim_State
state_key, state_name, country_key (FK)

Sample Query Comparison:

Star Schema (2 joins):

SELECT p.category_name, SUM(sales_amount)
FROM Fact_Sales f
JOIN Dim_Product p ON f.product_key = p.product_key
GROUP BY p.category_name

Snowflake Schema (4 joins):

SELECT c.category_name, SUM(sales_amount)
FROM Fact_Sales f
JOIN Dim_Product p ON f.product_key = p.product_key
JOIN Dim_Subcategory s ON p.subcategory_key = s.subcategory_key
JOIN Dim_Category c ON s.category_key = c.category_key
GROUP BY c.category_name

Trade-offs

Advantages

Reduced Data Redundancy
Normalization eliminates duplicate data. Category names stored once instead of repeating for every product. Saves storage space, especially with large dimension tables.
Easier Dimension Maintenance
Updates to hierarchical attributes (like changing a category name) only require updating one row in one table. Reduces risk of inconsistencies from multiple updates.
Better for Complex Hierarchies
Models complex, deep hierarchies more naturally. Geographic hierarchies (Country → State → City → Zip Code) are cleaner when normalized.

Disadvantages

Slower Query Performance
Requires more joins to access dimension attributes. Each additional join impacts query performance. Complex queries can become significantly slower than equivalent star schema queries.
Increased Complexity
More tables and relationships make the schema harder for business users to understand. Requires more sophisticated ETL logic. BI tools may struggle with deeply normalized structures.
Less BI Tool Friendly
Many BI tools are optimized for star schemas. Users must navigate multiple tables to access related attributes. Requires more complex metadata modeling in BI layer.

Detailed Comparison

AspectStar SchemaSnowflake Schema
Dimension StructureDenormalized (single table)Normalized (multiple tables)
Query PerformanceFaster (fewer joins)Slower (more joins)
Storage SpaceMore (redundant data)Less (normalized)
ComplexitySimple, intuitiveComplex, harder to navigate
MaintenanceUpdate redundant dataUpdate once in lookup table
BI Tool SupportExcellentGood but requires more setup
User UnderstandingEasy for business usersRequires more training
ETL ComplexityModerateHigher (manage multiple tables)

When to Use Snowflake Schema

Consider Snowflake When:
  • •Storage costs are a primary concern and space savings matter
  • •Dimension tables are extremely large with significant redundancy
  • •Complex, deep hierarchies are central to your data model
  • •Dimension maintenance complexity outweighs query performance needs
  • •Limited technical resources for managing complex structures