Data Dictionary
Canonical schema, validation rules and example records for every ingestion file.
Files
Eligibility File
Active and historical membership roster, plan assignment and PCP attribution.
Monthly · full snapshotContains PHI/PII7 required
Schema
11 columns| Column | Type | Req | Description | Validation |
|---|---|---|---|---|
| member_id | VARCHAR(20) | required | Unique member identifier | Alphanumeric, must be unique within file |
| first_name | VARCHAR(60) | required | Member legal first name | Non-empty, letters/hyphens/spaces |
| last_name | VARCHAR(60) | required | Member legal last name | Non-empty |
| dob | DATE | required | Date of birth | ISO YYYY-MM-DD · age 0–120 |
| gender | ENUM | required | Administrative gender | M, F, U |
| plan_id | VARCHAR(20) | required | Health plan / product code | Must exist in plan reference |
| group_id | VARCHAR(20) | optional | Employer / sub-group code | Optional, alphanumeric |
| effective_date | DATE | required | Coverage start | ISO date · ≤ term_date |
| term_date | DATE | optional | Coverage end | ISO date · null = active |
| pcp_npi | VARCHAR(10) | optional | Attributed PCP NPI | 10 digits if present |
| zip | VARCHAR(10) | optional | Member residence ZIP | 5 or 9 digits |
Example records
2 rows| member_id | first_name | last_name | dob | gender | plan_id | group_id | effective_date | term_date | pcp_npi | zip |
|---|---|---|---|---|---|---|---|---|---|---|
| M00012845 | Maria | Alvarez | 1956-04-12 | F | MA-HMO-01 | EMP-204 | 2024-01-01 | — | 1487209345 | 94110 |
| M00045902 | Robert | Chen | 1962-11-30 | M | MAPD-PPO-02 | EMP-110 | 2023-07-01 | — | 1932041128 | 10025 |
Validation rules
- ✓member_id must be unique across the file
- ✓effective_date ≤ term_date when both present
- ✓dob cannot be in the future
- ✓plan_id must match plan reference table
- ✓≥ 95% of rows must include pcp_npi (warning threshold)