VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

MA plan payment — Part D (plan x year)

medicare_silver.ma_plan_payment_part_d·live·silver·annual·Medicare Advantage Plan Payments
Rows
48,455
Columns
14
History
2015 to 2024
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1CMS Medicare Part D plan-payment reference data: per-plan, per-year averages of the federal subsidies CMS pays each Medicare Advantage-PD and standalone PDP plan (direct subsidy, reinsurance, low-income cost-sharing) plus the plan's average Part D risk score.
  2. 2It is a cost/payment benchmark dataset for understanding what the government pays drug plans, an annual national panel covering 2015-2024.

Grain

KeyGrain
payment_yearcontract_idplan_id
  1. 1One row per Medicare plan per payment year — uniquely keyed by (payment_year, contract_id, plan_id).
  2. 2Verified: DISTINCT on those three columns = 48,455, exactly equal to the table's row_count of 48,455. contract_name, organization_type, and all dollar/risk values are single-valued attributes of that key (0 groups had >1 name or org_type), so they are descriptive, not part of the key.
  3. 3The profiler's 6-column 'verified_unique_by' is an inflated superset.

How to read it

  1. 1Trend a specific drug plan's federal subsidies over time, or benchmark a sponsor against peers.
  2. 2Example: SELECT payment_year, avg_direct_subsidy_pmpm, avg_reinsurance_pmpm, avg_low_income_cost_sharing_pmpm, avg_part_d_risk_score FROM medicare_silver.ma_plan_payment_part_d WHERE contract_id='S5601' AND plan_id='001' ORDER BY payment_year — returns the year-by-year per-member-per-month subsidies CMS paid that standalone PDP and how its risk score moved.
  3. 3To compare plan types in one year, add WHERE payment_year=2024 and GROUP BY organization_type with AVG() over the value columns.

Traps

  1. 1Grain is (payment_year, contract_id, plan_id) — NOT contract_id or plan_id alone. plan_id has only 521 distinct values because '001','801', etc. are reused across thousands of contracts; it is meaningless without contract_id. contract_id+plan_id alone (11,408 combos) ignores the year and collapses the time series.
  2. 2No is_current/version column and no snapshot duplication. Each payment_year was ingested from exactly ONE CMS file: 1 distinct bronze_publication_id, sha256_content_hash, and source_url per year (10 each), 1 parser_artifact_hash overall, all asserted 2026-05-18. So you can SELECT freely without a dedup filter — the year-grain is already unique. Do not GROUP BY or dedup on the provenance columns expecting per-row variation.
  3. 3avg_direct_subsidy_pmpm is legitimately NEGATIVE for ~6,200 rows (min -123.40). A negative value means CMS nets a recoupment because the plan's risk-adjusted basic-benefit bid is below the standardized benchmark — it is correct data, not an error. Do not clamp/abs() it.
  4. 40.0 in avg_reinsurance_pmpm and avg_low_income_cost_sharing_pmpm is a real value (a floor), not a missing value — all four value columns are 100% non-null. Whole plan types legitimately sit at 0 (POS Contractor, LI NET Sponsor, MSA have 0 reinsurance and 0 LICS; Employer-direct PDPs have 0 LICS). Filter on organization_type, not on value=0, to scope to real Part-D-bearing plans.
  5. 5asserted_at is the ingestion timestamp (single 2026-05-18 backfill), NOT the data's effective period — always use payment_year as the time dimension. Likewise contract_name and organization_type can change across years for the same contract_id (renames/recategorization), so join/label by year, not statically.
  6. 6All amounts are AVERAGES across the plan's enrollees expressed PMPM (per-member-per-month), already aggregated by CMS — they are not enrollment-weighted totals and there is no member-count column here, so you cannot reconstruct total dollars or weight a national average from this table alone.

Columns (14)

ColumnTypeMeaning
payment_yearintCMS Part D payment/contract year the row describes (the time dimension). Integer, observed 2015-2024 (10 years). One CMS source file per year; part of the grain key. NOTE: this is the payment year, not a publication date.
contract_idstringCMS plan-sponsor contract identifier (e.g. H0022, S5601, R3175, E0654). First letter encodes contract type: H = MA/MA-PD coordinated-care & PFFS/Demo/PACE (38,235 rows, 1,254 contracts), S = standalone PDP (9,560 rows, 82 contracts), R = Regional PPO (588 rows), E = Employer/Union direct-contract PDP (39 rows), X = Demo (33 rows). NOT unique alone (1,373 distinct); one contract holds many plans across many years.
plan_idstringZero-padded 3-digit plan/PBP segment within the contract (observed '001'-'879'). Identifies a specific benefit package under the contract. NOT unique on its own (only 521 distinct values; the same plan_id like '001' or '801' recurs across thousands of contracts) — it is only meaningful combined with contract_id. Together contract_id+plan_id = the plan entity (11,408 distinct), and +payment_year = the grain.
contract_namestringPlan-sponsor / parent organization name in upper case (e.g. 'BLUE CROSS OF CALIFORNIA', 'IBT VOLUNTARY EMPLOYEE BENEFITS TRUST'). 1,166 distinct. Functionally dependent on contract_id within a year (0 cases of one contract+plan+year mapping to >1 name) — a descriptive label, NOT a key. Can drift year-to-year as sponsors rename/merge.
organization_typestringPlan/contract type classification. 11 values observed: Local CCP (35,755 — MA-PD HMO/PPO, the bulk), PDP (9,560 — standalone Part D), National PACE (1,425), Regional CCP (588), Demo (485), 1876 Cost (290), PFFS (279), Employer/Union Only Direct Contract PDP (39), POS Contractor (20), LI NET Sponsor (13), MSA (1). Use this to filter to true Part-D-bearing plan kinds. Functionally dependent on contract+plan+year (never multivalued per group).
avg_direct_subsidy_pmpmdoubleAverage CMS Part D direct-subsidy payment, per-member-per-month USD, for the plan in that year. CAN BE NEGATIVE (observed min -123.40, e.g. Imperial Health Plan of CA 2022; max +325.93; mean ~21.36). Negative = CMS net recoups (plan's risk-adjusted bid for basic benefit is below the national average / standardized bid amount). Fully populated (0 nulls).
avg_part_d_risk_scoredoubleAverage Part D (RxHCC) risk-adjustment score for the plan's enrollees in that year. Range observed 0.0-5.551, mean ~1.06 (1.0 = average-risk beneficiary). Drives the direct-subsidy calculation. Fully populated. A 0.0 here is a genuine reported value for some small/new plans, not a null.
avg_reinsurance_pmpmdoubleAverage federal reinsurance subsidy PMPM (USD) — CMS share (historically ~80%) of catastrophic-phase drug costs above the threshold. Range 0.0-5,195.89 (extreme highs are real specialty/high-cost plans, e.g. H6745 2016). 0.0 is a true floor for plan types with no catastrophic reinsurance flow (POS Contractor, LI NET Sponsor, MSA are all 0). Fully populated (0 nulls).
avg_low_income_cost_sharing_pmpmdoubleAverage Low-Income Cost-Sharing (LICS) subsidy PMPM (USD) — CMS payment covering cost-sharing for LIS/dual-eligible members. Range 0.0-635.51. 0.0 is a true floor (Employer-direct PDP, POS Contractor, LI NET, MSA are all 0; Demo plans always positive). Fully populated (0 nulls).
source_urlstringProvenance: the CMS download URL of the year's payment-data zip (e.g. .../downloads/2016-payment-data.zip). Exactly one URL per payment_year (10 distinct total). Identifies the official source file, not a per-row link.
sha256_content_hashstringProvenance: SHA-256 of the ingested source file content. Exactly 1 distinct value per payment_year (10 total) — every row from the same year shares one hash. Use to detect upstream file changes / confirm immutability of a year's load.
bronze_publication_idstringProvenance: internal bronze-layer publication identifier (e.g. 'ma_plan_payment_2016_19614634'). One distinct value per payment_year (10 total). Ties the silver row back to its bronze ingestion batch. Looks key-ish but is just the per-year load id.
asserted_attimestampProvenance: timestamp the row was asserted into the silver table (timestamp with tz). 10 distinct values, all on 2026-05-18 (19:34-19:36 UTC) — one assertion event per year-file in a single backfill load. This is an ingestion/processing time, NOT the data's effective date; do not use it as the time dimension (use payment_year).
parser_artifact_hashstringProvenance: hash of the parser code/artifact version that produced the rows. Exactly 1 distinct value across the whole table — all rows parsed by the same parser version. Use to detect parser changes on future reloads.

Query it

describe_data_source(source_fqn="medicare_silver.ma_plan_payment_part_d")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.ma_plan_payment_part_d WHERE payment_year = '<value>' AND contract_id = '<value>' AND plan_id = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keycontract_id
Joinhow_to_join(source_fqn="medicare_silver.ma_plan_payment_part_d")
Raw doorAthena (read-only via query_substrate): medicare_silver.ma_plan_payment_part_d

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