# MA pbp plan region area

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

Regional-plan service area (parallel to plan_area for regional PPO). Coverage: 2006-present.

**Grain.** One row per plan offering in one CMS region for one benefit year and one quarterly CMS snapshot — i.e. one row per (contract_id x plan_id x segment_id x ma_or_pdp_region_code x contract_year x quarter). VERIFIED unique: this 6-column set yields 335,374 distinct combinations = the full 335,374 row count. A nationally-offered PDP shows up as one row per region (up to 39 PDP regions), so plans explode 4-16x into rows; and multi-quarter years repeat the same plan-region up to 4 times (once per quarterly snapshot).

**How to use it.** To list the regions a given standalone PDP serves in the latest 2026 view: SELECT region_type, ma_or_pdp_region_code, region FROM medicare_silver.pbp_plan_region_area WHERE contract_id='S5601' AND plan_id='001' AND contract_year='2026' AND quarter=1 ORDER BY ma_or_pdp_region_code. This answers 'where is this Part D plan offered' (its geographic footprint) for that benefit year. For a year-over-year footprint trend, always pin quarter (e.g. quarter=1) to avoid double-counting multi-quarter snapshots.

**Traps.**
- NO is_current flag and quarter is a true snapshot dimension: several years (2018, 2019, 2023, 2024) carry up to 4 quarterly re-publications of the SAME plans (each a distinct CMS file with its own sha256), so a naive COUNT/aggregate without filtering quarter over-counts plan-regions up to 4x. To get one current view per year, filter quarter = max(quarter) for that contract_year.
- ma_or_pdp_region_code is meaningless without region_type: codes 01-39 are PDP regions when region_type='PD' but 01-26 are MA regions when region_type='MA', and the SAME numeric code maps to different geographies (e.g. '11' = Florida under PD, Michigan under MA). Always pair the code with region_type (and the region name).
- This is NOT a benefit or cost table despite being PBP-sourced: it has zero premium/copay/dollar columns. ONE row = a plan-region offering (geographic footprint). For actual cost/benefit values you must join to the other pbp_* / PBP benefit tables on contract_id+plan_id+segment_id+year.
- bid_id looks like a key but is broken: it is NULL for all of 2006 (7,536 rows) and matches contract_plan_segment in only ~37% of rows. segment_id is also format-unstable — '' vs '0' for the same logical value, flipping by quarter within a year (2019 Q1='' , Q2-Q4='0'). Reconstruct identity from contract_id/plan_id/segment_id, normalizing segment_id, rather than trusting bid_id.
- Several columns are dead/constant and carry no signal: version (always 0.0), partial_flag (100% null), pbp_segment_id (100% null - not the real segment), pending_flag (always '0'). pbp_a_ben_cov is null for the entire PD slice (MA-only). asserted_at is a warehouse load date (only 2026-05-16 / 2026-05-20), not a real-world date - don't use it for plan timing.
- The table is ~99% standalone Part D (region_type='PD', contract prefix 'S'); MA regional plans ('R', region_type='MA') are only 3,869 rows and the 'X'-prefixed orgtype=16 slice is a single CMS demo/test contract (1,598 rows). Also note eghp_flag: ~64% of PD rows are Employer Group Health Plans (eghp_flag='1') - filter on it to separate individual-market from employer/union-sponsored Part D.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare contract identifier (5 chars). Prefix encodes plan family: 'S####' = standalone Part D drug plan (PDP, the bulk = 329,907 rows / 153 contracts), 'R####' = MA regional plan (3,869 rows / 37 contracts), 'X####' = a single CMS demo/test contract (1,598 rows). Part of the grain key. Not null. |
| plan_id | string | 3-digit plan number within the contract (e.g. '001','801','802'). 473 distinct values. Part of the grain. NOT unique on its own. The contract_id+plan_id+segment_id triple = the plan benefit package (PBP). |
| segment_id | string | Plan segment number, part of the grain. TRAP: representation is inconsistent across snapshots — it is the empty string '' in 203,075 rows and the literal '0' in 132,299 rows, and the choice flips even within a single year by quarter (2019 Q1 = '', Q2-Q4 = '0'). Both '' and '0' mean 'base plan / no segmentation'. Only 2 distinct values overall; no real multi-segment plans observed in this table. |
| ma_or_pdp_region_code | string | 2-digit CMS region code (zero-padded '01'..'39'). SCOPED BY region_type: for region_type='PD' it is one of the 39 standalone-PDP regions (01-39); for region_type='MA' it is one of the 26 MA regions (observed 01-25). The SAME code means different geographies across the two systems — code '11' = 'Florida' under PD but 'Michigan' under MA. Part of the grain. Pair with region (the name) and region_type. |
| region_type | string | Which CMS regional system the code belongs to. Only two values: 'PD' = standalone Part D / PDP region (331,505 rows, the overwhelming majority) and 'MA' = Medicare Advantage region (3,869 rows). NOTE: value is 'PD' not 'PDP'. The first few sample rows the profiler returned were the rare MA slice and are not representative — this is essentially a PDP table. |
| version | double | DEAD COLUMN. Always 0.0 across all 335,374 rows. Carries no information. (double) |
| report_year | double | The CMS PBP report year as a number (e.g. 2013.0). VERIFIED identical to contract_id's contract_year in 100% of rows — it is just the numeric (double) twin of contract_year. Spans 2006-2026. |
| quarter | double | CMS quarterly release number (1.0-4.0) — part of the grain and the SNAPSHOT dimension. Most early years have only Q1; several recent years (2018, 2019, 2023, 2024) carry up to 4 quarterly re-publications of the SAME plan set. Each quarter is a genuinely distinct CMS file (sha256 differs per quarter), so the same plan-region appears as up to 4 separate rows in a year. No is_current flag exists; to get one row per plan-region per year you must filter to the max quarter. |
| bid_id | string | Bid identifier string, usually 'contract_plan_segment' (e.g. 'R3175_001_0'). UNRELIABLE as a key: it equals contract//'_'//plan//'_'//segment in only 124,763 of 335,374 rows, and it is NULL for all 7,536 rows of 2006. Do not use as a join key; reconstruct from contract_id/plan_id/segment_id instead. |
| contract_year | string | Plan/benefit year as a string ('2006'..'2026'). Part of the grain. This is the real-world year the plan offering applies to. observed_period, effective_period_start (Jan 1) and effective_period_end (Dec 31) are all derived from it. |
| eghp_flag | string | Employer Group Health Plan flag ('0'/'1'). MEANINGFUL: in the PD slice, 211,197 of 331,505 rows are '1' (employer/union-sponsored Part D, the so-called 800-series), vs 120,308 '0' (individual market). In the MA slice almost all are '0'. Use this to separate employer-group from individual-market plans in cost analysis. Never null. |
| orgtype | string | CMS organization type code. Three values, each tied to a slice: '10' (329,907) = the standalone PDP bulk, '11' (3,869) = MA regional plans, '16' (1,598) = the single 'X' demo/test contract. Correlates 1:1 with region_type/contract prefix. Never null. |
| partial_flag | string | DEAD COLUMN. 100% NULL across all rows. No information. |
| pbp_a_ben_cov | string | PBP Section A benefit-coverage indicator. NULL for the entire PD slice (331,505 rows); only populated ('1') for the 3,869 MA rows. Effectively MA-only and mostly absent — treat as null-means-not-applicable. |
| pbp_a_plan_type | string | PBP plan-type code. '29' = PDP (the 329,907-row bulk), '31' = MA regional, NULL for the orgtype-16/'X' demo slice (1,598). Redundant with orgtype/region_type. |
| pbp_segment_id | double | DEAD COLUMN. 100% NULL across all rows (a double). Despite appearing in the profiler's reported grain key, it adds nothing — the real segment is the string segment_id column. |
| pending_flag | string | DEAD COLUMN. Always '0' across all rows. No information (no pending plans in this extract). |
| region | string | Human-readable region name corresponding to ma_or_pdp_region_code, scoped by region_type (e.g. PD '11'='Florida', PD '01'='Northern New England (New Hampshire and Maine)'; MA '11'='Michigan'). Distinct names per system: 39 for PD, 23-26 for MA. Note region names can drift slightly across years (e.g. 'Pennsylvania and West Virginia' vs 'Pennsylvania, West Virginia'). |
| observed_period | date | Derived date = Jan 1 of contract_year (the start of the plan year the snapshot describes). Redundant with contract_year. (date) |
| asserted_at | date | Warehouse LOAD date, NOT a real-world plan date. Only two values exist: 2026-05-16 (initial backfill of 19 years, 173,047 rows) and 2026-05-20 (later load of 7 years, 162,327 rows). Provenance/bookkeeping only — do not use for plan-year analysis. |
| effective_period_start | date | Jan 1 of contract_year — the plan year start. The profiler's primary time column. Derived from contract_year (redundant). (date) |
| effective_period_end | date | Dec 31 of contract_year — the plan year end. Derived from contract_year. (date) |
| cms_vintage_sequence | int | Provenance counter (1-4) tracking which CMS source vintage / quarterly snapshot pass produced the row; moves with parse_revision. Operational lineage, not a business attribute. (int) |
| parse_revision | int | Internal parser revision (1 or 2) that produced the row. Provenance only. (int) |
| parser_artifact_hash | string | SHA-256 of the parser artifact/code that emitted the row. Provenance/reproducibility only. |
| source_url | string | The CMS download URL of the source PBP-PUF zip (e.g. '.../pbp-benefits-2019-q2.zip'). Encodes year and quarter of the source file. Provenance. |
| sha256_content_hash | string | SHA-256 of the source file's content. VERIFIED to differ per quarter (each quarterly CMS release is a distinct file), confirming quarter is a real snapshot dimension. Provenance/integrity. |

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