VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

MA pbp plan area

medicare_silver.pbp_plan_area·partial·silver·annual·Medicare Advantage & Part D Plan Landscape
Rows
N/A
Columns
27
History
2010-01-01 to 2026-01-01
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1The county-level service-area / availability roster from the CMS PBP (Plan Benefit Package) Benefits Public Use Files: it records which Medicare Advantage / Part C plan-benefit-packages are offered in which counties for each contract year, along with each plan's identity attributes (org type, plan type, EGHP/pending/partial flags).
  2. 2It is the geographic 'where is each MA plan available' spine used to scope MA plan analyses to a market/county and to join plan identity to enrollment, premiums, and Stars by contract+plan+county."

Grain

KeyGrain
contract_idplan_idsegment_idcounty_codeobserved_periodquartereghp_flag
  1. 1One row per Medicare Advantage / Part C plan-segment offered IN one county, per contract year, per quarterly PUF snapshot, per EGHP flag.
  2. 2The 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).
  3. 3Plainer: 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).
  4. 4It 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 read it

  1. 1Scope MA plans to a market and a single clean annual snapshot.
  2. 2Example: 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'.
  3. 3Answers: '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

  1. 1NO 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.
  2. 2(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.
  3. 3segment_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).
  4. 4Heavy 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.
  5. 5county_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.

Columns (27)

ColumnTypeMeaning
contract_idstringCMS 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_idstring3-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_idstringPlan 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_codestring5-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_yeardoubledouble. 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.
quarterdoubledouble 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_idstringComposite 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_yearstringstring. The contract year as text (e.g. '2015'). Redundant with report_year (verified equal after cast) and with observed_period year. 100% populated.
countystringHuman-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_flagstringEmployer 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.
orgtypestringCMS 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_flagstringPartial 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_covstringPBP 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_typestringPBP 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_iddoubledouble. 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_flagstringPending-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.
stcdstring2-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.
versiondoubledouble. DEAD/CONSTANT — always 0.0 across all rows. No information; not a real version dimension.
observed_perioddatedate, 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_atdatedate 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_startdatedate = 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_enddatedate = 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_sequenceintint 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_revisionintint, constant = 1 across the sample/observed data. The parser revision that produced the row; no current variation. Lineage field.
parser_artifact_hashstringsha256 of the parser artifact/code version that produced the row (e.g. '7853ed8e...'). Lineage/reproducibility; not a business key.
source_urlstringThe 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_hashstringsha256 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

describe_data_source(source_fqn="medicare_silver.pbp_plan_area")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.pbp_plan_area WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND county_code = '<value>' AND observed_period = '<value>' AND quarter = '<value>' AND eghp_flag = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keycontract_id
Joinhow_to_join(source_fqn="medicare_silver.pbp_plan_area")
Raw doorAthena (read-only via query_substrate): medicare_silver.pbp_plan_area
NoteGrain-governed; live in Glue / query_substrate. Source family pbp_benefits.

This page as markdown: /docs-md/data/medicare_silver_pbp_plan_area.md · every page in one file: /llms-full.txt