# PBP snf (cleaned view)

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

Cleaned source-faithful PBP snf 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 surface for snf (vs

**Grain.** One row per MA plan-segment per PBP snapshot. The minimal verified key is contract_id + plan_id + segment_id + report_year + quarter — 201,488 distinct combinations exactly equal the 201,488 rows (no duplicates). benefit_category ('snf'), snf_sub_benefit ('snf_tier_1'), and network_type ('in_network') are all constant and add nothing to the grain. report_year+quarter together identify the PBP PUF release the row was extracted from (e.g. 2026 Q1).

**How to use it.** Compare in-network SNF out-of-pocket exposure across MA plans for a given year. Example: SELECT contract_id, plan_id, segment_id, tier1_copay_interval1_amount AS days_1_20_copay, tier1_copay_interval2_amount AS days_21_100_copay, prior_hospitalization_required FROM medicare_semantic.vw_pbp_snf WHERE report_year=2026 AND quarter=1 AND snf_offered=true ORDER BY days_21_100_copay DESC — returns each 2026 plan's full SNF day-interval copay ladder (showing that the headline copay_amount of $0-$20 hides a ~$218/day charge after day 20), so you can rank plans by true SNF cost rather than the misleadingly cheap first-interval copay.

**Traps.**
- copay_amount and cost_share_string are the INTERVAL-1 (days 1-20) copay ONLY — they systematically understate SNF cost. The expensive days-21-100 copay (often ~$218/day) lives in tier1_copay_interval2_amount. copay_amount is provably identical to tier1_copay_interval1_amount (97,606 equal / 0 differ).
- ~18 columns are HARD-CODED NULL in the view definition (CAST(null AS ...)): coinsurance_pct, all limit_* and all benefit_amount_* columns, normalization_status/basis, aggregation_policy. They are schema-parity placeholders to match sibling PBP benefit views — verified 0 non-null. has_coinsurance can be true while coinsurance_pct is null, so you can never read the SNF coinsurance percentage from this view (go to medicare_silver.pbp_section_b2_snf).
- The offered/observability flags (offered_status, snf_offered, numeric_amount_observability, null_amount_reason) are about the (always-null) generic benefit-amount path and the pbp_b2_bendesc_yn flag — NOT about copay availability. 92,714 rows with offered_status='not_offered'/snf_offered=false STILL carry a real copay_amount. Do not filter on snf_offered to find plans-with-a-copay.
- This is a Presto VIRTUAL_VIEW with NO is_current column. Versioning is via the (report_year, quarter) snapshot itself. tier1_copay_interval_count can be '3'/'4' but the view exposes only interval 1 and interval 2 columns, so higher counts mean additional cost-ladder intervals exist in the silver source that are not surfaced here.
- Grain traps: the profiler reported a 7-column superset and flagged limit_period_code/benefit_amount_period_code as candidate keys — both are 100% null and irrelevant. The true minimal key is contract_id+plan_id+segment_id+report_year+quarter (201,488=201,488). Also segment_id has empty-string values (5,074 rows) that are distinct from '0' and break CAST-to-int; plan_id is only unique within a contract; quarter's 1-4 is the PBP release quarter, not a calendar date.
- prior_hospitalization_days_required and tier1_copay_interval_count are STRINGS (e.g. '1','3'), not numbers — cast before arithmetic. tier1_has_copay and has_copay are two different derivations that frequently disagree; prefer has_copay for a plan-level copay signal.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS MA contract ID (e.g. H0084, E5088, R-/S- prefixes). 1,972 distinct. Part of grain. H=local MA/MA-PD, R=regional PPO, E=employer/group, S=PDP-adjacent. |
| plan_id | string | Plan benefit package ID within the contract (e.g. '001','029'), 3-char zero-padded string. 656 distinct values but reused across contracts; only unique WITH contract_id. Part of grain. |
| segment_id | string | Plan segment (geographic/benefit segment) within the plan, string. Mostly '0' (181K rows); also '1'..'16'+; NOTE 5,074 rows have an EMPTY-STRING segment_id (not '0', not null) — casting it to int fails. Part of grain. |
| version | double | PBP bid submission/version number for that plan-year (double, 0-27). An attribute, NOT part of the grain — there is exactly one row per plan-snapshot regardless of version. Higher = later resubmission; counts decline with version. |
| report_year | double | PBP contract/benefit year (double, 2001-2026). Matches the year embedded in source_url exactly. This is the plan year the benefits apply to. Core time dimension. |
| quarter | double | PBP PUF release quarter within report_year (1-4). Pre-2018 is annual (Q1 only); 2018+ has multiple quarterly snapshots but NOT always all 4 (e.g. 2020 has no Q3, 2024 no Q2, 2025/2026 only Q1). This is the profiler's 'time_column' but its 1-4 range is the snapshot quarter, not a date. |
| benefit_category | string | Constant 'snf' for every row. Identifies this as the SNF benefit family. Carries no information within this view. |
| cost_share_string | string | Human-readable label synthesized in-view from the interval-1 copay only, e.g. '$0 copay' (91K), '$20.0 copay', '$40.0 copay'. NULL for 103,882 rows. Only ever expresses copay (never coinsurance, since coinsurance_pct is hardcoded null). Do not treat as the full SNF cost — it is the days-1-20 copay label. |
| copay_amount | double | SNF tier-1 copay for INTERVAL 1 ONLY (the PBP field pbp_b2_copay_mcs_amt_int1_t1, i.e. typically days 1-20). Populated in 97,606 rows; range $0-$40, median $0. IT EXACTLY EQUALS tier1_copay_interval1_amount (verified 97,606 equal / 0 differ). MAJOR TRAP: this omits the expensive later interval (days 21-100, often ~$218/day) — see tier1_copay_interval2_amount. |
| coinsurance_pct | double | HARD-CODED NULL in the view definition (CAST(null AS double)). 0 non-null rows. SNF coinsurance percentage is never carried here even when has_coinsurance=true. To know coinsurance you must go to the silver source. |
| limit_text | string | HARD-CODED NULL (placeholder for schema parity with sibling PBP benefit views). Always null. Ignore. |
| auth_required | boolean | Prior authorization required for SNF (boolean, from pbp_b2_auth_yn). true=97K+, null=common (~66K where the Y/N flag was blank/unmapped). null means 'not stated', not 'no'. |
| referral_required | boolean | Referral required (boolean, from pbp_b2_refer_yn). true/false/null. null = flag absent. |
| network_type | varchar(10) | Constant 'in_network'. This view carries only in-network SNF cost-sharing; there is no out-of-network row here. Carries no information. |
| max_oop_applies | boolean | HARD-CODED NULL placeholder. Always null. Ignore (use has_max_enrollee_oop instead for the boolean signal). |
| limit_quantity | double | HARD-CODED NULL placeholder. Always null. Ignore. |
| limit_unit | string | HARD-CODED NULL placeholder. Always null. Ignore. |
| limit_period_code | string | HARD-CODED NULL placeholder. Always null. Ignore. (Profiler listed this as a candidate key column, but it is 100% null and meaningless here.) |
| limit_period_label | string | HARD-CODED NULL placeholder. Always null. Ignore. |
| limit_quantity_per_year | double | HARD-CODED NULL placeholder. Always null. Ignore. |
| benefit_amount | double | HARD-CODED NULL placeholder (generic plan-funded benefit amount, not applicable to SNF cost-sharing). Always null. Ignore. |
| benefit_amount_kind | string | HARD-CODED NULL placeholder. Always null. Ignore. |
| benefit_amount_period_code | string | HARD-CODED NULL placeholder. Always null. Ignore. (Listed as a profiler candidate key but is 100% null.) |
| benefit_amount_period_label | string | HARD-CODED NULL placeholder. Always null. Ignore. |
| benefit_amount_annualized | double | HARD-CODED NULL placeholder. Always null. Ignore. |
| benefit_amount_pmpm | double | HARD-CODED NULL placeholder. Always null. Ignore. |
| normalization_status | string | HARD-CODED NULL placeholder. Always null. Ignore. |
| normalization_basis | string | HARD-CODED NULL placeholder. Always null. Ignore. |
| aggregation_policy | string | HARD-CODED NULL placeholder. Always null. Ignore. |
| offered_status | varchar(15) | Whether SNF is offered, derived from pbp_b2_bendesc_yn (benefit-description Y/N): 'not_offered' (152,615), 'offered' (34,484), 'offered_unknown' (14,389). TRAP: this reflects the benefit-description flag, NOT whether a copay exists — 92,714 'not_offered' rows still carry a copay_amount. |
| numeric_amount_observability | varchar(31) | Observability of the GENERIC benefit-amount fields (all of which are hardcoded null here): 'not_offered' (152,615) or 'offered_without_numeric_amounts' (48,873). Because benefit_amount is always null, this never says 'all_offered_numeric'. It is NOT a statement about copay availability. |
| null_amount_reason | varchar(30) | Why benefit_amount is null: 'not_offered' (152,615) or 'unknown_requires_crosswalk' (48,873). Again tied to the hardcoded-null benefit_amount path, not to copay. |
| annualization_policy | varchar(17) | Constant 'not_numeric' (because benefit_amount is always null, nothing to annualize). Carries no information here. |
| combined_limit_policy | varchar(29) | Constant 'none_detected'. Carries no information here. |
| consumer_document_reconciliation_status | varchar(11) | Constant 'not_checked' — rows have not been reconciled against consumer-facing documents (e.g. Medicare Plan Finder). Pipeline status flag, not a benefit attribute. |
| confidence_tier | varchar(23) | Constant 'source_faithful' — values are taken faithfully from the PBP source with no cross-document upgrade. Provenance flag. |
| additional_days_amount | double |  |
| additional_days_benefit_type_code | string |  |
| additional_days_benefit_type_label | string |  |
| additional_days_cost_share_tier_count | string |  |
| additional_days_cost_varies_by_tier | boolean |  |
| additional_days_limit_code | string |  |
| additional_days_offered | boolean |  |
| additional_days_unlimited | boolean |  |
| bendesc_yn_answer | boolean |  |
| benefit_layer | varchar(38) |  |
| benefit_period_code | string | PBP basis-period code for the SNF cost share: '1'='Per benefit period' (108,271, the SNF standard), '2'='Per calendar year' (1,120), '3' (12,533, label unmapped), '4' (204, label unmapped); 79,360 null. 122,128 non-null overall. |
| benefit_period_label | string | Human label for benefit_period_code, mapped for codes 1 and 2 only ('Per benefit period','Per calendar year'); NULL for codes 3/4 and where code is null (109,391 non-null). |
| 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_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: does SNF cost-share vary by provider tier. Mostly false (124,027); true only 777; null 76,684. null = not determinable. |
| deductible_amount | double |  |
| has_coinsurance | boolean | Boolean flag that the plan uses SNF coinsurance (true=37,819). TRAP: the actual percentage (coinsurance_pct) is ALWAYS null, so this flag tells you coinsurance exists but not its value. |
| has_copay | boolean | Boolean flag that the plan uses a SNF copay (true). Derived independently from tier1_has_copay and the two can disagree (e.g. 98,840 rows have has_copay=true while tier1_has_copay=false). Treat has_copay as the plan-level copay indicator. |
| has_deductible | boolean |  |
| has_discharge_cost_share | boolean |  |
| has_max_enrollee_oop | boolean | Boolean: a max enrollee out-of-pocket applies to SNF. Almost always false; true in a small number of rows (~942). |
| layer_benefit_type_code | string |  |
| layer_benefit_type_label | string |  |
| max_enrollee_oop_amount | double |  |
| max_enrollee_oop_period_code | string |  |
| original_medicare_cost_share_charged | boolean |  |
| prior_hospitalization_days_basis | string |  |
| prior_hospitalization_days_required | string | STRING count of qualifying inpatient days required before SNF coverage: '1' (138,639), '2' (4,146), '3' (234); null when not required. Stored as text, not numeric. |
| prior_hospitalization_required | boolean | Boolean: SNF requires a prior qualifying hospital stay. true=143,019, false=38,212, null=20,257. Pairs with prior_hospitalization_days_required (true→days '1'/'2'/'3'). |
| prior_stay_less_than_3_days_allowed | boolean |  |
| snf_in_package_category_list | boolean |  |
| snf_offered | boolean | Boolean mirror of offered_status: true (34,484), false (152,615), null (14,389, = offered_unknown). Same caveat as offered_status — does not gate the copay fields. |
| snf_package_id | string |  |
| snf_package_match | string |  |
| snf_package_name | string |  |
| snf_package_type_code | string |  |
| snf_package_type_label | string |  |
| snf_sub_benefit | string | Constant 'snf_tier_1'. This view exposes only the tier-1 SNF sub-benefit. Carries no information within the view but signals the view is NOT modeling multi-tier SNF. |
| snf_targeted_file | string |  |
| snf_targeting | varchar(36) |  |
| 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_interval_count | string |  |
| tier1_coinsurance_per_stay_pct | double |  |
| tier1_copay_interval1_amount | double | SNF tier-1 copay for day-interval 1 (typically days 1-20). Populated 97,606 rows; identical to copay_amount. The cheap early-stay copay. |
| tier1_copay_interval1_amount_resolved | double |  |
| tier1_copay_interval1_begin_day | double | First covered day of interval 1 (double, typically 1.0). Defines the day range over which interval1_amount applies. |
| tier1_copay_interval1_end_day | double | Last day of interval 1 (double, typically 20.0). The SNF cost ladder usually breaks at day 20. |
| tier1_copay_interval2_amount | double | SNF tier-1 copay for interval 2 (typically days 21-100). Populated 95,493 rows; commonly ~$218/day. THIS IS THE EXPENSIVE PART OF SNF COST that copay_amount/cost_share_string omit — must be read to assess true SNF exposure. |
| tier1_copay_interval2_amount_resolved | double |  |
| tier1_copay_interval2_begin_day | double | First day of interval 2 (double, typically 21.0). |
| tier1_copay_interval2_end_day | double | Last day of interval 2 (double). Often 100.0 (Medicare SNF benefit max) but can be lower (e.g. 45, 51) when the plan's interval structure splits coverage further. |
| tier1_copay_interval3_amount | double |  |
| tier1_copay_interval3_begin_day | double |  |
| tier1_copay_interval3_end_day | double |  |
| tier1_copay_interval_count | string | STRING count of day-intervals the plan defines for tier-1 SNF: '3' (72,056), '4' (23,437), '2' (2,113), '1' (1,234); null 102,648. NOTE: the count can exceed the 2 intervals exposed as columns (this view surfaces only interval 1 and 2), so '3'/'4' means additional intervals exist in the source that are NOT in this view. |
| tier1_copay_per_stay_amount | double |  |
| tier1_has_copay | boolean | Boolean copay indicator at the interval/tier level. Diverges from has_copay (e.g. false here while has_copay=true). Often null (102K). Prefer has_copay for a plan-level signal; treat this as interval-level metadata. |
| 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 | double |  |
| tier2_copay_interval2_amount | double |  |
| tier2_copay_interval2_begin_day | double |  |
| tier2_copay_interval2_end_day | double |  |
| tier2_copay_interval_count | string |  |
| source_pbp_section | varchar(2) | Constant 'B2' — the SNF section of the PBP. Provenance. |
| source_silver_table | varchar(47) | Constant 'medicare_silver.pbp_section_b2_snf' — the silver table this view reads. To recover the hardcoded-null fields (coinsurance %, additional intervals beyond 2), go here. Provenance. |
| lmi_authorities | string | Constant pipe-delimited string of two LMI (CMS contractor) reference-document URLs (CY2026 PBP Service Category Descriptions + Part C Bid Review Memorandum). Documentation pointers, not per-row data. Same for every row. |
| source_url | string | CMS download URL of the PBP-benefits ZIP the row came from (e.g. .../pbp-benefits-2012.zip). 40 distinct values; the embedded year matches report_year exactly; multiple URLs per year correspond to the quarterly snapshots. Provenance. |
| sha256_content_hash | string | SHA-256 of the source file content. 40 distinct, 1:1 with source_url. Content-addressed provenance / integrity check for the source ZIP. |

Tools that read it: get_pbp_benefit

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