# MA pbp vbid overlay

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

Union of 21-24 VBID overlay files. vbid_variant enum: {uf, ssbci, b19a_uf, b19a_ssbci, b19b_uf, b19b_ssbci, plain, discontinued_retained}. parent_section links to Section B (b13, b14, etc.). VBID introduced 2017; retained blanked 2026+.

**Grain.** One row per (contract_id, plan_id, segment_id, parent_section, vbid_variant, report_year, quarter) — i.e. one row per Medicare Advantage plan-segment x the specific PBP benefit section it flexes under VBID x the VBID variant, per benefit-year x per quarterly CMS PUF release. VERIFIED: those 7 columns produce exactly 172,041 distinct combinations = the full row_count, and dropping ANY single one of the 7 breaks uniqueness (none removable). cms_vintage_sequence and parse_revision are functionally determined by this key and add no distinctness. A single plan-segment typically spans 1-20 rows in one snapshot (one per VBID-flexed section).

**How to use it.** Count distinct VBID-participating MA plan-segments per benefit year, de-duplicating the quarterly snapshots: WITH latest AS (SELECT report_year, max(quarter) mq FROM pbp_vbid_overlay GROUP BY report_year) SELECT o.report_year, count(DISTINCT (o.contract_id,o.plan_id,o.segment_id)) FROM pbp_vbid_overlay o JOIN latest l ON o.report_year=l.report_year AND o.quarter=l.mq WHERE o.parent_section <> 'unknown' GROUP BY o.report_year ORDER BY 1; — answers 'how many MA plans offered VBID benefits each year' and yields the real adoption curve (~57 in 2017 to ~7,400 in 2026) without the snapshot double-count or the unknown-sentinel noise.

**Traps.**
- SNAPSHOT TABLE WITH NO is_current FLAG — the same benefit year is loaded from MULTIPLE quarterly CMS PUF releases, so the same plan-section repeats across quarters (2018/2019/2020/2022/2023/2024 are all multi-quarter; e.g. 2024 has q1,q3,q4 each ~17.7K rows). A naive COUNT(*) or COUNT(DISTINCT plan) across all rows DOUBLE/TRIPLE-counts. To get distinct VBID plans per year, dedup to one snapshot, e.g. JOIN to (SELECT report_year, max(quarter) FROM ... GROUP BY report_year). 'quarter' is a release/snapshot axis, NOT a within-year time period.
- parent_section='unknown' AND vbid_variant='unknown' are paired SENTINELS, not real values and not NULL — 76,436 rows (~44% of the table) where the parser could not classify the PBP section/variant. They co-occur perfectly (0 rows with only one 'unknown'), are heavily concentrated in 2022-2024, and inflate row counts. Filter them out (parent_section <> 'unknown') before any section-level analysis.
- asserted_at is an INGESTION timestamp (all rows 2026-05-16..2026-05-20), NOT a CMS publication/effective date — never use it to order CMS revisions or pick 'latest'; use report_year (benefit year) and quarter (release). Likewise observed_period / effective_period_start / effective_period_end are all DERIVED from report_year (Jan 1 and Dec 31 of that year) and carry no independent information.
- This is a MARKER / OVERLAY table — it carries only WHICH benefit sections a plan offers VBID flexibility in, NOT the benefit values. There are ZERO dollar/copay/limit columns. It tells you a plan participates in VBID and in which PBP sections; to get the actual benefit amounts you must JOIN on (contract_id, plan_id, segment_id, report_year) to the underlying PBP benefit tables. No nulls exist in any column (sentinels are used instead of NULL).
- cms_vintage_sequence (int 1-4) is NOT the grain and NOT a clean quarter mapping — it is a CMS release-sequence index that bleeds across quarters (vintage 4 = q1 AND q4). Use 'quarter' to identify the release. cms_vintage_sequence and parse_revision are provenance metadata that look key-ish but are functionally determined by the 7-column grain.
- Coverage is MA-only and grows steeply with VBID program adoption: contract_id is only H (MA/MAPD) and R (regional PPO) — no PDP. Distinct VBID plan-segments per year rose from 57 (2017) to ~7,400 (2026), so early years are thin. Do not treat low pre-2021 counts as data gaps; that is the actual size of the VBID program then.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare Advantage contract ID (the plan's parent contract). VERIFIED 1,188 distinct; all begin H (1,160 contracts, standard MA/MAPD) or R (28 contracts, regional PPO). No S/E prefixes — VBID is an MA-side flexibility, so PDP-only contracts never appear. Part of the grain key. |
| plan_id | string | 3-char plan benefit package ID within the contract (e.g. '001','053','805'), zero-padded string. 656 distinct, NOT unique on its own (reused across contracts). (contract_id, plan_id) identifies a plan; add segment_id for a plan-segment. Part of the grain key. |
| segment_id | string | Plan segment number as string, range '0'-'25'. '0' = unsegmented plan and dominates (154,554 of 172,041 rows, ~90%); non-zero values are CMS geographic/benefit segments of a segmented plan. Part of the grain key — a contract can offer different VBID section sets per segment (e.g. H0028/053 has segments 1,3,4 with different section sets). |
| parent_section | string | The CMS PBP benefit-section code under which the plan offers a VBID-flexed benefit: e.g. b10 (DME/prosthetics), b13 (Part B Rx/other), b13i (SSBCI), b14c, b16 (dental), b17 (vision), b18 (hearing), b19a/b19b, plus b1a/b1b/b2/b3/b4/b7/b9. CRITICAL: 'unknown' is a SENTINEL (76,436 rows) meaning the parser could not classify the section — it is NOT a real PBP section. Part of the grain key; one plan-segment has 1-20 such rows per snapshot. |
| vbid_variant | string | The VBID flexibility flavor for that section. Observed: b19b_uf and b19a_uf (UF = Uniform Flexibility / standard supplemental), b19b_ssbci (Special Supplemental Benefits for the Chronically Ill — always paired with parent_section b13i), b19a_plain/b19b_plain/plain (non-VBID/standard marker). CRITICAL: 'unknown' (76,436 rows) is a sentinel co-occurring EXACTLY with parent_section='unknown' (0 either-only). Part of the grain key. |
| report_year | int | The Medicare benefit/plan year the row describes (int). VERIFIED 2017-2026, 10 years. Primary temporal axis (= the year the VBID offering is effective), distinct from when CMS published or Vlada parsed it. Part of the grain key. |
| quarter | int | Which quarterly CMS PBP PUF RELEASE (1-4) this row was extracted from. The same benefit year is republished in multiple quarters (e.g. 2023 has q2,q3,q4 each ~16.5K rows). REQUIRED in the grain key because the same plan-section repeats across a year's quarterly releases — this is the SNAPSHOT axis, NOT a within-year time period. There is no is_current flag. |
| observed_period | date | Date = always January 1 of report_year (derived, not independent). Marks the start of the observation/benefit year. One distinct value per report_year; carries no information beyond report_year. |
| asserted_at | date | Date Vlada parsed/loaded the row — all values fall 2026-05-16 to 2026-05-20 regardless of benefit year. This is an INGESTION/bitemporal timestamp, NOT a CMS publication date. Do NOT use it to order CMS revisions or pick the latest snapshot; use report_year/quarter for that. |
| effective_period_start | date | Date = January 1 of report_year. Start of the annual coverage window the VBID offering applies to. Derived from report_year; the profiler picked this as the time column but it is fully determined by report_year (annual). |
| effective_period_end | date | Date = December 31 of report_year. End of the annual coverage window. Always a full calendar year [Jan 1, Dec 31] of report_year. Derived from report_year. |
| cms_vintage_sequence | int | Int 1-4, a CMS release-sequence index that roughly tracks the quarter ordinal within a year but is NOT a clean 1:1 (vintage 2 spans q1&q2, vintage 4 spans q1&q4). Functionally determined by the grain — adding it does NOT increase row distinctness. Use quarter, not this, to identify the release. |
| parse_revision | int | Int parser-version that produced the row: 1 (49,817 rows) or 2 (122,224 rows). Provenance/lineage of the extraction, not a business attribute. Functionally determined by the grain (no per-grain accumulation of multiple parse revisions). |
| parser_artifact_hash | string | SHA-256 of the parser artifact/code that produced the row (provenance). Lets you tie a row to the exact extractor build. Not a business key. |
| source_url | string | The CMS PBP Benefits PUF download URL this row came from, e.g. https://www.cms.gov/.../pbp-pufs/pbp-benefits-2019-q3.zip. Exactly one URL per (report_year, quarter) — confirms the underlying source is the CMS Plan Benefit Package (PBP) Benefits Public Use File. |
| sha256_content_hash | string | SHA-256 content hash of the source file (the .zip), for integrity/provenance and dedup of identical source content. Not a business key. |

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