Uber CLAD DQ MVP checks, SQL and visualizations

[chrisroederucdenver]

See update comment at bottom.

See tab for MS.11 here. The MVP ones have a "YES" in the column for Include in June Deliverable. June is a reference to something else. https://docs.google.com/spreadsheets/d/19cs4hTpLw9maU8UuSIcaj_0JSfcqSKcLQ6ESHy5_yFo/edit?gid=319835440#gid=319835440

Also, since we'll be running on Foundry, these queries should be written in Spark SQL and be part of a/the pipeline.

Pre-tasks

• [x] Where's the DQ SQL repo in CLAD?
  • Start here. We may have to move it later. https://foundry.cladplatform.org/workspace/data-integration/code/repos/ri.stemma.main.repository.e9cc160a-5e40-4f24-add6-29a11ab550fa/contents/refs%2Fheads%2Fmaster
• [x] Where will our data end up (TBD)? Can we send some data from the Sample data there?
  • https://foundry.cladplatform.org/workspace/compass/view/ri.compass.main.folder.266a68fe-b617-4b6c-b241-e927a9525449
  • CCDA/transforms/dq
• [ ] What tools will we use? It was Slate pasted into Notepad IIRC for N3C. Contour? Other possibilities are available too. Ask Sofia to clarify at the Claims DQ meeting Friday.
  • Code Workspaces, I started one here thought it's not obvious how to use it at first. LIKELY NOT.
  • Slate start is here
  • [x] Slate will feed into directly into (not into notebooks that feed) into workshop
  • Reference the N3C DQ portal

Counts

• OMOP people counts (done before splitting tickets)
  • [x] Gender (HorizontalStackedBar, a histogram basically) number people per value
    • [x] xml parsing and SQL
  • [x] Race (HorizontalStackedBar...) number people per value
    • [x] xml parsing and SQL
  • [x] Ethnicity (HeatMap...) number people per value
    • [x] xml parsing and SQL
  • [x] month of birth number people per value
    • [x] xml parsing and SQL
  • [x] OMOP people visualization (mostly done before splitting tickets)
  • CCDA people counts by (done before splitting tickets
  • [x] Gender (HorizontalStackedBar, a histogram basically) number people per value
  • [x] xml parsing and SQL
  • [x] Race (HorizontalStackedBar...) number people per value
  • [x] xml parsing and SQL
  • [x] Ethnicity (HeatMap...) number people per value
  • [x] xml parsing and SQL
  • [x] month of birth number people per value
  • [x] xml parsing and SQL
  • (not percents as originally hacked)

• 136 CCDA people visualization

• 134 CCDA other counts

• 135 CCDA visualization for other counts

Post Tasks

• [ ] We may need a pipeline to link the XML parsing to the SQL, and on to Slate, notepad and workspace.

[chrisroederucdenver]

consider how CLAD DQ "post-linkage" checks is different from the PPRL checks Isa Francisco is doing, and how those are different from evaluation plans the consider augmentation and concordance for the pilot project. Should be lots of overlap, don't want to duplicate...

[chrisroederucdenver]

Priority from meeting today: First 3 could be started:

• Number of persons by gender, race, ethnicity, month of birth.
• Number of visits by visit type.
• Histogram of visits per person, % of persons with 0.

This is next:

• Histogram of diagnoses per person, % of persons with 0.
• Histogram of medications per person, % of persons with 0.

We're not sure if we're getting Death data. Open Question.

[chrisroederucdenver]

Deceased Observation: https://www.hl7.org/ccdasearch/templates/2.16.840.1.113883.10.20.22.4.79.html uses templateID 2.16.840.1.113883.10.20.22.4.79 SNOMED code 419099009 "Dead"

[chrisroederucdenver]

@AdamLeeIT Hello!

[chrisroederucdenver]

tagging @bryanlaraway for FYI

[tannerzhang]

Adding James Cavallon for the DQ work per Steph's direction. I am finding James' Github handle

[chrisroederucdenver]

See #118 about the back-end queries. See #112 (closed/defered for now) for the inevitability of Site IDs. It's an open question, but will impact the visualization. The back-end should be modified to have a single site, "#1" and the viz should be able to handle multiple values when/if they arrive. This might be better with grouped bar charts than a heat-map. This seems OK because my current guess is we won't have 100 sites, but it is just a guess.

[AdamLeeIT]

updated data to slate for CCDA DQ checks

• [x] ccda_counts_by gender
• [x] ccda_counts_by race
• [x] ccda_counts_by ethnicity
• [x] ccda_counts_by birth_month (build failing)

[chrisroederucdenver]

Looks like Matt fixed the Postgres sync for ccda_counts_by_birth_month, I marked it done.