VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

MA pbp mrx gap coverage

medicare_silver.pbp_mrx_gap_coverage·partial·silver·annual·Part D Plans & Formularies
Rows
N/A
Columns
31
History
2018-01-01 to 2024-01-01
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A long-form record of Medicare Advantage / Part D plan drug benefits during the coverage gap ("donut hole"), parsed from CMS's quarterly Plan Benefit Package (PBP) Public Use Files.
  2. 2Each row describes, for one plan in one annual benefit year (as captured in one quarterly CMS snapshot), one Part D formulary tier and its gap-phase coverage attributes (drug-type code, human tier label, and a gap cost-share indicator).
  3. 3It is the cost-data substrate for answering "what does plan X cover in the gap, and on which tiers" across the MA/PDP market 2018-2024.

Grain

KeyGrain
contract_idplan_idsegment_idreport_yearquartermrx_tier_idmrx_tier_gap_cost_shareasserted_at
  1. 1One row per (plan-segment x benefit year x CMS quarterly snapshot x formulary tier x gap-cost-share indicator x ingestion batch).
  2. 2In 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.
  3. 3The 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.
  4. 4Dropping 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 read it

  1. 1For 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).
  2. 2This answers "how is each Part D plan's coverage-gap (donut-hole) benefit structured by formulary tier" for that year.
  3. 3Always 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

  1. 1NO 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.
  2. 2Multiple 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.
  3. 3mrx_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.
  4. 4Five 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.
  5. 5bid_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.
  6. 6mrx_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.
  7. 7Demo 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.
  8. 8parse_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).

Columns (31)

ColumnTypeMeaning
contract_idstringCMS 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_idstringPlan 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_idstringPlan 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.
versiondoubleCMS 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_yeardoubleBenefit/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.
quarterdoubleQuarter 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_idstringConcatenation 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_typestringCMS 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_sharestringGap-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_idstringFormulary 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_liststringHuman-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).
orgtypestringCMS 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_covstringPBP 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_typestringCMS 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_perioddateDate = 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_atdateIngestion/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_startdateBenefit-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_enddateBenefit-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_sequenceintCMS-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_revisionintParser revision number for this ingestion (int). CONSTANT = 2 for every row. Provenance metadata; carries no analytic signal currently.
parser_artifact_hashstringSHA-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_urlstringCMS 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_hashstringSHA-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_ynstringALWAYS 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_ynstringALWAYS NULL across all 516,458 rows. Schema placeholder ('gap covers all drugs Y/N') never populated. Carries no data.
mrx_tier_gap_drug_type_lmtdstringALWAYS NULL across all 516,458 rows. Schema placeholder ('gap coverage limited by drug type') never populated. Carries no data.
mrx_tier_gap_excluded_only_ynstringALWAYS NULL across all 516,458 rows. Schema placeholder ('gap covers excluded-only drugs Y/N') never populated. Carries no data.
mrx_tier_lblstringALWAYS 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_demostringY/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_demostringY/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_ordinaldouble

Query it

describe_data_source(source_fqn="medicare_silver.pbp_mrx_gap_coverage")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.pbp_mrx_gap_coverage WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND report_year = '<value>' AND quarter = '<value>' AND mrx_tier_id = '<value>' AND mrx_tier_gap_cost_share = '<value>' AND asserted_at = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keycontract_id,plan_id,segment_id,version,mrx_tier_id,report_year,quarter
Joinhow_to_join(source_fqn="medicare_silver.pbp_mrx_gap_coverage")
Raw doorAthena (read-only via query_substrate): medicare_silver.pbp_mrx_gap_coverage
NoteGrain-governed; live in Glue / query_substrate. Source family pbp_benefits.

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