MA pbp sas schema

What it is

1. 1A schema/data-dictionary (metadata) table for CMS Medicare Advantage / Part D Plan Benefit Package (PBP) Public Use Files.
2. 2Each row defines ONE column in ONE source PBP PUF table for ONE annual release/quarterly vintage: its name, SAS type (CHAR/NUM), byte length, and ordinal position.
3. 3It is the layout key used to parse and interpret the actual pbp_* benefit tables (e.g. pbp_b16_dental, pbp_mrx_tier, pbp_b13_other_services) that carry the real MA/Part-D plan cost-sharing values.
4. 4It contains NO benefit/cost values itself — it is metadata describing the structure of the cost data.

Grain

How to read it

1. 1To parse a PBP benefit PUF, fetch its column layout for a given year: SELECT column_position, column_name, column_type, column_length FROM medicare_silver.pbp_sas_schema WHERE table_name='pbp_b16_dental' AND report_year=2023 ORDER BY quarter, column_position.
2. 2This returns the exact ordered field list (e.g. pbp_a_hnumber CHAR(5) at pos 1, pbp_b16a_bendesc_amo_oe CHAR(1) ...) so you can correctly read/interpret the dental cost-sharing columns in the actual pbp_b16_dental benefit table.
3. 3Answers: 'what columns, in what order, of what type/width does this PBP table have for this benefit year?'

Traps

1. 1This is a DATA DICTIONARY, not data. It contains zero benefit/cost values — only column layouts. To get actual MA/Part-D plan cost-sharing you must query the described pbp_* benefit tables; use this table to know which column is which (name, position, CHAR/NUM type, length).
2. 2Minimal grain needs all four of (report_year, quarter, table_name, column_name); none alone is a key. column_name is NOT globally unique (header fields like pbp_a_hnumber/bid_id/segment_id/version repeat across 33-46 tables), and table_name+column_name without (year,quarter) collapses ~80k rows to ~41k. quarter is load-bearing.
3. 3table_name aliasing: the same logical PBP section appears under two naming schemes across years — older short names with a populated input_filename (pbp_b13, pbp_b16, planarea.txt) and newer descriptive names with NULL input_filename (pbp_b13_other_services, pbp_b16_dental, planarea). A naive GROUP BY table_name double-counts the same section.
4. 4NOT a snapshot/is_current table — there is no is_current column and no dedup is needed. The two ingestion batches (asserted_at 2026-05-15 / parse_revision 1, and 2026-05-20 / parse_revision 2) are disjoint on the grain key (0 overlap); the older batch holds 2001-2026 annual/Q1 baselines, the newer holds 2018-2024 quarterly reissues. Use all rows.
5. 5input_filename is NULL for 66,562 of 80,409 rows (~83%) — do not treat it as an identity or join key. Use table_name (with the aliasing caveat) instead.
6. 6cms_vintage_sequence is a per-year release ORDINAL, not the quarter and not a snapshot version — Q1 rows scatter across vintage 1/2/4. observed_period, effective_period_start, and effective_period_end are all 1:1 redundant derivations of report_year (calendar-year benefit period).
7. 7History is lopsided: 2001-2017 carry only ~8 coarse tables/year (the single annual file, quarter=1); coverage explodes from 2018 (33-39 tables/year) with quarterly reissues. 2025-2026 are partial (11 tables). Don't expect a given pbp_* table's layout to exist in early years.

Columns (17)

Query it

describe_data_source(source_fqn="medicare_silver.pbp_sas_schema")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.pbp_sas_schema WHERE report_year = '<value>' AND quarter = '<value>' AND table_name = '<value>' AND column_name = '<value>' LIMIT 100")