# curated.pos_orders

## Purpose

`curated.pos_orders` is the shared business order header in the warehouse.

It should answer the question: `what order happened, when, for what amount, in what business context, and from which source system?`

## Why this table matters

- gives one row per business order
- provides the main order-level measures: `subtotal`, `tax`, `total`
- keeps order lifecycle context such as `status`, `status_name`, and `order_status_code`
- keeps shared reporting context such as `business_date`, `source_system`, and tenant lineage
- creates a stable contract for future cross-source analyses such as store comparisons, channel mix, order-value anomalies, and customer-order behavior

## Grain

One row per business order in the curated order model.

## Column groups

- Business properties describe the order itself: identifiers used by users, timestamps, amounts, lifecycle, channel, customer, and store context.
- Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, and ingestion metadata.

## Business properties

| Column | Type | Nullable | Business Meaning | Notes |
|---|---|---|---|---|
| `order_id` | `VARCHAR(50)` | No | Business-facing order identifier used for joins and drill-through. | Preferred identifier for downstream business joins and drill-through. |
| `order_type` | `VARCHAR(50)` | Yes | Shared order type such as dine-in, carryout, or delivery. | |
| `payment_method` | `VARCHAR(50)` | Yes | Simplified payment summary at order level. | Intended for normalized payment semantics rather than raw tender detail. |
| `subtotal` | `DECIMAL(10, 2)` | Yes | Order value before tax and later adjustments. | |
| `total` | `DECIMAL(10, 2)` | Yes | Final order total. | |
| `created_at` | `TIMESTAMP` | Yes | Business timestamp of order creation in source semantics. | Not guaranteed to be UTC. |
| `customer_number` | `VARCHAR(50)` | Yes | Customer or loyalty identifier linked to the order. | |
| `id` | `INTEGER` | No | Source-native compact order identifier when available. | Source-specific helper identifier, not the preferred business-facing order key. |
| `table_number` | `VARCHAR(50)` | Yes | Dine-in table identifier. | |
| `tax` | `DECIMAL(10, 2)` | Yes | Tax amount for the order. | |
| `status` | `VARCHAR(50)` | Yes | Order state in readable business form. | Human-readable business status. |
| `terminal_id` | `VARCHAR(50)` | Yes | Terminal identifier tied to order capture. | |
| `points_used` | `INTEGER` | Yes | Loyalty points redeemed on the order. | |
| `additional_payment` | `DECIMAL(10, 2)` | Yes | Additional payment outside the main method. | |
| `pos_terminal_id` | `VARCHAR(50)` | Yes | POS-native terminal identifier when different from presentation-friendly fields. | Useful when the curated model needs the raw POS terminal identity alongside normalized labels. |
| `destination` | `VARCHAR(100)` | Yes | Operational destination or order routing label. | |
| `terminal_name` | `VARCHAR(255)` | Yes | Human-readable terminal label. | |
| `store_id` | `VARCHAR(199)` | Yes | Shared store identifier. | Canonical store identifier for cross-source reporting. |
| `store_name` | `VARCHAR(255)` | Yes | Human-readable store name. | |
| `status_name` | `VARCHAR(100)` | Yes | Readable lifecycle label, often aligned with `status`. | May temporarily match `status`, but kept as a separate semantic field. |
| `service_channel` | `VARCHAR(100)` | Yes | Shared service mode such as dine-in, drive-thru, pickup, or delivery. | Normalized service-mode classification. |
| `fulfillment_channel` | `VARCHAR(100)` | Yes | Shared fulfillment method used to complete the order. | Normalized fulfillment classification. |
| `created_at_utc` | `TIMESTAMP` | Yes | UTC-normalized order creation timestamp. | Preferred timestamp for cross-store and cross-timezone analysis. |
| `business_date` | `DATE` | Yes | Restaurant business date used for reporting and ETL partitioning. | May differ from the calendar date of `created_at_utc`. |
| `currency_code` | `VARCHAR(10)` | Yes | Currency of the monetary values. | Required when curated data spans multiple currency contexts. |
| `order_status_code` | `VARCHAR(100)` | Yes | Stable machine-friendly lifecycle code. | Distinct from readable labels so downstream logic can depend on a stable code set. |

## Technical and ETL properties

| Column | Type | Nullable | Technical Meaning | Notes |
|---|---|---|---|---|
| `order_key` | `BIGINT` | Yes | Warehouse-generated stable surrogate key for the order entity. | Surrogate warehouse key for dimensional or fact-style joins. |
| `source_system` | `VARCHAR(100)` | Yes | Canonical integration name used for lineage across source platforms. | Identifies which operational platform produced the record. |
| `source_order_id` | `VARCHAR(199)` | Yes | Original source identifier kept for reconciliation and drill-through. | Preserves the native source key even when curated identifiers are normalized. |
| `tenant_key` | `BIGINT` | Yes | Warehouse surrogate key for tenant or brand scope. | |
| `tenant_name` | `VARCHAR(255)` | Yes | Tenant or brand identifier injected by the ETL context. | Human-readable tenant or brand lineage value. |
| `_etl_batch_id` | `VARCHAR(256)` | Yes | ETL batch lineage identifier. | |
| `_etl_source_type` | `VARCHAR(100)` | Yes | Source adapter type used by ETL. | |
| `_etl_ingested_at` | `TIMESTAMP` | No | Warehouse ingestion timestamp for the curated row. | Required ingestion audit timestamp. |