Dimensional Modeling: Star Schema vs Snowflake Schema

Explain dimensional modeling approaches in data warehousing.

What Is Dimensional Modeling

Proposed by Ralph Kimball, this modeling method optimizes for analytical queries. Core components are Fact Tables and Dimension Tables.

Fact Table

Stores measurable values of business events (e.g., sales amount, quantity, clicks). Contains multiple foreign keys to dimension tables and one or more numeric measure columns.

Dimension Table

Describes the context of business events (e.g., customer, product, time, region). Provides the "slicing dimensions" for analysis.

Star Schema

Fact table at the center; dimension tables connect directly (denormalized). Simple queries, good performance — the mainstream choice.

Snowflake Schema

Dimension tables are further normalized (e.g., the region dimension splits into country, city, and district tables). Saves storage but requires more JOINs and adds complexity.

Practical Advice

Default to star schema. Only consider snowflake when storage costs are extremely sensitive. Modern cloud warehouses (BigQuery, Snowflake) make storage cheap, so normalization provides little benefit.