PBP inpatient hospital (cleaned view)

What it is

1. 1A Medicare Advantage (Part C) plan benefit-design table giving the inpatient acute-hospital cost-sharing terms (per-day copay schedule, coinsurance/auth/referral flags, lifetime-reserve / additional-days terms) for every MA plan, derived from CMS PBP Public Use Files section B1A; it captures what the ENROLLEE pays for an inpatient stay (not provider-paid rates).

Grain

How to read it

1. 1Find the current inpatient out-of-pocket cost design for a specific MA plan: SELECT contract_id, plan_id, segment_id, cost_share_string, copay_amount, tier1_copay_interval1_end_day, tier1_copay_interval2_amount, has_coinsurance, auth_required, has_max_enrollee_oop FROM medicare_semantic.vw_pbp_inpatient_hospital WHERE contract_id='H0028' AND plan_id='014' AND report_year=2026 AND quarter=1; - returns one row: e.g. $375/day copay for days 1-7 then $0, prior-auth required, counts toward MOOP.
2. 2To trend a market: GROUP BY report_year, AVG(copay_amount) WHERE copay_amount IS NOT NULL (remembering ~50% null and coinsurance plans excluded).
3. 3Join to hospital/commercial rate tables on the MA plan to compare member cost share vs provider-paid inpatient rates.

Traps

1. 1Real grain is just (contract_id, plan_id, segment_id, report_year, quarter) - 179,479 rows, 0 duplicates. The profiler reported a 9-column superset; the extra period/code columns (limit_period_code, benefit_amount_period_code, confidence_tier, additional_days_*_code, benefit_period_code) are passengers, not key columns. Also: segment_id MUST be in the key (drop it and segmented plans collide), and 'version' is functionally determined by the plan-period (exactly 1 per grain) so it never splits rows.
2. 2Time dimension is report_year + quarter, NOT quarter alone. The profiler's 'temporal' block picked quarter (1-4) as the time column and reported earliest=1.0/latest=4.0 - that is wrong. True history is report_year 2010-2026 across 31 distinct year-quarter snapshots. Periodicity is quarterly in recent years but early years have only 1 release/year and there are gaps (e.g. 2020 missing Q3, 2024 missing Q2).
3. 3Roughly a third of all columns are dead scaffolding or fully NULL. 100%-NULL: coinsurance_pct, limit_text, max_oop_applies, the entire limit_* and benefit_amount_* families, additional_days_offered, inpatient_sub_benefit, normalization_*, aggregation_policy, benefit_amount_kind. Single-constant: benefit_category, network_type, offered_status, numeric_amount_observability, null_amount_reason, annualization_policy, combined_limit_policy, consumer_document_reconciliation_status, confidence_tier, source_pbp_section, source_silver_table, lmi_authorities. Notably offered_status='offered' is meaningless; the real offered signal is inpatient_offered (False for 60k rows).
4. 4Numeric cost is only ~50% populated and copays-only. copay_amount/cost_share_string are present for 90,411 rows; coinsurance is NEVER numerically captured (coinsurance_pct is 100% NULL, cost_share_string is always '$X copay'). The constants numeric_amount_observability='offered_without_numeric_amounts' and null_amount_reason='unknown_requires_crosswalk' explain it: ~36k coinsurance-based plans and many copay plans have the dollar amount un-extracted. So a NULL copay does NOT mean $0 - it means not-captured. copay_amount is exactly equal to tier1_copay_interval1_amount.
5. 5Boolean flags are tri-state and one is broken. auth_required/referral_required/inpatient_offered are True/False/NULL where NULL = not-captured (not 'no'). Critically, tier1_has_copay is unreliable/inverted: it is False for all 90,411 rows that actually have a numeric copay and True only for 5,705 rows with no copay - use copay_amount IS NOT NULL or has_copay to detect a copay, never tier1_has_copay.
6. 6Provenance columns are file-level, not row-level. sha256_content_hash, source_url, and the year-quarter are all 1:1 (31 each) - one CMS PBP-benefits ZIP per release. The hash identifies the source file, not the row, so it cannot dedup or key individual records. This view is benefit-DESIGN (member cost share) for the inpatient acute section B1A only; it is not provider-paid rates and not out-of-network, and the per-stay copay schedule is a per-DAY copay for an initial day band (e.g. days 1-5 at $325, then $0 through day 90), not a flat per-admission amount.

Columns (189)

Query it

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