CMS HCC Payment Risk Score Weighting by Member Months

[pfehlinger]

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

1. Testing on a larger dataset than one member and use more relevant payment years.
2. Evaluating the rounding of scores and when is the right place to do the multiplication.
3. 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).
4. Creating a link to the financial_pmpm mart and ensuring it is the right and appropriate dependency.
5. 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

[github-actions[bot]]

Workflow has finished with the following statuses:

• Snowflake: failure

• Snowflake: skipped

• Snowflake: skipped

• Redshift: success

• Redshift: success

• Redshift: success

• BigQuery: success

• BigQuery: success

• BigQuery: success

[github-actions[bot]]

Workflow has finished with the following statuses:

• Snowflake: success

• Snowflake: failure

• Snowflake: skipped

• Redshift: success

• Redshift: success

• Redshift: success

• BigQuery: success

• BigQuery: success

• BigQuery: success

[github-actions[bot]]

Workflow has finished with the following statuses:

• Snowflake: success

• Snowflake: success

• Snowflake: success

• Redshift: success

• Redshift: success

• Redshift: success

• BigQuery: success

• BigQuery: success

• BigQuery: success

[github-actions[bot]]

Workflow has finished with the following statuses:

• Snowflake: success

• Snowflake: success

• Snowflake: success

• Redshift: failure

• Redshift: skipped

• Redshift: skipped

• BigQuery: success

• BigQuery: success

• BigQuery: success

[sarah-tuva]

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.

[pfehlinger]

@sarah-tuva I made the changes you suggested:

1. I added the ephemeral staging layer for member_months.
2. 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)

[github-actions[bot]]

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

[github-actions[bot]]

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

[github-actions[bot]]

Workflow has finished with the following statuses:

• Snowflake: success

• Snowflake: success

• Snowflake: success

• Redshift: success

• Redshift: success

• Redshift: success

• BigQuery: success

• BigQuery: success

• BigQuery: success