Hospital MRF (hospital_rates)

Hospital-reported rates from price transparency files. Requires the Plan Bridge to resolve network_id.

The Network ID Problem

Hospital MRFs publish rates with raw plan names — strings like 'Blue Cross PPO' or 'Aetna HMO Network 1'. Clear Rates's Rate Object Space uses standardized network_id values. Without the Plan Bridge translating plan names to network_id, hospital rates cannot join the Rate Object Space and are invisible to rate selection.

Pipeline Flow

1

NDC Preprocessing

Runs once before any hospital chunk. Normalizes NDC format variants (10-digit, 11-digit, hyphenated), maps to HCPCS via Medispan crosswalk.

→ tmp_ref_ndc_crosswalk

2

Plan Bridge

Built before hospital chunks run. Maps hospital_rates.id → network_name → network_id via keyword matching and confidence scoring.

→ tmp_ref_plan_bridge

3

Hospital MRF Build

JOIN hospital_rates ON plan_bridge.id → JOIN network_spine → match to Rate Object Space. 3 provider chunks run in parallel.

→ tmp_raw_mrf_hospital_rates

Contract Methodologies

Hospital MRFs can report rates under four different contract structures. Each maps to a separate output column.

Methodology	Description	Rate Column	Transformation Needed?
Fee Schedule	Fixed dollar amount per service	hospital_fee_schedule_dollar	No
Case Rate	Fixed dollar per episode/stay	hospital_case_rate_dollar	No
Percent of Total Billed	Percentage of gross charges	hospital_pct_of_total_billed_charges_pct	Yes — pct-to-dollar
Per Diem	Dollar per day of stay	hospital_per_diem_rate	Yes — per-diem-to-case

Multiple Rates per ROID — Dedup Priority

Dedup is applied per contract_methodology, so the same ROID can legitimately carry a fee schedule rate, a case rate, a pct-billed rate, and a per-diem rate as separate columns. Within each methodology, priority is:

Priority	Column	Direction
1	drug_billing_class	ASC
2	no modifiers + no revenue_code	-1 first (most specific)
3	negotiated_rate	DESC (tiebreaker)

negotiated_rate is a mixed-unit proxy column (dollar, percentage, or allowed amount depending on what the hospital reported). It is used only for tiebreaking — the actual per-methodology columns are preserved separately.

Key Gotchas

payer_id is BIGINT

hospital_rates.payer_id is BIGINT. Every join must cast: CAST(hr.payer_id AS VARCHAR) = pb.payer_id. Failing to cast causes a type mismatch in Trino and returns zero rows.

Billing class routing

billing_class = 'Professional' records route to the PG plan bridge, not the HOSP bridge. Do not expect Professional hospital MRF rates to appear in the HOSP plan bridge output.

Setting field and hcpcs_exceptions

setting = 'Inpatient' vs 'Outpatient' determines bill type. Some hospitals incorrectly classify all HCPCS codes as Inpatient. The pipeline detects these via hcpcs_exceptions logic and reclassifies affected codes to Outpatient.