MA pbp section d opt

What it is

1. 1CMS Medicare Advantage Plan Benefit Package (PBP) Section D "Optional Supplemental Benefits" data: one row per optional buy-up benefit package (e.g. a Dental/Vision rider the member can elect for an extra monthly premium) that an MA plan offers, parsed from the annual CMS PBP benefits ZIP files.
2. 2It captures each option's extra premium, its deductible, its plan-coverage maximum (cap), and which benefit categories it bundles.
3. 3Use it to see what supplemental cost-sharing buy-ups MA plans sell and at what member price.

Grain

How to read it

1. 1"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%'.
2. 2The dedup (rn=1) is essential so the duplicated quarterly snapshots don't inflate counts; the LIKE '%16a%' filters to options that bundle dental.

Traps

1. 1QUARTERLY 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.
2. 2MINIMAL 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).
3. 3THREE 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.)
4. 4NULLS 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').
5. 5bid_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.

Columns (36)

Query it

describe_data_source(source_fqn="medicare_silver.pbp_section_d_opt")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.pbp_section_d_opt WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND pbp_d_opt_identifier = '<value>' AND report_year = '<value>' AND quarter = '<value>' LIMIT 100")