Medicare Advantage, Part D, Medicaid

PBP health professionals (cleaned view)

medicare_semantic.vw_pbp_health_professionals·live·semantic_view·Annual plan years 2012–2026, with irregular intra-year quarterly PBP PUF snapshots (early years annual/Q1-only; later years 2–4 quarters, some quarters missing). 29 distinct source snapshot files.

Rows

N/A

Columns

History

2012 (report_year 2012, Q1) to 2026 (report_year 2026, Q1)

Card

yesgrain, traps, filters

Typed tools

What it is

1A semantic-layer view of Medicare Advantage member cost-sharing for office visits to health professionals (primary care and specialist), by plan and snapshot, used to answer 'what does an MA enrollee pay to see their doctor' and to track that across plans/years.

Grain

Key Grain

Key	Grain
`contract_idplan_idsegment_idreport_yearquarterhealth_professionals_sub_benefit`	1One row per Medicare Advantage plan-segment per benefit snapshot per professional-visit type. 2The MINIMAL verified-unique key is (contract_id, plan_id, segment_id, report_year, quarter, health_professionals_sub_benefit). 3Verified: 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). 4In 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.

contract_idplan_idsegment_idreport_yearquarterhealth_professionals_sub_benefit

1One row per Medicare Advantage plan-segment per benefit snapshot per professional-visit type.
2The MINIMAL verified-unique key is (contract_id, plan_id, segment_id, report_year, quarter, health_professionals_sub_benefit).
3Verified: 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).
4In 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 read it

1Get 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.
2Answers: which MA plan-segments offer a $0 primary-care visit in the 2026 plan year.
3(Always pin report_year + quarter to one snapshot; otherwise you mix 15 years of history.)

Traps

1The 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).
2No 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.
3report_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.
4Massive 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.
5Cost-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.

Columns (76)

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.

Query it

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

Item	Value
Typed tools that read it	get_pbp_benefit
Identity key	`contract_id,plan_id,segment_id`
Join	`how_to_join(source_fqn="medicare_semantic.vw_pbp_health_professionals")`
Raw door	Athena (read-only via query_substrate): medicare_semantic.vw_pbp_health_professionals
Note	Cleaned PBP reasoning surface (categories/health_professionals/contract.yaml); raw is pbp_section_*.

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