Intermediate LevelSQL & MySQL
What is the difference between Star Schema and Snowflake Schema in Data Warehousing?
❓ Interview Prompt: Compare Star Schema (denormalized dimension tables, fast analytical queries) vs Snowflake Schema (normalized dimension hierarchies, lower storage).
Evaluating OLAP Data Warehouse dimensional modeling schemas:
•
Star Schema: Central Fact table surrounded by denormalized Dimension tables. Highly performant for analytical queries because dimensions require fewer joins.
•
Snowflake Schema: Central Fact table surrounded by normalized Dimension tables (e.g. 'Product' dimension joins 'Category' dimension joins 'Department' dimension). Conserves storage space but requires complex multi-table joins.
Code Implementation Example
sql
-- Star Schema: Fact Sales table joined to denormalized Dimension Customer table
SELECT d.region, SUM(f.sales_amount) AS total_revenue
FROM fact_sales f
JOIN dim_customer d ON f.customer_key = d.customer_key
GROUP BY d.region;💡Querying an OLAP Data Warehouse Star Schema.
🎯 Interview Key Takeaway
Use Star Schema in Data Warehouses to minimize join overhead and optimize analytical reporting queries.
#SQL#Star Schema#Snowflake Schema#Data Warehouse#Intermediate
Back to all SQL & MySQL Questions→