# MA pbp mrx gap coverage

`medicare_silver.pbp_mrx_gap_coverage` · Medicare Advantage, Part D, Medicaid · status partial

Part D coverage-gap detail keyed by the source-native MRx tier identifier. Folded into pbp_mrx in 2026. Coverage: 2008-2025.

**Grain.** One row per (plan-segment x benefit year x CMS quarterly snapshot x formulary tier x gap-cost-share indicator x ingestion batch). In plain words: for a given plan segment in a given report_year, as published in a given CMS quarterly PUF vintage, each Part D drug tier gets one or more rows distinguished by the gap cost-share code — and because two ingestion runs loaded overlapping snapshots, asserted_at must be included to make the key exact. The minimal VERIFIED unique key is (contract_id, plan_id, segment_id, report_year, quarter, mrx_tier_id, mrx_tier_gap_cost_share, asserted_at) = 516,458 rows, 0 duplicates. Dropping asserted_at collapses to 452,545 groups (63,913 logical records were loaded twice, all from the 2021-Q1 and 2022-Q1 files); dropping mrx_tier_gap_cost_share further collapses because a single tier can carry multiple gap cost-share rows.

**How to use it.** For benefit year 2024, list each plan's tiers and gap cost-share variants while taking a single snapshot to avoid double counting: SELECT contract_id, plan_id, segment_id, mrx_tier_id, mrx_tier_label_list, mrx_tier_gap_cost_share FROM medicare_silver.pbp_mrx_gap_coverage WHERE report_year=2024 AND quarter=(SELECT MAX(quarter) FROM medicare_silver.pbp_mrx_gap_coverage WHERE report_year=2024) AND asserted_at=(SELECT MAX(asserted_at) FROM medicare_silver.pbp_mrx_gap_coverage). This answers "how is each Part D plan's coverage-gap (donut-hole) benefit structured by formulary tier" for that year. Always pin one quarter per report_year and one asserted_at to avoid snapshot/re-ingest double-counting; join to sibling PBP tables on (contract_id, plan_id, segment_id, report_year) for fuller plan context.

**Traps.**
- NO is_current column and the table is NOT deduped: 63,913 logical records were loaded in BOTH ingestion batches (asserted_at 2026-05-20 and 2026-05-25), all from the 2021-Q1 and 2022-Q1 source files. You will double-count 2021/2022 unless you pick ONE asserted_at (e.g. MAX(asserted_at)) or include asserted_at in your dedup. Total 516,458 rows vs only 452,545 logical records.
- Multiple quarterly snapshots per benefit year: the same report_year is republished in several quarters (e.g. 2018 has Q2/Q3/Q4). To get ONE benefit-year view per plan, also pick one quarter per report_year (e.g. the latest), or you triple-count plans. quarter is a snapshot label, not just a calendar field.
- mrx_tier_gap_cost_share looks like a duplicate-causing nuisance but is REAL grain: a single (plan, year, quarter, tier) can have several rows differing only by this code (NULL/'1'/'2'). Do not collapse it away blindly. NULL (~86%) is the normal 'no special gap cost-share' case, not missing data.
- Five columns are 100% NULL and carry zero information: mrx_tier_alt_gap_covg_excl_yn, mrx_tier_gap_all_drug_yn, mrx_tier_gap_drug_type_lmtd, mrx_tier_gap_excluded_only_yn, mrx_tier_lbl. They are empty schema placeholders — never filter or join on them.
- bid_id is NOT an independent key — it is exactly contract_id + '_' + plan_id + '_' + segment_id (verified 516,458/516,458). Likewise observed_period / effective_period_start / effective_period_end are all derived from report_year (Jan 1 / Jan 1 / Dec 31). Including any of these does not refine the grain.
- mrx_tier_drug_type is a coarse code (01/10/11) that does NOT cleanly name the tier — the same code spans many different tier names. Use mrx_tier_label_list for the human tier name; mrx_tier_id is just the tier number (1-7), also not self-describing.
- Demo flags are year-scoped: part_d_enhncd_cvrg_demo exists only for 2021-2022 and part_d_model_demo essentially only 2021; NULL elsewhere means 'demo not in effect that year', not 'no'. cms_vintage_sequence is not a reliable clean key either — it disagrees with quarter for the re-ingested 2021/2022 rows.
- parse_revision is a constant (2) and version (1-26) is a CMS bid-version attribute, not a snapshot selector — neither is needed in the grain key and neither should be used to pick 'the current' record (use asserted_at + quarter for that).

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare contract ID (e.g. H0022). 902 distinct. Part of the plan key. Letter prefix indicates contract type (H=MA/MA-PD local, R=Regional PPO, S=standalone PDP, etc.). Always populated. |
| plan_id | string | Plan ID within the contract (3-char, e.g. '001', zero-padded). 452 distinct values, NOT unique on its own. Part of the plan key. Always populated. |
| segment_id | string | Plan segment/sub-area ID. '0' = non-segmented plan (461,438 rows, ~89%); 1-7+ are geographic service-area segments of a segmented plan. Part of the plan key; do NOT drop it. Always populated. |
| version | double | CMS PBP bid record version number (double, 1-26, 23 distinct). The plan's bid submission version within that PUF release. NOT needed for the grain (the key is unique without it); only 30 grain-groups ever carry >1 version. A plan-bid-versioning attribute, not a snapshot discriminator. |
| report_year | double | Benefit/contract year the record applies to (double, 2018-2024, 7 values). Drives observed_period, effective_period_start (Jan 1) and effective_period_end (Dec 31). This is the real time dimension; periodicity is annual. |
| quarter | double | Quarter of the CMS PUF release that this snapshot came from (double, 1-4). The same report_year appears in multiple quarterly releases (republished benefit data). Part of the grain because it labels which quarterly vintage. CAUTION: mostly 1:1 with cms_vintage_sequence but they disagree for the re-ingested 2021/2022 records. |
| bid_id | string | Concatenation contract_id + '_' + plan_id + '_' + segment_id (e.g. 'H0022_001_0'). VERIFIED redundant: equals that concat in 516,458/516,458 rows. A convenience join key, NOT an independent identifier — do not treat as adding grain. Always populated. |
| mrx_tier_drug_type | string | CMS PBP code for the drug type of the tier (string: '11' ~64%, '01' ~32%, '10' ~4%). Roughly 01=generic-class, 10=brand-class, 11=mixed/other-catch-all. NOT a clean label — the same code maps to many different tier names (code '11' alone spans Preferred Generic, Brand, Specialty, etc.). Use mrx_tier_label_list for the human name. |
| mrx_tier_gap_cost_share | string | Gap-phase cost-share indicator code for the tier. Only 3 values: NULL (441,858, ~86%), '1' (61,839), '2' (12,761). NULL is the common case (no special gap cost-share row); '1'/'2' denote distinct gap cost-share variants of the SAME tier. This column is PART OF THE GRAIN — a single tier can produce multiple rows that differ only here. It is a small enum/flag, not a dollar or percent amount. |
| mrx_tier_id | string | Formulary tier number within the plan (string '1'-'7'). 1-5 are the standard tiers (dominant); 6 = ~29.5K rows; 7 = rare (79 rows). Part of the grain. Always populated. Tier semantics (preferred generic / generic / preferred brand / non-preferred / specialty) are conveyed by mrx_tier_label_list, not by the number alone. |
| mrx_tier_label_list | string | Human-readable tier name as filed by the plan (e.g. 'Generic Drugs', 'Preferred Brand', 'Specialty Tier', 'Vaccines ($0 cost sharing)'). Free-text-ish, high cardinality, plan-authored. 100% populated. This is the descriptive label to surface to users; it is functionally determined by the grain key (does not subdivide it). |
| orgtype | string | CMS organization type code (string, e.g. '01' local CCP/MA, '10' standalone PDP, '11' Regional PPO, '05' PACE, '04'/'06' cost/other). 100% populated. Classifies the offering org; functionally determined by the plan key. |
| pbp_a_ben_cov | string | PBP Section A benefit-coverage indicator (string '1' = 431,413, '2' = 401, NULL = 84,644). NULL is meaningful: it lines up exactly with the standalone-PDP rows (orgtype '10' / plan_type '29'), which have no Part A/B medical benefit section. Functionally determined by the plan key. |
| pbp_a_plan_type | string | CMS PBP plan-type code (string). Top values: '01' HMO (245K), '04' Local PPO (128K), '29' standalone-PDP-type (85K), '02' HMOPOS (48K), '31' Regional PPO, '48'/'49' PACE, '09' cost plan, '18' other. 100% populated. Classifies plan structure; functionally determined by the plan key. |
| observed_period | date | Date = Jan 1 of report_year (one value per year). Fully derived from report_year; carries no extra information beyond the benefit year start. Not part of the minimal grain (report_year already encodes it). |
| asserted_at | date | Ingestion/load date into the silver layer (date). Only TWO values: 2026-05-20 (452,545 rows, main batch) and 2026-05-25 (63,913 rows, a re-ingest of the 2021-Q1 and 2022-Q1 files). REQUIRED in the grain key to disambiguate the duplicated logical records — there is no is_current flag, so both copies persist. This is the snapshot/dedup discriminator. |
| effective_period_start | date | Benefit-year start = Jan 1 of report_year (date, 2018-2024). The profiler's nominal time column. Derived from report_year; one value per year. |
| effective_period_end | date | Benefit-year end = Dec 31 of report_year (date). Derived from report_year; one value per year. Confirms each row applies to a full calendar benefit year. |
| cms_vintage_sequence | int | CMS-assigned snapshot ordinal for the release (int, 1-4). Usually equals quarter, but NOT always: for the re-ingested 2021/2022 records the same quarter maps to two different vintage_sequence values (quarter=1 -> seq 1 and 4 in 2021; seq 1 and 2 in 2022). Treat as a vintage label, not a clean key; the grain uses quarter + asserted_at instead. |
| parse_revision | int | Parser revision number for this ingestion (int). CONSTANT = 2 for every row. Provenance metadata; carries no analytic signal currently. |
| parser_artifact_hash | string | SHA-256 of the parser artifact/code that produced the row (string). Provenance; functionally determined by the snapshot. Same hash across the sample (one parser build). |
| source_url | string | CMS download URL of the source PBP PUF zip (e.g. .../pbp-pufs/pbp-benefits-2018-q3.zip). Provenance — directly ties each row to a specific quarterly CMS file. One URL per (report_year, quarter) snapshot. |
| sha256_content_hash | string | SHA-256 content hash of the downloaded source file (string). Provenance/integrity of the raw CMS zip. Functionally determined by the snapshot. |
| mrx_tier_alt_gap_covg_excl_yn | string | ALWAYS NULL across all 516,458 rows. Schema placeholder (intended 'alternative gap coverage excluded Y/N') that was never populated. Carries no data. |
| mrx_tier_gap_all_drug_yn | string | ALWAYS NULL across all 516,458 rows. Schema placeholder ('gap covers all drugs Y/N') never populated. Carries no data. |
| mrx_tier_gap_drug_type_lmtd | string | ALWAYS NULL across all 516,458 rows. Schema placeholder ('gap coverage limited by drug type') never populated. Carries no data. |
| mrx_tier_gap_excluded_only_yn | string | ALWAYS NULL across all 516,458 rows. Schema placeholder ('gap covers excluded-only drugs Y/N') never populated. Carries no data. |
| mrx_tier_lbl | string | ALWAYS NULL across all 516,458 rows. Schema placeholder (an alternate tier-label field) never populated. Use mrx_tier_label_list for the tier name instead. |
| part_d_enhncd_cvrg_demo | string | Y/N flag for the Part D Enhanced/Senior-Savings-Model demonstration. Populated ONLY for report_year 2021 (61,236) and 2022 (97,752); 100% NULL for all other years. Y=59,826 / N=97,948 among populated. NULL outside 2021-2022 means 'not applicable / pre-demo', not 'no'. |
| part_d_model_demo | string | Y/N flag for the broader Part D payment-model demonstration. Populated almost only in 2021 (~1,214 rows total non-null). Y=88 / N=818 among populated, NULL otherwise. A rare niche flag; NULL is the overwhelming default and means not-applicable. |
| source_row_ordinal | double |  |

Query it: `query_substrate` with `SELECT ... FROM medicare_silver.pbp_mrx_gap_coverage WHERE ... LIMIT 100`; read `describe_data_source("medicare_silver.pbp_mrx_gap_coverage")` first.