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/brinkPos-curated-model-mapps/pos_hourly_sales.md 5,781 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

Brink mapping for `curated.pos_hourly_sales`

Main staging sources

staging.brink_sales2_orders
staging.brink_sales2_order_entries
staging.brink_settings2_items
staging.brink_settings2_item_groups

Mapping groups

Business property mappings describe how Brink hourly sales content is mapped or could be mapped into curated business fields.
Technical and ETL property mappings describe lineage, source reconciliation, tenant scoping, and ingestion metadata.

Business property mappings

Curated Column	Brink Staging Table	Brink Property Path	Current mapping	Status	Notes
`dob`	`staging.brink_sales2_orders`	`business_date`	`NULL`	`Not mapped`	Direct business date for aggregation.
`storeid`	`N/A`	`N/A`	`NULL`	`Not mapped`	The documented Brink staging model does not expose a store ID on these payloads.
`occasionid`	`staging.brink_sales2_orders`	`destination_id`	`NULL`	`Not mapped`	Best available service-occasion style field.
`keyvolume`	`staging.brink_sales2_orders`	`(order_count, guest_count)`	`NULL`	`Not mapped`	Derived aggregate, typically `count(*)` or `sum(guest_count)` depending on final grain rules.
`revid`	`staging.brink_sales2_order_entries`	`revenue_center_id`	`NULL`	`Not mapped`	Derived aggregate grouped by order-item revenue center.
`catid`	`staging.brink_settings2_item_groups`	`item_group_id`	`NULL`	`Not mapped`	Derived aggregate grouped by Brink item-group IDs.
`starthour`	`staging.brink_sales2_orders`	`opened_time`	`NULL`	`Not mapped`	Derive hour bucket from the UTC timestamp.
`startmin`	`staging.brink_sales2_orders`	`opened_time`	`NULL`	`Not mapped`	Derive minute bucket from the UTC timestamp.
`amount`	`staging.brink_sales2_order_entries`	`item_net_sales`	`NULL`	`Not mapped`	Best line-level monetary source for category and revenue-center aggregation.
`regionid`	`N/A`	`N/A`	`NULL`	`Not mapped`	No region field is documented in Brink staging.
`stophour`	`N/A`	`N/A`	`NULL`	`Not mapped`	Bucket end must be derived by warehouse logic, not sourced directly.
`stopmin`	`N/A`	`N/A`	`NULL`	`Not mapped`	Bucket end must be derived by warehouse logic, not sourced directly.
`store_name`	`N/A`	`N/A`	`NULL`	`Not mapped`	No store name is documented in the available Brink staging model.
`occasion_name`	`staging.brink_settings2_get_destinations`	`name`	`NULL`	`Not mapped`	Resolve destination/occasion name by joining `staging.brink_sales2_orders` on `destination_id` to `staging.brink_settings2_get_destinations` on `destination_id`.
`revenue_center_name`	`staging.brink_settings_get_revenue_centers`	`name`	`NULL`	`Not mapped`	Resolve revenue center name by joining `staging.brink_sales2_order_entries` (via `revid` / `revenue_center_id`) to `staging.brink_settings_get_revenue_centers` on `revenue_center_id`.
`category_name`	`staging.brink_settings_get_item_groups`	`name`	`NULL`	`Not mapped`	Resolve category name by joining `catid` (`item_group_id`) to `staging.brink_settings_get_item_groups` on `item_group_id`.
`transaction_count`	`staging.brink_sales2_orders`	`order_count`	`NULL`	`Not mapped`	Derived aggregate count of orders or transactions in the bucket.
`guest_count`	`staging.brink_sales2_orders`	`guest_count`	`NULL`	`Not mapped`	Derived aggregate sum by bucket.
`quantity_sold`	`N/A`	`N/A`	`NULL`	`Not mapped`	Brink order-item staging does not document a quantity field for line items.
`currency_code`	`N/A`	`N/A`	`NULL`	`Not mapped`	Currency is not documented in the available Brink staging model.
`timezone_name`	`N/A`	`N/A`	`NULL`	`Not mapped`	Timezone metadata is not documented in the available Brink staging model.

Technical and ETL property mappings

Curated Column	Brink Staging Table	Brink Property Path	Current mapping	Status	Notes
`hourly_sales_key`	`N/A`	`N/A`	`NULL`	`Not mapped`	Warehouse-generated key.
`source_system`	`N/A`	`N/A`	`constant 'brink_pos'`	`Derived constant`	Warehouse constant, not a Brink API field.
`tenant_key`	`N/A`	`N/A`	`NULL`	`Not mapped`	Warehouse-generated tenant key.
`tenant_name`	`N/A`	`N/A`	`parameter @tenant_id`	`ETL context`	Tenant metadata is outside the documented Brink staging payloads.
`_etl_batch_id`	staging.brink_sales2_orders	_etl_batch_id	`_etl_batch_id`	`Mapped`	ETL metadata propagated from staging.
`_etl_source_type`	staging.brink_sales2_orders	_etl_source_type	`_etl_source_type`	`Mapped`	ETL metadata propagated from staging.
`_etl_ingested_at`	staging.brink_sales2_orders	_etl_ingested_at_utc	`_etl_ingested_at_utc AS _etl_ingested_at`	`Mapped`	ETL metadata propagated from staging.

Portal and sample validation

Curated field	Additional validated source	How to use it
`occasionid`	`Sales2.GetOrders -> Orders[].Entries[].OrderItem.DayPartId`	The order response and WSDL confirm `DayPartId`, which can be a better meal-period input than destination for some hourly aggregates.
`occasion_name`	`Settings.GetDayParts -> DayPart.Name`	Brink portal WSDL confirms a day-part lookup endpoint for resolving `DayPartId`.
`category_name`	`Settings.GetItemGroups -> ItemGroup.Name`	Use this lookup to name hourly category aggregates derived from item groups.
`revenue_center_name`	`Settings.GetRevenueCenters -> RevenueCenter.Name`	Use this lookup to name hourly revenue-center aggregates derived from `RevenueCenterId`.