search

tuva-health / tuva

Main repo including core data model, data marts, reference data, terminology, and the clinical concept library
https://thetuvaproject.com/
193 stars 51 forks source link

add monthly patient risk models #627

Open wtyree opened 1 month ago

wtyree commented 1 month ago

Describe your changes

This PR modifies existing patient risk models to support monthly cumulative risk scores and adds new monthly risk factors and monthly risk scores models. A new base table cms_hcc__int_monthly_collection_dates was created to provide the monthly scaffolding for all payment years and all collection period ranges. For each payment and collection year, there are collection start and end periods that go from the beginning of the collection year to the end of each month. 2023-01-01 to 2023-01-31, 2023-01-01 to 2023-02-28, and so on. The existing intermediate models were then updated to pass the payment year and collection period end date through each model. There are then final monthly risk factors and risk scores models that maintain that monthly granularity. The existing final models were updated to maintain support for the existing version of the models.

This change enables a few different analyses:

How has this been tested?

We have tested this on our own Tuva implementation using our patient data. We have confirmed that the cms_hcc__patient_risk_scores are identical in this version.

To see what the YoY comparison might look like, you can use queries like:

select 
    month
    , "2022"
    , "2023"
    , "2024"
    , "2025"
from (
    select 
        payment_year
        , extract(month from collection_end_date) as month
        , normalized_risk_score
    from tuva.cms_hcc.patient_risk_scores_monthly
    ) as risk_scores
    PIVOT(avg(normalized_risk_score) FOR payment_year IN (ANY ORDER BY payment_year))
order by 1
;

One important callout: the adjustment rates end at payment year 2024. I have included some logic to use the max payment year from that file when the year is not available. This is something we use internally to allow us to see how we're progressing this year for payment year 2025. I'm not sure though how Tuva may want this to be handled. You can see an example of this here.

Reviewer focus

How well does this fit into Tuva's plans for this mart? My understanding of risk scores is still developing, have I overlooked anything obvious? In general, I would be willing to use this as a discussion starter if more is needed.

Checklist before requesting a review

Package release checklist

(Optional) Gif of how this PR makes you feel

Loom link

sarah-tuva commented 4 weeks ago

@wtyree this is amazing! I think it looks awesome. I'm running the CI tests now to make sure it works in the other data warehouses we support. I'll be running some tests locally on other datasets to compare results and let you know early next week.

Just for transparency, this will be a breaking change, so we will need to package it with other changes when we're ready for the next minor release.

Thanks again for sharing your enhancements to this data mart! 🙏

github-actions[bot] commented 4 weeks ago

Workflow has finished with the following statuses:

wtyree commented 3 weeks ago

Thanks for the review @sarah-tuva, I'm excited to be able to contribute! I will take a look at the recommendations for making things more generalizable to other data warehouses.

wtyree commented 3 weeks ago

@sarah-tuva I've made the requested changes and learned some things about using dbt in a way that's more portable! Thanks for the feedback. Let me know if there's anything else that I've missed.

sarah-tuva commented 3 weeks ago

@sarah-tuva I've made the requested changes and learned some things about using dbt in a way that's more portable! Thanks for the feedback. Let me know if there's anything else that I've missed.

Thanks for making those changes so quickly! I'll re-run the CI tests and finish my validation.

github-actions[bot] commented 3 weeks ago

Workflow has finished with the following statuses:

wtyree commented 2 weeks ago

Great! Thanks so much for the review and for the help getting this through. It will certainly be useful for us and I hope it will be useful for others as well.