Benefits_Enrollment

Purpose

The Benefits.Enrollment table stores employee benefit enrollment information. It tracks all benefit elections, coverage levels, and associated costs for employees. This table is essential for benefits administration, cost analysis, and compliance reporting.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
EnrollmentID	INT	NO	Primary key, unique identifier	BENEFIT_ENROLLMENTS	ENROLLMENT_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	BENEFIT_ENROLLMENTS	EMPL_ID	Direct mapping
BenefitType	VARCHAR(50)	NO	Type of benefit	BENEFIT_ENROLLMENTS	BENEFIT_TYPE	Mapped from codes
BenefitCode	VARCHAR(20)	NO	Specific benefit plan code	BENEFIT_ENROLLMENTS	PLAN_CODE	Direct mapping
CoverageLevel	VARCHAR(50)	NO	Level of coverage	BENEFIT_ENROLLMENTS	COVERAGE_LEVEL	Mapped from codes
StartDate	DATE	NO	Enrollment start date	BENEFIT_ENROLLMENTS	START_DATE	Direct mapping
EndDate	DATE	YES	Enrollment end date	BENEFIT_ENROLLMENTS	END_DATE	Direct mapping
IsActive	BIT	NO	Active enrollment flag	BENEFIT_ENROLLMENTS	IS_ACTIVE	Derived from dates
EmployeeContribution	DECIMAL(12,2)	NO	Employee's contribution amount	BENEFIT_ENROLLMENTS	EMPL_CONTRIB	Direct mapping
EmployerContribution	DECIMAL(12,2)	NO	Employer's contribution amount	BENEFIT_ENROLLMENTS	EMPR_CONTRIB	Direct mapping
TotalCost	DECIMAL(12,2)	NO	Total plan cost	BENEFIT_ENROLLMENTS	TOTAL_COST	Sum of contributions
DependentCount	INT	YES	Number of covered dependents	BENEFIT_ENROLLMENTS	DEPENDENT_COUNT	Calculated from dependents
WaiverFlag	BIT	NO	Benefit waiver indicator	BENEFIT_ENROLLMENTS	WAIVER_FLAG	Direct mapping
WaiverReason	VARCHAR(100)	YES	Reason for waiver if applicable	BENEFIT_ENROLLMENTS	WAIVER_REASON	Direct mapping
CreatedDate	DATETIME	NO	Record creation date	N/A	N/A	System generated
ModifiedDate	DATETIME	NO	Last modification date	N/A	N/A	System generated

Source System Mapping

Primary Source: BENEFIT_ENROLLMENTS Table

Contains benefit enrollment information
Updated weekly from benefits system
Key fields mapped directly to Benefits.Enrollment

Secondary Sources:

BENEFIT_PLANS Table
- Plan details and costs
- Used for validation and cost calculations
- Updated when plan changes occur
DEPENDENTS Table
- Dependent coverage information
- Used to calculate DependentCount
- Updated when dependent status changes

Data Transformation Rules

Active Status Derivation

IsActive = CASE
  WHEN EndDate IS NULL OR EndDate > GETDATE() THEN 1
  ELSE 0
END

Benefit Type Mapping

Source Code -> Target Type
'MED' -> 'Medical'
'DEN' -> 'Dental'
'VIS' -> 'Vision'
'LIF' -> 'Life Insurance'
'DIS' -> 'Disability'
'401' -> '401k'

Coverage Level Mapping

Source Code -> Target Level
'EE' -> 'Employee Only'
'ES' -> 'Employee + Spouse'
'EC' -> 'Employee + Children'
'FAM' -> 'Family'

Total Cost Calculation

TotalCost = EmployeeContribution + EmployerContribution

Data Quality Rules

Required Fields
- EnrollmentID
- EmployeeID
- BenefitType
- BenefitCode
- CoverageLevel
- StartDate
- IsActive
- EmployeeContribution
- EmployerContribution
- TotalCost
Validation Rules
- Each active employee must have at least one active enrollment
- EndDate must be after StartDate if present
- TotalCost must equal sum of contributions
- DependentCount must match actual covered dependents
Business Rules
- Active employees must have valid enrollments
- WaiverFlag requires WaiverReason
- Coverage levels must be valid for benefit type
- Contributions must be non-negative

Usage Notes

Reporting Considerations
- Use IsActive for current enrollment reporting
- Consider CoverageLevel for cost analysis
- Use DependentCount for dependent coverage reporting
- Track waivers for compliance reporting
Performance Considerations
- Indexed on EnrollmentID, EmployeeID, and BenefitCode
- Consider filtering on IsActive for current enrollments
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Core.Employee via EmployeeID
- Used in benefits cost analysis views
- Source for benefits dimension in data warehouse

Maintenance

Refresh Schedule
- Weekly incremental updates
- Full refresh monthly
- Emergency updates for benefit changes
Archive Strategy
- Historical records maintained in table
- Use IsActive for current vs. historical filtering
- Enrollment history tracked for compliance
Monitoring
- Track enrollment counts by benefit type
- Monitor contribution amounts
- Alert on validation rule violations
- Track waiver rates

Benefits.Enrollment Table Documentation