Snowflaking a star schema: Normalise Only the Right Things

This work is to be done in groups. The objective is to consolidate students’ abilities for designing snowflake schemata.

Consider the following star schema, where:

• dim_date with day/month/quarter/year attributes.
• dim_store with store_id, store_name, manager_name, region.
• dim_product with product_sku, name, brand, type, color, size, manufacturer.
• dim_customer with person attributes and a flattened address (address_line1, postal_code, city, region, country).
• fact_sales with FKs to date/product/customer/store + measures.

Using the provided star schema (Date, Store, Product, Customer, Fact Sales) and the rules below, propose a snowflake schema that:

• Normalises only dimensions (facts stay unchanged).
• Snowflakes only true hierarchies or shared lookups.
• Avoids over-normalising flat descriptors.

The Rules to Apply

1. Do not touch the fact table (grain and columns unchanged).
2. Snowflake only:
   1. Natural, stable hierarchies (e.g., City → Region/State → Country).
   1. Shared/conformed lookups reused by multiple dimensions (e.g., Region, Country, Brand, Manufacturer).
3. Keep flat descriptors inside the base dimension (e.g., product type/color/size; store manager_name).
4. Each snowflaked table has a surrogate key; child levels reference parent levels.
5. Prefer conformed subdimensions if multiple base dims share them (e.g., Region used by Customer and Store).

To Do 

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.

Task A — Identify what to snowflake (short list)

• Mark which attributes form hierarchies and which are shared across dimensions.
• Mark which attributes stay inline (not snowflaked) and give one-line reasons.

Hint: Customer geography is a real hierarchy; Store likely shares Region (and maybe Country). Product’s Brand and Manufacturer are shared lookups; Product’s type/color/size are flat descriptors.

Task B — Propose the snowflake schema (diagram + spec)

• Diagram (hand-drawn or tool):
  – Show the base dimensions (DimCustomer, DimStore, DimProduct, DimDate).
  – Show snowflaked subdimensions (e.g., DimCountry → DimRegion → DimCity → DimAddress; DimBrand; DimManufacturer).
  – Draw keys/relationships (surrogate keys; child → parent FKs).
• Mini spec sheet (bullet points per entity):
  – Name, business meaning, primary key type (surrogate), and key relationships.
  – 3–6 core attributes you would expect in each (names only).

Tip: Keep DimDate intact; do not split month/quarter/year out.

Task C — Conformed dimensions & reuse

• Explain in 4–6 lines how your design reuses Region/Country (and optionally City) across Customer and Store.
• State the benefit (deduplication, consistent reporting, governance).

Task D — What you deliberately did not normalise

• List 3–5 attributes you chose to keep inline (e.g., product color/size, store manager_name) with a one-line justification each (not a hierarchy, not reused, would add joins without value).

Task E — “Walk the query”

In 5–8 lines, describe in words how a report like “Yearly revenue by Country → Region → City and by Brand” would traverse your snowflake (which tables it touches, at a high level—no SQL).

Deliverables (one PDF)

• 1–2 page diagram (clear keys and relationships).
• 1 page mini spec for entities (Task B).
• Short answers for Tasks C–E (max ~1 page total).