PriceTransparency Rate Changes

Status: work in progress. This table is not included in evaluation packages.

Description

The same hospital, code and payer, seen in two monthly snapshots, and what the price did. Every US hospital publishes a machine-readable file (MRF) of its standard charges; each of those files is a level — a price as of the day it was posted. Nobody publishes the difference. This product is that difference: one row per negotiated-rate fact that our crawl has observed in two or more snapshots of the hospital's MRF, carrying the first and last rate, the change in dollars and percent, the second-to-last observation (so the most recent move is separable from the cumulative one), and which way it went.

It is derived entirely from PriceTransparency Negotiated Rates — no new source. A buyer who already ingests the raw MRFs has every input to this table and still has to build it, because building it requires a retained history of prior snapshots; the hospital's own file only ever shows today.

A second file rolls the diff up to the listed operator, so the series can be read as a repricing signal per ticker rather than per CCN.

  • Source: derived from hpt_rates (hospital-published MRFs, crawled from each hospital's own domain). No central government feed exists.
  • Refresh cadence: recomputed on every marketplace export, from whatever snapshots the rolling re-crawl has accumulated. The panel deepens with each refresh — a fact only enters this table on its second observation.
  • Row count (measured, 2026-08-29 export): 696 facts in hpt_rate_changes, across 3 CCNs, 145 billing codes and 25 payers; 402 rows in hpt_rate_changes_by_operator (1 ticker). This is small because the crawl has not yet re-visited most hospitals, not because the query is narrow — see Known limits.
  • Vintage: 2026-08-29 (max snapshot_date in the underlying fact table).
  • Observed range: first_snapshot 2026-05-22 → last_snapshot 2026-08-02. Every fact currently has n_snapshots = 2.

Grain and the payer key

The fact grain is (ccn, billing_code, billing_code_type, payer, plan_name, setting, rate_type) — the full identity of a price line in the source table.

payer is coalesce(payer_name_canonical, payer_name_raw). payer_name_canonical is null on 17.1% of source rows (1,843,851 of 10,752,221) — either an alias not yet in the mapping table, or a gross / cash / de-identified min-max row where no payer applies. payer_name_raw is null on 6.8% (728,021), and those raw nulls are a strict subset of the canonical nulls. Coalescing canonical → raw therefore keys 93.2% of rows on a name and leaves 6.8% keyed on a NULL payer, which is the correct value for a chargemaster or cash-price row. The canonical name is preferred first so a payer that is spelled two ways in two snapshots of the same file does not read as two different facts.

Rows with a null rate_amount are excluded before the diff (the source carries none today, but the rule is explicit). Where the same key appears more than once inside a single snapshot — 14.3% of keys, from differing modifiers / rate_methodology / description off the same file — those rows are collapsed to avg(rate_amount) for that snapshot, so a fact has exactly one rate per snapshot before any comparison.

Table: hpt_rate_changes

One row per fact observed in two or more snapshots. 696 rows.

Column Type Description Notes
ccn VARCHAR CMS Certification Number of the hospital publishing the rate. Joins to HCRIS Hospital Financials, CareLens Facility Quality and the Operator Crosswalk on ccn.
billing_code VARCHAR The procedure/service code the rate applies to. Only the curated ~708-code target list is extracted upstream.
billing_code_type VARCHAR Code system: CPT, MS-DRG, CMG, RC. —
payer VARCHAR Payer name, canonical where mapped, else the hospital's raw string. Null on gross / cash rows where no payer applies. Part of the key.
plan_name VARCHAR Plan within the payer, when the hospital publishes that granularity. Frequently null. Part of the key — see Known limits, a snapshot that stops publishing plan detail breaks the match.
setting VARCHAR Care setting (inpatient, outpatient, both, clinic, hospital, specialty). Same caveat as plan_name.
rate_type VARCHAR gross, negotiated, cash, min, max. Only compare like with like; a gross change and a negotiated change mean different things.
first_snapshot DATE Crawl date of the earliest observation of this fact. —
last_snapshot DATE Crawl date of the most recent observation. —
n_snapshots BIGINT Number of distinct snapshots this fact was observed in. Always ≥ 2 by construction. All rows are 2 today.
first_rate DOUBLE Rate at first_snapshot, in dollars. Mean of the snapshot's rows for this key where the file carried more than one.
last_rate DOUBLE Rate at last_snapshot. Same.
abs_change DOUBLE last_rate - first_rate, in dollars. —
pct_change DOUBLE (last_rate - first_rate) / first_rate * 100. Null when first_rate = 0 (no meaningful base). Never null for any other reason.
prior_rate DOUBLE Rate at the second-to-last observation. Equals first_rate when n_snapshots = 2, which is every row today.
prior_snapshot DATE Crawl date of the second-to-last observation. Equals first_snapshot when n_snapshots = 2.
latest_change_pct DOUBLE (last_rate - prior_rate) / prior_rate * 100 — the most recent move only. Null when prior_rate = 0. Equals pct_change while n_snapshots = 2.
changed BOOLEAN last_rate <> first_rate. —
direction VARCHAR up, down or flat. flat is last_rate = first_rate exactly.
source_mrf_changed BOOLEAN True when the MRF URL the fact was read from differs between first_snapshot and last_snapshot. Data-quality flag, not a signal. A hospital reposting at a new quarterly URL trips it harmlessly, but so does a CCN whose crawl resolved to a different facility's file. Filter it out before reading the table as a repricing series — see Known limits.

Table: hpt_rate_changes_by_operator

The same diff rolled up to (ticker, payer, billing_code, billing_code_type). 402 rows.

Each CCN contributes through exactly one crosswalk row: the same rule the A-7 by_operator panel applies (ticker-bearing row first, then largest ownership_pct, then operator_key), restricted to rows with a non-null ticker. CCNs with no listed operator are absent from this file entirely.

Column Type Description Notes
ticker VARCHAR Exchange ticker of the operator that owns the hospital. From operator_crosswalk; the CCN→owner link is CMS's, the owner→ticker link is ours.
payer VARCHAR Payer name, as in the fact table. Null for gross / cash / de-identified rows, which group together.
billing_code VARCHAR The procedure/service code. —
billing_code_type VARCHAR Code system. —
hospitals BIGINT Distinct CCNs behind this group. 1 for every row today.
facts BIGINT Number of fact rows aggregated. The denominator for the three share columns.
median_first_rate DOUBLE Median first_rate across the group's facts. —
median_last_rate DOUBLE Median last_rate. —
median_pct_change DOUBLE Median pct_change. Facts whose pct_change is null (zero base) are excluded from the median but counted in facts.
share_up DOUBLE Share of the group's facts with direction = 'up'. 0–1.
share_down DOUBLE Share with direction = 'down'. 0–1.
share_flat DOUBLE Share with direction = 'flat'. 0–1. The three shares sum to 1.
first_snapshot DATE Earliest first_snapshot in the group. —
last_snapshot DATE Latest last_snapshot in the group. —

Known limits

  • The panel starts 2026-05-22, so only a few snapshots exist today. The source has seven distinct snapshot_date values (2026-05-22, -05-23, -05-24, -07-29, -07-30, -08-02, -08-29), and they are two crawl waves, not seven monthly reporting periods. Of 2,421 CCNs in the source, 21 have been crawled on more than one date and only 8 across the May → July/August boundary. No fact yet has more than two observations, so latest_change_pct equals pct_change on every row and prior_* equals first_*. The columns are there because the table deepens with each refresh; the depth is not there yet. Anything read off this table today is a shape demonstration, not a market measurement.
  • snapshot_date is a crawl date, not a reporting period. Some pairs are two days apart within one wave (2026-05-22 → -05-23) rather than month-over-month. Filter on first_snapshot / last_snapshot if a minimum elapsed interval matters; 540 of the 696 facts span two different calendar months.
  • A rate present in one snapshot and absent in the next is not a change. Hospitals repost their files with different code coverage from quarter to quarter, and our extractor's own coverage of setting and plan_name has changed between crawls. Disappearance is therefore silent by design: a fact that stops appearing simply never enters this table, and nothing here should be read as a rate being withdrawn. The cost is real — at the (ccn, billing_code, billing_code_type, rate_type, payer) grain 7,714 keys recur across snapshots, but requiring setting and plan_name to match as well drops that to 696. CCN 230141 is the clearest case: its 2026-05-22 rows carry a null setting for all 8,860 rows, its 2026-05-24 rows carry inpatient / outpatient / both, and not one fact survives the join.
  • source_mrf_changed marks facts that may not be the same hospital. 536 of the 696 facts (all of CCN 444030) have it set: the May crawl read strhs-winchester_standardcharges.csv and the July crawl read riverview-regional-medical-center_standardcharges.csv.zip under the same CCN. Those 536 rows are a file-attribution change, not a repricing. The remaining 160 facts (CCNs 050222 and 141327, all rate_type = 'negotiated') are same-URL comparisons. Always filter WHERE NOT source_mrf_changed before drawing a conclusion about prices.
  • Nulls. pct_change and latest_change_pct are null only when their base rate is exactly 0. payer, plan_name and setting are nullable and are part of the key, so NULL is a distinct group value, not a wildcard — a NULL plan_name fact and a named-plan fact for the same code are two rows. Rows with a null rate_amount never enter the table. In hpt_rate_changes_by_operator, median_pct_change can be null if every fact in the group had a zero base.
  • direction = 'flat' is currently 0 rows. Given the two points above, that is a statement about how little comparable data exists, not evidence that hospital prices always move. Do not quote it.

Example queries (Snowflake)

-- 1. Negotiated rates that moved, excluding file-attribution changes.
SELECT "ccn", "billing_code", "payer", "first_rate", "last_rate", "pct_change"
FROM HEALTHPARSE_DATA.HPT_RATE_CHANGES.HPT_RATE_CHANGES
WHERE "rate_type" = 'negotiated'
  AND NOT "source_mrf_changed"
  AND "changed"
ORDER BY abs("pct_change") DESC;

-- 2. Where a payer repriced hardest, by operator.
SELECT "ticker", "payer", sum("facts") AS facts,
       APPROX_PERCENTILE("median_pct_change", 0.5) AS median_pct_change
FROM HEALTHPARSE_DATA.HPT_RATE_CHANGES.HPT_RATE_CHANGES_BY_OPERATOR
WHERE "payer" IS NOT NULL
GROUP BY "ticker", "payer"
ORDER BY abs(median_pct_change) DESC;

-- 3. Only the most recent move, once the panel is more than two deep.
SELECT "ccn", "billing_code", "payer", "prior_snapshot", "last_snapshot",
       "prior_rate", "last_rate", "latest_change_pct"
FROM HEALTHPARSE_DATA.HPT_RATE_CHANGES.HPT_RATE_CHANGES
WHERE "n_snapshots" >= 3
ORDER BY abs("latest_change_pct") DESC;