Olap

Overview Star schema and snowflake schema are two ways to structure dimension tables around a fact table in a data warehouse. Star schema keeps dimensions flat and denormalized for fast, simple joins, while snowflake schema splits dimensions into related sub-tables that are normalized to reduce redundancy. The choice trades query simplicity against storage efficiency and data integrity. Comparison Diagram Star Schema Snowflake Schema Fact Dim Dim Dim Dim Dimensions denormalized, one hop to fact Fact Dim Dim Dim Sub Dim Sub Dimensions normalized into sub-tables Comparison Table Aspect Star Schema Snowflake Schema Dimension structure Each dimension is a single flat table with all descriptive attributes together Dimensions are split into multiple related tables organized by hierarchy level Data redundancy Attributes like category or region repeat across many rows within a dimension Redundant attributes are moved into separate sub-tables and referenced by key Join complexity per query Fact table joins directly to each dimension, one hop per dimension Queries often need multi-level joins through sub-dimension chains to reach an attribute Query performance Fewer joins generally mean faster scans and simpler execution plans Extra joins across normalized levels typically add query latency and planning overhead Storage footprint Larger on disk due to repeated attribute values across rows Smaller footprint since each attribute value is stored once and referenced Update and integrity handling Updating a shared attribute means touching many rows, risking inconsistency Updating a shared attribute means changing one row in a sub-table, preserving integrity ETL and load complexity Simpler load logic since each dimension maps to one target table More complex load logic to populate and link multiple normalized tables correctly BI tool and end-user friendliness Flat structure maps naturally to how most BI tools expect dimensions Nested hierarchies can confuse drag-and-drop BI tools and require extra modeling Key Differences Star schema keeps each dimension as one flat table; snowflake schema breaks dimensions into normalized sub-tables. Star schema favors fewer joins and faster read performance; snowflake schema favors lower storage redundancy. Snowflake schema reduces update anomalies by centralizing shared attributes, improving data integrity. Star schema is generally the default recommendation in Kimball-style dimensional modeling for BI workloads. Snowflake schema’s extra joins increase query complexity for both engines and end users. When to Use Each Star Schema ...