A Snowflake Schema is a type of data warehouse schema where the dimension tables are normalized into multiple related tables. It gets its name because the diagram resembles a snowflake with branching structures. This schema is an extension of the Star Schema, offering better organization of data.
Key Features
- Normalized Dimensions: Dimension tables are split into multiple related tables to reduce redundancy.
- Complex Structure: The schema has multiple levels of related tables, making queries slightly more complex.
- Storage Efficiency: Normalization reduces data duplication and saves storage space.
- Support for Hierarchies: Ideal for dimensions with multiple hierarchical levels (e.g., country → state → city).
Structure
- Fact Table: Central table containing measurable data such as sales, revenue, or quantity.
- Dimension Tables: Related tables that provide contextual information about facts, e.g., customers, products, or time.
- Normalized Dimension Tables: Each dimension may be broken into smaller tables connected through foreign keys to reduce redundancy.
Example structure:
- Fact Table: Sales
- Dimension Tables: Product, Customer, Time
- Product Table further divided into: Product → Product Category → Product Subcategory
Advantages
- Reduces data redundancy in dimension tables
- Saves storage space
- Maintains data integrity
- Supports complex hierarchical relationships
Disadvantages
- Queries can be slower due to multiple joins
- Schema design is more complex than Star Schema
- Not as intuitive for end-users
Use Cases
- Large-scale data warehouses with highly normalized data
- Systems with complex hierarchies
- Scenarios where storage optimization is critical
Summary
The Snowflake Schema organizes data into normalized tables, offering storage efficiency and hierarchical clarity. It is preferred in environments where minimizing redundancy and maintaining structured hierarchies outweighs query simplicity.