VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

PBP partial hospitalization (cleaned view)

medicare_semantic.vw_pbp_partial_hospitalization·live·semantic_view·quarterly
Rows
N/A
Columns
57
History
report_year 2007 (Q1) to report_year 2026 (Q1)
Card
yesgrain, traps, filters
Typed tools
1

What it is

  1. 1A semantic (gold-layer) view of the Partial Hospitalization Program (PHP) benefit cost-share from CMS Medicare Advantage Plan Benefit Package (PBP) Public Use Files, section B5.
  2. 2One row tells you, for a given MA plan-segment in a given PBP snapshot, what the member pays for partial hospitalization (intensive outpatient psychiatric day-program): the copay or coinsurance, plus prior-auth/referral/MOOP flags.
  3. 3It is the partial-hospitalization slice of the normalized PBP benefit substrate, used to compare MA plan generosity over time.

Grain

KeyGrain
contract_idplan_idsegment_idreport_yearquarter
  1. 1One row per MA plan-segment per PBP report snapshot, keyed by (contract_id, plan_id, segment_id, report_year, quarter).
  2. 2Verified: those 5 columns yield exactly 193,172 distinct combinations = the full row_count of 193,172, and within every (report_year, quarter) the distinct-plan count equals the row count.
  3. 3Exactly one in-network PHP row per plan per snapshot (no network or sub-benefit fan-out).
  4. 4A single plan recurs across multiple year/quarter snapshots (full historical/snapshot table, not current-only).

How to read it

  1. 1Latest PHP cost-share for a plan: SELECT cost_share_string, copay_amount, coinsurance_pct, auth_required, referral_required FROM medicare_semantic.vw_pbp_partial_hospitalization WHERE contract_id='H0028' AND plan_id='008' AND report_year=2026.
  2. 2To benchmark across plans for a year, filter report_year=2026 AND quarter=1 and aggregate copay_amount (median ~$55) / coinsurance_pct (median 20%).
  3. 3No is_current flag — for 'newest' take MAX(report_year) and the latest quarter within it.

Traps

  1. 1No is_current / snapshot-dedup column. This is a full historical table: the same (contract_id, plan_id, segment_id) recurs across many report_year/quarter snapshots. Always filter by report_year (and quarter) — querying unfiltered double-counts a plan ~20x over the years. For 'latest', take MAX(report_year) then the latest quarter within it.
  2. 2The grain needs report_year AND quarter, not just contract/plan/segment (only 23,191 distinct). quarter is the PUF release quarter, not a fiscal quarter, and is sparse: most pre-2018 years have only Q1, while some recent benefit years were re-released in 2–4 quarters. The profiler also mis-picked 'quarter' as the time column — the real time axis is report_year.
  3. 3Many columns are entirely NULL or entirely constant and carry NO information here. 100% NULL: all limit_* and all benefit_amount_* columns, plus normalization_status/basis and aggregation_policy. Constant-for-all-rows: benefit_category, network_type='in_network', partial_hospitalization_axis='php', source_pbp_section='B5', offered_status='offered', numeric_amount_observability, null_amount_reason, annualization_policy, combined_limit_policy, confidence_tier='source_faithful', consumer_document_reconciliation_status='not_checked', source_silver_table, lmi_authorities. Don't filter or pivot on these expecting variation.
  4. 4numeric_amount_observability='offered_without_numeric_amounts' and null_amount_reason='unknown_requires_crosswalk' are view-level labels about the (always-null) benefit_amount family — they do NOT mean copay/coinsurance are missing. 90,606 rows have a copay and 46,175 have coinsurance. Read cost from copay_amount / coinsurance_pct, not from these labels.
  5. 5Cost-share is sparse and nearly mutually exclusive: 56,400 rows (29%) have NO copay, NO coinsurance and NULL cost_share_string (PHP offered but cost-share not captured); only 9 rows have both a copay and coinsurance. copay_amount=$0 is a REAL value ('$0 copay'), distinct from NULL=not captured — don't coalesce them. The min/max copay & coinsurance columns are exact duplicates of the point values here (no range).
  6. 6'version' looks like a key/snapshot version but is the PBP plan FILING version (1–27); exactly one version per plan-snapshot, so it is functionally dependent and must not be added to the grain or used as is_current. network_type and partial_hospitalization_axis are likewise constant single-values the profiler may suggest as grain columns but they add nothing.

Columns (57)

ColumnTypeMeaning
contract_idstringCMS MA contract ID (e.g. 'H0028'); 1,838 distinct. Part of the grain key. Join key to other PBP / Part C plan tables.
plan_idstringPlan ID (PBP number) within the contract, zero-padded string (e.g. '008'); 656 distinct values but unique only WITHIN a contract. Part of the grain key.
segment_idstringPlan segment (service-area split), string (e.g. '0','1','2'); 26 distinct. '0' = no segmentation. Part of the grain key — a segment can carry its own cost-share.
versiondoublePBP plan filing version number (double, 1–27, median ~4). An attribute of the plan filing, NOT a snapshot/dedup version and NOT part of the grain — verified exactly one version per (contract,plan,segment,year,quarter). Do not treat as is_current.
report_yeardoublePBP contract/benefit year (double, 2007–2026). Part of the grain key; the real time dimension. Note: the profiler mislabeled 'quarter' as the time column.
quarterdoublePBP PUF release quarter within report_year (double, 1–4). Part of the grain key. Sparse/irregular: most older years have only Q1; some recent years have 2–4 quarterly re-releases of the same benefit year.
benefit_categorystringConstant = 'partial_hospitalization' for every row (view is pre-filtered to that category). No discriminating info.
cost_share_stringstringHuman-readable cost-share rendering, e.g. '$55.0 copay', '20.0%', '$0 copay'. NULL on 56,400 rows (29%) where no numeric cost-share was captured. Top values: '$55.0 copay' (45.4k), '20.0%' (45.4k).
copay_amountdoubleMember copay in dollars (double). Populated on 90,606 rows; range $0–$750, median $55. $0 is a real value (distinct from NULL = not captured).
coinsurance_pctdoubleMember coinsurance percentage (double). Populated on 46,175 rows; range 0–45%, median 20%. Only 9 rows carry BOTH a copay and coinsurance.
limit_textstring100% NULL in this view — PHP has no captured quantity-limit text. Dead column here.
auth_requiredbooleanBoolean: prior authorization required for PHP. TRUE ~122k rows, FALSE ~17k, NULL ~53k (auth status not stated). NULL is meaningful = 'unknown', not False.
referral_requiredbooleanBoolean: referral required. Mostly FALSE; TRUE on ~41k rows; some NULL. NULL = not stated.
network_typevarchar(10)Constant = 'in_network' for every row. This view contains ONLY in-network PHP cost-share — no out-of-network rows.
max_oop_appliesbooleanBoolean: does this benefit accrue to the plan's MOOP. TRUE on essentially all rows. Near-constant.
limit_quantitydouble100% NULL in this view (no PHP day/visit limits captured). Dead column.
limit_unitstring100% NULL. Dead column for PHP.
limit_period_codestring100% NULL. (Offered as a candidate grain column by the profiler but entirely null and adds nothing.)
limit_period_labelstring100% NULL. Dead column.
limit_quantity_per_yeardouble100% NULL. Dead column.
benefit_amountdouble100% NULL — PHP is a cost-share benefit, not a benefit-dollar/allowance benefit, so no amount is captured. Dead column.
benefit_amount_kindstring100% NULL. Dead column.
benefit_amount_period_codestring100% NULL. (Profiler listed it as a candidate key but it is always null.)
benefit_amount_period_labelstring100% NULL. Dead column.
benefit_amount_annualizeddouble100% NULL. Dead column.
benefit_amount_pmpmdouble100% NULL. Dead column.
normalization_statusstring100% NULL in this view. Placeholder from the shared PBP semantic schema; not populated for PHP.
normalization_basisstring100% NULL. Placeholder.
aggregation_policystring100% NULL. Placeholder.
offered_statusvarchar(15)Constant = 'offered' for every row — every row represents a plan that offers PHP. No 'not_offered' rows. No discriminating info.
numeric_amount_observabilityvarchar(31)Constant = 'offered_without_numeric_amounts' for every row. A view-level label (do NOT read row-by-row): it does NOT mean copay/coinsurance are absent — 90,606 rows still carry a copay and 46,175 a coinsurance.
null_amount_reasonvarchar(30)Constant = 'unknown_requires_crosswalk' for every row. Refers to the (always-null) benefit_amount family, not to copay/coinsurance. Non-discriminating.
annualization_policyvarchar(17)Constant = 'not_numeric'. Non-discriminating.
combined_limit_policyvarchar(29)Constant = 'none_detected'. Non-discriminating.
consumer_document_reconciliation_statusvarchar(11)Constant = 'not_checked' — these rows have not been reconciled against consumer-facing documents (SB/EOC). Non-discriminating.
confidence_tiervarchar(23)Constant = 'source_faithful' — values taken faithfully from the PBP source with no inference. Non-discriminating.
coinsurance_pct_maxdoubleUpper bound of coinsurance. For PHP equals coinsurance_pct exactly (same 46,175 rows); no range info beyond the point value.
coinsurance_pct_mindoubleLower bound; equals coinsurance_pct exactly for PHP. Redundant with coinsurance_pct here.
copay_amount_maxdoubleUpper bound of copay; equals copay_amount exactly for PHP (same 90,606 rows). No extra range info.
copay_amount_mindoubleLower bound of copay; equals copay_amount exactly for PHP. Redundant with copay_amount.
has_coinsurancebooleanBoolean convenience flag = coinsurance present. TRUE on ~57k rows. NULL (~9.3k rows) where there is no cost-share signal at all (overlaps the all-null cost-share rows). NULL means 'no signal', not False.
has_copaybooleanBoolean convenience flag = copay present. TRUE on ~108k rows. NULL on ~9.3k all-blank rows. Use copay_amount IS NOT NULL for a hard test rather than relying solely on this flag.
has_deductiblebooleanBoolean: a benefit-specific deductible applies. Almost always FALSE/NULL — only ~13 rows TRUE. PHP rarely carries its own deductible.
iop_auth_requiredboolean
iop_coinsurance_pct_maxdouble
iop_coinsurance_pct_mindouble
iop_copay_amount_maxdouble
iop_copay_amount_mindouble
iop_has_coinsuranceboolean
iop_has_copayboolean
iop_referral_requiredboolean
partial_hospitalization_axisvarchar(3)Constant = 'php' for every row. A discriminator inherited from the shared view family; here it never varies.
source_pbp_sectionvarchar(2)Constant = 'B5' — PBP section B5 (Partial Hospitalization). Identifies the source PBP form section.
source_silver_tablevarchar(43)Constant = 'medicare_silver.pbp_section_b5_partial_hosp' — the upstream silver table this view is built from. Lineage pointer.
lmi_authoritiesstringConstant = a single LMI MA Benefits Mailbox URL (CY2026 PBP Service Category Descriptions PDF) — the authoritative benefit-category definition reference. Same for all rows.
source_urlstringProvenance: the CMS PBP PUF zip the snapshot came from — exactly one URL per (report_year, quarter). Recent: cms.gov/files/zip/pbp-benefits-YYYY[-quarter-Q].zip; older years use the legacy mcradvpartdenroldata path.
sha256_content_hashstringSHA-256 content hash of the source file for that snapshot. One distinct hash per (report_year, quarter); changes every release (the SAME plan has a different hash each snapshot). Provenance/integrity, not a row identifier.

Query it

describe_data_source(source_fqn="medicare_semantic.vw_pbp_partial_hospitalization")     # the card, live
query_substrate(sql="SELECT * FROM medicare_semantic.vw_pbp_partial_hospitalization WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND report_year = '<value>' AND quarter = '<value>' LIMIT 100")
ItemValue
Typed tools that read itget_pbp_benefit
Identity keycontract_id,plan_id,segment_id
Joinhow_to_join(source_fqn="medicare_semantic.vw_pbp_partial_hospitalization")
Raw doorAthena (read-only via query_substrate): medicare_semantic.vw_pbp_partial_hospitalization
NoteCleaned PBP reasoning surface (categories/partial_hospitalization/contract.yaml); raw is pbp_section_*.

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