Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Warehouse Model (93)

Analytical data warehouse schemas, curated tables, and Redshift/Athena data models.

warehouse-model/docs/features/ETL/warehouse-data/curated-model/pos_orders.md 5,536 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

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.