Star Schema & Snowflake Schema

Dimensional Modeling in Data Warehousing

Dr. Mohsin Dar

Assistant Professor

Cloud & Software Operations Cluster | SOCS

University of Petroleum and Energy Studies (UPES)

Database Systems - MTech First Semester

Introduction to Dimensional Modeling

What is Dimensional Modeling?
A design technique used in data warehousing to organize data for efficient querying and analysis.

Key Components:

Fact Tables: Store quantitative data (measures) about business processes
Dimension Tables: Store descriptive attributes related to facts
Keys: Primary and foreign keys that establish relationships
Measures: Numeric values that can be aggregated (sum, count, avg)

Primary Schemas: Star Schema and Snowflake Schema

Star Schema

Definition:

A star schema is a database organizational structure where a central fact table is surrounded by denormalized dimension tables, resembling a star shape.

Star Schema Structure

Dimension 1
(Customer)

Dimension 2
(Product)

↓

FACT TABLE
(Sales)

↓

Dimension 3
(Time)

Dimension 4
(Location)

Characteristics:

Simple, denormalized structure
One level of relationship between fact and dimensions
Dimension tables are not normalized

Snowflake Schema

Definition:

A snowflake schema is an extension of the star schema where dimension tables are normalized into multiple related tables, creating a snowflake-like pattern.

Snowflake Schema Structure

Customer

↓

City

↓

Country

Product

↓

Characteristics:

Normalized dimension tables
Multiple levels of relationships
Reduced data redundancy

Comparison: Advantages & Disadvantages

Aspect	Star Schema	Snowflake Schema
Query Performance	✓ Faster (fewer joins)	✗ Slower (more joins)
Storage Space	✗ More space (redundancy)	✓ Less space (normalized)
Query Complexity	✓ Simple queries	✗ Complex queries
Data Integrity	✗ Lower (redundancy)	✓ Higher (normalized)
Maintenance	✗ More updates needed	✓ Easier maintenance
ETL Complexity	✓ Simpler ETL process	✗ Complex ETL process

Star Schema - Advantages

Simplified Queries: Fewer joins required, making SQL queries easier to write and understand
Better Query Performance: Faster query execution due to denormalized structure
Easier to Understand: Intuitive structure for business users and analysts
Optimized for OLAP: Ideal for analytical processing and reporting
Faster Aggregations: Dimension attributes directly available without additional joins
Simpler ETL Process: Less complex data transformation logic

Best Use Case: When query performance is critical and storage is not a major concern

Star Schema - Disadvantages

Data Redundancy: Duplicate data in dimension tables increases storage requirements
Higher Storage Costs: Denormalized tables consume more disk space
Data Integrity Issues: Redundancy can lead to inconsistencies during updates
Complex Updates: Updating dimension attributes requires changes in multiple rows
Limited Flexibility: Not ideal for complex hierarchical relationships
Insertion Anomalies: Can occur when adding new dimension members

Trade-off: Performance vs. Storage and Data Integrity

Snowflake Schema - Advantages

Reduced Storage Space: Normalization eliminates data redundancy
Better Data Integrity: Normalized structure reduces update anomalies
Easier Maintenance: Changes to dimension attributes affect fewer records
Improved Data Quality: Less chance of inconsistent data
Supports Complex Hierarchies: Natural representation of hierarchical relationships
Flexible Structure: Easier to add new hierarchical levels

Best Use Case: When data integrity and storage efficiency are priorities

Snowflake Schema - Disadvantages

Complex Queries: Multiple joins required increase query complexity
Slower Query Performance: More joins lead to longer execution times
Difficult to Understand: Complex structure harder for business users
Complex ETL: More sophisticated data transformation required
Query Optimization Challenges: Database optimizer has more work to do
Increased Development Time: More complex to design and implement

Trade-off: Storage Efficiency vs. Query Performance and Complexity

Performance Considerations

Star Schema Performance:

Query Speed: Typically 2-3x faster than snowflake for complex queries
Join Operations: Fewer joins (1 join per dimension)
Index Strategy: Simple indexing on foreign keys
Aggregation: Faster due to denormalized dimensions

Snowflake Schema Performance:

Query Speed: Slower due to multiple joins
Join Operations: Multiple joins (hierarchical relationships)
Index Strategy: Complex indexing across normalized tables
Aggregation: Requires traversing hierarchy levels

Performance Optimization: Use materialized views, partitioning, and proper indexing strategies

Decision Criteria: Which Schema to Use?

Use Star Schema When:

Query performance is the top priority
Storage space is not a constraint
Dimension tables are relatively small
Users need simple, fast reporting
OLAP and BI tools are primary consumers
Data warehouse is read-intensive

Use Snowflake Schema When:

Storage optimization is critical
Complex hierarchical dimensions exist
Data integrity is paramount
Dimension tables are very large
Frequent dimension updates occur
Normalized data is required for compliance

Example: Retail Sales Star Schema

Business Scenario:

A retail company wants to analyze sales data across products, customers, time, and stores.

Fact Table: SALES_FACT

Primary Keys (Composite): - sale_id (Surrogate Key) Foreign Keys: - product_key → PRODUCT_DIM - customer_key → CUSTOMER_DIM - time_key → TIME_DIM - store_key → STORE_DIM Measures: - quantity_sold (Additive) - sales_amount (Additive) - discount_amount (Additive) - profit_amount (Additive) - unit_price (Non-additive)

Star Schema - Dimension Tables

PRODUCT_DIM
PK: product_key
Attributes: product_id, product_name, category, subcategory, brand, supplier_name, supplier_city, supplier_country, unit_cost

CUSTOMER_DIM
PK: customer_key
Attributes: customer_id, customer_name, age, gender, email, phone, address, city, state, country, postal_code, customer_segment

TIME_DIM
PK: time_key
Attributes: date, day, month, quarter, year, day_of_week, week_of_year, is_weekend, is_holiday, fiscal_period

STORE_DIM
PK: store_key
Attributes: store_id, store_name, store_type, manager_name, address, city, state, country, region, store_size, opening_date

Star Schema - Complete Visual

PRODUCT_DIM
product_key (PK)
product_name
category
brand
supplier_name

CUSTOMER_DIM
customer_key (PK)
customer_name
city
state
country

SALES_FACT
sale_id (PK)
product_key (FK)
customer_key (FK)
time_key (FK)
store_key (FK)
─────────────
quantity_sold
sales_amount
profit_amount

TIME_DIM
time_key (PK)
date
month
quarter
year

STORE_DIM
store_key (PK)
store_name
city
region
store_type

Note: All dimensions connect directly to the fact table with single-level relationships

Example: Retail Sales Snowflake Schema

Same Business Scenario - Normalized Approach

Same retail company with normalized dimension tables to reduce redundancy.

Fact Table: SALES_FACT (Same as Star Schema)

Keys and Measures remain identical Difference: Dimension tables are normalized into multiple related tables

Key Changes:

Dimension attributes split into normalized tables
Hierarchical relationships explicitly modeled
Reduced redundancy in dimension storage

Snowflake Schema - Normalized Dimensions

PRODUCT Hierarchy:

PRODUCT
product_key (PK)
category_key (FK)
supplier_key (FK)

→

CATEGORY
category_key (PK)
category_name
subcategory

→

SUPPLIER
supplier_key (PK)
supplier_name
city_key (FK)

CUSTOMER Hierarchy:

CUSTOMER
customer_key (PK)
customer_name
city_key (FK)

→

CITY
city_key (PK)
city_name
state_key (FK)

→

STATE
state_key (PK)
state_name
country_key (FK)

→

COUNTRY
country_key (PK)
country_name

STORE Hierarchy:

STORE
store_key (PK)
store_name
city_key (FK)

→

CITY
(Shared with Customer)

Snowflake Schema - Complete Visual

SALES_FACT
sale_id (PK)
product_key (FK)
customer_key (FK)
store_key (FK)
time_key (FK)
quantity_sold
total_amount

PRODUCT
product_key (PK)
product_name
category_key (FK)
supplier_key (FK)

CATEGORY
category_key (PK)
category_name
subcategory

SUPPLIER
supplier_key (PK)
supplier_name
city_key (FK)

CUSTOMER
customer_key (PK)
customer_name
city_key (FK)

↓

CITY
city_key (PK)
city_name
state_key (FK)

↓

STATE
state_key (PK)
state_name
country_key (FK)

↓

COUNTRY
country_key (PK)
country_name

STORE
store_key (PK)
store_name
city_key (FK)

↓

CITY
(Shared with Customer)

TIME
time_key (PK)
date
day_of_week
month
quarter
year
is_holiday

Summary and Key Takeaways

Star Schema

Simple and denormalized structure
Single fact table connected to dimension tables
Faster query performance for analytical queries
Higher storage requirements due to data redundancy

Snowflake Schema

Normalized dimension tables
Reduced data redundancy and storage requirements
More complex queries due to multiple joins
Better for complex hierarchies and slowly changing dimensions

Choosing the Right Schema

Use Star Schema for:
- Read-heavy analytical workloads
- Simpler data models
- When query performance is critical
Use Snowflake Schema when:
- Storage optimization is important
- Working with complex hierarchies
- Data integrity is a top priority

Final Thoughts

Both star and snowflake schemas have their place in data warehousing. The choice depends on your specific requirements for query performance, storage efficiency, and data complexity. Understanding these schemas is crucial for designing effective data warehouses that meet your organization's analytical needs.

Slide 18 of 18