Star Schema vs Snowflake Schema

"Star or snowflake, and why?" Interview for a BI role in Lahore or Karachi and you'll hear that question, and the interviewer wants a rule, not a definition. Both patterns start from the facts and dimensions of the last lesson and differ only in how the dimension tables are shaped. A star schema keeps each dimension as one flat table. A snowflake schema breaks dimensions into normalised sub-tables. Power BI has a strong opinion about which one it prefers, and so do we.

The star schema

Draw the fact table in the middle and one dimension at each point of a star. Each dimension is a single, denormalised table. dim_product carries SKU, subcategory, category, brand and supplier on one row, even though category repeats on thousands of product rows. Every dimension joins straight to the fact through one integer key.

What you get with a star:

• One join per dimension. Sales by category is fact to dim_product, done.
• Readable for business users. Every attribute of a product is in the product table.
• Fast on columnar engines. Optimisers recognise the star shape and handle it well.
• Some repeated text. "Lawn suit" is stored 4,000 times. On a columnar store that compresses to almost nothing.

The snowflake schema

A snowflake normalises the dimensions. dim_product keeps only product-level attributes and a subcategory_key; dim_subcategory holds the name and a category_key; dim_category holds the category name. Store to city to region works the same way. The dimension now branches like a snowflake crystal, which is where the name comes from.

What you get with a snowflake:

• No repeated text. The category name exists once. Renaming a category is a single-row update.
• More joins. Sales by region is fact to store to city to region, three joins instead of one.
• Harder for users. A manager exploring the model in Power BI sees six product-related tables instead of one.
• Smaller dimension storage. Only matters when dimensions have tens of millions of rows.

Side by side

Criterion	Star	Snowflake
Number of tables	Few	Many
Joins per query	One per dimension	Several per dimension
Query speed (BI workloads)	Faster	Slower
Storage for dimensions	Larger (repeated attributes)	Smaller
Ease of maintenance of hierarchies	Update many rows	Update one row
Business-user friendliness	High	Low
Power BI recommendation	Strongly preferred	Avoid; import and flatten
Common in	Marts, Power BI models, most modern warehouses	Inmon-style cores, very large dimensions, some ERP-driven warehouses

The same query, both ways

-- STAR: sales by region, one join
SELECT s.region, SUM(f.net_amount) AS net_sales
FROM dw.fact_sales f
JOIN dw.dim_store s ON s.store_key = f.store_key
GROUP BY s.region;

-- SNOWFLAKE: sales by region, three joins
SELECT r.region_name, SUM(f.net_amount) AS net_sales
FROM dw.fact_sales f
JOIN dw.dim_store  s  ON s.store_key  = f.store_key
JOIN dw.dim_city   c  ON c.city_key   = s.city_key
JOIN dw.dim_region r  ON r.region_key = c.region_key
GROUP BY r.region_name;

Both return the same answer. The star version is what a junior analyst writes on day one, and what a self-service user gets by dragging "Region" onto a visual. The snowflake version needs knowledge of the branch structure, and every missed join is a silent wrong answer. We've marked enough student assignments to know the missed join is never the one they expect.

The rule we teach

Design the core in stars unless you have a specific reason not to. Legitimate reasons for a snowflake do exist. A dimension with tens of millions of rows where a sub-table is shared by several dimensions (one geography table used by customer, store and supplier). A regulated requirement that a reference list (product categories, ICD medical codes) be maintained in exactly one place. An Inmon-style normalised core that publishes star-shaped marts. In practice many teams keep a small normalised reference area and flatten it into stars when publishing marts, which gives the maintenance benefit without the query cost.

A related pattern is the outrigger, a star dimension with one small side table hanging off it, for example dim_customer pointing to dim_date for "first purchase date" so all the date attributes come along. One outrigger is normal. A chain of them is a snowflake wearing a disguise.

Fourteen tables and no "department": an Islamabad private hospital group built its warehouse as a snowflake because the ERP vendor's model was normalised. Patient to ward to department to hospital to region, doctor to speciality to faculty. The first Power BI dashboards were slow, and doctors couldn't find "department" among 14 tables. The team kept the snowflake as the core and added a mart with two flat dimensions, dim_location (ward, department, hospital, region on one row) and dim_doctor. Dashboards ran about three times faster and the department heads could finally build their own reports.

Pin this on the wall

• Star means flat dimensions and one join each. Snowflake means normalised dimensions and more joins.
• On columnar engines the star's repeated text costs almost nothing, so the snowflake's storage advantage rarely matters.
• Power BI and most BI tools want stars. Flatten before publishing.
• Use a snowflake, or a small normalised reference area, only for very large shared sub-dimensions or strict single-place maintenance rules.
• One outrigger is fine. A chain of outriggers is a snowflake.

Your turn

1. Draw (on paper) a star schema for a university: fact_enrolment with dim_student, dim_course, dim_semester, dim_faculty. List five columns for each dimension.
2. Convert dim_course into a snowflake (course, department, faculty tables). Write the query "students enrolled per faculty" against both designs and count the joins.
3. A teammate argues that repeating "Lahore" on 300,000 customer rows wastes space. Estimate the storage cost (assume 6 bytes per value uncompressed) and explain why columnar compression makes it a non-issue.