CMS HCC Payment Risk Score Weighting by Member Months #453

pfehlinger · 2024-05-02T19:35:17Z

Describe your changes

This resolves #337 by adding two new columns to cms_hcc.patient_risk_scores: member_months and payment_risk_score_weighted_by_months. The member months are derived from the finanicial_pmpm mart, specifically financial_pmpm.member_months, utilizing payment year eligibility. Weighting is simply multiplying the payment_risk_score by member_months. This enables easier membership aggregations as a "population risk score" would now be sum(payment_risk_score_weighted_by_months)/sum(member_months). If a member does not have eligibility in the payment year, both fields are NULL.

I chose to link to financial_pmpm at the suggestion of @sarah-tuva (thank you!). I am calling this out explicitly in case there are concerns about dependencies between two marts.

The weighting occurs prior to rounding, thus there is a loss of accuracy due to rounding. Technically, the rounding for the score calculations today do not align with CMS rounding, namely rounding to the third decimal after each step. See pg. 11ff. If there is a desire to align with CMS, I would be happy to adjust the code to follow that methodology and update my weighting to align as well.

How has this been tested?

The tuva-demo data does not have 2019 eligibility which is necessary for this implementation. As such, I updated enrollment_end_date for patient_id 10013 in core.eligibility to be '2019-12-31'. I then executed dbt run --select tag:financial_pmpm and then dbt run --select tag:cms_hcc to confirm the value of 12 was showing for member_months and that the multiplication was correct.

Reviewer focus

Testing on a larger dataset than one member and use more relevant payment years.
Evaluating the rounding of scores and when is the right place to do the multiplication.
Evaluating if NULL is the right approach for when eligibility doesn't exist in the payment year or if there is another preferred method (pattern appears to be 0s but want to explicitly check).
Creating a link to the financial_pmpm mart and ensuring it is the right and appropriate dependency.
From the checklist below, I did not update the dbt_project.yml as I don't know the release number associated with this PR. I also didn't update the docs files as I don't what the necessary files are. Guidance for both of these would be appreciated :)

Checklist before requesting a review

I have updated the version number in dbt_project.yml file to reflect the release number of this PR
I have updated the docs files (by running dbt docs generate/serve and copying the necessary files into the docs folder)
I have commented my code as necessary
I have added at least one Github label to this PR
My code follows style guidelines
(Optional) I have recorded a Loom to explain this PR

(Optional) Gif of how this PR makes you feel

Loom link

sarah-tuva

Hi @pfehlinger, I'm sorry for the delayed review while I was out on PTO. This is looking really good! This solution makes a lot of sense, and I like how you calculated the member months for the total year.

I appreciate your comments about creating a dependency between CMS HCC and Financial PMPM. I'm not super concerned about this. We have other marts that have dependencies as well. We use the data type variables (claims_enabled / clinical_enabled) to help control this. In theory, someone has to have claims in order to run both CMS HCC and Financial PMPM.

I left two comments with requests for changes. I am running the CI testing as well to make sure the syntax works in the other data warehouses we support.

sarah-tuva · 2024-05-09T21:35:26Z

models/cms_hcc/final/cms_hcc__patient_risk_scores.sql

+        patient_id
+        , cast(substr(year_month, 1, 4) as integer) as eligible_year
+        , COUNT(1) as member_months
+    from  {{ ref('financial_pmpm__member_months') }}


One of our design patterns is to include an ephemeral staging model for every data mart. You can think of this as an "input layer" for the data mart. This helps with documentation and mapping.

You can see in the staging folder for this mart that we are referencing models from Core. Can you add a new staging model for financial_pmpm__member_months? You can then replace your reference in this CTE with the staging model. I would name it something like cms_hcc__stg_financial_pmpm__member_months.

sarah-tuva · 2024-05-09T21:37:58Z

models/cms_hcc/final/cms_hcc__patient_risk_scores.sql

+        , cast(substr(year_month, 1, 4) as integer) as eligible_year
+        , COUNT(1) as member_months
+    from  {{ ref('financial_pmpm__member_months') }}
+    group by


Can you add a payment_year filter to ensure that the member months being used in this calculation are from the correct date range? You can see how we do this in the intermediate model for eligibility. You can include the Jinja code for payment year at the top and then add a where clause to make sure your eligible_year matches the payment_year.

@sarah-tuva It technically isn't necessary as in the join I am joining on year, thus it wouldn't matter. I can include for performance considerations if it is necessary. The larger question I have is: Is tuva constrained to run a single payment year at a time? Or could multiple payment years run at once? If it is the latter or the latter is desirable in a future state, then I would opt to not have the filter. If it is only designed to run serially one year at a time, then I can add the filter.

@pfehlinger I missed that join down below. That works for me!

To answer your question, the data mart is currently designed to run one payment year at a time. Snapshots can be enabled to support analytics over multiple payment years. We have had some internal discussions around refactoring this mart to run all possible payment years with the given data. This would change the ability for someone to run analytics on any year they choose, though. I would love to hear your feedback on what you think would be most useful.

sarah-tuva · 2024-05-28T16:49:25Z

Hi @pfehlinger, the CI testing failed for Redshift. It looks like the substring syntax doesn't work in Redshift but does in BigQuery and Snowflake. Dbt has some cross-database macros we can look at using or create a custom macro. Let me know if you want any help with creating/testing this.

…ment_risk_score by member months

…. Added ephemeral staging for member months in cms_hcc

pfehlinger · 2024-06-03T21:09:00Z

@sarah-tuva I made the changes you suggested:

I added the ephemeral staging layer for member_months.
I created a macro for substring to support cross platform usage.

I didn't do the eligible year per my comment above. There are still all those other items in terms of proper PR I would like to learn (docs & version number)

sarah-tuva · 2024-06-03T23:03:13Z

models/cms_hcc/staging/cms_hcc__stg_financial_pmpm__member_months.sql

+{{ config(
+     enabled = var('cms_hcc_enabled',var('financial_pmpm_enabled', var('claims_enabled',var('clinical_enabled',var('tuva_marts_enabled',False))))) | as_bool
+   )
+}}


I would remove the var reference for clinical. That could cause issues.

Suggested change

{{ config(

enabled = var('cms_hcc_enabled',var('financial_pmpm_enabled', var('claims_enabled',var('clinical_enabled',var('tuva_marts_enabled',False))))) | as_bool

)

}}

{{ config(

enabled = var('cms_hcc_enabled',var('financial_pmpm_enabled', var('claims_enabled',var('tuva_marts_enabled',False)))) | as_bool

)

}}

github-actions · 2024-06-04T00:08:24Z

Workflow has finished with the following statuses:

Snowflake: success

Snowflake: success

Snowflake: failure

Redshift: success

Redshift: success

Redshift: failure

BigQuery: success

BigQuery: success

BigQuery: failure

This comment was marked as outdated.

Sign in to view

sarah-tuva requested changes May 9, 2024

View reviewed changes

This comment was marked as outdated.

Sign in to view

pfehlinger added 2 commits June 3, 2024 15:58

Adding member months from financial_pmpm and using that to weight pay…

6f89663

…ment_risk_score by member months

Added substring macro to support cross platform nature of the project…

86b95c2

…. Added ephemeral staging for member months in cms_hcc

pfehlinger force-pushed the weighted_risk_score branch from 19e537c to 86b95c2 Compare June 3, 2024 21:06

sarah-tuva reviewed Jun 3, 2024

View reviewed changes

This comment was marked as outdated.

Sign in to view

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

CMS HCC Payment Risk Score Weighting by Member Months #453

CMS HCC Payment Risk Score Weighting by Member Months #453

pfehlinger commented May 2, 2024

This comment was marked as outdated.

sarah-tuva left a comment

sarah-tuva May 9, 2024

sarah-tuva May 9, 2024 •

edited

pfehlinger Jun 3, 2024

sarah-tuva Jun 3, 2024

This comment was marked as outdated.

This comment was marked as outdated.

This comment was marked as outdated.

sarah-tuva commented May 28, 2024

pfehlinger commented Jun 3, 2024

sarah-tuva Jun 3, 2024 •

edited

This comment was marked as outdated.

github-actions bot commented Jun 4, 2024

CMS HCC Payment Risk Score Weighting by Member Months #453

Are you sure you want to change the base?

CMS HCC Payment Risk Score Weighting by Member Months #453

Conversation

pfehlinger commented May 2, 2024

Describe your changes

How has this been tested?

Reviewer focus

Checklist before requesting a review

(Optional) Gif of how this PR makes you feel

Loom link

This comment was marked as outdated.

sarah-tuva left a comment

Choose a reason for hiding this comment

sarah-tuva May 9, 2024

Choose a reason for hiding this comment

sarah-tuva May 9, 2024 • edited

Choose a reason for hiding this comment

pfehlinger Jun 3, 2024

Choose a reason for hiding this comment

sarah-tuva Jun 3, 2024

Choose a reason for hiding this comment

This comment was marked as outdated.

This comment was marked as outdated.

This comment was marked as outdated.

sarah-tuva commented May 28, 2024

pfehlinger commented Jun 3, 2024

sarah-tuva Jun 3, 2024 • edited

Choose a reason for hiding this comment

This comment was marked as outdated.

github-actions bot commented Jun 4, 2024

sarah-tuva May 9, 2024 •

edited

sarah-tuva Jun 3, 2024 •

edited