Kimball-style Snowflake warehouse unifying SAP ERP, Oracle WMS, MQTT truck IoT, and carrier APIs into conformed facts, SCD-2 dimensions, and an OTIF / fill-rate KPI engine.
Supply-chain operations need a single analytical surface across ERP, WMS, in-transit telemetry, and third-party signals to answer questions like on-time-in-full, fill rate and lane-level performance. The Kimball build for that is well-understood but rarely shown end-to-end. This is the full pattern — conformed dimensions across order and shipment grain, SCD-2 history for slow-moving attributes, IoT windowed aggregation, and a KPI engine on top of the marts.
SAP HANA (ERP orders), Oracle WMS (picking events), MQTT truck telemetry, weather and carrier APIs all ingest via a DAG factory that reads one sources.yaml. Adding a source is a config change, not new DAG code.
Supplier, warehouse, and product dimensions are SCD-2 (effective_from / effective_to) via MERGE. Every fact grain (order, shipment, telemetry, weather) shares the same dimension keys.
Truck telemetry rolls up at 1-minute, 5-minute and 1-hour grain. Z-score anomaly detection at 2.5σ flags suspicious sensor drift; cold-chain breach detection alerts on pharma shipments outside 2–8°C.
Four KPIs computed declaratively from the fact tables — OTIF, fill rate, average lead time, backorder rate — with lane / carrier / SKU drilldowns. Output is consumed by BI without recomputation.
New source = one YAML entry + scheduler restart. No new DAG code. Easier to onboard non-engineers; easier to audit the source surface.
One config bug crashes all DAGs at parse time. Mitigated with a CI lint that loads the YAML through the factory before merge.
Supplier change-of-address, warehouse capacity revisions, product reclassification — all SCD-2. High-volume fact rows are immutable inserts. Right balance of history vs. write cost.
KPI definitions are version-controlled and operate against pre-aggregated marts. Recompute is cheap; reproducing a historical KPI is one query, not a re-run.
Real WMS deployments hit hand-written SQL views maintained by the operations team. The interface in this repo documents the contract; production swaps the implementation behind it.