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_hourly_sales.md 3,733 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

curated.pos_hourly_sales

Purpose

This table stores hourly aggregated sales facts when a source can provide or derive them. It is intended for quick trend analysis over time without needing to recompute hourly windows from transaction-level data.

Because it is an aggregate surface, it is best suited for reporting and anomaly baselines rather than detailed forensic analysis.

Schema contract

The deployed SQL under backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/ is the source of truth for the current column names, types, and nullability.

Column groups

Business properties describe the business entity, event, fact, or reference record represented by the table.
Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, slowly changing dimension controls, and ingestion metadata.

Business properties

Column	Type	Nullable	Business Meaning
`dob`	`DATE`	Yes	Business date for the sales aggregate.
`storeid`	`INTEGER`	Yes	Store identifier associated with the aggregate.
`occasionid`	`INTEGER`	Yes	Occasion or meal-period identifier.
`keyvolume`	`INTEGER`	Yes	Volume metric used by the source aggregate.
`revid`	`INTEGER`	Yes	Revenue bucket or revenue-center identifier.
`catid`	`INTEGER`	Yes	Category identifier included in the aggregate.
`starthour`	`INTEGER`	Yes	Starting hour of the aggregate bucket.
`startmin`	`INTEGER`	Yes	Starting minute of the aggregate bucket.
`amount`	`DOUBLE PRECISION`	Yes	Sales amount for the bucket.
`regionid`	`INTEGER`	Yes	Region identifier for roll-up reporting.
`stophour`	`INTEGER`	Yes	Ending hour of the aggregate bucket.
`stopmin`	`INTEGER`	Yes	Ending minute of the aggregate bucket.
`store_name`	`VARCHAR(255)`	Yes	Readable store name associated with the aggregate bucket.
`occasion_name`	`VARCHAR(255)`	Yes	Readable occasion or meal-period name.
`revenue_center_name`	`VARCHAR(255)`	Yes	Readable revenue center name.
`category_name`	`VARCHAR(255)`	Yes	Readable category name included in the aggregate.
`transaction_count`	`INTEGER`	Yes	Number of transactions contributing to the aggregate bucket.
`guest_count`	`INTEGER`	Yes	Number of guests represented in the aggregate bucket when available.
`quantity_sold`	`DECIMAL(18, 4)`	Yes	Total quantity sold in the aggregate bucket when available.
`currency_code`	`VARCHAR(10)`	Yes	ISO or business-standard currency code used for the aggregate sales amount.
`timezone_name`	`VARCHAR(100)`	Yes	Canonical IANA or business-standard time zone name used for the time bucket.

Technical and ETL properties

Column	Type	Nullable	Technical Meaning	Notes
`hourly_sales_key`	`BIGINT`	Yes	Stable warehouse key for the hourly sales aggregate row.
`source_system`	`VARCHAR(100)`	Yes	Canonical name of the source platform that produced the hourly aggregate.
`tenant_key`	`BIGINT`	Yes	Stable warehouse key for the tenant, brand, or client that owns the aggregate bucket.
`tenant_name`	`VARCHAR(255)`	Yes	Readable tenant, brand, or client name associated with the aggregate bucket.
`_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.