Medicare Advantage, Part D, Medicaid

PBP transportation (cleaned view)

medicare_semantic.vw_pbp_transportation·live·semantic_view·annual

Rows

N/A

Columns

History

report_year 2001 to report_year 2026

Card

yesgrain, traps, filters

Typed tools

What it is

1A semantic, analysis-ready view of the Medicare Advantage Non-Emergency Medical Transportation (NEMT) benefit as filed in the CMS Plan Benefit Package (PBP) Public Use File, section B10b.
2One row tells you, for a specific MA plan in a specific contract year, whether it offers member transportation, the per-trip copay, the trip limit (e.g.
324 trips/year), trip type, destination scope, and mandatory-vs-supplemental status — fully decoded from CMS numeric PBP codes plus heavy provenance/normalization metadata.
4It is the cost-of-transportation-benefit fact for MA supplemental-benefit analysis.

Grain

Key Grain

Key	Grain
`contract_idplan_idsegment_idreport_yearquarter`	1One row per MA plan segment per contract year per quarterly PUF re-release snapshot. 2The MINIMAL verified-unique key is (contract_id, plan_id, segment_id, report_year, quarter): tested at 201,488 distinct = 201,488 total rows (exact). 3Most plan-years appear in only one quarter (75,577 of them), but later years (2018+) are re-published up to 4 times a year, and each quarterly republish is a separate row with its own snapshot. 4There is exactly one PBP bid `version` per grain key, so `version` is NOT part of the grain. 5NEMT in v1 is modeled as a single atomic row per plan-year-quarter (transportation_combined_package_status='atomic_grain_use_helper_view_for_overlay'); it does NOT explode into one-row-per-mode or one-row-per-trip-type, even though a plan may cover several modes.

contract_idplan_idsegment_idreport_yearquarter

1One row per MA plan segment per contract year per quarterly PUF re-release snapshot.
2The MINIMAL verified-unique key is (contract_id, plan_id, segment_id, report_year, quarter): tested at 201,488 distinct = 201,488 total rows (exact).
3Most plan-years appear in only one quarter (75,577 of them), but later years (2018+) are re-published up to 4 times a year, and each quarterly republish is a separate row with its own snapshot.
4There is exactly one PBP bid `version` per grain key, so `version` is NOT part of the grain.
5NEMT in v1 is modeled as a single atomic row per plan-year-quarter (transportation_combined_package_status='atomic_grain_use_helper_view_for_overlay'); it does NOT explode into one-row-per-mode or one-row-per-trip-type, even though a plan may cover several modes.

How to read it

1To find which MA plans offered $0-copay NEMT in 2026 and their annual trip allowance, query the latest snapshot per plan-year and filter to offered: SELECT contract_id, plan_id, segment_id, copay_amount, limit_quantity_per_year, transportation_trip_type_label, transportation_benefit_type_label FROM medicare_semantic.vw_pbp_transportation WHERE report_year=2026 AND quarter=(SELECT max(quarter) FROM medicare_semantic.vw_pbp_transportation WHERE report_year=2026) AND offered_status='offered' AND copay_amount=0.
2This answers 'which plans give free member transportation and how many trips/year' — use limit_quantity_per_year (already annualized) for cross-plan comparison, and watch transportation_trip_type_label since a 'One-way' cap of 24 ≈ 12 round trips.

Traps

1report_year is the real time axis and the profiler MISSED it (no 'year' in its key heuristics). The true minimal grain is (contract_id, plan_id, segment_id, report_year, quarter) — verified 201,488 distinct = 201,488 rows. quarter is NOT a coverage quarter; it is which quarterly re-release of that year's PBP PUF the row came from (maps 1:1 to source_url). There is NO is_current column: to get the final filing for a plan-year, take MAX(quarter) per (contract,plan,segment,report_year).
2Three boolean-flavored offer states, and NULL is meaningful. offered_status = not_offered (66%) / offered (29%) / offered_unknown (5%). transportation_offered is NULL exactly for the 'offered_unknown' rows — ALL of report_year 2001 plus scattered 2018–2024. Never treat NULL offered as 'not offered'; filter offered_status explicitly when computing adoption rates.
3This benefit has NO dollar amount. benefit_amount, benefit_amount_kind/period/annualized/pmpm, coinsurance_pct, and max_oop_applies are 100% NULL. The only cost field is copay_amount ($0–$350, present in 32,190 rows ≈ only when offered). transportation_copay_amount_min always == copay_amount; _max differs in just 28 rows (mode-specific copays). The 'limit' is trip COUNT (limit_quantity, unit always 'trips'), not money.
4transportation_mode_label is 100% NULL — the only way to know covered modes is transportation_mode_code, which is a variable-length BITMASK string (lengths 4/5/6, e.g. '011010') whose width grew over the years as CMS added mode columns. Decode position-by-position; do not treat it as a single ordinal code. Likewise trip_type codes 3/4/5 have NULL labels.
5~15 columns are single-value constants that encode the view's SCOPE, not data: benefit_category='transportation', transportation_trip_purpose='non_emergency_medical', transportation_sub_benefit='nemt_plan_allowed', transportation_ssbci_chronic_care_status='not_applicable_v1_nemt_only' (this view EXCLUDES SSBCI/chronically-ill transportation), network_type='in_network', confidence_tier='source_faithful', consumer_document_reconciliation_status='not_checked' (NOT reconciled against SB/EOC consumer docs), source_pbp_section='B10B'. lmi_authorities is empty-string for all rows.
6version is the PBP bid-submission version (0–14+) and is NOT part of the grain (exactly one version per grain key). sha256_content_hash is a per-FILE hash (40 distinct = 40 source ZIPs), not a row identifier — do not use it to dedupe rows. For per-mode/combined views the data tells you to use a separate overlay/helper view (transportation_combined_package_status='atomic_grain_use_helper_view_for_overlay'); this table is the atomic per-plan-year grain and does NOT roll up across modes or trip types (aggregation_policy).

Columns (85)

Column	Type	Meaning
`contract_id`	string	CMS MA contract ID (e.g. H0104, E5088). 1,972 distinct. Part of grain. H/R = MA contracts, E = employer/EGWP.
`plan_id`	string	Plan ID within the contract (3-char, e.g. '001','801'). 656 distinct. Part of grain. Combine with contract_id for the full plan identity.
`segment_id`	string	Plan segment / service-area segment ('0' for most; up to '26'). 27 distinct. Part of grain; a single plan_id can have multiple geographic segments with different benefits.
`version`	double	CMS PBP bid submission version of this plan filing (0–14+, higher = later re-submission). NOT part of the grain — exactly one version per (contract,plan,segment,year,quarter). Tells you which bid iteration the row reflects.
`report_year`	double	Contract/benefit year the plan is in effect (2001–2026, 26 distinct). This is the real time axis; profiler missed it as a key. Part of grain.
`quarter`	double	Which quarterly re-release of that year's PBP PUF the row came from (1–4). NOT a calendar quarter of coverage — it is the publication snapshot. Maps 1:1 to source_url (e.g. pbp-benefits-2024-quarter-1.zip). Part of grain; there is no is_current flag, so to get the latest filing for a plan-year take MAX(quarter).
`benefit_category`	string	Constant 'transportation' for all 201,488 rows. Tag identifying this slice of the unified PBP semantic layer.
`cost_share_string`	string	Human-readable cost share, e.g. '$0 copay'. Populated only when a copay exists (32,190 rows); NULL otherwise. coinsurance never appears here.
`copay_amount`	double	Per-trip copay in dollars. Range $0–$350. Non-null for 32,190 rows — present essentially only when offered_status='offered'; always NULL when not_offered. Always equals transportation_copay_amount_min.
`coinsurance_pct`	double	100% NULL. Transportation NEMT is copay-only in this PUF section; coinsurance is never used. Ignore this column for analysis.
`limit_text`	string	Raw limit string as filed, e.g. '12.0 trips/3' (12 trips, periodicity code 3=year). NULL when no limit. Source text behind the parsed limit_* columns.
`auth_required`	boolean	Boolean — prior authorization required for the benefit. Non-null for 48,873 rows; NULL = not stated / not offered.
`referral_required`	boolean	Boolean — referral required. Non-null for 58,882 rows; NULL where not stated.
`network_type`	varchar(10)	Constant 'in_network'. This view carries only the in-network view of the benefit.
`max_oop_applies`	boolean	100% NULL. Whether the benefit counts toward MOOP is not populated for transportation in this section.
`limit_quantity`	double	Number of trips allowed (the cap), unit = trips. Range 1–1,500. Non-null for 44,652 rows (the plans that cap trips). NULL = no cap stated or not offered.
`limit_unit`	varchar(10)	Constant 'trips' for all rows. The unit of limit_quantity.
`limit_period_code`	string	CMS periodicity code for the trip limit: 3=Every year (dominant), 6=Every month, 5=Every three months, 4=Every six months, 7=other. NULL = no periodic limit. Drives limit_period_label and the per-year annualization.
`limit_period_label`	varchar(18)	Decoded label of limit_period_code ('Every year','Every month','Every three months','Every six months'). NULL when no limit.
`limit_quantity_per_year`	double	limit_quantity annualized using limit_period_code (e.g. 2 trips/month -> 24/year). Non-null for 43,830 rows, max 1,500. Use THIS for cross-plan trip-allowance comparison; it normalizes monthly/quarterly limits to an annual figure.
`benefit_amount`	double	100% NULL. Transportation has no dollar-allowance benefit (unlike flex/OTC benefits), so all benefit_amount* columns are empty. Do not expect a dollar value here.
`benefit_amount_kind`	string	100% NULL (no dollar benefit on this benefit).
`benefit_amount_period_code`	string	100% NULL (no dollar benefit).
`benefit_amount_period_label`	string	100% NULL (no dollar benefit).
`benefit_amount_annualized`	double	100% NULL (no dollar benefit).
`benefit_amount_pmpm`	double	100% NULL (no dollar benefit).
`normalization_status`	varchar(36)	How the limit was normalized: 'not_populated' (142,934 — no numeric limit, usually not offered), 'normalized' (44,602 — limit parsed+annualized), 'offered_no_numeric_amount' (13,902 — offered but no number), 'amount_present_period_not_annualized' (50 edge cases).
`normalization_basis`	varchar(20)	Constant 'cms_periodicity_code' — annualization is driven by the CMS periodicity code, not free text.
`aggregation_policy`	varchar(47)	Constant 'trip_count_no_rollup_across_modes_or_trip_types' — declares that trip counts are NOT summed across transport modes or one-way/round-trip; treat each row's limit as the filed scope, do not aggregate.
`offered_status`	varchar(15)	Whether the plan offers transportation: 'not_offered' (132,795 ≈ 66%), 'offered' (58,504 ≈ 29%), 'offered_unknown' (10,189 ≈ 5%, flag indeterminate — all of 2001 plus scattered 2018–2024). The headline offer flag for adoption analysis.
`numeric_amount_observability`	varchar(31)	Two values: 'not_offered' (132,795) and 'offered_without_numeric_amounts' (68,693). Note: 'offered_without_numeric_amounts' covers ALL offered rows here because there is no dollar amount — copay/limit live in their own columns.
`null_amount_reason`	varchar(26)	Why an amount field is null: 'not_offered' (132,795), 'not_null_numeric_amount' (44,652 = has a limit number), 'unlimited' (11,119), 'unknown_requires_crosswalk' (10,090), 'offered_no_numeric' (2,832). The key for interpreting NULLs correctly.
`annualization_policy`	varchar(17)	Constant 'not_numeric' — there is no dollar amount to annualize (trip-count annualization is handled by limit_quantity_per_year instead).
`combined_limit_policy`	varchar(29)	Constant 'none_detected' — no shared/combined limit across benefits was detected for transportation in v1.
`consumer_document_reconciliation_status`	varchar(11)	Constant 'not_checked' — these values have not been reconciled against consumer-facing docs (SB/EOC); they are straight from the bid filing.
`confidence_tier`	varchar(23)	Constant 'source_faithful' — values are faithful to the CMS source with no inference/override applied.
`cost_share_populated_in_bid`	boolean	Boolean: True for exactly 32,190 rows — equals 'a copay is present'. True when copay_amount is non-null, False (169,298) otherwise. Quick filter for 'has a stated cost share'.
`has_coinsurance`	boolean
`has_copay`	boolean
`transportation_benefit_type_code`	string	CMS code: 2=Mandatory (55,072), 1=Additional (444), 3=Optional (177), 4=Both (78), NULL=not offered (145,717). 'Mandatory' = basic supplemental included in premium; Optional/Additional = buy-up.
`transportation_benefit_type_label`	varchar(10)	Decoded label of the above: Mandatory / Additional / Optional / Both. NULL when not offered.
`transportation_combined_package_status`	varchar(40)	Constant 'atomic_grain_use_helper_view_for_overlay' — tells you THIS view is the atomic per-plan-year grain; to see the benefit combined across modes/trip-types use a separate helper/overlay view.
`transportation_copay_amount_max`	double	Max per-trip copay across modes for the plan. Non-null for 32,190 rows, max $350. Differs from _min in only 28 rows (plans with mode-specific copays); otherwise equals copay_amount.
`transportation_copay_amount_min`	double	Min per-trip copay. Non-null 32,190, always equals copay_amount. With _max it gives the copay range when a plan varies copay by mode (rare, 28 rows).
`transportation_destination_scope_code`	string	CMS code: 1=Plan-approved Health-related Location (55,672), 2=Any Health-related Location (2,832), NULL=not offered (142,984). How restrictive the allowed destinations are.
`transportation_destination_scope_label`	varchar(37)	Decoded: 'Plan-approved Health-related Location' / 'Any Health-related Location'. NULL when not offered.
`transportation_has_max_dollar`	boolean
`transportation_is_unlimited`	boolean	Boolean — trips are unlimited. True 11,119, False 44,652, NULL 145,717 (not offered / not stated). When True, expect limit_quantity NULL with null_amount_reason='unlimited'.
`transportation_is_unlimited_code`	string	CMS code form: 1=Yes(unlimited), 2=No, NULL=n/a. Backs transportation_is_unlimited.
`transportation_is_unlimited_label`	varchar(3)	Decoded 'Yes'/'No', NULL when n/a.
`transportation_leg_code`	varchar(4)
`transportation_leg_in_ssbci_package_list`	boolean
`transportation_max_dollar`	double
`transportation_max_dollar_period_code`	string
`transportation_max_dollar_period_description`	string
`transportation_max_dollar_period_label`	varchar(18)
`transportation_mode_code`	string	Variable-length BITMASK string of covered transport modes (lengths 4/5/6, e.g. '011010','000001'). Each position is a mode flag (cab/van/wheelchair-van/public-transit/etc.); the width grew over the years as CMS added mode columns. Non-null only when offered. NOT a single ordinal code — decode position-by-position.
`transportation_mode_label`	string	100% NULL — the decoded human-readable mode list is NOT populated. GOTCHA: transportation_mode_code is the ONLY way to know modes, and it is a raw multi-flag bitmask with no label.
`transportation_offered`	boolean	Boolean offer flag: True 58,504, False 132,795, NULL 10,189. NULL is meaningful = 'offered_unknown' (offered_status), concentrated in 2001 (entire year) and 2018–2024. Use COALESCE/treat NULL explicitly when computing offer rates.
`transportation_offered_code`	string	CMS code: 1=Yes (58,504), 2=No (132,795), NULL (10,189). Backs transportation_offered.
`transportation_offered_label`	varchar(3)	Decoded 'Yes'/'No', NULL when unknown.
`transportation_package_id`	string
`transportation_package_name`	string
`transportation_package_type_code`	string
`transportation_package_type_label`	string
`transportation_ssbci_chronic_care_status`	varchar(27)	Constant 'not_applicable_v1_nemt_only' — this view is scoped to standard NEMT only; it does NOT cover SSBCI (Special Supplemental Benefits for the Chronically Ill) transportation. Do not use it to measure SSBCI/chronic transportation.
`transportation_sub_benefit`	string	Constant 'nemt_plan_allowed' — identifies the sub-benefit as plan-allowed non-emergency medical transportation.
`transportation_targeting`	varchar(36)
`transportation_trip_purpose`	varchar(21)	Constant 'non_emergency_medical' — all rows are NEMT; emergency ambulance is a different benefit/section.
`transportation_trip_type_code`	string	CMS code: 1=One-way (51,390), 2=Round Trip (4,112), 4 (178), 3 (80), 5 (15), NULL=not offered (145,713). How the trip limit is counted.
`transportation_trip_type_label`	varchar(10)	Decoded: 'One-way' / 'Round Trip'; codes 3/4/5 have NULL labels (label coverage incomplete — 145,986 NULL). GOTCHA: a 'one-way' 24-trip limit ≈ 12 round trips; trip_type matters when comparing limits.
`source_pbp_section`	varchar(4)	Constant 'B10B' — the PBP form section for ambulance/transportation supplemental benefits this data is parsed from.
`source_silver_table`	varchar(54)	Constant 'medicare_silver.pbp_section_b10_amb_trans' — the upstream silver table this semantic view is built on. Lineage pointer.
`lmi_authorities`	string	Empty string ('') for 100% of rows (not NULL). LMI/authority provenance is unused for this benefit; carries no information here.
`source_url`	string	The exact CMS PBP PUF ZIP this row was ingested from. Maps 1:1 to (report_year, quarter): 40 distinct URLs, e.g. pbp-benefits-2024-quarter-1.zip, pbp-benefits-2026.zip. Provenance + how to fetch the raw file.
`sha256_content_hash`	string	Content hash of the SOURCE FILE, not the row: 40 distinct hashes = 40 distinct source_urls (one hash per ingested ZIP). Use for file-level provenance/integrity, NOT as a row identifier.
`section_d_group_index`	int
`section_d_group_name`	string
`section_d_group_amount`	double
`section_d_group_period_code`	string
`section_d_group_period_label`	string
`section_d_group_amount_annualized`	double
`section_d_group_category_codes`	string
`section_d_group_count`	bigint
`section_d_group_link_basis`	string

Query it

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

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