Claim & EOB unnesting

[niquola]

Claims & EOBs are not very database-friendly resources because of their aggregated nature and "sequences" logic.
One of the possible solutions is to split them into header and item parts - ClaimHeader & ClaimItem, and resolve sequences:

resourceType: 'Claim'
diagnoses:
- sequence: 1
   ....
item:
- diagnosesSequence: [1, 2]
  item-content...
- diagnosesSequence: [2]
  item-content...

resourceType: ClaimHeader
diagnoses:
- sequence: 1
   ....
---
resourceType: ClaimItem
id: claim-id+sequence
diagnoses: 
item-content...
---
resourceType: ClaimItem
id: claim-id+sequence
diagnoses: []
item-content...

[EvanMachusak]

This is how Microsoft did it:
https://github.com/microsoft/FHIR-Analytics-Pipelines/blob/d0ae7a76b0f653a78aac3e05ce5abe27e6e5d4b4/FhirToDataLake/scripts/sql/r4/Resources/Claim.sql

We should discuss this project. Has Microsoft solved the entire SQL-on-FHIR problem already?

[dkapitan]

Interesting. What I don't understand: are the columns in the DDL just single level columns with . as a naming convention, or does this create structs and nested columns?

[EvanMachusak]

I think so. They unrolled the meta property into columns, e.g.
[meta.versionId]

Brackets in MSSQL at least are like quoted names. You can put special characters within them, including dot (.)

[dkapitan]

Thanks for clarifying. Has been a while since I use T-SQL/MS SQL, and wasn't sure what the syntax meant. That raises the next question: do we want to go for simple names in our transformations (lowercase, limited number of characters) or are we going the case-sensitive quoted names route?