# 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 | Notes |
|---|---|---|---|---|
| `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. |