People Analytics Monthly Headcount and Attrition ETL Script Generator

Role: Staff People Analytics Engineer specializing in automated workforce reporting pipelines.

Context

• HRIS data structure: {{hris_source_format}}
• Target cadence: {{target_reporting_cadence}}
• Organization grouping: {{business_unit_hierarchy}}
• Turnover calculation logic: {{attrition_calculation_rule}}
• Storage destination: {{output_storage_destination}}
• Threshold alert trigger: {{alert_threshold_turnover}}

Task

Write a self-contained, modular Python reporting script that ingests HRIS transaction records, computes monthly active headcount and annualized voluntary/involuntary turnover across organizational tiers, flags departments exceeding baseline churn thresholds, and publishes the summarized metric table to the designated storage destination.

Method

1. Ingest raw log data based on {{hris_source_format}} with strict column type validation and date parsing.
2. Apply snapshot interval logic matching {{target_reporting_cadence}} to isolate active employee states on baseline measurement dates.
3. Segment employee census counts across {{business_unit_hierarchy}} dimensions including tenure bands and job families.
4. Calculate voluntary and involuntary separation rates strictly following {{attrition_calculation_rule}}.
5. Flag any business unit or department that breaches {{alert_threshold_turnover}} during the reporting cycle.
6. Format output dataset into normalized reporting schemas with standardized metric naming conventions.
7. Implement destination push handlers to transfer aggregated data cleanly to {{output_storage_destination}} with error handling.

Constraints

• Code MUST be written in clean Python 3.10+ using pandas or polars with complete type hints.
• MUST NOT hardcode credentials or connection strings; load all secrets via environment variables.
• MUST handle missing termination reason codes gracefully by classifying them into an unmapped category.
• Include inline logging for every major transformation stage to facilitate pipeline observability.

Output format

Provide the response in the following sequence:

1. Architecture Overview (1 brief paragraph describing data flow).
2. Complete Python Script (single executable code block containing imports, config class, transformation functions, and main execution block).
3. Operational Instructions (concise deployment checklist with required environment variables).

Self-review

• Confirm all 6 context variables are actively utilized in the code logic or configuration block.
• Verify the turnover rate formulas strictly adhere to {{attrition_calculation_rule}}.
• Ensure exception handling covers empty source batches and failed storage uploads.