Silver layer: clean, normalize, and reshape for analytics

Silver converts Bronze into conformed tables: unpivot wide daily sales into long facts, extract product/store dimensions, clean prices, and standardize the calendar with event / SNAP helpers.

Notebooks 02–04 finished Schema: walmart_m5_silver 59.2M long sales rows

silver_sales

59.2M

long daily facts

silver_products

3,049

SKUs

silver_stores

CA / TX / WI

silver_calendar

1,969

2011-01-29 → 2016-06-19

Notebooks and outputs

Notebook	Tables	Key result
`02_silver_sales`	`silver_sales`	Wide→long for `d_1`…`d_1941` → 59,181,090 rows; quality checks passed
`03_silver_products`	`silver_products`, `silver_stores`, `silver_sell_prices`	3,049 products · 10 stores · 6,841,121 prices; referential checks passed
`04_silver_calendar`	`silver_calendar`	1,969 dates; event_flag + snap helpers; quality checks passed

Critical transform: wide → long sales

Bronze keeps competition-native wide columns. Silver unpivots them and maps day_id to real dates via calendar.

Bronze (wide)
item_id | store_id | d_1 | d_2 | ... | d_1941

        │  melt / stack
        ▼

Silver (long)
date | day_id | item_id | store_id | quantity

✓0 unmatched calendar days
✓0 null item/store/date keys
✓0 negative quantities
✓0 duplicate item×store×day rows
✓Partitioned by date for prune-friendly reads

Silver table contracts

Table	Grain	Important columns
`silver_sales`	item × store × day	`date, day_id, wm_yr_wk, item_id, dept_id, cat_id, store_id, state_id, quantity`
`silver_products`	item	`item_id, dept_id, cat_id`
`silver_stores`	store	`store_id, state_id`
`silver_sell_prices`	store × item × week	`store_id, item_id, wm_yr_wk, sell_price` (price > 0)
`silver_calendar`	date	calendar attrs + `event_flag`, `snap_*`, `snap_any_flag`