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: 7,954,124 (2026-09-11 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).

    The count is lower than the 10,752,221 shipped on 2026-08-29 despite a wider crawl, because that export was overstated. See Hospital identity below: 40% of the rows carried a crawl placeholder in ccn instead of a CCN, and a large share of those were second copies of a file already extracted under the hospital's real CCN. Reconciling them collapsed 1,611,971 duplicate facts and dropped 1,475,152 rows that still cannot be tied to a hospital from the product. Every row that ships now joins on ccn; on 2026-08-29 four in ten did not.

  • Vintage: 2026-09-11 (max snapshot_date observed at export time: 2026-09-02).

  • Coverage (2026-09-11 export): snapshot_date values span 2026-05-22 to 2026-09-02, covering 1,925 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. 274 of the hospitals map to a publicly traded operator, across 10 tickers.

Hospital identity: how ccn is established, and what is excluded

Since July 2024 every hospital must publish cms-hpt.txt at the root of the site hosting its MRF (45 CFR 180.50(b)(8)). Multi-location systems — Ascension, HCA, Atrium, Fairview, Trinity — satisfy that with ONE file listing a location-name and mrf-url per facility. The file names the locations; it does not carry their CCNs.

The crawl queue therefore keys those extra locations under a synthetic placeholder, txt:<location-name> (e.g. txt:Mercy_Hospital_of_Buffalo), and a reconciliation step resolves each one to a real CCN by name plus state before the rates can be attributed. Until the 2026-09-11 fix that reconciliation never reached the rate rows, so the placeholder itself sat in ccn for 4,389,684 rows (40% of the table) and those rows joined to nothing.

What changed on 2026-09-11:

  • 1,258 of 2,787 placeholder locations (45%) were resolved to a CCN. Matching is deliberately conservative — exact or token-identical name within the states the publishing domain is known to operate in, with provider-based emergency departments and named campuses attributed to their parent hospital's CCN, which is the CCN they bill under. A wrong CCN is worse than an unmatched one, so anything ambiguous was left unmatched.
  • 1,089,687 rate rows were re-pointed at the resolved CCN, and 1,611,971 were removed as exact duplicates of a fact already held under it — the same file reached twice, once via the hospital's own MRF URL and once via the system file.
  • source_location_key now carries the cms-hpt.txt location-name those rows came from, so the attribution stays auditable.
  • The facility identifier is now guarded at the database level: a placeholder can no longer be written into it, and a location is skipped until it has been reconciled.

Still unresolved and therefore NOT in this product: 375 locations, 1,475,152 rate rows. The dominant reason (350 of 375) is that CMS's own name for the facility is not recoverable from the system's brand name — CMS lists "UH PARMA MEDICAL CENTER" where the system publishes "University Hospitals Parma Medical Center", or the facility is newer than the HCRIS/Care Compare reference (Novant Health Kernersville, Atrium Health Mercy). 20 are genuinely ambiguous (two "JEFFERSON HOSPITAL"s in the states that domain operates in), and 5 have no usable name or no inferable state. These rows stay in the source database with their placeholder for a later matching pass; they are filtered out of the export by the ccn shape rule, so nothing in the shipped product fails to join.

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. Always a real certification number — 6 characters, or 10 for the freestanding facilities CMS certifies that way. Rows whose hospital could not be identified are excluded from this product entirely (see Hospital identity), so this column never needs a validity filter.
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 (5,666,016 rows, 1,284 hospitals), MS-DRG (1,398,034 rows, 1,427 hospitals), CMG — inpatient rehabilitation case-mix groups (789,260 rows, 103 hospitals), RC — revenue codes (100,814 rows, 417 hospitals). Counts from the 2026-09-11 export. The source schema also supports HCPCS/CDM. The hospital counts are lower than the 2026-08-29 figures because those counted crawl placeholders as distinct hospitals; these count identified CCNs.
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. —

Not shipped: source_location_key

The source database carries one more column on this table, hpt_rates.source_location_key. When a rate came out of a multi-facility cms-hpt.txt it holds the location-name block the file published it under, as txt:<location-name> — so a row whose ccn was established by reconciling a location name against CMS's own facility names stays distinguishable from one whose MRF was found for a single CCN directly. It is provenance, never a join key, and it is deliberately not in the shipped schema: the published Snowflake / Databricks / BigQuery listings are already on the column set above, and the value is the same for every row from a given file. Ask if you need the lineage for an audit.

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;