# MA pbp field dictionary

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

Parsed PBP{YEAR}_dictionary.xls/xlsx. 26K rows / year mapping (file, field) → (type, length, service category, codes). Substrate-as-self-documenting layer.

**Grain.** One row per (report_year, file/PBP-section, field name, code value) within a single CMS publication vintage. Two physical archetypes share the table: ~268,159 "field header" rows (carry type/length/title, one per field+code) and ~386,180 pure "code-enumeration" rows (title/type NULL, carry just codes/code_values for the field's remaining allowed values). 654,345 total rows. The MINIMAL verified-unique key is (report_year, quarter, file, name, codes, code_values, cms_vintage_sequence, field_title) = exactly 654,345 distinct. There is no single natural PK: the same field name can appear twice in one file/year with different field_title/title (e.g. pbp_a_ben_cov is both "Coverage Criteria" and "Enrollee Type:"), which is why both code_values AND field_title are needed on top of the obvious (year,quarter,file,name,codes,vintage) key — dropping either reintroduces 94 duplicate groups (188 rows).

**How to use it.** Decode a coded PBP field for a given benefit year. Example: SELECT codes, code_values FROM medicare_silver.pbp_field_dictionary WHERE report_year=2026 AND name='pbp_a_ben_cov' AND cms_vintage_sequence=(SELECT max(cms_vintage_sequence) FROM medicare_silver.pbp_field_dictionary WHERE report_year=2026 AND name='pbp_a_ben_cov') AND codes IS NOT NULL — answers "what does each value of the pbp_a_ben_cov (Coverage Criteria) column in pbp_section_a mean in 2026" (1 -> 'Part A and Part B', 2 -> 'Part B Only'), so you can label the raw codes stored in the pbp_section_* benefit tables.

**Traps.**
- This is a DICTIONARY/codebook, not benefit data. It carries NO plan, contract (H-number), premium, or dollar values — it describes the COLUMNS of the sibling pbp_section_* / pbp_step_* fact tables. To decode a coded value in those tables you join on (file->table, name, codes) and read code_values, scoped to the matching report_year (and ideally the same cms_vintage_sequence).
- No is_current flag and the same report_year can have up to 4 coexisting vintages (cms_vintage_sequence 1-4 = quarterly CMS republications, 2018-2024). If you query a coded field for a year without filtering to MAX(cms_vintage_sequence) per report_year you will get duplicate/old definitions. quarter loosely tracks vintage but is unreliable (2021 has 2 vintages both quarter=1).
- name is NOT a unique field identifier within a year/file: the same name can hold two different meanings with different field_title/title (e.g. pbp_a_ben_cov is both 'Coverage Criteria' and 'Enrollee Type:'). That is why the true unique key needs BOTH code_values AND field_title on top of (report_year,quarter,file,name,codes,cms_vintage_sequence); dropping either reveals 94 duplicate groups.
- Two row archetypes are mixed: ~268K 'header' rows carry type/length/title/field_title (one per field, with its first code), and ~386K pure code rows carry only codes/code_values with title/type/length NULL. So NULL type/title does NOT mean a missing field — it means that row is an additional code value of a field defined on another (header) row. To list fields, filter title IS NOT NULL; to enumerate a field's allowed values, take all rows for that (year,file,name).
- codes is a dirty code 'system': the same logical code appears as '1' and float-cast '1.0' in different years, label text is not normalized ('Part B Only' vs 'Part B only'), and some codes are ratio strings like '1 in 2' / '1 in 3' (per-period cost-share), not integers. Match defensively across years.
- service_category_derived is 100% NULL (dead column). service_category (7% filled) and json_question (8% filled) are sparse/recent-only. observed_period and effective_period_start are exact duplicates of each other and both equal Jan-1-of-report_year, so all three are redundant. asserted_at is the May-2026 pipeline load date, NOT a CMS date. The file column also contains ~1,129 junk rows ('planArea.txt', 'accompany this PBP submission.', empty) from parser leakage.

| column | type | meaning |
|---|---|---|
| report_year | int | CMS PBP benefit/contract year the field definition applies to. INT, 2001-2026, 26 distinct values. Primary time dimension; always equals year(effective_period_start) and year(observed_period) (0 mismatches). Field schema grows over time: ~5,000-6,200 distinct field names across ~65-79 files per recent year. |
| quarter | int | CMS quarterly republication index within the report_year, INT 1-4. Part of the grain (dropping it loses ~22K rows). NOT the calendar quarter of validity — it tracks which quarterly PBP PUF release this definition came from. Pre-2018 years are single-release (quarter=1 only); from 2018 on, multiple quarters appear. Caveat: not perfectly aligned to vintage — e.g. 2021 has 2 vintages both labeled quarter=1 (sourced from updated-07012021.zip vs updated-08022021.zip). |
| file | string | The PBP section/sub-file the field belongs to, e.g. pbp_Section_A, pbp_Section_D, pbp_b1a_inpat_hosp, pbp_b13_other_services, pbp_b16_dental. 129 distinct values. Maps (after normalization) to the sibling fact tables pbp_section_a, pbp_section_b1a_inpat_hosp, pbp_section_d, etc. GOTCHA: mixed casing/prefix (pbp_Section_A vs pbp_b1a... and the actual table is pbp_section_b1a_inpat_hosp), and it is finer-grained than the tables (pbp_Section_C_1, pbp_b13_other_services_2, pbp_b13h_other_services_1 are sub-files unioned into one table). Also contains 1,129 junk/non-standard rows (file='planArea.txt', 'accompany this PBP submission.', or empty) from parser leakage / plan-area files. |
| name | string | The machine field/column name as it appears in the PBP data tables, e.g. pbp_a_hnumber, pbp_a_ben_cov, segment_id, pbp_b1a_bendesc_yn. 11,250 distinct. This is the JOIN key from a PBP fact table column to this dictionary. A field name is repeated across many rows (one per allowed code value, plus collisions across files/years). |
| type | string | SAS-style storage type of the field: only two values, 'NUM' (152,319) or 'CHAR' (115,843). Populated only on the ~268,159 field-header rows; NULL on the 386,180 pure code-enumeration rows. |
| length | int | Field width (character/numeric length) from the SAS/fixed-width layout, INT. Populated only on header rows (same ~268,162 coverage as type); NULL on pure code rows. |
| service_category | string | Benefit service-category label as given by CMS. SPARSE: only 47,719 non-null (~7%), concentrated in recent years. Often NULL — its absence does not mean the field has no service category, just that CMS did not tag it in that vintage. |
| field_title | string | The LONG / verbose human description of the field, frequently phrased as the full CMS question, e.g. 'Does the plan provide Inpatient Hospital-Acute Services as a supplemental benefit under Part C?' or short labels like 'H Number'. ~268,065 non-null. Part of the grain because the same name can carry two different field_title meanings in one file/year. Distinct from 'title' (which is the short label). |
| json_question | string | The question text / form-field phrasing as surfaced in CMS's JSON/online PBP entry form, or the literal token 'DERIVED' for computed fields. SPARSE: 53,796 non-null (~8%), recent years only. |
| title | string | The SHORT abbreviated field label, e.g. 'IHA Supp Benefits YN', 'Coverage Criteria', 'Plan ID'. ~268,159 non-null (header rows). NOT used as the grain key (it is trimmed/normalized vs field_title — keying on title leaves 24 residual dup groups because e.g. 'Enrollee Type:' (field_title) vs 'Enrollee Type' (title)). |
| codes | string | The raw code value as stored in the PBP data (the left side of the decode). 485,738 non-null, 589 distinct. GOTCHA: NOT a clean integer code system — values include '1', but also float-cast '1.0' (and '2'/'2.0' across different years — same meaning, different string), and ratio/frequency strings like '1 in 2', '1 in 3' (cost-share-per-period codes). String matching across years must account for the '1' vs '1.0' formatting drift. |
| code_values | string | The human-readable label for the code (the right side of the decode), e.g. 'Part A and Part B', 'Part B Only', 'HMO', 'Local CCP', 'Yes', 'Additional Days'. 485,738 non-null (1:1 populated with codes), 1,594 distinct. GOTCHA: label text is not normalized across vintages ('Part B Only' vs 'Part B only'), which is part of why it is needed in the grain key. |
| service_category_derived | string | DEAD COLUMN. 0 non-null rows across the entire table — reserved/never populated. Do not use. |
| observed_period | date | DATE, the benefit-year start as observed; always Jan 1 of report_year and always identical to effective_period_start (0 differences). Redundant with report_year/effective_period_start — a bitemporal 'observed' anchor. |
| asserted_at | date | DATE the row was asserted/loaded by Vlada's ingestion pipeline (bitemporal 'asserted' timestamp), NOT a CMS date. Only 3 values, all in May 2026 (2026-05-15, 2026-05-20, 2026-05-25) — the whole dictionary was ingested in one window. Useful for pipeline auditing, not for plan-year analysis. |
| effective_period_start | date | DATE the definition is effective from = Jan 1 of report_year (year always equals report_year, 0 mismatches). The profiler's chosen time column. Redundant with report_year/observed_period. |
| effective_period_end | date | DATE the definition is effective through = Dec 31 of report_year. Together with effective_period_start it just brackets the calendar benefit year. |
| cms_vintage_sequence | int | INT 1-4, the sequence number of the CMS source publication within a report_year. 1:1 with distinct source_url and distinct sha256_content_hash per year. =1 for single-release years (2001-2017, 2025-2026); 2-4 for years CMS republished the PBP PUF quarterly (2018-2024). Part of the grain. To get 'the latest CMS view' of a year, filter to MAX(cms_vintage_sequence) for that report_year (there is no is_current flag). |
| parse_revision | int | INT 1-2, Vlada parser revision number for the source artifact. Adds no grain resolution beyond cms_vintage_sequence (not part of the unique key); provenance/versioning of the extraction logic. |
| parser_artifact_hash | string | SHA-256 hash of the parser artifact/code that produced these rows. Provenance — lets you tie a row back to the exact extraction build. |
| source_url | string | The CMS download URL of the source ZIP, e.g. https://www.cms.gov/files/zip/pbp-benefits-2022-quarter-2.zip or .../pbp-benefits-2021-updated-08022021.zip. 1:1 with cms_vintage_sequence within a year; documents the exact CMS publication. |
| sha256_content_hash | string | SHA-256 of the source file content. Content-addressable provenance; 1:1 with source_url/cms_vintage_sequence within a year. Distinct sha => distinct CMS publication. |

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