# MA pbp plan area

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

Plan × county service area. ~2.3M rows per modern year. PlanArea + PlanRegionArea consolidated via area_type enum.

**Grain.** One row per Medicare Advantage / Part C plan-segment offered IN one county, per contract year, per quarterly PUF snapshot, per EGHP flag. The MINIMAL verified-unique key is (contract_id, plan_id, segment_id, county_code, observed_period, quarter, eghp_flag) = 57,326,072 distinct = exactly the row_count (57,326,072). Plainer: it is the county-level service-area / availability roster of every MA plan-benefit-package — which plans are offered in which counties each year — snapshotted up to 4 times a year (one quarterly PUF release each). It is NOT a benefit-detail or cost-amount table; it carries the plan's identity/area attributes, not premiums or cost-sharing dollars.

**How to use it.** Scope MA plans to a market and a single clean annual snapshot. Example: SELECT DISTINCT contract_id, plan_id, county, stcd, pbp_a_plan_type, orgtype FROM pbp_plan_area WHERE stcd='TX' AND county_code='45610' AND report_year=2026 AND quarter=(SELECT MAX(quarter) FROM pbp_plan_area WHERE report_year=2026) AND eghp_flag='0'. Answers: 'which non-employer MA plans (and of what plan type) are offered in Harris County, TX for 2026?' Filtering to one quarter (latest cms_vintage_sequence) collapses the 1–4 quarterly republications to one record per plan-county-year; excluding eghp_flag='1' removes employer-group plans."

**Traps.**
- NO is_current column and the same plan-county-year appears up to 4 times (once per quarterly PUF snapshot, quarter 1:1 with cms_vintage_sequence). 2010-2017 have only quarter=1; 2018+ carry Q2/Q3/Q4 reloads. Always filter to a single quarter (e.g. MAX(quarter) / latest cms_vintage_sequence) or you will 2x-4x count plans. Distribution: 18.1M plan-county-year keys appear once, 6.3M appear 3x, 3.9M appear 4x.
- (contract_id, plan_id, segment_id, county_code, observed_period, quarter) is NOT unique — eghp_flag is required for the true grain. Exactly 539 keys appear twice differing ONLY in eghp_flag ('0' vs '1') with otherwise byte-identical content and the SAME sha256_content_hash; a source-PUF artifact, not a real versioning event. sha256/version/cms_vintage_sequence do NOT separate these dupes — only eghp_flag does.
- segment_id is a STRING with '' (empty, 41.9M rows) AND '0' (15.4M rows) both meaning 'segment 0 / unsegmented' — empty string is NOT missing data, it is the dominant valid value. The separate pbp_segment_id (double) column is 100% NULL and must be ignored. Also bid_id always ends in '_0' for the segment slot regardless of segment_id, so bid_id <> contract||plan||segment_id for all 41.9M empty-segment rows (a false mismatch).
- Heavy redundancy / dead columns: report_year == contract_year == year(observed_period) == year(effective_period_start) (all verified equal); version is always 0.0; parse_revision is always 1; pbp_segment_id is always NULL. asserted_at (2026-05-16 / 2026-05-20) is the lakehouse load batch, NOT a CMS publish date — do not use it as a time axis. Use report_year (or observed_period) for the plan year.
- county_code is the 5-digit CMS SSA code, NOT FIPS (Harris TX = 45610, LA = 05200) — you must apply an SSA-to-FIPS crosswalk before joining to FIPS-keyed geographic data. Also: this table carries plan identity + availability only (org type, plan type, flags) — it has NO premium, no cost-sharing, no benefit-amount columns, so it cannot answer 'how much does the plan cost'; use the PBP benefit-detail / premium tables for dollars. Code systems pbp_a_plan_type/orgtype are the PBP-file code sets (e.g. plan_type 04=Local HMO, 01=Local PPO, 09=PFFS, 30=MSA/Cost, 07=Regional PPO) and differ from the plan_type codes in enrollment/landscape files.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare Advantage / Part C contract number. H-prefix = standard MA contracts (1,644 distinct, 56.9M rows); E-prefix = employer/union direct (EGHP) contracts (11 distinct, 433K rows). No S/R/PDP-only contracts — this is the MA/Part-C PBP universe. 100% populated. Joins to plan-level PBP, enrollment, and Stars tables on contract_id. |
| plan_id | string | 3-digit plan-benefit-package (PBP) number within the contract (e.g. '801', '803'). 656 distinct values. Unique only WITHIN a contract; (contract_id, plan_id) is the plan key. 100% populated. |
| segment_id | string | Plan segment number (for service-area-segmented plans). STRING with a data-quality quirk: 41.9M rows = '' (empty string), 15.4M = '0', and 1–24 for genuinely segmented plans (regional PPOs / large employer plans, < 60K rows total). Empty-string and '0' BOTH mean 'segment 0 / unsegmented' — do not treat '' as missing. Part of the grain key. 100% populated (as empty string at minimum). |
| county_code | string | 5-digit CMS SSA county code (NOT FIPS). Always exactly 5 chars, 100% populated. 3,274 distinct counties. Example: Harris TX = 45610, Los Angeles CA = 05200, Miami-Dade FL = 10120. This is THE row dimension — one row per county the plan is offered/available in. Use a SSA↔FIPS crosswalk to join to FIPS-keyed data. |
| report_year | double | double. The CMS PUF contract/benefit year (2010–2026). Identical to contract_year (cast) and to year(observed_period) — verified 0 mismatches. The plan-year dimension. |
| quarter | double | double 1–4. The WITHIN-YEAR quarterly PUF snapshot, 1:1 with cms_vintage_sequence (Q1↔seq1 ... Q4↔seq4). Years 2010–2017 have only Q1; 2018+ years carry multiple quarterly re-publications of the same annual PBP roster. THIS is the snapshot/dedup dimension — there is no is_current flag, so to get one record per plan-county-year you must pick one quarter (e.g. MAX(quarter) or the latest cms_vintage_sequence). |
| bid_id | string | Composite plan-bid identifier, format contract_id_plan_id_0 (e.g. 'H0332_803_0'). ALWAYS uses '_0' for the segment portion regardless of the segment_id column value — so bid_id does NOT equal contract//plan//segment_id when segment_id is '' (41.9M such 'mismatches', which are not real mismatches). Effectively redundant with (contract_id, plan_id). 100% populated. |
| contract_year | string | string. The contract year as text (e.g. '2015'). Redundant with report_year (verified equal after cast) and with observed_period year. 100% populated. |
| county | string | Human-readable county name (e.g. 'Harris', 'Los Angeles', 'Rio Grande'). 100% populated. Decode/label for county_code; pair with stcd for uniqueness (county names repeat across states). |
| eghp_flag | string | Employer Group Health Plan flag, string '0' (5.58M) or '1' (51.74M). 100% populated. IMPORTANT: this is part of the unique grain — 539 plan-county-year-quarter keys appear twice, differing ONLY in eghp_flag (a source PUF quirk where one plan-county is listed under both EGHP='0' and '1' with otherwise identical content + identical sha256). Note value distribution skews to '1' here, which is unusual for a flag and reflects how CMS populates section-A EGHP indicator in the benefits PUF. |
| orgtype | string | CMS organization type code, string, 100% populated. 01 = Local Coordinated Care Plan (55.6M, dominant), 04 = PFFS (770K), 13 = MSA/Cost (423K), 02 = Regional PPO (412K), 06, 08, 05, 14 = other. 1:1 correlated with pbp_a_plan_type. |
| partial_flag | string | Partial service-area indicator. 99.94% NULL; only 33,161 rows = '*' (the only non-null value). Marks the small set of plan-county rows flagged as partial-county service areas. Treat NULL as 'not partial / not flagged'. |
| pbp_a_ben_cov | string | PBP Section-A benefit-coverage type code, string: '1' (50.7M, ~88%), '2' (5.79M, ~10%), NULL (805K, ~1.4%). Indicates the coverage-bundle category (Part C health-only family vs health+drug family). NULL is concentrated in specific plan types (30 MSA/Cost, 07 Regional PPO, 18) — a structural null, not random missingness. |
| pbp_a_plan_type | string | PBP Section-A plan type code, string, 100% populated. 04 = Local HMO (39.5M, dominant), 01 = Local PPO (12.1M), 02 (3.95M), 09 = PFFS (770K), 30 = MSA/Cost (423K), 07 = Regional PPO (412K), plus a long tail (18, 20, 48, 40, 38, 37, 49, 05, 39). 1:1 correlated with orgtype. This is the plan-type used in PBP files (distinct code system from the plan_type in enrollment/landscape files). |
| pbp_segment_id | double | double. DEAD COLUMN — 100% NULL (0 non-null across all 57.3M rows). Do not use; the real segment dimension is the string segment_id column. |
| pending_flag | string | Pending-plan indicator, string '0' (57,325,821) or '1' (only 251 rows). 100% populated. '1' marks the tiny set of pending/not-yet-approved plan-area rows. |
| stcd | string | 2-letter USPS state code (e.g. 'TX','CA','CO','AL'). 56 distinct values (50 states + DC + territories). 100% populated. Pair with county/county_code for geographic joins. |
| version | double | double. DEAD/CONSTANT — always 0.0 across all rows. No information; not a real version dimension. |
| observed_period | date | date, always Jan-1 of the report year (2010-01-01 ... 2026-01-01), 17 distinct values. Year(observed_period) == report_year == contract_year (verified). The canonical annual time anchor. |
| asserted_at | date | date the row was loaded into the silver lakehouse: only two values, 2026-05-16 and 2026-05-20 (a two-batch backfill ingest). NOT a CMS publish date and NOT per-year. Several years (2018-2020, 2022-2024) have rows from both batches — the Q2/Q3/Q4 snapshots were loaded on 2026-05-20. Provenance/lineage only; do not use as a business time axis. |
| effective_period_start | date | date = Jan-1 of the contract year (== observed_period). The plan-year coverage start. Used as the profiler's time column; 17 annual values 2010–2026, annual periodicity. |
| effective_period_end | date | date = Dec-31 of the contract year (e.g. 2015-12-31). The plan-year coverage end. Always the calendar-year close paired with effective_period_start. |
| cms_vintage_sequence | int | int 1–4. The CMS publication sequence within a year, 1:1 with quarter (1=Q1...4=Q4). Use the MAX value per plan-county-year to select the latest within-year republication (de-facto 'is_current' surrogate, since no is_current column exists). |
| parse_revision | int | int, constant = 1 across the sample/observed data. The parser revision that produced the row; no current variation. Lineage field. |
| parser_artifact_hash | string | sha256 of the parser artifact/code version that produced the row (e.g. '7853ed8e...'). Lineage/reproducibility; not a business key. |
| source_url | string | The CMS source ZIP the row was extracted from, e.g. https://www.cms.gov/.../pbp-pufs/pbp-benefits-{year}.zip. One URL per report year (the PBP Benefits PUF). Confirms provenance = CMS PBP Benefits Public Use Files. |
| sha256_content_hash | string | sha256 of the source file content (one value per source ZIP / year-batch; identical across all rows from the same file). De-dup/integrity at the file level — note it does NOT distinguish the 539 eghp duplicate rows (they share the same hash). |

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