SQL on FHIR WG Meetings



SQL on FHIR WG Meeting — February 4, 2025
Arjun Sanyal
Principal Antidote Solutions
Gino Canessa
Principal Software Engineer at Microsoft
John Grimes
Principal Research Consultant CSIRO
Bashir Sadjad
Software Engineer at Google
Feb 4, 2025
Topics discussed:
- Pathling and FHIR Data Pipes both build one maximal schema per resource type, and they part ways on extensions. Data Pipes assumes you know which extensions you care about and promotes them to top-level fields, so querying US Core ethnicity is as easy as reading a column — provided you decided in advance that you wanted it. Pathling instead handles undeclared extensions with field IDs and a top-level map, which buys arbitrary extension content at the cost of gnarlier queries and worse performance.
- John's argument against the maximal schema is that it isn't strictly possible. FHIR's JSON is theoretically infinite — Questionnaire items nest without limit, extensions can appear anywhere — so you end up parameterising a recursion depth and choosing which of the twenty-odd open types to instantiate at every polymorphic slot, or the schema runs to a thousand-plus lines. Pick a depth beyond anything reasonable and it works fine in practice, but it's a knob, not a schema.
- Big schemas aren't free. In Spark the schema is compiled into an expression and shipped between workers at query time, so a 2,000-line schema inflates the payload that gets passed around to distribute the work; the compact version cut that overhead and let John delete a lot of encoder code. Bashir reported that DuckDB with the wide schema performed really badly in his own testing, and Arjun added that it isn't only about speed — some systems simply refuse schemas of that size.
- The new spec chases two things the existing implementations treat as secondary. Zero loss: put a FHIR decimal into a native numeric type and you've thrown away the precision and scale it was captured at, and dates lose fidelity the same way, so the spec stores those as strings and annotates them with derived native types for efficient querying — an idea taken from work Dan Gottlieb did years ago. Version agnosticism: Bashir pushed back that field names change between versions so no schema can be version-independent, and John conceded the point but narrowed his claim to one spec, many FHIR versions — the rules (a union of primitive types across versions, repeating elements, choice types) generalise even though each generated schema doesn't.
- Arjun asked whether anyone actually cares about primitive extensions. John had never seen one in the wild; Gino countered with data-absent-reason on primitives, which turns up when you're pulling in legacy data and is exactly the thing you'd want to know about. John conceded some Australian IGs have adopted them — and noted they're painful for the maximal approach, since an optional primitive extension on every field doubles or triples the schema, while the JSON-shaped approach just tolerates one turning up.