Context

FlexPerks, an HR SaaS company serving 12,000 employers, currently stores employee benefits elections in a single transactional PostgreSQL schema optimized for the application. Reporting, payroll exports, and compliance extracts are generated by ad hoc SQL and nightly scripts, which break whenever a new benefit type, carrier rule, or enrollment attribute is introduced.

You need to design a data pipeline and analytics-ready schema that supports a flexible employee benefits system where plans, coverage tiers, dependents, deductions, life events, and carrier-specific attributes evolve frequently without requiring repeated full reloads or schema rewrites.

Scale Requirements

• Employers: 12,000
• Employees: 8M active, 20M historical
• Open enrollment peak: 25K enrollment changes/minute
• Daily batch volume: 150M source rows across HRIS, payroll, carrier, and eligibility feeds
• Latency target: <15 minutes for operational reporting, nightly SLA for payroll exports by 4 AM local employer time
• Storage: 40 TB raw history, 7-year retention for audit/compliance

Requirements

1. Design an ingestion and transformation pipeline for HRIS, payroll, carrier eligibility, and enrollment event data.
2. Model a flexible benefits schema that supports new benefit types and plan attributes without frequent table redesigns.
3. Preserve full history for elections, coverage changes, dependent updates, and payroll deduction changes using auditable SCD/event patterns.
4. Support idempotent reprocessing, late-arriving files, and backfills for corrected payroll periods.
5. Produce analytics-ready tables for enrollment status, employer-level participation, deduction reconciliation, and carrier export readiness.
6. Implement data quality checks for eligibility mismatches, duplicate elections, invalid effective dates, and missing dependent relationships.
7. Orchestrate employer-specific batch windows and downstream dependencies reliably.

Constraints

• AWS-first stack; existing systems already use S3 and Airflow.
• HIPAA/PII controls required: encryption, row-level access, audit logs, and least-privilege access.
• Incremental cloud budget increase capped at $35K/month.
• Source systems are inconsistent: SFTP CSV, REST APIs, CDC from PostgreSQL, and carrier files with changing layouts.