The first sketch of a layered design — normalise below, define flattened views above — Apr 7, 2023

Groundwork — resources as logical models, primitives as SQL types

A demo of modelling FHIR resources as logical models and mapping FHIR's primitive types onto ANSI SQL types, as groundwork for building real schemas in Trino. It ran aground on the same rock every time: an Encounter has many participants, each with its own type code, and there is no obvious rule for spreading that into columns.

Dan's layering — cheap on load, everything above gets easier

Dan Gottlieb

Principal, Data standards Central Square Solutions

Some transformations you want on literally every query — splitting Patient/123 references so you can join on the raw ID, treating every date as a range with a known uncertainty. Those were the slowest part of every query on BigQuery and Athena, because string splitting blocks the query optimiser. Do them once on load and everything above gets easier.

Why one canonical flat model doesn't work

Nikolai Ryzhikov

CTO at Health Samurai

A component's code is a CodeableConcept with an array of codings, so no general algorithm produces systolic and diastolic columns. You can do it if you assume LOINC, which is fine for US Core and not fine in general. Flattening has to be use-case specific — defined per view, possibly generated from a profile's slices, rather than baked into the base layer.

Ryan's working FHIRPath-to-BigQuery translation

Ryan demoed a working translation of FHIRPath into BigQuery SQL, one expression per column — the seed of what became ViewDefinition. Build the expression tree, walk it recursively, emit a SQL fragment per node, and the full query string emerges as you come back up.

Pathling was already translating FHIRPath to SQL by the same route, which the group took as evidence the approach holds. The one place it broke down was getting the raw ID out of a reference, which needed a non-FHIRPath shorthand.

Why re-flatten FHIR into OMOP at all?

Donna, joining for the first time from the payer side, made the argument against it. Every large plan already converted its administrative and clinical databases into FHIR under the 2021 mandate, so turning it back into the structures the inputs came from invites the obvious question of why not just compute upstream instead.

Her own answer was timeliness: claims are available a day after adjudication, not a month later when the payment cycle closes.