stg_postgres__orders

staging view

Orders, with a derived day column.

Definition

Orders typed and joined-ready. Adds `order_day = toDate(order_at)` for use as a MergeTree partition / sort key downstream, and preserves the lifecycle `status` that defines "revenue-eligible" (everything except `cancelled` and `refunded`).

10 of 10

Column

Description

Tests · PII

order_id

Primary key — UInt64.

customer +3 PII7 tests

customer_id

FK to `stg_postgres__customers.customer_id`. NOT NULL.

customer +18 PII2 tests

order_at

Order placement timestamp (renamed from `order_date`). DateTime64(6).

order_day

Calendar date the order was placed — `toDate(order_at)`. Used as MergeTree partition / sort key downstream.

status

Order lifecycle state — `pending`, `paid`, `shipped`, `delivered`, `cancelled`, `refunded`. Revenue rollups exclude `cancelled` and `refunded`.

1 test

currency

ISO-4217 currency code of `total_amount`. Defaults to `USD` on the source.

total_amount

Order grand total in `currency`. Decimal(12,2).

2 tests

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.