Star vs Snowflake Schema

Reading Time: 8 Mins | Level: Beginner to Advanced

1. What is Dimensional Modeling?

Dimensional modeling is the industry-standard data warehousing design technique introduced by Ralph Kimball. It organizes tables into two distinct categories:

1. 1
   Fact Tables: Contain quantitative, measurable business transactions and events (e.g. Sales_Amount, Units_Sold, Order_Date). Usually narrow, with millions of rows.
2. 2
   Dimension Tables: Contain descriptive context and attributes used to filter, slice, and group the facts (e.g. Customer_Name, Product_Category, Store_Location). Usually wide, with fewer rows.

2. Star Schema vs Snowflake Schema

Star Schema (Recommended by Microsoft for Power BI):

• Fact table sits in the center, directly connected to surrounding Dimension tables.
• Looks like a star ($star$).
• De-normalized dimensions: Fast, simple, highly optimized for Power BI's VertiPaq engine.

Snowflake Schema:

• Dimensions are further normalized into sub-dimension tables (e.g. Product links to Subcategory, which links to Category).
• Requires more relationship hops, increasing query complexity.