What the same procedure costs across hospitals, the charge-to-payment markup, and whether spending buys better outcomes — a two-fact star schema on public CMS data.
Healthcare prices are incoherent — the same procedure carries wildly different charges, and almost nobody pays the charge anyway.
Does paying a hospital more buy better outcomes?
The join: HRRP's Facility ID is the inpatient file's Rndrng_Prvdr_CCN — the CMS Certification Number. That shared key is the whole project.
Two facts at different grains, joined on demand through the conformed dim_provider.
A dbt relationships test enforces the CCN join as a contract — if a future CMS release breaks it, the build fails loudly.
The charge is a list price decoupled from the cost of care, or from what anyone pays. The payment — what's actually exchanged — is far more uniform.
From nervous-system procedures to transplants, the markup sits in a tight 5.6–6.4× band. The list price isn't tracking complexity — it's a roughly fixed multiple applied across the board.
A faint downward tilt — the best-paid quintile dips just below expectation — but payment explains under 1% of the variance in readmissions.
Paying a hospital 2.6× more buys, at most, a rounding error of better outcomes.
Anchor on the payment, not the charge — and stop assuming a pricier hospital is a better one.
The 20× charge spread is negotiating theater; what's actually exchanged varies only 1.7×. Any analysis built on charges is measuring a fiction.
And the comfortable assumption that higher spend signals higher quality didn't survive the join — r = −0.081. The honest read is barely-there, not zero, and that's the answer payers keep arriving at.
Including the cross-fact relationships test on the CCN join. The cost-vs-quality finding is NTILE(5) payment quintiles joined across both facts through the conformed provider dimension, with CORR() computed in SQL.
The list price is a story. The payment is the data.