Consider the following star schema, where:<\/p>\n\n\n\n
- \n
dim_date<\/code>\u00a0with day\/month\/quarter\/year attributes.<\/li>\n\n\n\ndim_store<\/code>\u00a0with\u00a0store_id<\/code>,\u00a0store_name<\/code>,\u00a0manager_name<\/code>,\u00a0region<\/code>.<\/li>\n\n\n\ndim_product<\/code>\u00a0with\u00a0product_sku<\/code>,\u00a0name<\/code>,\u00a0brand<\/code>,\u00a0type<\/code>,\u00a0color<\/code>,\u00a0size<\/code>,\u00a0manufacturer<\/code>.<\/li>\n\n\n\ndim_customer<\/code>\u00a0with person attributes and a\u00a0flattened address<\/strong>\u00a0(address_line1<\/code>,\u00a0postal_code<\/code>,\u00a0city<\/code>,\u00a0region<\/code>,\u00a0country<\/code>).<\/li>\n\n\n\nfact_sales<\/code>\u00a0with FKs to date\/product\/customer\/store + measures.<\/li>\n<\/ul>\n\n\n\n![\"\"](\"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-content\/uploads\/sites\/64\/2025\/10\/image.png\")
<\/figure>\n\n\n\nUsing the provided star schema (Date, Store, Product, Customer, Fact Sales) and the rules below,\u00a0propose a snowflake schema<\/strong> that:<\/p>\n\n\n\n
- \n
- Normalises only dimensions<\/strong>\u00a0(facts stay unchanged).<\/li>\n\n\n\n
- Snowflakes only true hierarchies or shared lookups<\/strong>.<\/li>\n\n\n\n
- Avoids over-normalising<\/strong>\u00a0flat descriptors.<\/li>\n<\/ul>\n\n\n\n
The Rules to Apply<\/h2>\n\n\n\n
- \n
- Do not touch the fact table<\/strong>\u00a0(grain and columns unchanged).<\/li>\n\n\n\n
- Snowflake only<\/strong>:
- Natural, stable\u00a0hierarchies<\/strong>\u00a0(e.g., City \u2192 Region\/State \u2192 Country).<\/li><\/ol>\n
- \n
- Shared\/conformed lookups<\/strong>\u00a0reused by multiple dimensions (e.g., Region, Country, Brand, Manufacturer).<\/li>\n<\/ol>\n<\/li>\n\n\n\n
- Keep flat descriptors<\/strong>\u00a0inside the base dimension (e.g., product type\/color\/size; store manager_name).<\/li>\n\n\n\n
- Each snowflaked table has a\u00a0surrogate key<\/strong>; child levels reference parent levels.<\/li>\n\n\n\n
- Prefer\u00a0conformed<\/strong>\u00a0subdimensions if multiple base dims share them (e.g., Region used by Customer and Store).<\/li>\n<\/ol>\n\n\n\n
To Do <\/h2>\n\n\n\n
You can complete and\/or re-use the initial snowflake schema that you have handed in. Mark which rules you applied in you initial solution and complete or revisit decisions that do not follow the rules.<\/p>\n\n\n\n
Task A \u2014 Identify what to snowflake (short list)<\/h3>\n\n\n\n
- \n
- Mark\u00a0which attributes<\/strong>\u00a0form\u00a0hierarchies<\/strong>\u00a0and\u00a0which are shared<\/strong>\u00a0across dimensions.<\/li>\n\n\n\n
- Mark\u00a0which attributes stay inline<\/strong>\u00a0(not snowflaked) and give one-line reasons.<\/li>\n<\/ul>\n\n\n\n
Hint: Customer geography is a real hierarchy; Store likely shares Region (and maybe Country). Product\u2019s Brand and Manufacturer are shared lookups; Product\u2019s type\/color\/size are flat descriptors.<\/p>\n\n\n\n
Task B \u2014 Propose the snowflake schema (diagram + spec)<\/h3>\n\n\n\n
- \n
- Diagram<\/strong>\u00a0(hand-drawn or tool):
– Show the\u00a0base dimensions<\/strong>\u00a0(DimCustomer<\/code>,\u00a0DimStore<\/code>,\u00a0DimProduct<\/code>,\u00a0DimDate<\/code>).
– Show\u00a0snowflaked subdimensions<\/strong>\u00a0(e.g.,\u00a0DimCountry \u2192 DimRegion \u2192 DimCity \u2192 DimAddress<\/code>;\u00a0DimBrand<\/code>;\u00a0DimManufacturer<\/code>).
– Draw\u00a0keys\/relationships<\/strong>\u00a0(surrogate keys; child \u2192 parent FKs).<\/li>\n\n\n\n- Mini spec sheet<\/strong>\u00a0(bullet points per entity):
– Name<\/strong>,\u00a0business meaning<\/strong>,\u00a0primary key type<\/strong>\u00a0(surrogate), and\u00a0key relationships<\/strong>.
– 3\u20136\u00a0core attributes<\/strong>\u00a0you would expect in each (names only).<\/li>\n<\/ul>\n\n\n\nTip: Keep
DimDate<\/code> intact; do not split month\/quarter\/year out.<\/p>\n\n\n\nTask C \u2014 Conformed dimensions & reuse<\/h3>\n\n\n\n
- \n
- Explain in 4\u20136 lines how your design\u00a0reuses<\/strong>\u00a0Region\/Country (and optionally City)\u00a0across Customer and Store<\/strong>.<\/li>\n\n\n\n
- State the\u00a0benefit<\/strong>\u00a0(deduplication, consistent reporting, governance).<\/li>\n<\/ul>\n\n\n\n
Task D \u2014 What you deliberately did not<\/strong> normalise<\/h3>\n\n\n\n
- \n
- List\u00a03\u20135 attributes<\/strong>\u00a0you chose to\u00a0keep inline<\/strong>\u00a0(e.g., product color\/size, store manager_name) with a one-line justification each (not a hierarchy, not reused, would add joins without value).<\/li>\n<\/ul>\n\n\n\n
Task E \u2014 \u201cWalk the query\u201d<\/h3>\n\n\n\n
In 5\u20138 lines, describe in words<\/strong> how a report like \u201cYearly revenue by Country \u2192 Region \u2192 City and by Brand\u201d<\/em> would traverse your snowflake (which tables it touches, at a high level\u2014no SQL).<\/p>\n\n\n\n
Deliverables (one PDF)<\/h2>\n\n\n\n
- \n
- 1\u20132 page\u00a0diagram<\/strong>\u00a0(clear keys and relationships).<\/li>\n\n\n\n
- 1 page\u00a0mini spec<\/strong>\u00a0for entities (Task B).<\/li>\n\n\n\n
- Short answers for Tasks C\u2013E (max ~1 page total).<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
Snowflaking a star schema: Normalise Only the Right Things This work is to be done in groups. The objective is to consolidate students\u2019 abilities for designing snowflake schemata. Consider the following star schema, where: Using the provided star schema (Date, … Continue reading
- 1 page\u00a0mini spec<\/strong>\u00a0for entities (Task B).<\/li>\n\n\n\n
- 1\u20132 page\u00a0diagram<\/strong>\u00a0(clear keys and relationships).<\/li>\n\n\n\n
- State the\u00a0benefit<\/strong>\u00a0(deduplication, consistent reporting, governance).<\/li>\n<\/ul>\n\n\n\n
- Mini spec sheet<\/strong>\u00a0(bullet points per entity):
- Mark\u00a0which attributes stay inline<\/strong>\u00a0(not snowflaked) and give one-line reasons.<\/li>\n<\/ul>\n\n\n\n
- Keep flat descriptors<\/strong>\u00a0inside the base dimension (e.g., product type\/color\/size; store manager_name).<\/li>\n\n\n\n
- Shared\/conformed lookups<\/strong>\u00a0reused by multiple dimensions (e.g., Region, Country, Brand, Manufacturer).<\/li>\n<\/ol>\n<\/li>\n\n\n\n
- Snowflake only<\/strong>:
- Snowflakes only true hierarchies or shared lookups<\/strong>.<\/li>\n\n\n\n