OHDSI Home | Forums | Wiki | Github

Linking Cost to Other Tables

Hi, I’m not sure where the best place to ask this question, but I’m on a team that’s beginning to implement OMOP v5.4. So I have a couple of questions:

  1. How does Cost typically link to all other tables in OMOP?
  2. How is the Cost table typically used in the final product?

Any resources would be helpful. Thank you!

One way I am using cost tables is linking it to drug_exposure. When an invoice comes in for a drug - it first creates a drug_exposure record. Using the derived total cost, what was charged, paid, etc - a cost record is created where the cost.cost_event_id = drug_exposure.drug_exposure_id. Thus linking cost to a drug or prescription.

Same can be done for procedures, devices, etc where you have cost information from the source.

A use case we have been using the above is total cost of antibiotics. Similarly, total cost of patient visits who had antibiotics.


1 Like

I’m interested in this same question. In particular, if the data is claims data where absolutely everything ties back to a visit_detail record representing a claim line (and a visit_occurrence record representing some courser grain), does it make more sense to link cost to visit_detail instead of drug_exposure? This would be sort of making a hub-and-spoke where visit_detail is the hub and all the other tables connect back to that.

If the cost table links directly to drug_exposure, or condition_occurrence, procedure_occurrence, etc., then that seems potentially confusing. If someone was starting at visits and wanted to see cost info, they’d have to join to at least 3 other tables (drug_exposure, condition_occurrence, procedure_occurrence) first, then join to the cost table 3 times (1x per exposure/occurrence table). That seems like a less straightforward setup than routing all relationships through visit.

Approach (A) always join cost to visits seems like it aligns with advice from @Gowtham_Rao on this thread proposing the use of pseudo-visits for cost records from claims data.

It seems like other guidance I’ve seen, though, is for approach (B) of linking cost to drug_exposure/condition_occurrence/procedure_occurrence/etc. and I’m unsure which is the preferred approach.

@Dan_Angelelli suggestion regarding linking it to visit_detail is also a valid approach.

When I make decisions regarding options - I think “locally” - meaning what is the use case I need to deal with and balance the decision against future use cases. In my case, we were interested in detailed antibiotic drug use and costs. So the query goes from drug_exposure → person/visits and via drug_exposure_id to costs using sub-selects.

For other context - it may make more sense and from a SQL perspective use visit_detail and joins.

1 Like

So to make sure I’m tracking, though it is possible to link cost to multiple tables, it doesn’t seem ideal or practical even due to the complexity of SQL joins, even if there are potential use cases where it may be informative to join cost with drug_exposure/condition_occurrence/procedure_occurrence, etc, but rather it’s best to choose one “central” table. Does that summarize or am I missing the mark?

We need to fix an error in the CDM spec for cost table in blue:
There is no column payer_plan_id

v3 of OMOP CDM had 2 tables by the way… (for drug cost and for procedure cost).

When we merged in later versions, seems the merger was not guided well and maybe has room for improvement.

No text in CDM spec (I just re-read it carefully) (or draft or accepted convention) is guiding how to link cost-rowA (or rows) with procedure-rowB. (same for drug costs)
Or advising CDM users not to do such a link. (and there seem to be a need for it).

One proposal can be: Let’s add the polymorphic join key (like for note (and note_nlp) linking to their linked events).

Also, the use of visit detail for cost purposes should be written up as formal/ Themis/Themis-draft convention.

Also, some people see visit detail only for transfers within hospital (and I am one of them)).

Okay this is all very helpful.
Thank you @mkwong for that feedback on thinking locally. We’re thinking about a very broad set of use cases, so I think it makes sense to join cost to visit_detail in our case but I see the use cases where that wouldn’t be the best approach.

@Vojtech_Huser if I’m understanding you correctly, I agree! All the options you and contexts @mkwong and you said seem feasible.

Hm. That may work for hospitalizations, but even there I don’t think it would. Because pharmacy and simple office visits don’t have any visit details. It only makes sense for complicated and extended visits.

But I am not sure what the problem is. The COST table essentially is just an extension to the other tables incurring the cost, even when there is no record of any visit.

  • DRUG_EXPOSURE through the pharmacy or administration by the provider
  • PROCEDURE_OCCURRENCE for whatever the procedure is
  • DEVICE_EXPOSURE for the device
  • VISIT_OCCURRENCE like for facility costs
  • VISIT_DETAIL if there is more than on facility

Conditions, most observations, death, specimen, care sites, providers, observation periods, payers and plans - all are free or not incurred by health care.

If you want to calculate cost of something, you either follow directly from that thing to the COST table. Or, if you want all the indirect costs, you first establish everything that happened because of that entity (e.g. pain medication after a surgery), follow from there to the COST table and add it all up.

hmmm. I think that makes sense. It’s a little less straightforward of a mental model coming from a claims source data perspective, but I’m pretty sure I’m following.

Hi @Dan_Angelelli and @philm - I’m Jen Duryea - the original designer of the current Cost Table. I can give background why you want to associate costs to different event records (not just visits).

if you are coming from a claims source perspective, then I suggest assigning the costs to the actual values that derived that cost. With a little more detail from @Christian_Reich comment above, US claims are paid in the following ways:

  • cpt/hcpcs code - assign that cost record to that cpt/hcpcs code (most likely in the procedure or drug_exposure table)
  • ndc code - assign that cost to the ndc code (most likely in the drug_exposure table)
  • DRG or Revenue code - assign that cost record to the visit or visit_detail record (I am one that believes you can represent different claims under a visit_detail record - similar to what @Gowtham_Rao suggests)

It is important to assign the cost the code that generated the payment so that you have the most accurate and flexible model that covers a variety of questions (i.e. how much did rituximab cost patients last year? how much were myocardial infarction hospitalizations in the last year?). As for the SQL joins involved, I would argue that OMOP is more of a logical model and you can structure you tables as you see fit to minimize joins if necessary.

@Vojtech_Huser - I can help add documentation for how to link cost a to procedure b. These conversations were prior to THEMIS and all of the new fangled processes OHDSI has in place to make changes. The idea was to use the cost_domain_id to link to cost to the event table the event lives in and use the cost_event_id to link the event in that domain to the ID column. So you can assign a cost record directly to a drug_exposure table, reference “drug” for cost_domain_id and the drug_exposure_id to the cost_event _id.

1 Like

This is very helpful!
I was thinking through how DRG or revenue code based costs would be tracked since they aren’t part of a drug_exposure or a procedure, but you answered that before I could articulate a question on it! Assigning those costs to the visit record makes sense to me.

Much appreciation for the background

1 Like

@jenniferduryea I am taking you up on your offer.

Would you be open to comment on the convention in this Themis issue that I created for it here

1 Like

@Vojtech_Huser I added some background, use case, and implementation notes to the Themis convention here Link cost row A to procedure row B · Issue #190 · OHDSI/Themis · GitHub. Please let me know if you need more info than this or if you want to further discuss, please send me a message and we can set up a meeting :slight_smile:

@jenniferduryea @Dan_Angelelli @Vojtech_Huser

The Cost table is lacking ETL conventions, User Guide documentation, clarity on what to do when a cost is associated with a non-standard concept_id and is mapped to > 1 standard concept_id (NDC codes, CPT4, etc.), what to do with DRG codes, what to do when the patient has multiple insurance plans, etc.

In order to properly define each of these fields with appropriate conventions and ETL guidelines, we need a couple solid use cases with de-identified data to mock up and model the field definitions, conventions and guidelines for this table. I can help guide this process, but I don’t have the data or use case to move it forward.

Please reply to the MS Teams post here and we can get started on this initiative!

Hey @MPhilofsky - I got an error when trying to access the Teams link. Can you direct email me the info?

1 Like