# PBP inpatient hospital (cleaned view)

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

Cleaned source-faithful PBP inpatient hospital 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 surf

**Grain.** One row per Medicare Advantage plan-segment per CMS PBP release period. The minimal verified key is (contract_id, plan_id, segment_id, report_year, quarter): all 179,479 rows are distinct on this 5-column key with ZERO duplicate groups, and every combination appears exactly once. The profiler's 9-column key (adding limit_period_code, benefit_amount_period_code, confidence_tier, additional_days_benefit_type_code, additional_days_limit_code, benefit_period_code) was a superset — those extra columns are passenger attributes functionally determined by the plan-period, not grain discriminators.

**How to use it.** Find 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. To trend a market: GROUP BY report_year, AVG(copay_amount) WHERE copay_amount IS NOT NULL (remembering ~50% null and coinsurance plans excluded). Join to hospital/commercial rate tables on the MA plan to compare member cost share vs provider-paid inpatient rates.

**Traps.**
- Real 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.
- Time 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).
- Roughly 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).
- Numeric 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.
- Boolean 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.
- Provenance 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.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS MA contract ID (e.g. 'H0028'); the H/R/S-number identifying the Medicare Advantage organization contract. 1,680 distinct. Grain key part 1. |
| plan_id | string | Plan benefit package ID within the contract (e.g. '001','014'), 3-char zero-padded. 656 distinct values (repeats across contracts). Grain key part 2. (contract_id+plan_id) = the public plan identifier. |
| segment_id | string | PBP service-area segment within the plan ('0'..'25'). '0' for 92% of rows (single-segment plans); >0 = geographically segmented benefit variants. Grain key part 3 - must be included or rows collide. |
| version | double | CMS PBP file VERSION/revision number for that plan-period (1-27, right-skewed; the resubmission sequence). Functionally determined by the grain (exactly 1 version per plan-period) - a passenger attribute, NOT part of the key and NOT a Vlada snapshot marker. |
| report_year | double | PBP contract/benefit year (double: 2010.0-2026.0, 17 distinct). Grain key part 4 - the real time dimension together with quarter. Cast to int for joins. |
| quarter | double | CMS release quarter within report_year (1.0-4.0). Grain key part 5. NOTE: the profiler mislabeled this as THE time column; the true period is report_year+quarter. Not every year has 4 quarters. |
| benefit_category | string | CONSTANT = 'inpatient_hospital' for all rows. Scaffolding/label, carries no signal here. |
| cost_share_string | string | Human-readable cost share, e.g. '$325.0 copay'. Populated ~50% (90,411 rows, 292 distinct). ALWAYS a copay string - it NEVER expresses coinsurance (0 rows). NULL when no numeric copay was extracted. |
| copay_amount | double | Per-day inpatient copay (USD) for the FIRST stay-day band. Range $0-$2,230, median ~$293. Populated 90,411 rows (~50%). EXACTLY equal to tier1_copay_interval1_amount (0 mismatches) - it is a convenience surface of that field. |
| coinsurance_pct | double | 100% NULL across all rows. Placeholder - coinsurance percentages are NOT captured in this view even though ~36k plans are coinsurance-based. |
| limit_text | string | 100% NULL. Dead placeholder column. |
| auth_required | boolean | Prior authorization required for inpatient admission. Tri-state: True (125,337) / False (11,148) / NULL (42,994). NULL = not captured for that plan-period, NOT 'no'. |
| referral_required | boolean | Referral required. Tri-state: False (123,980) / True (39,046) / NULL (16,453). NULL = not captured. |
| network_type | varchar(10) | CONSTANT = 'in_network'. All rows are in-network terms; out-of-network is not represented in this view. Scaffolding. |
| max_oop_applies | boolean | 100% NULL. Dead placeholder (use has_max_enrollee_oop instead for the MOOP flag). |
| limit_quantity | double | 100% NULL. Dead placeholder. |
| limit_unit | string | 100% NULL. Dead placeholder. |
| limit_period_code | string | 100% NULL. Dead placeholder (the profiler listed it as a candidate key only because all-NULL is trivially constant). |
| limit_period_label | string | 100% NULL. Dead placeholder. |
| limit_quantity_per_year | double | 100% NULL. Dead placeholder. |
| benefit_amount | double | 100% NULL. Dead placeholder - the entire benefit_amount_* normalization family is unpopulated in this view. |
| benefit_amount_kind | string | 100% NULL. Dead placeholder. |
| benefit_amount_period_code | string | 100% NULL. Dead placeholder. |
| benefit_amount_period_label | string | 100% NULL. Dead placeholder. |
| benefit_amount_annualized | double | 100% NULL. Dead placeholder. |
| benefit_amount_pmpm | double | 100% NULL. Dead placeholder (per-member-per-month normalization not applicable/unpopulated here). |
| normalization_status | string | 100% NULL. Dead placeholder. |
| normalization_basis | string | 100% NULL. Dead placeholder. |
| aggregation_policy | string | 100% NULL. Dead placeholder. |
| offered_status | varchar(15) | CONSTANT = 'offered'. Scaffolding - do NOT use it to tell if inpatient is actually offered; use inpatient_offered (which is False for 60k rows). |
| numeric_amount_observability | varchar(31) | CONSTANT = 'offered_without_numeric_amounts'. A view-level data-quality flag stating the dollar amounts are not fully observable from this section alone. Explains why ~50% of copay/coinsurance values are NULL. |
| null_amount_reason | varchar(30) | CONSTANT = 'unknown_requires_crosswalk'. Documents WHY numeric amounts are missing: resolving them needs a join to the raw PBP crosswalk not yet applied. |
| annualization_policy | varchar(17) | CONSTANT = 'not_numeric'. Scaffolding. |
| combined_limit_policy | varchar(29) | CONSTANT = 'none_detected'. Scaffolding. |
| consumer_document_reconciliation_status | varchar(11) | CONSTANT = 'not_checked'. Provenance flag: these benefits have not been reconciled against consumer-facing plan documents (Summary of Benefits). |
| confidence_tier | varchar(23) | CONSTANT = 'source_faithful'. Means values are faithful to the raw PBP source (not editorially adjusted/inferred). |
| additional_days_amount | double | Cost (USD) for inpatient 'additional days' (beyond the standard interval, e.g. lifetime-reserve-style extra days). Populated only 2,797 rows - exclusively when additional_days_limit_code='2' (avg ~$110, range $1-$274). NULL otherwise. |
| additional_days_benefit_type_code | string | PBP code for the additional-days benefit type: '2' (dominant, 106k), '4' (37), '3' (2); NULL for 73k rows where additional-days terms weren't parsed. Raw CMS PBP code - meaning requires the PBP data dictionary. |
| additional_days_benefit_type_label | string |  |
| additional_days_coinsurance_interval1_begin_day | double |  |
| additional_days_coinsurance_interval1_end_day | double |  |
| additional_days_coinsurance_interval1_pct | double |  |
| additional_days_coinsurance_interval2_begin_day | double |  |
| additional_days_coinsurance_interval2_end_day | double |  |
| additional_days_coinsurance_interval2_pct | double |  |
| additional_days_coinsurance_interval_count | string |  |
| additional_days_copay_amount | double |  |
| additional_days_copay_interval1_amount | double |  |
| additional_days_copay_interval1_begin_day | double |  |
| additional_days_copay_interval1_end_day | double |  |
| additional_days_copay_interval2_amount | double |  |
| additional_days_copay_interval2_begin_day | double |  |
| additional_days_copay_interval2_end_day | double |  |
| additional_days_copay_interval_count | string |  |
| additional_days_cost_share_basis | string |  |
| additional_days_cost_share_lowest_tier | string |  |
| additional_days_cost_share_string | string |  |
| additional_days_cost_share_tier_count | string |  |
| additional_days_cost_varies_by_tier | boolean | Boolean: whether additional-days cost varies by provider tier. Populated 92,388 rows. |
| additional_days_limit_code | string | PBP code for the additional-days limit ('1' = standard/no extra cost dominant; '2' = the case where additional_days_amount is populated). NULL for 73k rows. |
| additional_days_offered | boolean | 100% NULL. Dead placeholder (infer offering from the code columns instead). |
| additional_days_tier2_coinsurance_interval1_begin_day | double |  |
| additional_days_tier2_coinsurance_interval_count | string |  |
| additional_days_tier2_copay_interval1_amount | double |  |
| additional_days_tier2_copay_interval1_begin_day | double |  |
| additional_days_tier2_copay_interval1_end_day | double |  |
| additional_days_tier2_copay_interval_count | string |  |
| additional_days_tier3_copay_interval_count | string |  |
| additional_days_unlimited | boolean |  |
| benefit_layer | varchar(38) |  |
| benefit_period_code | string | PBP benefit-period basis code: '3' (87,467, dominant, label not crosswalked), '1'='Per benefit period' (32,472), '2'='Per calendar year' (2,115), '4' (225, no label); NULL 57,200. Raw CMS code; the label column only resolves codes 1 and 2. |
| benefit_period_label | string | Human label for benefit_period_code, but ONLY populated for codes 1 ('Per benefit period') and 2 ('Per calendar year'). NULL for the dominant code '3' and code '4' - partial crosswalk. |
| benefit_period_other_text | string |  |
| coinsurance_structure_same_as_medicare_covered | boolean |  |
| copay_amount_max | double |  |
| copay_amount_max_resolved | double |  |
| copay_amount_min | double |  |
| copay_amount_min_resolved | double |  |
| copay_per_day_amount | double |  |
| copay_structure_same_as_medicare_covered | boolean |  |
| cost_share_basis | string |  |
| cost_share_lowest_tier | string |  |
| cost_share_tier_count | string |  |
| cost_share_varies_by_tier | boolean | Boolean: inpatient cost share varies by provider tier. Populated 155,766 rows (~87%). |
| deductible_amount | double |  |
| has_coinsurance | boolean | Boolean: plan uses coinsurance for inpatient. True for 36,358 rows. These rows have NO numeric copay AND no captured coinsurance_pct - their dollar cost is uncaptured here. Populated 166,770 rows. |
| has_copay | boolean | Boolean: plan uses a copay for inpatient. True 123,306 - but only ~73% of has_copay=True rows actually carry a numeric copay_amount (the rest are flagged-but-unextracted). Best presence signal alongside copay_amount IS NOT NULL. |
| has_deductible | boolean | Boolean: inpatient deductible applies. Populated 166,770 rows. |
| has_discharge_cost_share | boolean | Boolean: a per-discharge cost share applies. Populated 88,114 rows. |
| has_max_enrollee_oop | boolean | Boolean: inpatient costs count toward the plan's max enrollee out-of-pocket. Populated 166,746 rows. Use this, NOT the dead max_oop_applies column. |
| inpatient_hospital_in_package_category_list | boolean |  |
| inpatient_hospital_package_id | string |  |
| inpatient_hospital_package_match | string |  |
| inpatient_hospital_package_name | string |  |
| inpatient_hospital_package_type_code | string |  |
| inpatient_hospital_package_type_label | string |  |
| inpatient_hospital_targeted_file | string |  |
| inpatient_hospital_targeting | varchar(36) |  |
| inpatient_offered | boolean | Boolean: whether the plan ACTUALLY offers the inpatient acute benefit. True 106,323 / False 60,447 / NULL 12,709. THIS is the real 'is it offered' signal (offered_status is a meaningless constant). False rows are plans that don't offer/segment this benefit. |
| inpatient_sub_benefit | varchar(49) | 100% NULL. Dead placeholder (this view is the single inpatient-acute benefit; no sub-benefit split populated). |
| layer_benefit_type_code | string |  |
| layer_benefit_type_label | string |  |
| lifetime_reserve_day_copay_resolved | double |  |
| max_enrollee_oop_amount | double |  |
| max_enrollee_oop_period_code | string |  |
| original_medicare_cost_share_charged | boolean |  |
| part_a_deductible_amount_resolved | double |  |
| tier1_charges_medicare_defined_coinsurance | boolean |  |
| tier1_charges_medicare_defined_copay | boolean |  |
| tier1_coinsurance_interval1_begin_day | double |  |
| tier1_coinsurance_interval1_end_day | double |  |
| tier1_coinsurance_interval1_pct | double |  |
| tier1_coinsurance_interval2_begin_day | double |  |
| tier1_coinsurance_interval2_end_day | double |  |
| tier1_coinsurance_interval2_pct | double |  |
| tier1_coinsurance_interval3_begin_day | double |  |
| tier1_coinsurance_interval3_end_day | double |  |
| tier1_coinsurance_interval3_pct | double |  |
| tier1_coinsurance_interval_count | string |  |
| tier1_copay_interval1_amount | double | Tier-1 per-day copay for stay-day interval 1 (the first band, always beginning day 1). Identical to copay_amount. The canonical per-day inpatient copay. 90,411 non-null. |
| tier1_copay_interval1_amount_resolved | double |  |
| tier1_copay_interval1_begin_day | double | Start day of interval 1. CONSTANT = 1.0 whenever populated (interval 1 always starts at admission day 1). |
| tier1_copay_interval1_end_day | double | Last day of the interval-1 copay band (e.g. 5, 6, 7, 8...). 27 distinct values. Defines how many initial days carry the interval-1 copay (e.g. days 1-5 at $325). |
| tier1_copay_interval2_amount | double | Per-day copay for interval 2 (days after interval 1, typically through day 90). Almost always 0.0 - the dominant MA design is 'copay days 1-5, then $0'. 89,843 non-null. |
| tier1_copay_interval2_amount_resolved | double |  |
| tier1_copay_interval2_begin_day | double | Start day of interval 2 (= interval1_end_day + 1, e.g. 6, 7, 8). 89,843 non-null. |
| tier1_copay_interval2_end_day | double | End day of interval 2 (typically 90, the inpatient benefit-period day cap). 89,843 non-null. |
| tier1_copay_interval3_amount | double |  |
| tier1_copay_interval3_begin_day | double |  |
| tier1_copay_interval3_end_day | double |  |
| tier1_copay_interval_count | string | PBP-reported number of copay intervals as a STRING ('1','2','3','4'; dominant '3'). NULL for 70,429 rows. Note it's a code/count string, not the literal count of populated interval columns (only 2 intervals are exposed as columns). |
| tier1_copay_per_stay_amount | double |  |
| tier1_has_coinsurance | boolean | Boolean: tier-1 uses coinsurance. Populated only 36,061 rows (roughly the coinsurance-based plans). NULL elsewhere. |
| tier1_has_copay | boolean | UNRELIABLE / effectively inverted: it is False for 90,411 rows that DO carry a numeric copay, and True for only 5,705 rows (all with NO copay_amount). Do NOT use this to detect copay presence - use copay_amount IS NOT NULL or has_copay. |
| tier1_lifetime_reserve_coinsurance_interval1_begin_day | double |  |
| tier1_lifetime_reserve_coinsurance_interval1_end_day | double |  |
| tier1_lifetime_reserve_coinsurance_interval1_pct | double |  |
| tier1_lifetime_reserve_coinsurance_interval2_begin_day | double |  |
| tier1_lifetime_reserve_coinsurance_interval2_end_day | double |  |
| tier1_lifetime_reserve_coinsurance_interval2_pct | double |  |
| tier1_lifetime_reserve_coinsurance_interval_count | string |  |
| tier1_lifetime_reserve_days_interval_count | string | PBP code/count (string '1'-'4', dominant '1') describing lifetime-reserve-days copay intervals for tier 1. NULL for 70,431 rows. |
| tier1_lifetime_reserve_interval1_amount | double |  |
| tier1_lifetime_reserve_interval1_begin_day | double |  |
| tier1_lifetime_reserve_interval1_end_day | double |  |
| tier1_lifetime_reserve_interval2_amount | double |  |
| tier1_lifetime_reserve_interval2_begin_day | double |  |
| tier1_lifetime_reserve_interval2_end_day | double |  |
| tier1_lifetime_reserve_interval3_amount | double |  |
| tier1_lifetime_reserve_interval3_begin_day | double |  |
| tier1_lifetime_reserve_interval3_end_day | double |  |
| tier2_charges_medicare_defined_cost_share | boolean |  |
| tier2_copay_amount_max | double |  |
| tier2_copay_interval1_amount | double |  |
| tier2_copay_interval1_begin_day | double |  |
| tier2_copay_interval1_end_day | string |  |
| tier2_copay_interval2_amount | double |  |
| tier2_copay_interval2_begin_day | double |  |
| tier2_copay_interval2_end_day | double |  |
| tier2_copay_interval3_amount | double |  |
| tier2_copay_interval3_begin_day | double |  |
| tier2_copay_interval3_end_day | double |  |
| tier2_copay_interval_count | string |  |
| tier2_copay_per_stay_amount | double |  |
| tier2_lifetime_reserve_days_interval_count | string |  |
| tier2_lifetime_reserve_interval1_amount | double |  |
| tier2_lifetime_reserve_interval1_begin_day | double |  |
| tier2_lifetime_reserve_interval1_end_day | double |  |
| tier2_lifetime_reserve_interval2_amount | double |  |
| tier2_lifetime_reserve_interval2_begin_day | double |  |
| tier2_lifetime_reserve_interval2_end_day | double |  |
| tier3_charges_medicare_defined_cost_share | boolean |  |
| tier3_copay_interval1_amount | double |  |
| tier3_copay_interval1_begin_day | double |  |
| tier3_copay_interval1_end_day | double |  |
| tier3_copay_interval2_amount | double |  |
| tier3_copay_interval2_begin_day | double |  |
| tier3_copay_interval2_end_day | double |  |
| tier3_copay_interval_count | string |  |
| tier3_copay_per_stay_amount | double |  |
| tier3_lifetime_reserve_days_interval_count | string |  |
| tier3_lifetime_reserve_interval1_amount | double |  |
| tier3_lifetime_reserve_interval1_begin_day | double |  |
| tier3_lifetime_reserve_interval1_end_day | double |  |
| source_pbp_section | varchar(3) | CONSTANT = 'B1A' - the CMS PBP form section for Inpatient Hospital - Acute. Confirms provenance/section lineage. |
| source_silver_table | string | CONSTANT = 'medicare_silver.pbp_section_b1a_inpat_hosp' - the upstream silver table this semantic view is built from. Lineage pointer. |
| lmi_authorities | string | CONSTANT pipe-delimited string of LMI MA-benefits authority document URLs (CY2026 PBP Service Category Descriptions + Part C Bid Review Memorandum). Reference/methodology citations, identical on every row. |
| source_url | string | CMS download URL of the originating PBP-benefits ZIP for that release (e.g. .../pbp-benefits-2010.zip, /files/zip/pbp-benefits-2026.zip). 31 distinct - exactly one per report_year+quarter snapshot. |
| sha256_content_hash | string | Content hash of the source PBP file for provenance. 31 distinct, mapping 1:1 to year-quarter and to source_url - it is a FILE-level (per-release) hash, NOT a per-row hash. Do not treat it as a row identifier. |

Tools that read it: get_pbp_benefit

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