# PBP health professionals (cleaned view)

`medicare_semantic.vw_pbp_health_professionals` · Medicare Advantage, Part D, Medicaid · status live

Cleaned source-faithful PBP health professionals rows: era-normalized, CMS codes decoded, with offered_status/numeric_amount_observability/null_amount_reason/annualization_policy/combined_limit_policy/confidence_tier. The agent reasoning su

**Grain.** One row per Medicare Advantage plan-segment per benefit snapshot per professional-visit type. The MINIMAL verified-unique key is (contract_id, plan_id, segment_id, report_year, quarter, health_professionals_sub_benefit). Verified: GROUP BY on these 6 columns yields 342,958 groups with max group size = 1, exactly equal to the 342,958 total rows; dropping ANY one of the 6 reduces the distinct count (so none is removable). In words: each MA plan-segment contributes exactly two rows per PBP snapshot — one for primary_care and one for specialist. network_type, source_pbp_section, and version add no distinctness (all functionally determined by the key), so they are NOT part of the grain.

**How to use it.** Get the current $0-PCP-copay landscape: SELECT contract_id, plan_id, segment_id, copay_amount, coinsurance_pct, cost_share_string FROM medicare_semantic.vw_pbp_health_professionals WHERE health_professionals_sub_benefit='primary_care' AND report_year=2026 AND quarter=1 AND copay_amount=0. Answers: which MA plan-segments offer a $0 primary-care visit in the 2026 plan year. (Always pin report_year + quarter to one snapshot; otherwise you mix 15 years of history.)

**Traps.**
- The grain is finer than it looks: it needs health_professionals_sub_benefit. Each plan-segment-snapshot has TWO rows (primary_care + specialist). The table profiler's proposed key (with limit_period_code, benefit_amount_period_code) is wrong — those columns are 100% NULL here. Correct minimal key = contract_id+plan_id+segment_id+report_year+quarter+health_professionals_sub_benefit (342,958 rows, verified max-group=1).
- No is_current / latest-snapshot column. This is append-only snapshot history: every (report_year, quarter) is retained and a plan appears many times (e.g. H0028/004 specialist copay went $5→$25→$40→$35 across years). To get current cost share, filter to MAX(report_year) and the latest quarter yourself; do NOT aggregate raw across years or you double-count the same plan.
- report_year (2012–2026) is the real time axis; quarter (1–4) is intra-year and IRREGULAR (2012–2017 = Q1 only; later years have 2–4 quarters, and some quarters are missing, e.g. 2020 has no Q3). Within a single year, the same plan repeats across quarters with usually-identical values.
- Massive null-column trap: the entire limit_* and benefit_amount_* family, plus normalization_*, aggregation_policy, max_oop_applies, are 100% NULL (schema shared with sibling benefit views). And many status columns are single-valued CONSTANTS (offered_status='offered', confidence_tier='source_faithful', numeric_amount_observability='offered_without_numeric_amounts', null_amount_reason='unknown_requires_crosswalk', network_type='in_network', benefit_category='health_professionals'). Don't filter/segment on these expecting variation.
- Cost-share nulls are semantically split: has_copay/has_coinsurance/has_deductible NULL (17,072 rows) means the cost share could NOT be parsed (always co-occurs with cost_share_string NULL) — different from explicit False ('no copay' was determined). copay_amount=0.0 is a REAL $0 copay (very common MA benefit), not missing. auth_required/referral_required NULL (~half of rows) means 'not captured', not 'no'. And copay_amount can differ from copay_amount_min/max (13,491 rows) — use min/max to detect tiered copays, not the single copay_amount.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS MA contract id (e.g. H0028). 1,594 distinct. Part of grain. Letter prefix (H/R/S) = contract type. |
| plan_id | string | Plan id (PBP number) within the contract, e.g. '004'. Zero-padded 3-char string. 656 distinct values but NOT unique alone; identity = contract_id+plan_id (16,758 distinct plans). Part of grain. |
| segment_id | string | Plan segment id (geographic/benefit segment), e.g. '0','1','2'. 26 distinct values; most plans are single-segment '0'. contract+plan+segment = 18,050 distinct plan-segments. Part of grain. |
| version | double | CMS PBP plan record version (bid resubmission number), 1.0–27.0. Functionally determined by the grain (0 grain-combos have >1 version), so it is a per-row attribute, NOT a dedup/snapshot axis. Higher numbers are rarer (later resubmissions). |
| report_year | double | Benefit/plan year, 2012.0–2026.0 (15 years). THIS is the real history axis. Stored as double. |
| quarter | double | PBP PUF release quarter within the year (1.0–4.0). Intra-year snapshot marker, NOT the primary time axis. Coverage is irregular: 2012–2017 have only Q1; later years have 2–4 quarters (e.g. 2020 has Q1,Q2,Q4 — no Q3). Together (report_year, quarter) = the snapshot. |
| benefit_category | string | CONSTANT = 'health_professionals' for all 342,958 rows. View tag; carries no information here. |
| cost_share_string | string | Human-readable rendering of the member cost share, e.g. '20.0%', '$0 copay', '$40.0 copay', or combos like '$40.0 copay + 20.0%'. NULL for 64,046 rows (cost share not specified/unparseable). Most common: '20.0%' (95,109), '$0 copay' (60,933). |
| copay_amount | double | Representative member copay in USD, 0–60, median ~$12. Populated for 181,195 rows. 0.0 is a REAL value ($0 copay, very common in MA). NOT necessarily the only copay — see copay_amount_min/max for ranges. |
| coinsurance_pct | double | Representative member coinsurance percent, 0–40, median 20. Populated for 98,556 rows. A plan typically has copay OR coinsurance (only 1,076 rows have both flags). |
| limit_text | string | 100% NULL in this view. Shared schema column for benefit-quantity limits used by sibling benefit views; health-professional visits have no such limits. |
| auth_required | boolean | Prior authorization required for the visit (boolean). NULL for a large share (~53% of rows when combined with referral) meaning NOT captured, not 'false'. Distribution: True+no-referral 73,074; True+referral 37,180; many NULLs. |
| referral_required | boolean | Referral required to see this professional (boolean). NULL = not captured. Specialists more often require referral. |
| network_type | varchar(10) | CONSTANT = 'in_network' for all rows. This view holds only in-network cost share; out-of-network is not represented here. |
| max_oop_applies | boolean | 100% NULL. Schema-shared flag, not populated for this benefit. |
| limit_quantity | double | 100% NULL in this view (no visit-count limits captured). |
| limit_unit | string | 100% NULL in this view. |
| limit_period_code | string | 100% NULL in this view. NOTE: the table profiler proposed this as a grain key — it is useless (all-null); ignore it for grain. |
| limit_period_label | string | 100% NULL in this view. |
| limit_quantity_per_year | double | 100% NULL in this view. |
| benefit_amount | double | 100% NULL in this view. (Health professionals = cost-share benefit, not a dollar-allowance benefit like OTC/flex.) |
| benefit_amount_kind | string | 100% NULL in this view. |
| benefit_amount_period_code | string | 100% NULL in this view. Also wrongly proposed as a grain key by the profiler — ignore. |
| benefit_amount_period_label | string | 100% NULL in this view. |
| benefit_amount_annualized | double | 100% NULL in this view. |
| benefit_amount_pmpm | double | 100% NULL in this view. |
| normalization_status | string | 100% NULL in this view. |
| normalization_basis | string | 100% NULL in this view. |
| aggregation_policy | string | CONSTANT NULL for all rows. |
| offered_status | varchar(15) | CONSTANT = 'offered' for all rows. Every row here is an offered benefit (the view contains only offered health-professional benefits). |
| numeric_amount_observability | varchar(31) | CONSTANT = 'offered_without_numeric_amounts'. Declares that the benefit is offered but the source did not always carry a clean numeric amount (explains the copay/coinsurance NULLs). |
| null_amount_reason | varchar(30) | CONSTANT = 'unknown_requires_crosswalk'. When a numeric amount is null, the stated reason is that a crosswalk is needed — uniform for the whole view. |
| annualization_policy | varchar(17) | CONSTANT = 'not_numeric'. No annualization performed (no dollar allowance to annualize). |
| combined_limit_policy | varchar(29) | CONSTANT = 'none_detected'. No combined/shared limits detected for this benefit. |
| consumer_document_reconciliation_status | varchar(11) | CONSTANT = 'not_checked'. Values have NOT been reconciled against consumer-facing plan documents (Summary of Benefits). |
| confidence_tier | varchar(23) | CONSTANT = 'source_faithful'. Data is a faithful pass-through of the CMS PBP source (no enrichment/inference applied). |
| benefit_layer | string |  |
| benefit_unlimited | boolean |  |
| coinsurance_pct_max | double | Upper bound of a coinsurance RANGE. Populated for 98,556 rows; differs from coinsurance_pct_min in only 1,929 rows (i.e. usually a single value). |
| coinsurance_pct_min | double | Lower bound of the coinsurance range. |
| copay_amount_max | double | Upper bound of a copay RANGE. Use this (with min) to detect tiered/varying copays: copay_amount differs from min/max in 13,491 rows. |
| copay_amount_min | double | Lower bound of the copay range. When equal to copay_amount and copay_amount_max (167,704 of 181,195 copay rows), the plan has a single copay. |
| deductible_amount | double |  |
| deductible_basis | string |  |
| filed_service_name | string |  |
| has_coinsurance | boolean | Flag: plan charges coinsurance. NULL for the same 17,072 unparsed rows. copay and coinsurance are usually mutually exclusive. |
| has_copay | boolean | Flag: plan charges a copay. True 187,908; False 124,978; NULL 17,072. NULL ALWAYS co-occurs with cost_share_string NULL = cost share could not be parsed at all (flags never set), distinct from explicit False (parsed 'no copay'). |
| has_deductible | boolean | Flag: a deductible applies to this benefit. Almost always False (True only 2 rows); NULL for the 17,072 unparsed rows. |
| has_max_enrollee_oop | boolean | Flag: a max enrollee out-of-pocket applies. True only 109 rows; False 325,753; NULL 17,096. Effectively near-constant False. |
| health_professionals_offered | boolean |  |
| health_professionals_sub_benefit | string | The professional-visit type: exactly two values, 'primary_care' (171,479) and 'specialist' (171,479). PART OF GRAIN — this is what splits each plan-segment-snapshot into 2 rows. 1:1 with source_pbp_section (B7A=primary_care, B7C=specialist). |
| label_note | string |  |
| limit_period_text | string |  |
| max_enrollee_oop_amount | double |  |
| max_enrollee_oop_period_code | string |  |
| max_enrollee_oop_period_label | string |  |
| max_enrollee_oop_period_text | string |  |
| max_plan_benefit_amount | double |  |
| max_plan_benefit_offered | boolean |  |
| max_plan_benefit_period_code | string |  |
| max_plan_benefit_period_label | string |  |
| registry_l2_id | string |  |
| routine_chiropractic_unlimited | boolean |  |
| routine_podiatry_offered | boolean |  |
| routine_podiatry_unlimited | boolean |  |
| routine_podiatry_visit_count | double |  |
| routine_podiatry_visit_period_code | string |  |
| routine_podiatry_visit_period_label | string |  |
| routine_podiatry_visits_per_year | double |  |
| supplemental_benefit_type | string |  |
| telehealth_service_categories | string |  |
| source_pbp_section | varchar(3) | CMS PBP section code, exactly 'B7A' (primary_care) or 'B7C' (specialist). Perfectly 1:1 with health_professionals_sub_benefit, so redundant with it. |
| source_silver_table | varchar(42) | CONSTANT = 'medicare_silver.pbp_section_b7_health_prof'. The single upstream silver table feeding this view; lineage pointer. |
| lmi_authorities | string | CONSTANT single value: pipe-delimited URLs to LMI/CMS CY2026 PBP Service-Category-Descriptions and Part-C Bid-Review reference PDFs. Definitional authority for the benefit category, same for every row. |
| source_url | string | CMS PBP PUF zip download URL for that snapshot (e.g. pbp-benefits-2018-q4.zip). 29 distinct values = the 29 snapshot files; provenance for which release the row came from. |
| sha256_content_hash | string | SHA-256 of the source file content. 29 distinct values, 1:1 with source_url — the immutable fingerprint of each ingested snapshot file. Provenance/audit, not a row id. |

Tools that read it: get_pbp_benefit

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