# MA pbp section d opt

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

Section D opt: Part D optional supplemental drug benefits. Pre-2022 used `opts` spelling. Coverage: All years.

**Grain.** One row per optional supplemental benefit OPTION, per plan-segment, per CMS quarterly snapshot. Minimal verified key = contract_id + plan_id + segment_id + pbp_d_opt_identifier + report_year + quarter (40,968 distinct = 40,968 rows). Within one snapshot the four-part natural key contract_id+plan_id+segment_id+pbp_d_opt_identifier is already unique; report_year+quarter are snapshot dimensions whose quarters carry identical content (dedup to one quarter/year -> 17,983 annual records).

**How to use it.** "What's the typical extra monthly premium for an optional dental/vision rider in 2025, and how many MA options offer one?" -> SELECT APPROX_PERCENTILE(pbp_d_amt_opt_premium,0.5) AS median_monthly_premium, COUNT(*) AS options FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY contract_id,plan_id,segment_id,pbp_d_opt_identifier,report_year ORDER BY quarter DESC) rn FROM medicare_silver.pbp_section_d_opt WHERE report_year=2025) t WHERE rn=1 AND pbp_d_opt_other_benefits LIKE '%16a%'. The dedup (rn=1) is essential so the duplicated quarterly snapshots don't inflate counts; the LIKE '%16a%' filters to options that bundle dental.

**Traps.**
- QUARTERLY SNAPSHOTS DUPLICATE THE YEAR, AND THERE IS NO is_current FLAG. The same plan year is republished under up to 4 quarters with byte-identical content (2023 Q1 vs Q4: 0 premium diffs, 0 row churn). Total 40,968 rows but only 17,983 distinct annual records. ALWAYS dedup to one quarter per report_year (e.g. ROW_NUMBER() PARTITION BY contract_id,plan_id,segment_id,pbp_d_opt_identifier,report_year ORDER BY quarter DESC) before counting plans/options or you will multi-count by ~2.3x.
- MINIMAL GRAIN IS 6 COLUMNS, NOT THE PROFILER'S 14. The profiler reported a bloated superset including pbp_a_plan_type and five pbp_d_opt_maxplan_* columns. pbp_a_plan_type is constant per plan (functionally dependent, not needed), and pbp_d_opt_maxplan_cov is 100% NULL so it cannot contribute to a key. The true minimal key is contract_id+plan_id+segment_id+pbp_d_opt_identifier+report_year+quarter (and just the first four within any single snapshot).
- THREE DEAD COLUMNS ARE 100% NULL: pbp_d_opt_maxplan_cov, pbp_d_pick_benefits, pbp_d_pick_benefits_other. Do not build logic on them. (One of them, pbp_d_opt_maxplan_cov, even appears in the profiler's candidate key -- ignore it.)
- NULLS THAT CARRY MEANING, gated by Y/N flags: pbp_d_opt_deduct_amt/deduct_cats are null unless pbp_d_opt_deduct_yn='1' (only 5,088 rows have a deductible); pbp_d_opt_maxplan_ben_cov_amt and pbp_d_opt_maxplan_per are null unless pbp_d_opt_maxplan_ben_cov_yn='1' (null period = 'no cap'). A null here means 'feature absent', not 'unknown'. Also CMS Y/N codes are 1=Yes / 2=No (2 is NOT 'unknown').
- bid_id IS NOT AN INDEPENDENT KEY: it is exactly contract_id||'_'||plan_id||'_'||segment_id (verified 0 mismatches). Use pbp_d_opt_description (100% filled) for the option label, NOT pbp_d_opt_name (96.6% null). And in pbp_d_opt_secb_cats a lone ';' is a placeholder for empty -- treat it as null when parsing the category list.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS MA contract ID (e.g. H0028). 359 distinct. Part of natural key. Letter+4 digits (H/R/S prefixes seen). |
| plan_id | string | Plan ID within the contract (e.g. '016'). 3-char zero-padded string. 355 distinct values. Part of natural key. NOT unique on its own. |
| segment_id | string | Plan segment ('0' for non-segmented plans = 34,266 rows; '1'..'7'+ for geographically segmented plans). Part of natural key. |
| pbp_d_opt_identifier | string | Sequence number of the optional supplemental benefit package within the plan (e.g. '001','002','003'). The discriminator that makes the row unique within a plan-snapshot. A plan can offer up to 5 options (median 1). |
| version | double | CMS PBP software/template version number for that filing year (double, 2.0..14.0+). Metadata, not a record key. Varies by report_year, NOT a dedup key. |
| report_year | double | Plan/benefit year the option applies to (double: 2018..2026). Part of the snapshot grain. Equals year of effective_period_start/observed_period. |
| quarter | double | CMS quarterly publication snapshot (1,2,3,4). Part of grain because the same plan year appears under multiple quarters, but content is IDENTICAL across quarters of a year. Pick one quarter (e.g. latest) per report_year to dedup to annual truth. |
| bid_id | string | Redundant derived key = contract_id // '_' // plan_id // '_' // segment_id (verified 0 mismatches). Convenience identifier; carries NO information beyond its three components. Do not treat as an independent key. |
| orgtype | string | CMS organization-type code (string). '01'=38,493 (local CCP/HMO-PPO, dominant), '11', '04', '06', '02'. Correlates with pbp_a_plan_type. Identifies the org structure of the contract. |
| pbp_a_ben_cov | string | PBP Section A benefit-coverage indicator (string). Almost always '1' (40,860); '2'=67; 41 null. Effectively constant; low analytic value. |
| pbp_a_plan_type | string | MA plan-type code (string) carried from PBP Section A. '01'=HMO (21,419), '04'=Local PPO (12,491), '02'=HMO-POS (4,583), '31'=Regional PPO (1,194), '09'=PFFS (922), '18', '07'. Note: profiler listed this in the candidate key but it is functionally determined by the plan (constant per bid), so it is NOT needed for the minimal grain. |
| pbp_d_amt_opt_premium | double | Extra MONTHLY premium (USD) the member pays to elect this optional package. The headline COST field. Range $2.50-$157.00, median ~$29.65. 50 rows null. |
| pbp_d_opt_apply_moop_yn | string | Whether this option's cost-sharing applies toward the plan MOOP (maximum out-of-pocket). CMS Y/N convention: '1'=Yes (86), '2'=No (40,841, almost always), 41 null. Nearly constant 'No'. |
| pbp_d_opt_deduct_amt | double | Deductible USD for the option, populated only when pbp_d_opt_deduct_yn='1'. Range $10-$6,250. Null = no separate deductible (the common case). |
| pbp_d_opt_deduct_cats | string | Semicolon-delimited list of PBP benefit-category codes the deductible applies to (e.g. '16b;', '16a;16b;', '16c1;16c2;...'). Populated for exactly the 5,088 rows where pbp_d_opt_deduct_yn='1'; null otherwise (null = no deductible). |
| pbp_d_opt_deduct_yn | string | Whether the option carries a separate deductible. '1'=Yes (5,088), '2'=No (35,880). Drives whether deduct_amt/deduct_cats are populated. |
| pbp_d_opt_description | string | Human-readable label of the option, 100% populated. The de-facto display name. Mix of marketing names ('Dental Platinum Rider', 'Enhanced Dental and Vision Package') and CMS codes ('MyOption DEN478', 'MyOption Vision'). Use THIS, not pbp_d_opt_name. |
| pbp_d_opt_maxplan_ben_cov_amt | double | Plan-coverage maximum / annual benefit cap (USD) the option pays before the member is fully responsible. Populated only when pbp_d_opt_maxplan_ben_cov_yn='1'. Range $0-$30,000. Null = no plan max (uncapped). |
| pbp_d_opt_maxplan_ben_cov_yn | string | Whether the option has a maximum plan-coverage cap. '1'=Yes (19,661), '2'=No (21,307). Gates maxplan_ben_cov_amt and maxplan_per. |
| pbp_d_opt_maxplan_per | string | Periodicity code for the coverage max ('3'=per year is dominant at 18,103; '6', '2', '4' rare). NULL exactly when maxplan_ben_cov_yn='2' (21,307 nulls) -> null carries meaning: 'no cap, so no period'. |
| pbp_d_opt_maxplan_per_d | string | Free-text description of a non-standard coverage-max periodicity. 99.5% null (only 187 populated); used for combined INN/OON dental+vision max notes. Niche. |
| pbp_d_opt_name | string | Optional explicit name of the package, but 96.6% NULL (39,567 null). Largely unused legacy field -- prefer pbp_d_opt_description for the label. |
| pbp_d_opt_other_benefits | string | Semicolon-delimited list of PBP section codes for the benefit categories bundled in this option (16a/16b=Dental, 17a/17b=Vision, plus sub-codes like 16a1..16b7). E.g. '16a;16b;' (11,717), '16a;16b;17b;'. ~18% null. Tells you WHAT the option covers. |
| pbp_d_opt_secb_cats | string | Semicolon-delimited supplemental-benefit category codes (e.g. '16a;16b;', '17a;17b;', '14c;'). ~25% populated (10,064). Caveat: a lone ';' appears as a placeholder/empty value (589 rows) -- treat ';' as effectively null. |
| pbp_d_pick_benefits | string | DEAD COLUMN: 100% NULL (0 of 40,968 populated). Reserved 'pick-your-benefit' field unused in this dataset. |
| observed_period | date | Provenance: date the benefit year is observed for (= Jan 1 of report_year). Redundant with effective_period_start; one of the snapshot/period columns. |
| asserted_at | date | Provenance: date Vlada parsed/asserted the record (2026-05-16 or 2026-05-20). Pipeline metadata, not a business field. |
| effective_period_start | date | Start of the plan year the option is in force (Jan 1 of report_year). The table's primary time column; spans 2018-01-01 .. 2026-01-01. |
| effective_period_end | date | End of the plan year (Dec 31 of report_year). Always the calendar year-end paired with effective_period_start. |
| cms_vintage_sequence | int | Integer vintage rank of the CMS quarterly republish (1..4), broadly tracking quarter (Q2->2, Q3->3, Q4->4) but not perfectly 1:1 (early years mix). Useful for picking the latest vintage within a year. |
| parse_revision | int | Vlada parser revision (1 or 2). 7,062 rows at rev 1 (the as-of-2026-05-16 load), 33,906 at rev 2. Pipeline metadata. |
| parser_artifact_hash | string | SHA-256 of the parser artifact used (provenance lineage). Metadata for reproducibility. |
| source_url | string | CMS source ZIP the row came from (e.g. https://www.cms.gov/files/zip/pbp-benefits-2023-quarter-1.zip). One source_url per (report_year, quarter). Provenance. |
| sha256_content_hash | string | SHA-256 of the source content (provenance/integrity). Metadata. |
| pbp_d_opt_maxplan_cov | string | DEAD COLUMN: 100% NULL (0 of 40,968). Note: the profiler wrongly listed this in the candidate grain key -- it is entirely null and contributes nothing to uniqueness. |
| pbp_d_pick_benefits_other | string | DEAD COLUMN: 100% NULL (0 of 40,968). Companion to pbp_d_pick_benefits, unused. |

Tools that read it: get_pbp_benefit

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