Star Schema and Snowflake Schema in Data Warehouse Design

Definition. Both are dimensional modelling schemes: a central fact table holding measurable events (foreign keys plus measures) surrounded by dimension tables holding the context (who, what, where, when). They differ only in how the dimensions are normalised.

1. Star schema

• One fact table at the centre; each dimension is a single, denormalised table joined directly to the fact table.
• The join pattern looks like a star; every query needs a fixed, small number of joins.
• Attributes such as city, state and country repeat inside one store-dimension table — some redundancy, but simple and fast queries.

2. Snowflake schema

• The normalised form of the star: each dimension is split into several related tables following its hierarchy (Store → City → State → Country).
• The join pattern looks like a snowflake; hierarchies are stored once, with no redundancy.
• Saves storage and prevents update anomalies, but needs more joins, so queries are slower and the model is harder for end users to navigate.

3. Comparison

Basis	Star schema	Snowflake schema
Normalisation	Dimensions denormalised	Dimensions normalised
Structure	Fact + one table per dimension	Fact + several related dimension tables
Joins per query	Few	More
Query performance	Faster	Slower
Storage	Redundant, larger	Less redundancy, smaller
Design / maintenance	Simple	More complex
Hierarchy integrity	Repetition, possible inconsistency	Clean, single source of truth
Popularity	Most common default	Chosen for large / hierarchical dimensions

4. Example — star schema for retail sales

DimDate(dDateKey, day, month, year, quarter)        -----+
DimProduct(prodKey, name, category, brand)          -----+---- FactSales(salesKey, dDateKey, prodKey,
DimStore(storeKey, name, city, state, country)      -----+     storeKey, custKey, qty, amount)
DimCustomer(custKey, name, segment)                 -----+

FactSales carries the foreign keys plus the measures qty and amount; the dimensions supply the attributes used in filters, group-by and drill-down. For the snowflake version, DimStore is normalised into Store → City → State → Country and DimProduct into Product → Category, giving the flaked extension of the star.

Conclusion

Star = speed and simplicity; snowflake = storage efficiency and hierarchy integrity. Most warehouses begin with a star schema and snowflake a dimension only when it is very large or deeply hierarchical.