SQL on FHIR WG Meetings
SQL on FHIR WG Meeting — June 19, 2024
Jun 19, 2024

Topics discussed:

  • The draft Parquet spec does not fix a schema per resource — it describes a method for deriving one from the resource definition, mirroring how JSON already handles repeating elements, choice types and primitive extensions. The full representation is theoretically infinite, but any given instance can leave fields out; it only has to agree on column names, their location, and their types. Two datasets that both conform should then merge via Parquet's schema evolution, which is the unproven part, since it has to evolve at both column and structure level. The payoff is that you can hand JSON to DuckDB or Spark, let their existing schema inference do the work, and land very close to conformant.
  • Everything is a string except integers and booleans, deliberately: keep the decimal scale as written and the date exactly as represented, because getting FHIR back out again is a goal. That is awkward to query, so optional annotations carry the query-friendly form alongside the original — start and end instants for partial dates, like a birth date with no day; decimals; and quantities canonicalised to UCUM canonical units, so a query for everything above 30°C can hit rows recorded in Fahrenheit, provided you canonicalise the query too.
  • The fixed-schema counter-argument: nominate the recursion depth up front, generate the schema without ever seeing data, and drop anything deeper. Lossy, but SQL written against it always works — whereas against a data-derived minimal schema, SQL referring to a field that is not there simply fails. The concession was that a view runner is general enough to return an empty collection for a missing field, but hand-written SQL that assumes a shape is another matter. The reason to go minimal at all is performance, since schema size and query time track each other closely, and the draft claims to allow both.
  • Someone asked the obvious question: if this works, do you still need ViewDefinitions? Yes — querying the nested form properly is still a great deal of SQL and type handling, and the view is the generic tool that plucks the columns out. The layering is analytics, then views, then data; views are agnostic of the format underneath, which makes a common data layer an enabler rather than a competitor.
  • FHIR to OMOP ran into a real mismatch: OMOP's provider table wants one specialty per provider, but specialty lives on PractitionerRole, not Practitioner. One demo streams NDJSON, emits partial OMOP rows from each resource, and coalesces the fragments by provider id. In ViewDefinition terms that is just a join of two views — columns from two resources is a join, rows from two resources is a union, as with OMOP's visit table fed by a filtered slice of Encounter plus other types — and since you control the output columns you can make views union-compatible. Keeping a view scoped to one resource is the point: reinventing joins and generic query is an explicit non-goal, and flat intermediate tables are far easier to debug and to write SQL over than lateral view explode across nested FHIR.