VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

MA pbp changelog

medicare_silver.pbp_changelog·partial·silver·"annual"
Rows
N/A
Columns
16
History
"2001 (report_year 2001; effective_period_start 2001-01-01)" to "2026 (report_year 2026; effective_period_start 2026-01-01). 26 annual benefit years, one calendar year apart (avg gap ~365 days)."
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1"A parsed, structured version of the CMS Medicare Advantage / Part D Plan-Benefit-Package (PBP) Benefits README + CHANGE-LOG text files that ship inside each year's PBP Benefits ZIP.
  2. 2Each row is one line of that documentation — file<->SAS pairings, section descriptions, header prose, and the actual schema-change events (variables added/removed, column-length changes, files removed).
  3. 3It is a documentation / schema-evolution log used to interpret how the PBP source-file layout changed year over year, NOT a benefits or cost-amount fact table."

Grain

KeyGrain
report_yearquarterchange_seqcms_vintage_sequence
  1. 1"One row per parsed line of a CMS PBP-Benefits README/CHANGE-LOG document, per source-file vintage.
  2. 2The minimal verified unique key is (report_year, quarter, change_seq, cms_vintage_sequence) = exactly 4,650 distinct combinations == 4,650 rows. change_seq is a per-document line ordinal that resets for each (report_year, quarter, cms_vintage_sequence), so all four columns are required;
  3. 3(report_year, quarter, change_seq) alone is only 4,397/4,650.
  4. 4Equivalently (report_year, source_url, change_seq) is also unique."

How to read it

  1. 1"To find every PBP schema change in a given benefit year, filter to the event categories and the latest vintage, e.g.: SELECT report_year, change_category, file_name, field_name, change_description FROM medicare_silver.pbp_changelog WHERE report_year = 2026 AND cms_vintage_sequence = (SELECT max(cms_vintage_sequence) FROM medicare_silver.pbp_changelog WHERE report_year = 2026) AND change_category IN ('variable_added','variable_removed','column_length_change','file_removed') ORDER BY change_seq; -- answers: which PBP/MRX fields were added, dropped, or resized for CY2026 (e.g.
  2. 2PBP_A_SNP_COND length 23 -> 33), so you know how the upstream PBP source layout shifted before parsing the benefit facts."

Traps

  1. 1NO is_current / latest flag and no dedup column. Multiple CMS source ZIPs (vintages) per year are ALL retained as separate rows, so the same logical README line appears once per vintage. For the 2018-2024 years (2-4 vintages each) a naive COUNT or GROUP BY report_year double/quadruple-counts. To get the latest view per year, filter to MAX(cms_vintage_sequence) per report_year (or join on the newest source_url/sha256_content_hash).
  2. 2change_seq looks like a key but is NOT — it is a 1-based line ordinal that RESETS inside every (report_year, quarter, cms_vintage_sequence) document (max change_seq == that document's row count). (report_year, quarter, change_seq) alone is only 4397/4650 unique. The MINIMAL verified key is (report_year, quarter, change_seq, cms_vintage_sequence) = 4650/4650. Equivalent natural key (report_year, source_url, change_seq) works too; but (source_url, change_seq) or (sha256_content_hash, change_seq) WITHOUT report_year/quarter is only 4525 because one 2022 ZIP is loaded under two quarters.
  3. 3This is NOT a benefit/cost-amount table and contains NO plan, contract, premium, copay, or dollar values. It is the parsed CMS README + CHANGE-LOG shipped inside each PBP Benefits ZIP — a documentation/schema-evolution log. ~98% of rows (txt_sas_pairing + file_description + description + policy_note = 4533) are README boilerplate (file<->SAS pairings, section descriptions, header prose). Only 117 rows are real schema-change events (variable_added/removed, column_length_change, file_removed). To analyze actual PBP schema drift, filter change_category to those four event types and read field_name + change_description.
  4. 4Empty strings '' are used instead of NULL for file_name and field_name and they CARRY MEANING: field_name is populated ONLY for variable_added/variable_removed/column_length_change (115 rows); file_name is '' for description/policy_note/column_length_change. Predicates must test = '' not IS NULL.
  5. 5All five date columns are DERIVED from report_year, not real event dates: observed_period == effective_period_start == Jan 1 of report_year (0 rows differ), effective_period_end == Dec 31 of report_year. They bracket the benefit calendar year and tell you nothing about when a change actually happened. asserted_at is the ingestion date (May 2026) and parse_revision/parser_artifact_hash/sha256_content_hash are pipeline-provenance, NOT content time. Do not use any of them as a 'change occurred on' date.
  6. 6Sibling/scope caveat: this is the PBP-Benefits documentation changelog only (covers the PBP_*/MRX_* section files and their SAS programs). It is metadata ABOUT the PBP source files, distinct from the actual parsed benefit/cost fact tables in medicare_silver. Use it to interpret schema changes across PBP vintages (e.g. a field's storage length changed 5->4 in 2026), not to retrieve plan benefits.

Columns (16)

ColumnTypeMeaning
report_yearintThe CMS Plan-Benefit-Package (PBP) contract/benefit year that the source README/changelog document describes (e.g. 2024). int. Range 2001-2026 across 26 years. All the date columns are derived purely from this. This is the primary partition/filter.
quarterintThe CMS quarterly publication slot (1-4) the parsed source ZIP was filed under for that year. Part of the grain. NOTE it is NOT a clean publication-quarter: for 2001-2017 and 2025-26 every row is quarter=1 (only one annual ZIP existed); for 2018-2024 it tracks the quarterly re-release. One known quirk: the 2022 quarter-2.zip is loaded under BOTH quarter=1/vintage=1 and quarter=2/vintage=2.
change_categorystringType of line parsed from the CMS README/CHANGE-LOG text. 8 values: txt_sas_pairing (2163, README .txt<->.sas file manifest), file_description (2080, one-line description of each PBP section file), description (272, free-text README header prose), variable_added (85), column_length_change (23), policy_note (18), variable_removed (7), file_removed (2). Only the last group of event types (variable_added/removed, column_length_change, file_removed = 117 rows, ~2.5%) are actual schema-change events; the other ~98% is README boilerplate.
file_namestringThe PBP section file the row refers to (e.g. 'pbp_b2_snf.txt', 'pbp_b1a_inpat_hosp.txt'). Populated for txt_sas_pairing, file_description, variable_added/removed, file_removed. EMPTY STRING '' (not NULL) for category=description, policy_note, and column_length_change (those carry the field in field_name instead, or are header prose with no file).
field_namestringThe PBP/MRX variable (uppercase column name, e.g. 'PBP_B14E_COPAY_EHC', 'MRX_ALT_RED_COST_SHARING_ITEMS') affected by the change. Populated ONLY for variable_added, variable_removed, and column_length_change (115 of 4650 rows). EMPTY STRING '' for all other categories. This + change_category is how you find which PBP fields were added/dropped/resized in a given year.
change_descriptionstringThe raw parsed text of the changelog/README line. For event rows it carries the detail (e.g. 'length 5 -> 4', the added-variable note); for txt_sas_pairing it is 'paired with <name>.sas'; for file_description it is the section description; for description/policy_note it is verbatim README prose. The human-readable payload of the row.
change_seqint1-based line/sequence ordinal of the row WITHIN its (report_year, quarter, cms_vintage_sequence) document. Range 1-254. It RESETS for every source document (max change_seq == row count of that document, e.g. 168 for each 2024 vintage), so it is NOT a global key and is meaningless without the year/quarter/vintage context. Preserves the original line order of the README.
observed_perioddatedate. Always identical to effective_period_start (0 rows differ) = Jan 1 of report_year. Derived field, carries no independent information; it is the bitemporal 'observed period' stamp set to the benefit year start.
asserted_atdatedate. Bitemporal 'when this row was loaded/asserted into the warehouse' — the ingestion timestamp, NOT content time. Only 3 distinct values, all in May 2026 (2026-05-15 x2341, 2026-05-20 x2056, 2026-05-25 x253), correlating with parse_revision (the 2341 on 05-15 == parse_revision=1 count). Use for provenance/run-tracking, never as event time.
effective_period_startdatedate. Jan 1 of report_year (e.g. 2024-01-01). Derived from report_year; marks the start of the benefit-year coverage window. Not an actual change date.
effective_period_enddatedate. Dec 31 of report_year (e.g. 2024-12-31). Derived from report_year; the end of the benefit-year coverage window. Together with effective_period_start it just brackets the calendar year — it does NOT pinpoint when a schema change occurred.
cms_vintage_sequenceintint 1-4. Per-report_year ordinal of the distinct CMS source ZIP that produced the row (1 = first/earliest release that year, then 2,3,4 for quarterly updates). REQUIRED disambiguator in the grain. Strictly 1:1 with source_url within a year (1 tiny exception around the 2022 quarter-2.zip cross-mapping). Years 2001-2017,2025,2026 have only vintage=1; 2018-2024 have 2-4 vintages. This is the reliable 'which CMS publication' key.
parse_revisionintint, values 1 (2341 rows) or 2 (2309 rows). A re-parse/run marker for the ingestion pipeline (which version of the parse run produced the row). CONSTANT within the grain key (0 groups have >1 value), so it is provenance metadata, NOT part of the grain. Correlates with asserted_at.
parser_artifact_hashstringstring sha256. Hash of the parser code/artifact that generated the row (the lineage of HOW it was parsed). 40 distinct values — roughly one per source document (vs 41 source files), i.e. the parser version used per ZIP. Provenance/reproducibility, not analytical content.
source_urlstringstring. The CMS.gov download URL of the PBP Benefits ZIP this row was parsed from (e.g. 'https://www.cms.gov/files/zip/pbp-benefits-2022-updated-07012022.zip'). 41 distinct URLs, never reused across report_years. 1:1 with cms_vintage_sequence within a year. The upstream provenance pointer.
sha256_content_hashstringstring. sha256 of the downloaded source-file CONTENT (the ZIP/README bytes), used for content-addressed dedup and change detection. 41 distinct values, 1:1 with source_url. Same hash never spans report_years; one hash spans 2 quarters (the 2022 quarter-2.zip dual-load). Use to detect whether CMS republished identical vs changed content.

Query it

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

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