Model a daily inventory snapshot

MediumNot attempted yetAsked at Databricks

Warehouse stock is written to the lakehouse once a day per SKU. The question the business asks is always "what was on hand on this date" — and answering it by replaying every movement since the beginning of time is what a snapshot fact exists to avoid.

What the grader expects

dim_sku sku_key INTEGER sku_code VARCHAR fact_inventory_daily sku_key INTEGER as_of DATE on_hand INTEGER

Constraints

One row per SKU per day — the grain is the point of the table
on_hand is the level on that day, not the movement since the day before
The same SKU appears on many days and the same day carries many SKUs
The trap is a movements fact: it can answer "what changed" and cannot answer "what was on hand" without summing history every time

Example 1stock on the latest day

Input · dim_sku

sku_key	sku_code
1	AA-1

Input · fact_inventory_daily

as_of	on_hand	sku_key
2026-01-01	10	1
2026-01-02	7	1

Output

sku_code	on_hand
AA-1	7

Example 2many SKUs on the same day

Input · dim_sku

sku_key	sku_code
1	AA-1
2	BB-2

Input · fact_inventory_daily

as_of	on_hand	sku_key
2026-02-01	4	1
2026-02-01	6	2

Output

total_on_hand
10

Topics

data modelingsnapshot fact