# MA plan payment — Part D (plan x year)

`medicare_silver.ma_plan_payment_part_d` · Medicare Advantage, Part D, Medicaid · status live · 48,455 rows

CMS Part D plan-payment reference: per plan+year averages of the federal subsidies CMS pays each MA-PD / standalone PDP (avg_direct_subsidy, avg_reinsurance, avg_low_income_cost_sharing) plus the plan's average Part D risk score (avg_part_d_risk_score).

**Grain.** One row per Medicare plan per payment year — uniquely keyed by (payment_year, contract_id, plan_id). Verified: 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. The profiler's 6-column 'verified_unique_by' is an inflated superset.

**How to use it.** Trend a specific drug plan's federal subsidies over time, or benchmark a sponsor against peers. Example: 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. To compare plan types in one year, add WHERE payment_year=2024 and GROUP BY organization_type with AVG() over the value columns.

**Traps.**
- Grain 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.
- No 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.
- avg_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.
- 0.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.
- asserted_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.
- All 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.

| column | type | meaning |
|---|---|---|
| payment_year | int | CMS 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_id | string | CMS 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_id | string | Zero-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_name | string | Plan-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_type | string | Plan/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_pmpm | double | Average 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_score | double | Average 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_pmpm | double | Average 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_pmpm | double | Average 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_url | string | Provenance: 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_hash | string | Provenance: 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_id | string | Provenance: 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_at | timestamp | Provenance: 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_hash | string | Provenance: 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: `query_substrate` with `SELECT ... FROM medicare_silver.ma_plan_payment_part_d WHERE ... LIMIT 100`; read `describe_data_source("medicare_silver.ma_plan_payment_part_d")` first.