PriceTransparency Negotiated Rates

Description

Hospital price-transparency negotiated-rate facts, extracted from hospitals' federally-mandated machine-readable files (MRFs) under CMS-1717-F2 (effective Jan 1, 2021). Every US hospital must publish a machine-readable file of standard charges — gross charges, payer-specific negotiated rates, discounted cash prices, and de-identified min/max rates — for every item and service. There is no central federal registry; hospitals host their own files in inconsistent formats (JSON/CSV/TXT, v1.1 legacy or v2.0 current schema). This product is built by crawling each hospital's own MRF and extracting rows only for a curated list of ~500 high-volume/CMS-shoppable CPT/HCPCS/MS-DRG codes (raw MRFs are never persisted — extraction-only, which is what keeps this tractable instead of multi-terabyte). One row per (hospital × billing code × payer × plan × setting × rate type). As of 2026-07-28 the target list is 708 codes: the original acute CPT/MS-DRG set plus 475 IRF case-mix groups and 24 revenue codes, added so inpatient rehabilitation and behavioural hospitals — which bill under neither CPT nor MS-DRG — are extractable at all.

  • Source: Hospital-published MRFs, crawled directly from each hospital's own domain (no central government feed).
  • Refresh cadence: rolling re-crawl; hospitals update MRFs on their own schedules (typically monthly per CMS rule).
  • Row count: 10,752,221 (2026-08-29 export) — the largest product in this catalog. The extracted code list was expanded from 209 to 708 codes on 2026-07-28 (inpatient rehabilitation case-mix groups and behavioural revenue codes added).
  • Vintage: 2026-08-29 (max snapshot_date observed at export time).
  • Coverage (2026-08-29 export): snapshot_date values span 2026-05-22 to 2026-08-29, covering 2,421 distinct hospitals (CCNs) and 613 distinct billing codes observed with rates, out of the 708-code target list — not every hospital publishes every curated code. 267 of the hospitals map to a publicly traded operator, across 9 tickers.

Table: hpt_negotiated_rates

Snowflake: HEALTHPARSE_DATA.HPT_NEGOTIATED_RATES.HPT_NEGOTIATED_RATES

Column Type Description Notes
ccn VARCHAR CMS Certification Number of the hospital publishing the rate. Joins to HCRIS Hospital Financials and CareLens Facility Quality on ccn.
billing_code VARCHAR The procedure/service code the rate applies to. Only the curated 708-code target list is extracted (imaging, surgery, lab, maternity, office visits, preventive, MS-DRGs, IRF case-mix groups, and behavioural revenue codes) — this is not an exhaustive chargemaster.
billing_code_type VARCHAR Code system: CPT (8,111,911 rows, 1,700 hospitals), MS-DRG (1,772,169 rows, 1,898 hospitals), CMG — inpatient rehabilitation case-mix groups (789,260 rows, 103 hospitals), RC — revenue codes (78,881 rows, 306 hospitals). Counts from the 2026-08-29 export. The source schema also supports HCPCS/CDM.
billing_code_description VARCHAR Plain-text description of the billing code, as published by the hospital (not a standardized CPT/DRG title). Wording varies hospital to hospital for the same code.
payer_name_raw VARCHAR Payer/plan name exactly as the hospital's MRF reported it. Highly inconsistent across hospitals (e.g. "BCBS", "Blue Cross Blue Shield", "Anthem BCBS" all refer to related-but-distinct entities).
payer_name_canonical VARCHAR Payer name normalized to a small canonical set (e.g. "Blue Cross Blue Shield", "UnitedHealthcare", "Self-Pay (Cash)") via a manually curated alias table. Null for gross/self-pay-style rows where no specific payer applies (e.g. gross charge or cash price rows) and for payer names not yet mapped in the alias table.
plan_name VARCHAR Specific plan name within a payer, when the hospital's file breaks rates out at that granularity. Frequently null — not every hospital publishes plan-level detail.
setting VARCHAR Care setting the rate applies to. Observed values: inpatient, outpatient, both, clinic, hospital, specialty. Null for a meaningful share of rows where the hospital's file didn't specify.
rate_type VARCHAR What kind of price this row represents: gross (chargemaster rate), negotiated (payer-specific contracted rate), cash (self-pay discounted price), min/max (de-identified minimum/maximum negotiated rate across all payers, as CMS requires). This is the field to filter on to compare like-for-like prices — a gross row and a negotiated row for the same code aren't comparable without filtering.
rate_amount DOUBLE The dollar amount for this rate/code/payer/setting combination. —
rate_methodology VARCHAR How the negotiated rate was determined (e.g. fee schedule, percent of charges, case rate, per diem), when the hospital discloses it. Often null — not all hospitals disclose methodology.
modifiers VARCHAR CPT modifier(s) applicable to this rate row, if any. —
snapshot_date DATE Date this row was extracted from the hospital's MRF during the crawl. Use this, not ingested_at, to know how current a given rate is — MRFs are re-crawled on a rolling basis, not all at once.
source_mrf_url VARCHAR Direct URL to the machine-readable file this row was extracted from. Useful for buyer-side audit/verification back to the primary source.
ingested_at TIMESTAMP WITH TIME ZONE When this row was last written to the source database. —

Example queries (Snowflake)

-- 1. Negotiated-rate spread for a specific CPT code across hospitals in a state.
SELECT "ccn", "payer_name_canonical", "rate_amount", "snapshot_date"
FROM HEALTHPARSE_DATA.HPT_NEGOTIATED_RATES.HPT_NEGOTIATED_RATES
WHERE "billing_code" = '70551'        -- MRI brain without contrast
  AND "rate_type" = 'negotiated'
ORDER BY "rate_amount";

-- 2. Median negotiated rate per payer for a code, across all hospitals.
SELECT "payer_name_canonical",
       APPROX_PERCENTILE("rate_amount", 0.5) AS median_rate,
       count(*) AS n_rates
FROM HEALTHPARSE_DATA.HPT_NEGOTIATED_RATES.HPT_NEGOTIATED_RATES
WHERE "billing_code" = '27447'        -- total knee replacement
  AND "rate_type" = 'negotiated'
  AND "payer_name_canonical" IS NOT NULL
GROUP BY "payer_name_canonical"
ORDER BY median_rate DESC;

-- 3. Cheapest cash (self-pay) price for a code across all crawled hospitals.
SELECT "ccn", "rate_amount", "snapshot_date", "source_mrf_url"
FROM HEALTHPARSE_DATA.HPT_NEGOTIATED_RATES.HPT_NEGOTIATED_RATES
WHERE "billing_code" = '45378'        -- diagnostic colonoscopy
  AND "rate_type" = 'cash'
ORDER BY "rate_amount" ASC
LIMIT 20;