maiven gateway · online··
manifest · 1d ago
Sign in Back to catalog
Definition
Ephemeral join — attaches client segment/tier + home region, the fulfilling site's region/type, the coarse ship-to region (never consumer PII), and a line-level rollup (total_lines, accurate_lines, units, avg_pick_time_sec) to each outbound order. Derives cycle_time_hrs (order→ship), line_accuracy_pct, ship_month, and is_on_time (ship_date ≤ promised_date). order_value aliases raw order_value_eur (INC-11); site_region coalesces raw sites.region (INC-08). Dims LEFT-joined and coalesced to 'unknown' so a bad FK surfaces NULL (staging relationships test flags it) rather than crashing. Inlined into mart_fulfillment_daily + mart_order_outcomes.
27 of 27
Column
Description
Tests · PII
client_id
From stg_gcs__whse_orders.client_id. The multi-tenant isolation key, carried unchanged onto mart_order_outcomes.
site_id
Fulfilling site FK. From stg_gcs__whse_orders.site_id.
consignee_id
Pseudonymous ship-to FK. Surrogate — NOT PII; the consignee's name/address/email are never pulled forward.
carrier
Carrier code. From staging.
temp_class
The order's primary temperature class. From staging.
client_segment
Owning-client segment (raw client_segment, INC-07); coalesced to 'unknown'.
client_tier
Owning-client tier (strategic / key / standard). From stg_gcs__whse_clients (LEFT JOIN).
site_region
coalesce(sites.region, 'unknown') (INC-08) — the site's region dimension.
dest_region
coalesce(orders.dest_region, 'unknown') — the ship-to region (distinct from site_region).
consignee_country
Coarse ship-to country (not PII). From stg_gcs__whse_consignees (LEFT JOIN).
order_date
Order placed. From staging.
promised_date
SLA promise date. From staging.
ship_date
Shipped date. Nullable while open. From staging.
delivered_date
Delivered date. Nullable until delivered. From staging.
ship_month
Derived toStartOfMonth(ship_date). Grain key for mart_fulfillment_daily.
order_status
Order status. From staging.
order_value
Alias of raw order_value_eur (INC-11), Decimal(14,2).
is_otif
On-time-in-full flag (1/0). From staging.
is_on_time
Derived (ship_date ≤ promised_date). Nullable while unshipped.
is_in_full
Shipped-complete flag (1/0). From staging.
cycle_time_hrs
Derived dateDiff('hour', order_date, ship_date). Nullable while unshipped.
total_lines
count() of the order's lines (rollup).
accurate_lines
countIf(is_accurate = 1) across the order's lines (rollup).
units
sum(units_picked) across the order's lines (rollup).
avg_pick_time_sec
avg(pick_time_sec) across the order's lines (rollup).
line_accuracy_pct
Derived round(accurate_lines / nullif(total_lines,0) * 100, 2).