What is star schema and snowflake schema?

Star schema and snowflake schema are two fundamental dimensional modeling techniques used in data warehouses to organize data for analytical querying. They both involve a central fact table surrounded by dimension tables, but they differ significantly in the normalization level of their dimension tables, impacting storage, query performance, and design complexity.

1. Star Schema

A star schema is the simplest and most commonly used data warehouse schema. It consists of a central 'fact table' which stores quantitative measures (facts) and foreign keys to 'dimension tables'. Each dimension table contains descriptive attributes related to the facts. The key characteristic of a star schema is that its dimension tables are denormalized, meaning they contain all attributes for a specific dimension in a single table, without further sub-dimension tables.

In a star schema, each dimension table is directly joined to the fact table. This structure resembles a star, with the fact table at the center and dimension tables radiating outwards. This simplicity typically leads to straightforward and high-performance queries, as most queries involve a single join between the fact table and one or more dimension tables.

Advantages of Star Schema

• Simplicity: Easy to understand, design, and navigate.
• Query Performance: Fewer joins generally lead to faster query execution, especially for large fact tables.
• Easier Aggregation: Simpler to build aggregate tables for performance optimization.
• BI Tool Compatibility: Many business intelligence (BI) tools are optimized for star schemas.

Disadvantages of Star Schema

• Data Redundancy: Denormalized dimensions can lead to higher data redundancy and increased storage space.
• Less Flexible: Adding new attributes or changing existing ones might require significant changes and impact multiple records.
• Slower ETL: Loading data (ETL process) might be slower due to larger, denormalized dimension tables.

2. Snowflake Schema

A snowflake schema is an extension of the star schema where the dimension tables are normalized. This means that if a dimension table in a star schema contains hierarchical information or repeating attributes, those attributes are extracted into separate 'sub-dimension' tables. These sub-dimension tables are then linked back to the original dimension table, forming a hierarchy of normalized dimension tables. The central fact table remains the same.

The normalization process in a snowflake schema reduces data redundancy and improves data integrity. However, this comes at the cost of increased complexity in the schema design and potentially more joins required for analytical queries, as a query might need to traverse multiple dimension tables to retrieve all necessary attributes.

Advantages of Snowflake Schema

• Reduced Data Redundancy: Normalization minimizes data duplication, leading to more efficient storage (though this advantage is less pronounced with modern storage costs).
• Better Data Integrity: Enforcing referential integrity across normalized dimensions helps maintain data consistency.
• More Flexible: Easier to add or modify attributes in dimensions without affecting other parts of the dimension hierarchy.
• Efficient Dimension Updates: Updates to dimensional data are more efficient due to less redundancy.

Disadvantages of Snowflake Schema

• Increased Complexity: The schema design is more complex due to a higher number of tables and relationships.
• Slower Query Performance: Queries often require more joins (between the fact table and multiple dimension/sub-dimension tables), which can degrade performance.
• Harder to Understand: Users and BI tools might find it more challenging to navigate and query.
• More ETL Complexity: The ETL process can become more complicated to manage due to more tables.

3. Key Differences Summary