maiven gateway · online··
manifest · 1d ago
Sign in Back to catalog
Definition
Cleaned mirror of `raw_gcs.whse_orders`. Coerces the CSV's `Nullable(String)` columns into typed equivalents, derives `ship_month` via `toStartOfMonth(ship_date)`, and enforces FKs to `stg_gcs__whse_clients`, `stg_gcs__whse_sites`, and `stg_gcs__whse_consignees`. `client_id` is the row-policy key propagated to mart_order_outcomes. `dest_region` is the ship-to region, distinct from the site-derived `site_region` dimension.
23 of 23
Column
Description
Tests · PII
client_id
Foreign key to stg_gcs__whse_clients — the owning client and row-policy key.
client_contact +1 PII2 tests
site_id
Foreign key to stg_gcs__whse_sites — the fulfilling site.
2 tests
consignee_id
Foreign key to stg_gcs__whse_consignees — the delivery recipient. Points at consumer PII.
consignee +1 PII2 tests
order_ref
Human order number. Free-text.
order_date
Date the order was placed. Coerced from String → Date.
1 test
promised_date
SLA promise date (order_date + 1..7d). Coerced from String → Date.
1 test
ship_date
Ship date. Nullable — null while unshipped. Coerced from String → Nullable(Date).
delivered_date
Delivery date. Nullable — null until delivered. Coerced from String → Nullable(Date).
ship_month
First day of the month of `ship_date` — `toStartOfMonth(ship_date)`. Nullable. Grain key for mart_fulfillment_daily / metric_fulfillment.
carrier
Carrier.
2 tests
dest_country
ISO-3166 alpha-2 destination country.
1 test
dest_region
Ship-to region. Distinct raw column from the site-derived site_region dimension.
2 tests
order_status
Order status.
2 tests
order_value_eur
Order value in EUR. Coerced from String → Decimal(14,2). Raw name aliased to `order_value` downstream.
1 test
line_count
Denormalized count of child order lines. Coerced from String → UInt16.
units_total
Total units across all lines. Coerced from String → Int32.
is_on_time
1 if delivered_date <= promised_date, else 0. Coerced from the CSV's "true"/"false" strings.
1 test
is_in_full
1 if all lines shipped complete, else 0. Coerced from the CSV's "true"/"false" strings.
1 test
is_otif
1 if is_on_time AND is_in_full, else 0. Coerced from the CSV's "true"/"false" strings.
1 test
source_file_url
Airbyte-supplied `_ab_source_file_url` — the gs:// URI the row came from.
source_file_modified_at
Airbyte-supplied `_ab_source_file_last_modified` — object mtime of the source file.
airbyte_extracted_at
When Airbyte extracted this row from GCS.