Medicare Advantage, Part D, Medicaid

PBP preventive (cleaned view)

medicare_semantic.vw_pbp_preventive·live·semantic_view·quarterly

Rows

N/A

Columns

History

2007 Q1 to 2026 Q1 (18 distinct report_years 2007-2026; 32 distinct year x quarter snapshots; 2007-2017 are Q1-only and later years add only some quarters — e.g. 2020 = Q1/Q2/Q4 no Q3, 2021 = Q1 only, 2025/2026 = Q1 only so far, so the quarterly cadence is irregular/partial)

Card

yesgrain, traps, filters

Typed tools

What it is

1A semantic (curated) view over CMS Medicare Advantage Plan Benefit Package (PBP) Section B-14 "Preventive Services", exposing — for every MA plan-segment in every quarterly PBP filing snapshot — the three preventive-care sub-benefits (Medicare-mandated/ACA preventive, routine physical exam, and other supplemental preventive) with their member cost-share (copay / coinsurance), auth/referral requirements, and limit flags.
2It is the cost-share view of what MA plans charge (almost always $0) for preventive care, intended for benefit-design analysis and time-series tracking of MA preventive coverage.

Grain

Key Grain

Key	Grain
`contract_idplan_idsegment_idreport_yearquarterpreventive_sub_benefit`	1One row per MA plan-segment per quarterly PBP snapshot per preventive sub-benefit. 2The minimal VERIFIED unique key is (contract_id, plan_id, segment_id, report_year, quarter, preventive_sub_benefit): 555,516 distinct = 555,516 total rows, every key appearing exactly once. 3Equivalently each plan-segment-snapshot expands to exactly 3 rows (one per sub-benefit), and 555,516 / 3 = 185,172 distinct plan-segment-snapshots. version is NOT part of the grain — the view is already deduped to one PBP file version per snapshot (verified max 1 version per key).

contract_idplan_idsegment_idreport_yearquarterpreventive_sub_benefit

1One row per MA plan-segment per quarterly PBP snapshot per preventive sub-benefit.
2The minimal VERIFIED unique key is (contract_id, plan_id, segment_id, report_year, quarter, preventive_sub_benefit): 555,516 distinct = 555,516 total rows, every key appearing exactly once.
3Equivalently each plan-segment-snapshot expands to exactly 3 rows (one per sub-benefit), and 555,516 / 3 = 185,172 distinct plan-segment-snapshots. version is NOT part of the grain — the view is already deduped to one PBP file version per snapshot (verified max 1 version per key).

How to read it

1To get the current member cost-share for routine physical exams across MA plans: SELECT contract_id, plan_id, segment_id, copay_amount FROM medicare_semantic.vw_pbp_preventive WHERE report_year=2026 AND quarter=1 AND preventive_sub_benefit='routine_physical_exam' AND benefit_present_yn=TRUE.
2This answers 'what do MA plans charge members for a routine physical in 2026' (answer: $0 for the vast majority, with a small tail up to $25).
3For the ACA-mandated preventive package use preventive_sub_benefit='mandated_medicare_preventive' (copay and coinsurance are uniformly $0/0%).
4Always filter to one (report_year, quarter) snapshot.

Traps

1NO is_current / is_latest column. The view is append-style across snapshots: the SAME plan-segment appears once per sub-benefit in EVERY quarterly snapshot it was filed in. To get a point-in-time view you MUST filter to a single (report_year, quarter); the latest snapshot is 2026 Q1. Aggregating without a snapshot filter double-counts plans across years.
2preventive_sub_benefit (= source_pbp_section) is the column that makes the grain 3x and IS part of the unique key. Keying only on plan-segment-snapshot collapses to 185,172 rows. The three sub-benefits have COMPLETELY different population semantics: mandated_medicare is a constant $0 (copay=0, coins=0) ACA package; routine_physical_exam carries the only real copay tail ($0 mostly, up to $25); other_supplemental_preventive has copay/coinsurance 100% NULL (deferred to a 'v2' per-benefit detail).
3This is an 'offered-without-numeric-amounts' view (numeric_amount_observability and null_amount_reason are constants saying so): ALL benefit_amount_* columns, all normalization_*/aggregation_policy columns, and all structured limit_* columns are 100% NULL. limit_text's only value is the un-parsed string '2'. Do not treat absent dollar amounts as $0 for other_supplemental_preventive — they are simply not extracted here.
4Many columns are single-valued constants with no analytic signal: benefit_category='preventive', network_type='in_network', max_oop_applies=TRUE, offered_status='offered', confidence_tier='source_faithful', consumer_document_reconciliation_status='not_checked', annualization_policy='not_numeric', combined_limit_policy='none_detected', source_silver_table, lmi_authorities. cost_share_string is largely boilerplate per sub-benefit, not a parsed amount.
5Meaningful NULLs: has_max_plan_limit is NULL (139,844 rows) specifically on the mandated_medicare sub-benefit = 'not applicable', not missing. preventive_attestation='1' only ever appears within mandated_medicare_preventive. benefit_present_yn (not offered_status) is the real 'does the plan offer this service' flag — routine_physical is present in only ~54% of plan-snapshots even though every plan still gets a row.
6version (1-27) is NOT a grain column — it is already deduped to one version per snapshot, so do not add it to keys/joins. Use benefit_present_yn=TRUE (not offered_status, which is constant) to identify plans that actually offer a sub-benefit.

Columns (59)

Column	Type	Meaning
`contract_id`	string	CMS Medicare Advantage contract ID (1,832 distinct). Prefix encodes contract type: H = local MA HMO/PPO (1,793), R = regional PPO (37), E = employer/EGWP (2). First level of the plan key.
`plan_id`	string	3-char plan ID within the contract (e.g. '001','007','011'). 656 distinct values but only unique in combination with contract_id and segment_id.
`segment_id`	string	Plan segment / service-area segment ('0' for non-segmented plans; 26 distinct). Third part of the plan key.
`version`	double	PBP source-file version/revision number (1-27, 26 distinct). NOT part of the grain — already deduped to a single chosen version per plan-snapshot (verified max 1 version per grain key). Informational only.
`report_year`	double	Contract/benefit year of the PBP filing (2007-2026). Part of the snapshot key.
`quarter`	double	Quarter of the PBP PUF release within report_year (1-4). Part of the snapshot key. Most early years carry only Q1; later years add some additional quarters.
`benefit_category`	string	Constant label 'preventive' on every row (single-valued). Identifies this as the preventive-services view; not a discriminating dimension.
`cost_share_string`	string	Human-readable cost-share summary, but largely sub-benefit-specific BOILERPLATE: 'mandated_medicare_preventive' always = '$0 (ACA-mandated preventive)'; 'other_supplemental_preventive' always = 'see per-benefit-type detail (v2)' (amounts not extracted here); 'routine_physical_exam' is '$0 copay', a real '$X copay + Y%' string, or NULL. Do not parse the boilerplate as a real per-service amount.
`copay_amount`	double	Member copay in dollars. Populated only for mandated_medicare_preventive (always 0.0) and routine_physical_exam (0.0 on most, small tail of $3/$5/$10/$15/$20/$25). 100% NULL for other_supplemental_preventive (deferred to a future v2 per-benefit detail). 245,575 of 555,516 rows non-null.
`coinsurance_pct`	double	Member coinsurance percent. Populated (and always 0.0) ONLY for mandated_medicare_preventive (185,172 rows); NULL for the other two sub-benefits. Effectively a constant zero for the mandated package.
`limit_text`	string	Raw, UNINTERPRETED PBP limit field — the only observed non-null value is the bare string '2' (97,199 rows). The structured limit_* columns derived from it are all NULL here, so treat this as an un-parsed source code, not a usable quantity.
`auth_required`	boolean	Boolean: prior authorization required for the preventive sub-benefit. True on ~32.7K rows overall; varies by sub-benefit (mandated 18,241; other_supplemental 14,223; routine_physical 266).
`referral_required`	boolean	Boolean: referral required. True on ~27.4K rows; varies by sub-benefit (mandated 18,453; other_supplemental 8,248; routine_physical 665).
`network_type`	varchar(10)	Constant 'in_network' on every row (single-valued). This view carries only in-network cost-share; out-of-network is not represented.
`max_oop_applies`	boolean	Boolean — TRUE on 100% of rows. Indicates the benefit counts toward the plan MOOP. Constant, not discriminating.
`limit_quantity`	double	Structured numeric limit quantity. 100% NULL in this view (limit parsing not applied to preventive).
`limit_unit`	string	Unit for the limit quantity. 100% NULL in this view.
`limit_period_code`	string	Period code for the limit (e.g. per year). 100% NULL in this view.
`limit_period_label`	string	Human label for the limit period. 100% NULL in this view.
`limit_quantity_per_year`	double	Annualized limit quantity. 100% NULL in this view.
`benefit_amount`	double	Plan-paid benefit dollar amount (used by sibling PBP views with dollar allowances). 100% NULL for preventive — preventive has no dollar-allowance benefit.
`benefit_amount_kind`	string	Kind/type of benefit_amount. 100% NULL in this view.
`benefit_amount_period_code`	string	Period code for benefit_amount. 100% NULL in this view.
`benefit_amount_period_label`	string	Period label for benefit_amount. 100% NULL in this view.
`benefit_amount_annualized`	double	Annualized benefit_amount. 100% NULL in this view.
`benefit_amount_pmpm`	double	Benefit_amount expressed per-member-per-month. 100% NULL in this view.
`normalization_status`	string	Status of dollar-amount normalization. 100% NULL here (nothing to normalize because no numeric amounts).
`normalization_basis`	string	Basis used for normalization. 100% NULL in this view.
`aggregation_policy`	string	How multiple source rows were aggregated. 100% NULL in this view.
`offered_status`	varchar(15)	Constant 'offered' on every row (single-valued). Indicates the sub-benefit row was emitted because the section exists in the PBP; it does NOT by itself confirm the plan offers that specific service (see benefit_present_yn).
`numeric_amount_observability`	varchar(31)	Constant 'offered_without_numeric_amounts' on every row. Flags that this preventive view intentionally lacks fully-extracted dollar amounts — a key caveat for cost analysis.
`null_amount_reason`	varchar(30)	Constant 'unknown_requires_crosswalk' on every row. Explains why numeric amounts are absent: resolving them needs a downstream crosswalk (the v2 per-benefit detail).
`annualization_policy`	varchar(17)	Constant 'not_numeric' on every row — consistent with no numeric amounts to annualize.
`combined_limit_policy`	varchar(29)	Constant 'none_detected' on every row — no combined in/out-of-network limit detected for preventive.
`consumer_document_reconciliation_status`	varchar(11)	Constant 'not_checked' on every row — these PBP-derived values have NOT been reconciled against consumer-facing documents (SB/EOC). Provenance caveat.
`confidence_tier`	varchar(23)	Constant 'source_faithful' on every row — values are faithfully carried from the PBP source (not modeled/inferred). Single-valued.
`benefit_present_yn`	boolean	Boolean: whether the plan actually offers this preventive sub-benefit in this snapshot. TRUE on 417,812 rows; can be NULL/false otherwise. Per sub-benefit: mandated always present (185,172), other_supplemental present on 133,301, routine_physical present on only 99,339 (~54%). USE THIS, not offered_status, to gate 'does the plan offer it'.
`coinsurance_pct_max`	double
`copay_amount_max`	double	Upper bound of an observed copay range; non-null on the same 245,575 rows as copay_amount (mandated + routine_physical with a value).
`copay_amount_min`	double	Lower bound of an observed copay range; non-null on the same 245,575 rows as copay_amount.
`has_coinsurance`	boolean	Boolean flag that a coinsurance applies. True on 7,766 rows (mostly other_supplemental 5,369 + routine_physical 2,397; never set for the always-zero mandated package).
`has_copay`	boolean	Boolean flag that a (non-trivial) copay applies. True on 133,177 rows (other_supplemental 68,288 + routine_physical 64,889; never set for the mandated $0 package).
`has_deductible`	boolean	Boolean flag that a deductible applies to the preventive benefit. Extremely rare — TRUE on only 25 rows.
`has_max_enrollee_limit`	boolean	Boolean flag of a per-enrollee maximum limit. Rare — TRUE on 222 rows.
`has_max_plan_limit`	boolean	Boolean flag of a plan-level maximum limit. TRUE on 22,100 rows, FALSE on most, and NULL on 139,844 rows (the mandated_medicare rows where the concept does not apply). The NULLs are meaningful (not-applicable), not missing data.
`preventive_attestation`	string	Plan attestation flag, value '1' or NULL. '1' on 162,955 rows, ALL within mandated_medicare_preventive (a plan attestation specific to the Medicare-mandated package); NULL for the other two sub-benefits where it does not apply.
`preventive_offered`	boolean
`preventive_package_id`	string
`preventive_package_match_basis`	string
`preventive_package_name`	string
`preventive_package_type_code`	string
`preventive_package_type_label`	string
`preventive_sub_benefit`	string	The discriminator that triples the grain — one of exactly 3 values, each 185,172 rows and each tied 1:1 to a PBP sub-section: 'mandated_medicare_preventive' (B14A, the ACA/Medicare-mandated $0 package), 'routine_physical_exam' (B14B), 'other_supplemental_preventive' (B14C). Part of the unique key.
`preventive_targeting`	varchar(36)
`source_pbp_section`	varchar(4)	PBP source sub-section code, perfectly correlated with preventive_sub_benefit: B14A=mandated_medicare_preventive, B14B=routine_physical_exam, B14C=other_supplemental_preventive. Redundant with preventive_sub_benefit.
`source_silver_table`	varchar(56)	Constant 'medicare_silver.pbp_section_b14_preventive' — the upstream silver table this view is built from. Single-valued provenance pointer.
`lmi_authorities`	string	Constant URL to the LMI 'CY 2026 PBP Service Category Descriptions.pdf' — the authoritative definition document for the PBP service categories. Single-valued reference, not row-specific.
`source_url`	string	URL of the CMS PBP PUF zip the row came from — exactly one per snapshot (32 distinct URLs = 32 distinct year x quarter snapshots), e.g. .../pbp-pufs/pbp-benefits-2019-q1.zip. Provenance for the snapshot.
`sha256_content_hash`	string	Content hash of the source PBP file — exactly one per snapshot (32 distinct, 1:1 with source_url). Use to detect/dedup re-ingested identical files.

Query it

describe_data_source(source_fqn="medicare_semantic.vw_pbp_preventive")     # the card, live
query_substrate(sql="SELECT * FROM medicare_semantic.vw_pbp_preventive WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND report_year = '<value>' AND quarter = '<value>' AND preventive_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_preventive")`
Raw door	Athena (read-only via query_substrate): medicare_semantic.vw_pbp_preventive
Note	Cleaned PBP reasoning surface (categories/preventive/contract.yaml); raw is pbp_section_*.

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