Member Months

View Code on GitHub

A member month represents one person's enrollment in one health plan during one calendar month. Member months provide the enrollment denominator for measures such as per-member-per-month cost and utilization rates. Because they are derived from eligibility rather than claim activity, an enrolled person has a member-month record even when that person has no claims in the month.

Output and Grain

Claims Preprocessing creates claims_preprocessing.member_month. Its grain is one record per:

• person_id
• member_id
• year_month
• payer
• plan
• data_source

year_month is a string formatted as YYYYMM. member_month_id is a synthetic hash of those six grain fields. Eligibility extension columns do not flow into this output: member_month is derived at a different grain, so an extension needs an explicit downstream join or aggregation rule. See the Column Extensions guide.

The final core.member_month table keeps the same grain and identifier and adds payer-supplied, custom, and Tuva-calculated provider attribution fields.

How Tuva Creates Member Months

Tuva creates member months from normalized_layer.normalized__eligibility in the following sequence:

1. Preserve the enrollment span. enrollment_start_date and a finite enrollment_end_date remain native SQL DATE values. The legacy open-span alias 9999-12-31 is converted to null before member-month construction.
2. Set an as-of end date. Tuva uses the earlier of a finite enrollment_end_date and the date portion of tuva_last_run. For an open span, the as-of end is the date portion of tuva_last_run.
3. Build the supported month spine. Tuva uses one row per calendar month from January 1900 (190001) through December 2100 (210012).
4. Find every month that overlaps the span. A month is included when the enrollment start is on or before the month end and the as-of enrollment end is on or after the month start. Both boundaries are inclusive.
5. Reject reversed spans. A span whose start date is after its as-of end date produces no member months.
6. Project the monthly grain. Tuva selects distinct monthly records and creates member_month_id from the six grain fields listed above.

The overlap rule means that Tuva counts calendar months of coverage, not days of coverage. There is no daily proration or minimum-covered-days requirement.

Worked Examples

A Finite Span That Covers Partial Months

Suppose the source eligibility record is processed with tuva_last_run on or after 2024-03-02:

person_id	member_id	enrollment_start_date	enrollment_end_date	payer	plan	data_source
person_1	member_1	2024-01-20	2024-03-02	Acme	Gold	claims_a

Tuva creates three member months:

person_id	member_id	year_month	payer	plan	data_source
person_1	member_1	202401	Acme	Gold	claims_a
person_1	member_1	202402	Acme	Gold	claims_a
person_1	member_1	202403	Acme	Gold	claims_a

January and March each count as a full member month because the enrollment span overlaps at least one day in those months. A one-day span, such as 2024-01-31 through 2024-01-31, likewise creates one January member month.

Open and Finite Spans

Assume tuva_last_run is 2024-02-15 00:00:00:

span	enrollment_start_date	enrollment_end_date	Member months created
Open	2024-01-01	null	202401, 202402
Finite	2024-01-01	2024-03-31	202401, 202402

Both spans stop at the run date for expansion, but the entire calendar month containing that date is included. The finite span does not produce March because Tuva never materializes a future member month.

A finite or open span whose start date is after tuva_last_run produces no member months until the run date reaches that span. Later runs add newly elapsed months while the finite end remains in the future or the span remains open.

Closing an Open Span

Suppose an open span beginning 2024-01-01 is processed with tuva_last_run = 2024-04-15. It produces January through April. If the source later closes that same span with enrollment_end_date = 2024-03-31, the next build produces January through March and removes April.

The eligibility span keeps the same eligibility_id because that identifier is based on person_id, member_id, enrollment_start_date, payer, plan, and data_source; the mutable end date is intentionally excluded. The January through March member_month_id values also remain unchanged because their monthly grain fields have not changed.

Supported Date Range

The member-month spine covers every complete calendar month from January 1900 through December 2100. Within that supported range, both finite and open spans are bounded by tuva_last_run, so an otherwise valid span cannot create future member months.

Populated enrollment_start_date values and ordinary finite enrollment_end_date values must be between 1900-01-01 and the current date plus 12 calendar months, inclusive. Structural Data Quality verifies that each warehouse column is a native SQL DATE; one S2 Logical invalid check per field enforces the semantic range. Correct those failures before relying on member-month outputs.

The Logical validity range and the processing spine answer different questions. Future benefit-year enrollment can be valid up to 12 months ahead, but member months still stop at tuva_last_run; dates after 2100 cannot be expanded because the package-owned spine ends at 210012. The spine prevents unsupported or future months from being materialized, but it is not the universal validity rule for other Input Layer dates.

For backward compatibility, an input enrollment_end_date of 9999-12-31 is not applicable to the finite-date range check and is canonicalized to null before member-month construction. Map an open span to null for the canonical 1.0 contract.

Duplicate and Overlapping Spans

The stable eligibility-span key is person_id, member_id, enrollment_start_date, payer, plan, and data_source. Two source rows that differ only in enrollment_end_date are two versions of the same span, not two valid spans. They violate Structural Data Quality primary-key uniqueness and should be resolved before downstream processing.

Separate spans can still overlap. Tuva's Logical Data Quality checks report finite-span overlaps with the general overlap test and open/open conflicts with the more specific multiple-open test, so the same open/open defect is not counted twice. Both tests use the same person, member, payer, plan, and data source scope. The member-month model does not choose a winning span. If overlapping spans produce the same six-field monthly grain, its select distinct collapses the duplicate projection. That does not make the underlying overlap valid: resolve the Data Quality finding because duplicate spans can still affect eligibility and other downstream logic.

Concurrent coverage under a different member, payer, plan, or data source intentionally produces separate member-month records because those fields are part of the grain.

For this reason, a raw count of core.member_month rows across multiple plans represents plan-level enrollment months, not necessarily unique person-months. Filter to the intended coverage population or count distinct people when the analysis requires a person-level denominator.

Attribution and Downstream Use

Claims Preprocessing joins provider attribution to the same six-field member-month grain. When provider attribution is enabled, externally supplied payer and custom attribution can be joined from the Input Layer, and Tuva's yearly claims-based assignment is copied to each eligible member month for the same person, data source, and calendar year. core.member_month publishes those fields through left joins, so member months are retained even when no provider can be assigned.

Member months also support other claims workflows:

• Medical claim enrollment matching uses the first populated date among claim_line_start_date, claim_start_date, and admission_date, then joins the resulting month on the complete member-month grain.
• Pharmacy claim enrollment matching uses paid_date and the same grain.
• core.cost and core.utilization start from core.member_month, preserving eligible member months even when no claim-derived cost or utilization is present.

The medical and pharmacy eligibility checks use the same 190001 through 210012 supported month range and the same tuva_last_run run-month boundary as member-month construction. A claim whose matching date falls outside that month range, or after the run month, cannot be reported as having matching member-month eligibility.