stg_postgres__shipments

staging view 1 PII columns

Shipments with day-truncated milestones.

Definition

Per-order shipment rows with lifecycle timestamps. `shipped_at`, `estimated_delivery_at`, and `delivered_at` are all Nullable — `label_created` shipments haven't shipped; in-transit shipments haven't been delivered. SLA derivations belong in `int_shipments__sla` so this layer stays close to the source.

15 of 15

Column

Description

Tests · PII

shipment_id

Primary key — UInt64.

2 tests

order_id

FK to `stg_postgres__orders.order_id`. NOT NULL.

customer +2 PII2 tests

shipping_address_id

FK to `stg_postgres__addresses.address_id`. Nullable.

carrier

Carrier — `ups`, `fedex`, `dhl`, `usps`, `royal_mail`.

1 test

tracking_number

Carrier-issued tracking id. Nullable; behavioural PII.

PII

service_level

`standard`, `express`, or `overnight`. Nullable for carriers without a service tier.

status

Lifecycle — `label_created`, `in_transit`, `delivered`, `returned`, `lost`.

1 test

shipped_at

When the carrier picked up the parcel. Nullable for `label_created`.

shipped_day

`toDate(shipped_at)`. Convenience for daily marts.

estimated_delivery_at

Carrier ETA at label time. Nullable.

delivered_at

When the parcel was delivered. Nullable while in-flight.

delivered_day

`toDate(delivered_at)`.

source_created_at

Row insert timestamp from the source DB. DateTime64(6).

source_updated_at

Row update timestamp from the source DB. DateTime64(6).

airbyte_extracted_at

When Airbyte extracted this row from postgres-source.