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_order_items.md 4,438 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

curated.pos_order_items

Purpose

curated.pos_order_items is the shared business order-line fact in the warehouse.

It should answer the question: what exactly was in the basket, in what quantity, at what price, with what item status, and under which order?

Why this table matters

gives one row per business order item
links item behavior back to the shared order header through order_id
supports basket analytics, mix analysis, and future recommendation logic
exposes readable business fields such as item_name, unit_price, and item_status
keeps lineage needed for cross-source reconciliation through source_system and source_order_item_id

Grain

One row per business order item in the curated order model.

Column groups

Business properties describe the order line itself: product identity, quantity, price, item state, modifier context, and business time.
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 owning the line.	Foreign-business key linking the line back to the shared order header.
`item_id`	`INTEGER`	No	Source or canonical item identifier.
`item_name`	`VARCHAR(255)`	No	Human-readable item name captured on the line.	Snapshot of the item label used on the order line.
`quantity`	`INTEGER`	Yes	Sold quantity for the line item.
`unit_price`	`DECIMAL(10, 2)`	Yes	Selling price for one unit on the line.	Unit-level price before line-level aggregation.
`id`	`INTEGER`	No	Source-native order line identifier.	Native line identifier for reconciliation with source records.
`size_name`	`VARCHAR(100)`	Yes	Size or variant label for the line item.
`modifiers`	`VARCHAR(65535)`	Yes	Raw or serialized modifier representation for the line.	Preserves source-level modifier detail when a normalized representation is not available.
`kds_delay`	`INTEGER`	Yes	Kitchen delay or prep hint attached to the line.
`category_id`	`VARCHAR(199)`	Yes	Shared item category identifier.	Canonical category identifier for cross-source product grouping.
`category_name`	`VARCHAR(255)`	Yes	Readable category label.
`item_status`	`VARCHAR(100)`	Yes	Shared business status of the line such as `active`, `voided`, `deleted`, or `cleared`.	Normalized line lifecycle status.
`modifier_summary`	`VARCHAR(65535)`	Yes	Readable normalized summary of applied modifiers.	Intended for analytics-friendly modifier reporting.
`created_at_utc`	`TIMESTAMP`	Yes	UTC timestamp representing line creation time or best available order-line time proxy.	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.

Technical and ETL properties

Column	Type	Nullable	Technical Meaning	Notes
`order_item_key`	`BIGINT`	Yes	Warehouse-generated stable surrogate key for the order line 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_item_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.