Repository Docs & Knowledge Base

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

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

warehouse-model/docs/features/ETL/warehouse-data/curated-model/labor_shifts.md 3,953 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

curated.labor_shifts

curated.labor_shifts is the shared labor shift fact table in the warehouse.

It should answer the question: what labor shift happened, who worked it, when, for how long, and what was the compensation context?

gives one row per labor shift
provides the main labor measures: minutes_worked, regular_minutes_worked, overtime_minutes_worked, extended_minutes_worked
keeps shift timing context such as start_time, end_time, and business_date
keeps break aggregation context with break_count and total_break_minutes
provides compensation context with pay_rate and declared_tips
creates a stable contract for future cross-source labor cost and workforce analyses

One row per labor shift.

Business properties describe the shift: identity, timing, worked minutes breakdown, compensation, and break aggregation.
Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, and ingestion metadata.

Column	Type	Nullable	Business Meaning	Notes
`shift_id`	`VARCHAR(36)`	No	Source-native shift identifier.	Preferred identifier for downstream joins.
`employee_id`	`BIGINT`	Yes	Employee who worked the shift.	Joins to `curated.employees`.
`job_id`	`BIGINT`	Yes	Job (workforce role) for the shift.	Joins to `curated.pos_jobs`.
`business_date`	`DATE`	Yes	Restaurant business date for reporting and ETL partitioning.
`start_time`	`TIMESTAMPTZ`	Yes	Shift start time with timezone.
`end_time`	`TIMESTAMPTZ`	Yes	Shift end time with timezone.
`minutes_worked`	`INTEGER`	Yes	Total minutes worked during the shift.
`regular_minutes_worked`	`INTEGER`	Yes	Regular (non-overtime) minutes worked.
`overtime_minutes_worked`	`INTEGER`	Yes	Overtime minutes worked.
`extended_minutes_worked`	`INTEGER`	Yes	Extended minutes worked beyond standard overtime.
`pay_rate`	`DECIMAL(18, 4)`	Yes	Hourly pay rate for the shift.
`declared_tips`	`DECIMAL(18, 4)`	Yes	Tips declared by the employee for the shift.
`shift_number`	`SMALLINT`	Yes	Sequential shift number within the source system.	Maps from `brink_labor_shifts.number`.
`break_count`	`INTEGER`	Yes	Number of breaks taken during the shift.	Aggregated from `brink_labor_breaks` via LEFT JOIN + GROUP BY.
`total_break_minutes`	`INTEGER`	Yes	Total minutes of break time during the shift.	Aggregated from `brink_labor_breaks` via LEFT JOIN + GROUP BY.
`currency_code`	`VARCHAR(10)`	Yes	Currency of monetary values.	Required when curated data spans multiple currency contexts.

Column	Type	Nullable	Technical Meaning	Notes
`source_system`	`VARCHAR(100)`	Yes	Canonical integration name used for lineage across source platforms.	Identifies which operational platform produced the record.
`source_shift_id`	`VARCHAR(199)`	Yes	Original source identifier kept for reconciliation and drill-through.	Preserves the native source key.
`labor_shift_key`	`BIGINT`	Yes	Warehouse-generated stable surrogate key for the labor shift entity.	Surrogate warehouse key for dimensional joins.
`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.