State programs

States M1 Enrollment tracker: medicaid_mco_state_month_reconciliation_mat_v1

medicare_silver.medicaid_mco_state_month_reconciliation_mat_v1·partial·silver

Rows

3,738

Columns

130

History

N/A

Card

nocolumns only

Typed tools

What it is

1Medicaid product M1 (Enrollment tracker) of the States lane: the Medicaid MCO enrollment tracker: members by plan and parent by state by month, every cell graded (observed, estimated, untested, not published), with parent rollups and the quarterly tie-out to what public parents report to the SEC.
2Start from medicaid_mco_enrollment_tracker_v2 (state x plan x parent x month) or its views vw_medicaid_mco_enrollment_tracker_v2_parent_state_month / _parent_national_month.
3READ basis ON EVERY ROW: observed is the state's printed figure; estimated is ours, and method names how (M-a carry-forward, M-b plan share x state total, M-e the tracker's nowcast model, PUB-PRELIM the publisher's own preliminary estimate; docs/research/states/M1-TRACKER-V2-DESIGN.md section 4), with interval_low / interval_high, inputs_as_of and assumption_ids. modeled is an M-e value where the method missed the accuracy bar: INDICATIVE ONLY, its interval is the measured error, and it is excluded from members_total; never sum it into an observed or estimated total (the views carry members_observed, members_estimated, members_modeled and members_with_modeled apart). not_published and not_applicable rows have members null: they are not zeros.
4Filter measure_class (members, member_months, weighted_members, average_monthly_members) and never add across it: average_monthly_members (with averaging_window;
5RI from 2023-12 prints average daily enrollment per month) never adds into members. measure is the publisher's own label.
6California is observed 2021-01..2026-09;
7Kaiser's publisher-suppressed county cells carry a labelled lower bound (assumption A-SUPPRESSED-LB).
8TWO PARENT QUESTIONS, two view pairs: the dated views vw_medicaid_mco_enrollment_tracker_v2_parent_dated_state_month / _parent_dated_national_month answer 'who owned it then' (parent_as_of_month;
9WellCare is its own parent before 2020, so Centene July 2016 is 5.92M there); the current views _parent_state_month / _parent_national_month answer 'today's companies' history' (Centene July 2016 is 9.93M there), with parent the CURRENT binding today (parent_status, parent_basis); never mix the two in one series.
10Assumptions: medicaid_mco_enrollment_assumption_v1 is the per-state register (populations counted, count basis, auto-assignment, carve-ins); medicaid_mco_enrollment_assumptions_v1 is the build's.
11SEC: read capture ratios only from vw_medicaid_mco_enrollment_tracker_v2_sec_tieout (like for like, duals adjusted per docs/research/states/M1-SEC-COUNT-BASIS.csv); comparability_class says when the filed number is broader than the tracker.
12SHOW THE GRADED adj_* COLUMNS, NEVER ONLY THE RATIO: each adjustment is its own labelled column (e.g. adj_subcontract_molina_la / _out measured from DMHC, adj_risk_share_healthy_blue_nc, adj_subcontract_lacare_anthem ESTIMATED with its low bound in the detail, adj_dsnp_from_ma_gap a JUDGMENT that a dual is counted in both lines, the 'once' alternative in the detail); a capture above 1.00 (UNH Q2 2026 1.007) means a judgment column over-explains.
13The two NY columns keep their _held names for schema stability but are LIVE: adj_ny_chp_held (NY Child Health Plus members, CNC 188,720, ELV 40,405) and sec_ny_essential_plan_excluded_held (NY Essential Plan is not Medicaid and comes off the SEC side, CNC -388,602), source NY DOH healthweb-back.health.ny.gov, recorded by the States lane as allowed by Ben on 2026-11-01; null where no NY figure exists or it is not measured (UNH), never zero. sec_asc_excluded is per quarter; exits are listed apart.
14ESTIMATE INTERVALS widen with the TOTAL horizon where the state total is carried past its last published month (months since the last observed plan value + months the total is carried): inputs_as_of is the last published total month and value_status says 'state total carried from <month>'.
15TEXAS: observed monthly by MCO x program 2007-09..2026-02 from HHSC MCO Financial Statistical Reports (tx_medicaid_mco_fsr_member_months_v1: filter is_latest_filing = true, since restated filings stay as rows, and grain); after 2026-02 plan values scale by HHSC's own monthly total.
16234 TX observed members rows are FRACTIONAL by rule (part-month adjustments in the filed member months;
17HHSC defines the line as members enrolled in the month and it ties to HHSC final SFY averages within 0.04-0.38%, assumption A-TX-FSR-RETRO): they stay measure_class members, never rounded, so a fractional-members check exempts source_family state_fsr. measure_class members_hhsc_prelim_plan_total (HHSC 2026-05 preliminary per-plan totals) never adds to members. tx_medicaid_mco_enrollment_other_sources_v1 holds TDI HMO quarter-ends, 1115 waiver tables, CHIP 2003-13 and HHSC preliminary months, one series per row (filter series and period_kind), never mixed with the FSR months.
18INPUTS behind the tracker, read only with their labels: medicaid_m1_program_observation_v1 is the programs the spine lacked (MN, PA CHC, NY MLTC/MAP/FIDA-IDD, IN PathWays, CO Denver Health, OH MyCare, WI Family Care, WV MHP, CA SCAN, DC Dual Choice), one row per published cell (read value_unit and source_time_basis: some are member months). medicaid_mco_plan_enrollment_hunt_v1 and _hunt_cogank_v1 are hunted series (ND VT CO GA monthly, MA AR quarterly, NE EQR points, KS year-to-date averages): filter tracker_use, because only observed_monthly and observed_point rows are observations; quarterly_anchor and average_anchor are anchors, check_only and not_read are never a figure, and grain says month, quarter, rolling_quarter, calendar_ytd or point_in_time. medicaid_mcpar_plan_avg_enrollment_v1 is MCPAR annual average monthly members by plan (PY2023-25, 48 states), an anchor only, never a monthly count; suspect = true rows (77) are flagged. medicaid_mco_state_month_reconciliation_mat_v1 is the earlier plan-sum vs state and CMS totals reconciliation (cms_monthly_denominator_available = false is a missing denominator, never zero).
1931 state(s): AZ CA DC FL GA HI IA IL IN KY LA MI MN MO MS NJ NM NV NY OK OR PA RI SC TN TX UT VA WA WI WV.

Grain

Key	Grain
N/A	1The Medicaid MCO enrollment tracker: members by plan and parent by state by month, every cell graded (observed, estimated, untested, not published), with parent rollups and the quarterly tie-out to what public parents report to the SEC. 23,738 rows (Athena, 2026-10-29). 3Period 2000-01-01 to 2026-10-01 (observation_month).

Columns (130)

Column	Type	Meaning
`state_code`	varchar(2)
`observation_month`	date
`reported_managed_medicaid_plan_membership_exposure`	double
`plan_observation_rows`	bigint
`plan_identities`	bigint
`included_program_classes`	string
`plan_source_tables`	string
`unresolved_or_ambiguous_identity_rows`	bigint
`total_medicaid_enrollment`	bigint
`total_chip_enrollment`	bigint
`total_medicaid_and_chip_enrollment`	bigint
`contextual_medicaid_minus_plan_exposure`	double
`plan_exposure_to_medicaid_ratio`	double
`report_revision`	string
`final_report`	string
`is_final_report`	boolean
`cms_source_url`	string
`cms_source_hash`	string
`cms_semantic_row_sha256`	string
`cms_monthly_denominator_available`	boolean
`reported_plan_membership_exposure`	boolean
`contextual_residual_promoted_as_ffs`	boolean
`component_waterfall_complete`	boolean
`unduplicated_beneficiary_total_authorized`	boolean
`national_aggregation_authorized`	boolean
`waterfall_component_basis`	varchar(65)
`waterfall_comprehensive_capitated_total`	double
`waterfall_comprehensive_capitated_not_in_plan_rows`	double
`waterfall_mma_specialty`	bigint
`waterfall_mma_child_welfare`	bigint
`waterfall_childrens_medical_services`	bigint
`waterfall_pace`	bigint
`waterfall_ffs_mma_eligible`	bigint
`waterfall_ffs_tpl`	bigint
`waterfall_ffs_excluded_from_mma`	bigint
`waterfall_publisher_total`	bigint
`waterfall_chip_inside_publisher_total`	double
`waterfall_explained_non_plan_medicaid`	double
`waterfall_unexplained_residual`	bigint
`waterfall_residual_label`	varchar(475)
`waterfall_source_file`	string
`waterfall_source_document_sha256`	string
`waterfall_residual_before_definitional_exclusions`	bigint
`definitional_partial_dual_excluded_by_cms`	bigint
`definitional_qmb_only`	bigint
`definitional_slmb_only`	bigint
`definitional_qi_qdwi`	bigint
`definitional_exclusion_label`	varchar(316)
`definitional_source_url`	string
`definitional_source_hash`	string
`waterfall_chc_capitated`	bigint
`waterfall_ffs_and_other_derived`	double
`waterfall_chip_separate_outside_total`	bigint
`waterfall_chip_inside_basis`	varchar(232)
`waterfall_ffs_and_other_label`	varchar(271)
`waterfall_nj_familycare_mco_total`	bigint
`waterfall_nj_ffs_or_pace`	bigint
`waterfall_nj_mchip`	bigint
`waterfall_nj_schip`	bigint
`waterfall_nj_hmo_source_hashes`	string
`waterfall_ny_mmc_publisher_total`	bigint
`waterfall_ny_harp`	bigint
`waterfall_ny_mltc`	bigint
`waterfall_ny_map`	bigint
`waterfall_ny_fida_idd`	bigint
`waterfall_tx_medicaid_managed_care_total`	bigint
`waterfall_tx_star_plus`	bigint
`waterfall_tx_star_health`	bigint
`waterfall_tx_dual_demo`	bigint
`waterfall_il_mc_publisher_total`	bigint
`waterfall_il_partial_benefits`	bigint
`waterfall_il_state_only_hbia`	bigint
`waterfall_il_state_only_hbis`	bigint
`waterfall_mi_medicaid_eligible`	bigint
`waterfall_mi_healthy_michigan_plan`	bigint
`waterfall_az_acute_plans_subtotal`	bigint
`waterfall_az_altcs_pc_subtotal`	bigint
`waterfall_az_ffs_subtotal`	bigint
`waterfall_az_msp_subtotal`	bigint
`waterfall_az_emergency_services`	bigint
`waterfall_az_qmb_only`	bigint
`waterfall_az_slmb`	bigint
`waterfall_az_qi1`	bigint
`waterfall_az_transplant_option`	bigint
`waterfall_az_kidscare`	bigint
`waterfall_az_printed_exclusions`	bigint
`waterfall_ia_mco_medicaid`	int
`waterfall_ia_ffs_medicaid`	int
`waterfall_ia_hawki_medical`	int
`waterfall_ia_hawki_dental_only`	int
`waterfall_ia_medicaid_dental_only`	int
`waterfall_ia_medicaid_expansion_chip_proxy`	bigint
`waterfall_ia_dental_source_hash`	varchar(64)
`waterfall_ia_medical_source_locator`	string
`waterfall_ia_dental_source_locator`	string
`waterfall_nm_ffs`	bigint
`waterfall_nm_unassigned`	bigint
`waterfall_nm_family_planning`	bigint
`waterfall_nm_emergency_only`	bigint
`waterfall_nm_coe_vs_total_delta`	bigint
`waterfall_nm_source_locators`	string
`waterfall_nv_chip_caseload`	bigint
`waterfall_nv_chip_mco`	bigint
`waterfall_nv_dashboard_slmb`	bigint
`waterfall_nv_dashboard_mco_delta`	bigint
`waterfall_nv_chip_mco_vs_caseload_delta`	bigint
`waterfall_nv_source_locators`	string
`materialized_at`	timestamp
`waterfall_va_title_xix_total`	bigint
`waterfall_va_title_xxi_total`	bigint
`waterfall_va_mco_total`	bigint
`waterfall_va_mco_title_xix_total`	bigint
`waterfall_va_pace`	bigint
`waterfall_va_qmb_qi_limited`	bigint
`waterfall_va_doc_emergency_limited`	bigint
`waterfall_va_family_planning_limited`	bigint
`waterfall_va_aca_doc_emergency_limited`	bigint
`waterfall_va_printed_exclusions`	bigint
`waterfall_va_plan_rows_minus_mco_total`	double
`waterfall_va_source_locators`	string
`waterfall_tn_select`	bigint
`waterfall_tn_kbb_didd`	bigint
`waterfall_tn_awaiting`	bigint
`waterfall_tn_source_locators`	string
`waterfall_wa_chp`	bigint
`waterfall_wa_mcs`	bigint
`waterfall_wa_aem`	bigint
`waterfall_wa_pccm`	bigint
`waterfall_wa_source_locators`	string
`waterfall_wa_fimc_minus_plan_rows`	double

Query it

describe_data_source(source_fqn="medicare_silver.medicaid_mco_state_month_reconciliation_mat_v1")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.medicaid_mco_state_month_reconciliation_mat_v1 WHERE <filter> LIMIT 100")

Item	Value
Typed tools that read it	none; raw SQL
Identity key	N/A
Join	`how_to_join(source_fqn="medicare_silver.medicaid_mco_state_month_reconciliation_mat_v1")`
Raw door	Athena (read-only via query_substrate): medicare_silver.medicaid_mco_state_month_reconciliation_mat_v1 (filter state_code)

This page as markdown: /docs-md/data/medicare_silver_medicaid_mco_state_month_reconciliation_mat_v1.md · every page in one file: /llms-full.txt