sl-dolt

We're trying to reclaim the word

We're doing something a bit similar at DoltHub, where databases are managed with a GitHub like workflow, and where you get the pros and cons of that kind of system. Our hospital price database (work ongoing daily) is community built: https://www.dolthub.com/repositories/dolthub/transparency-in... (context: https://docs.google.com/document/d/1NifwgKHBCeF35ZRZsfpgg4bE...)

> I'd love to see what this map looks like filtered for specific services.

Sure. I did something like that with our insurance data. Did a quick folium visualization. You can start from here:

https://www.dolthub.com/repositories/dolthub/hospital-prices...

A limitation of our dataset is it excludes percentage-based contracts. So those won't show up.

Anyways, if you have the hospital NPI you want, you can look up all of its dollar-amount negotiated rates in this table. You can link that to the `hospital` table, geocode however you want, and make a map of the different rates. Note, however, that the insurance data published here is kind of a mess and, I think, contains many many mistakes (originating in the source data.)

BTW, I'm not sure if this insurance-derived hospital data exists anywhere else. I believe it doesn't.

That's really cool. I'll probably end up trying to reproduce this by following your work step-by-step. I'm not too familiar with OSM, besides having used Nominatim.

Along the same lines we're crowdsourcing a database of hospital prices, starting this week. I'm reviewing our first pull request as we speak. You can follow our progress live. The data is here: https://www.dolthub.com/repositories/dolthub/standard-charge...

We talked a little about the problems we're encountering here: https://docs.google.com/document/d/1uMx1sUYwP_uE7ebd3PtGvF0t... and here https://docs.google.com/document/d/1NifwgKHBCeF35ZRZsfpgg4bE...

Author here. The problem is checking compliance. By publishing these files, they seem compliant, but really aren't. Since the payors are the guardians of the data, it's very difficult to check that what they're posting is correct. That was the main thrust of the article.

Completely agree. There are databases like this out there (All-Payer-Claims database) but they're extremely expensive (it would cost millions to get data for the entire US) and I have no idea why.

Thanks. Will fix that.

I'm the author. When I write articles I have to make a choice: make them readable by the public, or detailed enough to satisfy the experts. I try to strike a balance, but I can't have both.

Please take a look at the CMS Price Transparency Guide https://github.com/CMSgov/price-transparency-guide and familiarize yourself with the schema. You can also take a look at the federal ruling: https://www.ecfr.gov/current/title-45/subtitle-A/subchapter-...

The metadata you're talking about is specified in the files themselves. I've limited my search to fee-for-service (non-capitated, non-derived, non-bundled) institutional claims.

You can write to me if you have more questions. [email protected]

I'm the author.

Take an example like this https://github.com/CMSgov/price-transparency-guide/discussio...

I don't know how closely you've worked with this data -- you clearly have some kind of expertise -- but how do you explain this?

The insurance companies had 18 months to talk to the CMS and ask for a better data model. If they're not able to explain how much things cost with 5 different negotiated types -- negotiated, percentage, derived, fee schedule, and capitation -- then they should have asked for another one.

The hospital and insurance rates are both fee-for-service base rates for items billed individually. If there's some nuance in interpreting how "fee for service" "dollar amount negotiated" goes, definitely write to me and let me know. I talked with experts in healthcare pricing before I published this.

You can write to me at [email protected] if you wanna hit me with more questions.

I'm the author. Publishing my reply to you (as I understood your question):

> No one can produce the corrected/missing rates but the insurance companies themselves. All we can do is point out when we’ve found rates/patterns that don’t make sense. E.g. https://github.com/CMSgov/price-transparency-guide/discussio...

This is basically what health insurance companies do. They want higher billed rates from hospitals so that they can offer better "discounts." In reality, no one pays the billed rates.

This is all insurers, all US hospitals, and 70 billing codes (CMS-specified "shoppable" services).

I think this is a first of its kind. Please give your feedback.

We recently created this database of 70 CMS shoppable services for all US hospitals. It comes from the recently published insurance MRF data.

We created this database in a distributed way using simple tool I wrote to process the JSON. https://github.com/dolthub/data-analysis/tree/main/transpare...

Absolutely interested, on my end at least. I wrote this to manage the transparency in coverage files: https://github.com/dolthub/data-analysis/tree/main/transpare... but I'm always looking for better techniques.

Edit: Oh wow, I see you used it on those exact files. How about that.

I'm the author, feel free to AMA.

We're working on making all the data from the Transparency in Coverage Act open source to whatever extent possible.

I wrote here about our efforts to make this work and what we found: https://news.ycombinator.com/item?id=34445914

The following query will return all the prices for the billing code 85032 (manual red blood cell count) that are greater than $10:

> select npi, reporting_entity_name, reporting_entity_type, negotiated_rate from npi_rate join (select id, insurer_id, negotiated_rate from rate where ( (code_id in (select id from code where billing_code = 85032)) and (negotiated_rate)) y on y.id = npi_rate.rate_id inner join insurer on insurer.id = insurer_id order by negotiated_rate;

This is being built as we speak and will slowly grow over the next few weeks. You can join the effort in building it or you can explore and tell me what you find. [email protected]

You can stream gzipped files to work with them out of memory. Can you stream this? Is that even a use-case to consider?

I'm the author. The CMS has done a lot since then, including beginning to enforce their Transparency in Coverage act, which is even more comprehensive than the Transparency in Pricing act (which is what this article is about.)

See the article I wrote on this last month: https://www.dolthub.com/blog/2022-09-02-a-trillion-prices/

I'm the author. That's right. At least, starting from our database, you won't have to do the hard work of tracking down each of these 5000+ URLs.

I'm the author of the article, but I'm not a lawyer.

Regarding your first point:

I'm aware of the copyright on billing codes. I suspect it means "you can't make your own billing codes based off of our system."

I don't think it means you can't republish the codes anywhere. They're republished all the time.

Someone can jump in and correct me.

Secondly:

The CMS law required hospitals to itemize their procedures by billing code _and_ by insurance company. Not that they all do that, but in theory, these negotiated rates should allow you to price shop between hospitals. The "list prices" are effectively meaningless.

I'm the author. The CMS law relates to specific pricing data, where each billing code is itemized, and prices are given down to the insurer level. These prices have only been available since Jan 1, 2021.

Aggregate data (or "list prices", MSRPs) have been available in some cases for a while now, depending on the state and context.