Retail sales snowflake schema
This snowflake schema records retail sales in FactSale and keeps the descriptive dimensions in related tables. Each sale identifies a date, product and store, then records quantity and revenue. Product rolls up through subcategory to category. Store rolls up through city to region. Those normalized chains are the difference between a snowflake schema and a simpler star schema: the fact table remains central, while repeated hierarchy attributes move into their own tables. Use this pattern when reporting needs consistent category and geography rollups across several facts or when those hierarchies change independently. It also gives data stewards a distinct place to maintain the product and location hierarchies used in reports.
Open it in the AI editor with a prompt pre-filled — keep what works, change what doesn't.
Scenario
Retail sales reporting
Key decisions
- Fact grain: Store one row for each sale line.
- Product hierarchy: Keep category data outside the product dimension.
- Geography hierarchy: Normalize stores through city and region.
When to reuse this
Use this when product and store hierarchies are shared and need separate maintenance.
Frequently asked questions
What makes this a snowflake schema?
What is the fact table grain?
Why normalize store geography?
Tweak it with chat, export PNG/SVG, or fork it for your own use case.