Scenario for Snowflake Model
🔍 Comparison Snapshot (TOP)
⭐ Star Schema
FACT → DIM_PRODUCT (flat)
❄️ Snowflake Schema
FACT → Product → SubCategory → Category → Department
🎯 What the Company Wants to View in Reports
Business Context
A large retail enterprise wants enterprise-level analytical reporting, not just simple dashboards.
They want reports that:
Work across millions of products
Follow official category hierarchies
Support frequent hierarchy changes
Match master data definitions
📊 Report Requirement 1
“Sales by Department → Category → SubCategory”
Business Expectation
View sales at Department level
Drill down to Category
Drill further to SubCategory
Restructure categories without reloading all product data
🔹 Grain, Granularity & KPIs (Explained)
Grain (Fact Table Grain)
One row per Product per Store per Day
This defines the lowest level at which data is stored in the fact table.
Granularity (Reporting Levels)
Using the snowflaked hierarchy, the business can analyze data at multiple levels:
Department level – Executive overview
Category level – Business unit performance
SubCategory level – Merchandising analysis
Product level – SKU-level analysis
This is possible without changing the fact table, only by navigating dimension hierarchies.
Key KPIs Required
Total Sales Amount
Total Quantity Sold
Gross Margin
Average Selling Price (ASP)
Discount Amount
Sales Growth %
These KPIs aggregate cleanly across all hierarchy levels.
❌ Why This Is Weak in Star Schema
Flat Product Dimension
DIM_PRODUCT
Product_Key | Product_Name | SubCategory | Category | Department
Problems
Category and Department values are repeated for every product
If a Category moves to a new Department →
❗ millions of product rows must be updatedHigh risk of inconsistent category names
Hard to audit hierarchy changes over time
Result
Maintenance-heavy
Error-prone
Poor governance
✅ Why Snowflake Works
FACT → Product → SubCategory → Category → Department
Benefits
Category and Department stored once
Reclassification requires updating only hierarchy tables
Clean drill-down and roll-up paths
Enterprise-controlled hierarchy
📊 Report Requirement 3
“Impact Analysis When Categories Change”
Business Expectation
When a category is moved or renamed, reports should:
Update instantly
Not require massive data reloads
❌ Star Schema Issue
Category change = product dimension reload
Long ETL windows
High operational risk
✅ Snowflake Solution
Update one hierarchy table
Fact data remains untouched
Faster, safer changes
🧠 Core Reason Star Is Not Enough
Star schema collapses hierarchy into flat dimensions, which breaks scalability, governance, and change management when hierarchies are large and dynamic.
🧾 One-Line Business Justification
“Snowflake schema enables governed, hierarchical reporting that a flat star model cannot support efficiently at enterprise scale.”
If you want, I can next:
Add Date / Geography grain examples
Convert this into Northwind scenario
Make it interview-ready Q&A
Create a diagram with keys
Just tell me 👍
No comments:
Post a Comment