Parquet as the interoperability format, and SQL on FHIR in low-resource clinics — Mar 17, 2023
The full pipeline — Synthea to four columnar engines
The demo ran the whole pipeline: Synthea files, a few simple transformations, then Spark to infer a schema and write Parquet, which loaded straight into DuckDB, ClickHouse and DataFusion (BigQuery and Redshift look easy too).
Parquet started to look like the interoperability format for the group — if you can produce it, most columnar engines will take it, and it ties into Arrow so the data can move in memory afterwards. The same queries then ran on every engine, which was the point of the exercise.
The transformations were deliberately small and lossless
Originals are kept alongside:
- Category codings get a generated array of
system|codestrings so you can filter with an array-contains instead of digging - Reference IDs are parsed out
- Extensions are keyed by the last segment of their URL with dashes turned into underscores (nested extensions the same way, URL preserved)
- Observation components are indexed by code
That last one was flagged as not strict by its own author: it takes the first coding and ignores the system, so a component coded with several concepts is ambiguous.
Every database spells nested access differently
Spark indexes arrays from zero, DuckDB from one, ClickHouse needs string notation past the first level, DataFusion has no array-contains at all yet.
Hence the proposal: a very small macro layer that expands one path expression into whatever the target speaks — a handful of functions like array-contains and age, no ifs or loops, plus a reference implementation.
The objection: a macro must cover every variation of every database, and one missed edge case means the query won't translate.
With table-valued functions I could polyfill whatever plain SQL can't do and compile CQL into SQL. If that works you don't need a CQL engine at all — load the Parquet into DuckDB and run the queries.
Schema inference is the sore spot
Most tools sample the first n rows to guess the shape, and FHIR has recursion points where nesting can go arbitrarily deep — any real dataset stops somewhere, but the schema itself is recursive, so Parquet from dataset A and Parquet from dataset B may not be compatible.
If you need them to be, JSON is your lingua franca and the Parquet schema gets layered on afterwards. Spark reads all the data rather than sampling, which is more stable, but Daniel had already hit the general problem — DuckDB choked on FHIR NDJSON that the small HL7 samples didn't trip.
A use case from Kenya and Tanzania
Daniel presented a maternal-care programme in Kenya and Tanzania as a use case: about ten FHIR resource types, a clean episode (roughly four antenatal visits, delivery, two postnatal visits, immunisation), and dashboards that drive the day-to-day operation of a clinic.
The constraints shape the architecture — intermittent internet, affordability as a design criterion — so the sketch is a FHIR-conformed data lake on plain blob storage per district with DuckDB in read-only mode, possibly compiled to WebAssembly for tablets.
His reason for showing up: SQL is what local analysts and developers already read, so standardised queries are the whole point. Agreed next step: generate synthetic data with Synthea, sketch the dashboard queries, and use them to decide which transformations are actually needed.