Workforce Shift Rollup and Anomaly Flagging Script

Role: Lead People Analytics Data Engineer specializing in operational HR workforce data pipelines.

Context

• Analytics data warehouse: {{warehouse_platform}}
• Raw facility access log table: {{badge_event_table}}
• Canonical employee roster table: {{roster_table}}
• Aggregation interval: {{aggregation_granularity}}
• Weekly overtime ceiling in hours: {{overtime_threshold_hours}}
• Destination analytics mart table: {{output_mart_table}}

Task

Author an idempotent SQL rollup and anomaly detection script that transforms high-frequency access events from {{badge_event_table}}, enriches them with worker metadata from {{roster_table}}, aggregates them at the {{aggregation_granularity}} level, and writes daily shift totals and overtime warnings exceeding {{overtime_threshold_hours}} hours into {{output_mart_table}}.

Method

1. Define Common Table Expressions (CTEs) to deduplicate raw badge taps from {{badge_event_table}} based on device timestamp.
2. Pair alternating badge-in and badge-out events using window functions to calculate precise shift durations.
3. Handle unmatched single-tap events by applying parameterized heuristic fallback shift boundaries.
4. Join calculated shift sessions with department and contract metadata from {{roster_table}}.
5. Roll up total hours, break times, and night shifts according to {{aggregation_granularity}}.
6. Compute conditional indicator flags for any worker exceeding {{overtime_threshold_hours}} within the rolling cycle.
7. Write an atomic MERGE / UPSERT statement targeting {{output_mart_table}} to guarantee idempotence during backfills.

Constraints

• MUST write ANSI-standard SQL optimized natively for {{warehouse_platform}} dialect syntax.
• MUST NOT cause duplicate shift entries when run multiple times against overlapping date ranges.
• All null badge-out scenarios must be explicitly flagged with an anomaly code.
• Calculations for {{overtime_threshold_hours}} must respect local timezone offsets defined in the roster.

Output format

• Section 1: Pipeline Architecture & Windowing Strategy (1 short paragraph).
• Section 2: Complete Executable SQL / MERGE Script (cleanly formatted, ready for scheduling).
• Section 3: Anomaly & Orphan Tap Detection Queries (2 monitoring SQL queries).
• Section 4: Performance Optimization Notes for Large Tables (bulleted, max 4 items).

Self-review

• Verify that the MERGE statement key covers both the employee ID and {{aggregation_granularity}} timestamp.
• Check that overtime flag logic triggers strictly above {{overtime_threshold_hours}}.
• Confirm window functions partition by employee and session boundaries without cross-worker data leakage.