Member Enrollment Data: Health Plan Data Dictionary, Model Design, and Analytics Guide

Member enrollment data is the foundation of every health plan analytics platform. Before a claim can be adjudicated, before a quality measure can be calculated, before a risk score can be generated — the member must be enrolled. Member data connects every domain of healthcare analytics: claims, pharmacy, clinical, quality, and risk adjustment all flow through the member as the central entity.

This guide covers everything a health plan data architect, data modeler, or data engineer needs to know about member enrollment data — from core data elements and enrollment standards to production-ready Star Schema design for Snowflake, Databricks, and BigQuery.

What Is Member Enrollment Data?

Member enrollment data captures the administrative relationship between an individual and their health insurance plan. It answers the fundamental question that drives all healthcare analytics: was this person covered, under which plan, and during what time period?

A health plan member is an individual enrolled in a health insurance product — whether a Medicare Advantage plan, a commercial employer-sponsored plan, a Medicaid managed care plan, or an ACA marketplace plan. The member enrollment record documents that relationship with precision: when coverage started, when it ended, which plan was selected, what benefits apply, and what the member owes in cost-sharing.

Member enrollment data flows through several administrative channels:

CMS MARx enrollment files transmit Medicare Advantage enrollment transactions between CMS and health plans, communicating member additions, changes, and disenrollments on a monthly basis with prospective payment implications.

EDI 834 transactions are the HIPAA standard electronic enrollment and disenrollment transactions used between employers, brokers, and health plans for commercial coverage. The 834 communicates member demographics, coverage elections, and effective dates in a standardized format.

State Medicaid enrollment files vary by state and managed care organization but typically include eligibility span data, aid category codes, managed care plan assignments, and retroactive eligibility changes that create complex enrollment histories.

Core Member Enrollment Data Elements

Every healthcare data team working with member data needs to understand these fundamental fields:

The member identifier (mbr_id) is the unique key assigned to each health plan member. It is the primary foreign key across all analytical domains — claims, pharmacy, clinical, and quality data all reference the member by this identifier. Member ID management is complex because members re-enroll after gaps, switch plans, and may receive different IDs from different source systems.

The member effective date (mbr_eff_dt) marks the start of a coverage span. Claims adjudication verifies the service date falls on or after the effective date. HEDIS continuous enrollment denominators are defined by effective and termination date ranges.

The member termination date (mbr_term_dt) marks the end of coverage. A NULL termination date indicates active coverage. Healthcare data teams use COALESCE(mbr_term_dt, '9999-12-31') to represent open-ended spans in SQL date range queries.

The member birth date (mbr_birth_dt) is essential for HCC risk adjustment demographic calculations, HEDIS age-stratified measure denominators, and Medicare eligibility verification. Age should always be calculated at query time from birth date rather than stored as a static field.

The member gender code (mbr_gndr_cd) follows CMS and X12 EDI conventions with values M, F, and U. Gender drives demographic RAF score coefficients in CMS-HCC risk adjustment models and is required for HEDIS gender-stratified measure reporting.

The member group number (mbr_grp_nbr) identifies the employer group or plan sponsor. For Medicare Advantage, group numbers identify Special Needs Plans and employer group waiver plans.

The member relationship code (mbr_rel_cd) identifies whether the member is the primary subscriber or a dependent, using X12 standard codes including 18 for self, 01 for spouse, and 19 for child.

The member dual eligibility (mbr_dual_elig) indicator identifies members covered by both Medicare and Medicaid — a population with significantly higher clinical complexity and distinct risk adjustment coefficients under the CMS-HCC model.

The member RAF score (mbr_raf_scr) is the Risk Adjustment Factor score representing predicted healthcare costs relative to the average Medicare beneficiary. Use our HCC Calculator to estimate RAF scores from ICD-10 diagnosis codes.

Member Enrollment Data Model Design

The member enrollment Star Schema must support three primary analytical use cases: point-in-time eligibility queries for claims adjudication, continuous enrollment period calculations for HEDIS denominators, and population-level analytics for risk stratification and care management.

Below is the production-ready Star Schema generated by the mdatool AI Data Modeling tool. This schema supports Medicare Advantage analytics including HEDIS quality measurement, HCC risk adjustment, and member care gap tracking.

Star Schema Overview

The schema centers on FACT_MEMBER_ENROLLMENT as the primary fact table capturing enrollment spans with financial terms. Satellite tables extend the model with risk scores and care gaps, while shared dimension tables for members, plans, and dates support cross-domain analytics.

Fact Table: FACT_MEMBER_ENROLLMENT

-- Snowflake DDL — generated with mdatool AI Data Modeling
-- mdatool.com/tools/modeling
CREATE TABLE FACT_MEMBER_ENROLLMENT (
  MBR_ENRL_KEY        INTEGER         NOT NULL,   -- surrogate key
  MBR_KEY             INTEGER         NOT NULL,   -- FK to DIM_MEMBER
  PLAN_KEY            INTEGER         NOT NULL,   -- FK to DIM_PLAN
  EFF_DT_KEY          INTEGER         NOT NULL,   -- FK to DIM_DATE (effective)
  TERM_DT_KEY         INTEGER         NOT NULL,   -- FK to DIM_DATE (termination)
  ENRL_TYP_CD         VARCHAR(10),                -- enrollment type code
  MBR_PREM_AMT        DECIMAL(10,2),              -- member premium amount
  MBR_DED_AMT         DECIMAL(10,2),              -- annual deductible amount
  MBR_OOP_MAX_AMT     DECIMAL(10,2),              -- out of pocket maximum
  MBR_COPAY_AMT       DECIMAL(10,2),              -- standard copay amount
  MBR_COINS_PCT       DECIMAL(5,2),               -- coinsurance percentage
  DUAL_ELIG_IND       CHAR(1),                    -- dual eligible indicator
  LIS_IND             CHAR(1),                    -- low income subsidy indicator
  LOAD_DT             TIMESTAMP_NTZ   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_FACT_MEMBER_ENROLLMENT
    PRIMARY KEY (MBR_ENRL_KEY)
);

Dimension Tables

-- Member demographics with SCD Type 2 for historical tracking
CREATE TABLE DIM_MEMBER (
  MBR_KEY             INTEGER         NOT NULL,
  MBR_ID              VARCHAR(50)     NOT NULL,   -- member identifier (PHI)
  MBR_FIRST_NM        VARCHAR(100),               -- first name (PHI)
  MBR_LAST_NM         VARCHAR(100),               -- last name (PHI)
  BIRTH_DT            DATE,                       -- date of birth (PHI)
  MBR_GNDR_CD         CHAR(1),                    -- gender code (M/F/U)
  MBR_STATE_CD        CHAR(2),                    -- state code
  MBR_ZIP_CD          VARCHAR(10),                -- zip code (PHI)
  MBR_CNTY            VARCHAR(50),                -- county
  MBR_PREF_LANG       VARCHAR(10),                -- preferred language (ISO 639-1)
  MBR_RACE_ETHN       VARCHAR(50),                -- race/ethnicity (OMB standard)
  MBI_ID              VARCHAR(11),                -- Medicare beneficiary ID
  ESRD_IND            CHAR(1),                    -- end stage renal disease
  SNP_TYP_CD          VARCHAR(10),                -- special needs plan type
  EFF_START_DT        DATE            NOT NULL,   -- SCD2 start date
  EFF_END_DT          DATE,                       -- SCD2 end date (NULL = current)
  CURR_ROW_IND        BOOLEAN         NOT NULL DEFAULT TRUE,
  CONSTRAINT PK_DIM_MEMBER PRIMARY KEY (MBR_KEY)
);

-- Health plan and benefit package dimension
CREATE TABLE DIM_PLAN (
  PLAN_KEY            INTEGER         NOT NULL,
  PLAN_ID             VARCHAR(20)     NOT NULL,   -- plan identifier
  PLAN_NM             VARCHAR(255),               -- plan name
  PLAN_TYP_CD         VARCHAR(10),                -- HMO/PPO/PFFS/SNP
  COV_TYP_CD          VARCHAR(10),                -- coverage type code
  MBR_PLN_YR          SMALLINT,                   -- plan year
  CNTRCT_NBR          VARCHAR(20),                -- CMS contract number
  PBP_NBR             VARCHAR(10),                -- plan benefit package number
  STAR_RTG_NBR        DECIMAL(3,1),               -- CMS star rating
  SNP_IND             CHAR(1),                    -- special needs plan indicator
  STATE_CD            CHAR(2),                    -- state code
  EFF_START_DT        DATE            NOT NULL,
  EFF_END_DT          DATE,
  CURR_ROW_IND        BOOLEAN         NOT NULL DEFAULT TRUE,
  CONSTRAINT PK_DIM_PLAN PRIMARY KEY (PLAN_KEY)
);

-- Date dimension for enrollment span analytics
CREATE TABLE DIM_DATE (
  DT_KEY              INTEGER         NOT NULL,   -- YYYYMMDD integer
  FULL_DT             DATE            NOT NULL,
  YR_NBR              SMALLINT,
  QTR_NBR             SMALLINT,
  MO_NBR              SMALLINT,
  MO_NM               VARCHAR(20),
  WK_NBR              SMALLINT,
  DAY_OF_WK_NBR       SMALLINT,
  DAY_OF_WK_NM        VARCHAR(20),
  HLDY_IND            BOOLEAN,
  CONSTRAINT PK_DIM_DATE PRIMARY KEY (DT_KEY)
);

-- Member risk score satellite — updated quarterly from CMS
CREATE TABLE DIM_MEMBER_RISK_SCORE (
  RISK_KEY            INTEGER         NOT NULL,
  MBR_KEY             INTEGER         NOT NULL,   -- FK to DIM_MEMBER
  RAF_SCR             DECIMAL(10,3),              -- risk adjustment factor score
  MBR_RSK_SCR         DECIMAL(10,3),              -- member risk score
  HCC_CNT             SMALLINT,                   -- HCC category count
  MODEL_VER_CD        VARCHAR(10),                -- V24 or V28
  SCORE_DT            DATE,                       -- score calculation date
  DEMO_RAF_SCR        DECIMAL(10,3),              -- demographic RAF component
  DIAG_RAF_SCR        DECIMAL(10,3),              -- diagnosis RAF component
  DUAL_ELIG_CD        VARCHAR(10),                -- dual eligibility category
  LIS_LVL_CD          VARCHAR(10),                -- LIS level code
  LOAD_DT             TIMESTAMP_NTZ   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_DIM_MEMBER_RISK_SCORE PRIMARY KEY (RISK_KEY)
);

-- HEDIS care gap tracking — one row per measure per member per year
CREATE TABLE DIM_MEMBER_CARE_GAP (
  CARE_GAP_KEY        INTEGER         NOT NULL,
  MBR_KEY             INTEGER         NOT NULL,   -- FK to DIM_MEMBER
  PLAN_KEY            INTEGER         NOT NULL,   -- FK to DIM_PLAN
  MEAS_YR             SMALLINT        NOT NULL,   -- HEDIS measurement year
  HEDIS_MEAS_CD       VARCHAR(20),                -- HEDIS measure code
  HEDIS_MEAS_NM       VARCHAR(255),               -- HEDIS measure name
  GAP_OPEN_DT         DATE,                       -- care gap opened date
  GAP_CLOSE_DT        DATE,                       -- care gap closed date
  GAP_STS_CD          VARCHAR(10),                -- open/closed/excluded
  GAP_CLOSE_IND       CHAR(1),                    -- gap closed indicator
  OUTREACH_CNT        SMALLINT,                   -- outreach attempts
  LOAD_DT             TIMESTAMP_NTZ   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT PK_DIM_MEMBER_CARE_GAP PRIMARY KEY (CARE_GAP_KEY)
);

Generate this complete schema instantly using the mdatool AI Data Modeling tool — select Member Enrollment as your domain, Star Schema as your architecture, and Snowflake as your platform. The tool generates production-ready DDL with ISO-11179 standard column names in 30 seconds.

Point-in-Time Eligibility Queries

The most common challenge in member enrollment analytics is answering the question: was this member covered on this specific date? The enrollment span model with effective and termination dates enables efficient point-in-time queries:

-- Was member covered on a specific service date?
SELECT
    e.MBR_KEY,
    m.MBR_ID,
    p.PLAN_NM,
    e.EFF_DT_KEY,
    e.TERM_DT_KEY
FROM FACT_MEMBER_ENROLLMENT e
JOIN DIM_MEMBER m ON e.MBR_KEY = m.MBR_KEY
    AND m.CURR_ROW_IND = TRUE
JOIN DIM_PLAN p ON e.PLAN_KEY = p.PLAN_KEY
    AND p.CURR_ROW_IND = TRUE
JOIN DIM_DATE eff ON e.EFF_DT_KEY = eff.DT_KEY
JOIN DIM_DATE trm ON e.TERM_DT_KEY = trm.DT_KEY
WHERE m.MBR_ID = 'MBR_12345678'
AND '2026-03-15'::DATE BETWEEN eff.FULL_DT
    AND COALESCE(trm.FULL_DT, '9999-12-31'::DATE);

HEDIS Continuous Enrollment Calculations

HEDIS measures require members to be continuously enrolled for defined periods — typically 11 out of 12 months with no more than one gap in coverage. The enrollment span model supports this calculation:

-- Identify members with continuous enrollment for HEDIS Year 2026
SELECT
    m.MBR_KEY,
    m.MBR_ID,
    COUNT(DISTINCT e.MBR_ENRL_KEY) AS enrollment_spans,
    MIN(eff.FULL_DT) AS first_eff_dt,
    MAX(COALESCE(trm.FULL_DT, '2026-12-31'::DATE)) AS last_term_dt
FROM FACT_MEMBER_ENROLLMENT e
JOIN DIM_MEMBER m ON e.MBR_KEY = m.MBR_KEY
    AND m.CURR_ROW_IND = TRUE
JOIN DIM_DATE eff ON e.EFF_DT_KEY = eff.DT_KEY
JOIN DIM_DATE trm ON e.TERM_DT_KEY = trm.DT_KEY
WHERE eff.FULL_DT <= '2026-12-31'
AND COALESCE(trm.FULL_DT, '2026-12-31') >= '2026-01-01'
GROUP BY m.MBR_KEY, m.MBR_ID
HAVING MIN(eff.FULL_DT) <= '2026-02-01'  -- enrolled by Feb 1
ORDER BY m.MBR_KEY;

Common Member Analytics Use Cases

Health plan data teams use member enrollment data across a wide range of analytical programs:

HCC Risk Adjustment Submissions aggregate diagnosis data from claims and link to member enrollment records to produce annual risk adjustment submissions to CMS. The HCC Calculator allows you to estimate RAF scores from ICD-10 diagnosis codes for individual members.

HEDIS Quality Measure Reporting uses enrollment span data to identify eligible denominators for each measure, verify continuous enrollment requirements, and attribute members to their enrolled plan for measure reporting. Accurate enrollment data directly affects HEDIS rates and CMS Star Ratings.

Care Gap Identification and Closure identifies members overdue for preventive screenings, chronic disease management visits, and medication adherence by comparing HEDIS measure criteria against member service history. The DIM_MEMBER_CARE_GAP table tracks open and closed gaps for outreach prioritization.

Member Risk Stratification combines enrollment demographics with risk scores and care gaps to tier members by predicted utilization into high-risk, rising-risk, and low-risk categories for care management program assignment.

Medicare Advantage Star Ratings analytics track CMS quality bonus payment eligibility by measuring HEDIS performance rates, CAHPS survey scores, and health outcomes measures for the enrolled Medicare Advantage population.

Dual Eligible Population Management identifies members with both Medicare and Medicaid coverage for targeted care coordination programs, D-SNP enrollment eligibility assessment, and health equity analytics.

Member Data Quality Considerations

Member data quality issues cascade across every downstream analytics use case. Healthcare data teams must address these common challenges:

Enrollment span gaps occur when a member disenrolls and re-enrolls with a coverage gap. Analytics pipelines must determine whether these gaps represent true coverage interruptions or administrative processing delays, as the treatment differs for HEDIS continuous enrollment calculations.

Demographic data completeness is often incomplete for race, ethnicity, and preferred language — fields that are voluntary for members to provide. CMS EDGE server imputation methods can supplement missing values for risk adjustment purposes.

Member identity resolution challenges arise when the same individual has multiple member IDs across different plan years, different affiliated plans, or different source systems. Master member indexes using probabilistic matching on name, date of birth, and ZIP code resolve these identities for longitudinal analytics.

Retroactive eligibility changes are common in Medicaid and Medicare when eligibility is determined after the fact. These changes require retroactive claim reprocessing, premium reconciliation, and careful handling in data warehouse effective dating logic.

Dual eligibility status changes occur throughout the year as members gain or lose Medicaid eligibility. Real-time dual eligibility status affects cost-sharing obligations, care coordination program eligibility, and risk adjustment coefficients.

Member Enrollment Data Tools

mdatool provides several free tools designed for member enrollment data work:

• AI Data Modeling — Generate a complete member enrollment schema for Snowflake, BigQuery, or Databricks in 30 seconds
• Data Model Canvas — Visualize your member schema as an interactive ER diagram with PDF export
• HCC Calculator — Calculate Medicare Advantage RAF scores from ICD-10 diagnosis codes
• [NPI Lookup](/tools/npi-lookup) — Look up provider NPIs for member-to-provider attribution analytics
• SQL Linter — Validate member enrollment SQL against ISO-11179 naming standards
• DDL Converter — Convert member schema DDL between Snowflake, BigQuery, and Databricks

Frequently Asked Questions

What is the difference between a member and a subscriber? The subscriber is the primary policyholder in whose name the insurance policy is issued. A member may be either the subscriber or a dependent enrolled under the subscriber's policy. In X12 EDI 834 transactions, the subscriber appears in the 2000B loop while dependents appear in the 2000C loop. Analytics pipelines must handle both subscriber and dependent records correctly for family cost-sharing accumulation and coordination of benefits processing.

How do you handle retroactive enrollment changes in a data warehouse? Retroactive enrollment changes require a slowly changing dimension Type 2 approach on the member and enrollment tables, maintaining a history of all effective date changes with row-level effective dating. Claims adjudicated during the retroactive period may need reprocessing to reflect the updated eligibility. Most data warehouses maintain a separate enrollment adjustment table to track retroactive changes and their downstream impact.

What is a Medicare Advantage enrollment span? A Medicare Advantage enrollment span is the period during which a beneficiary is enrolled in a specific Medicare Advantage plan, defined by an effective date and termination date. CMS transmits enrollment information through the MARx system with monthly effective dates aligned to the first of each month. The enrollment span determines which plan receives monthly capitation payments from CMS for that beneficiary.

How is dual eligibility used in risk adjustment? Dual eligibility status affects the demographic coefficient applied to a member's RAF score in the CMS-HCC model. Full benefit dual eligible members receive higher demographic coefficients because of their typically greater clinical complexity and social needs. CMS also requires health plans to track dual eligibility status for health equity reporting and D-SNP program compliance.

What is the HEDIS continuous enrollment requirement? Most HEDIS measures require members to be enrolled in the health plan for a defined continuous period — typically 11 of 12 months in the measurement year with no more than one gap in enrollment. Members who do not meet the continuous enrollment requirement are excluded from the measure denominator. Healthcare data teams calculate continuous enrollment using date overlap logic applied to enrollment span tables.

How do you calculate member months for PMPM analytics? Member months are calculated by counting the number of calendar months a member was enrolled during a period. For each enrollment span, count the months between the effective date and termination date inclusive. Sum member months across all spans for the population to produce total member months. Divide total cost or utilization by total member months to calculate per-member-per-month metrics used in actuarial analysis and plan financial reporting.

What is the difference between member effective date and enrollment date? The member effective date is when coverage benefits become active — the date from which claims will be covered. The enrollment date is when the administrative enrollment transaction was processed in the plan system. These dates differ because enrollment processing may occur days or weeks before coverage becomes effective, particularly during open enrollment periods when coverage begins on the first of the following month.