maiven gateway · online··
manifest · 1d ago
Sign in Back to catalog
Definition
Ephemeral join — attaches product temp/hazard class + value/dims + the owning client_id, the storing site's region/type, and the owning client's segment to each inventory lot, plus an outbound-demand rollup (shipped units per sku × site over the trailing 90 days ending at logistics_as_of) so velocity can be derived. Derives days_of_cover (qty_on_hand / avg daily units out), turnover_ratio (units_out_window / avg_qty_on_hand), near_expiry_flag (lot_expiry within 30 days of the fixed logistics_as_of anchor — single-sourced, never the wall clock), and inv_month (toStartOfMonth(received_date), INC-12). Inlined into mart_inventory_health.
17 of 17
Column
Description
Tests · PII
lot_id
From stg_gcs__whse_inventory.lot_id. Primary key.
2 tests
client_id
Owning client (denormalized from the product). The multi-tenant isolation key.
sku
Product FK. From staging.
site_id
Storing site FK. From staging.
client_segment
Owning-client segment (via the product's owner); coalesced to 'unknown'.
site_region
coalesce(sites.region, 'unknown') (INC-08).
temp_class
Product temperature class. From stg_gcs__whse_products (LEFT JOIN).
hazard_class
Product hazard class (ADR class or none). From stg_gcs__whse_products (LEFT JOIN).
zone
Storage zone. From staging.
bin
Bin location (alias of raw bin_location). From staging.
qty_on_hand
Units on hand. From staging.
lot_expiry
Lot expiry date. Nullable for non-perishable. From staging.
status
Alias of raw inventory_status (available / allocated / quarantine / damaged / expired).
inv_month
Derived toStartOfMonth(received_date) (INC-12).
days_of_cover
Derived round(qty_on_hand / nullif(avg_daily_units_out,0), 1). Nullable when no velocity.
turnover_ratio
Derived round(units_out_window / nullif(avg_qty_on_hand,0), 3).
near_expiry_flag
Derived toUInt8(lot_expiry ≤ addDays(logistics_as_of, 30)) — single-sourced from the fixed anchor.