Retail Sales Snowflake Schema

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.

סוג ERDתקן Chen 1976 / Everest 1976 (crow's foot)מנוע schematex-erdעודכן 24.9.2026

הבקשה

“A retail sales snowflake schema: a FactSale table linked to Date, Product, and Store dimensions; normalize Product through Subcategory to Category and Store through City to Region.”

ואז נסוAdd a customer dimension to FactSaleAdd a brand table below Product

מה מופיע בתרשים הזה

פענחו את ההחלטות שמאחוריו.

01Fact grain

Store one row for each sale line.

02Product hierarchy

Keep category data outside the product dimension.

03Geography hierarchy

Normalize stores through city and region.

Use this when product and store hierarchies are shared and need separate maintenance.

עיון בכל תבניות ה־ERD ←

שאלות נפוצות

What makes this a snowflake schema?

Its dimensions are normalized into hierarchy tables, such as Product to Subcategory to Category.

What is the fact table grain?

Each FactSale row represents one recorded sale line with its measures.

Why normalize store geography?

City and region can be maintained once and reused by every store.