A new open-source health data standard called SQL on FHIR is helping bridge the gap between fast-growing volumes of structured health data and the tools used to analyse it.
The lead author of a new paper titled SQL on FHIR – Tabular views of FHIR data using FHIRPath, John Grimes from CSIRO’s Australian e-Health Research told Pulse+IT that SQL on FHIR (pronounced sequel on fire) was developed to help analysts, clinicians, and public health teams make better use of FHIR data using tools and skills they already had.
“We need to give people the tools to make use of this data, but with their existing skills and within their existing technology platforms – so that’s what SQL on FHIR is all about,” Mr Grimes said.
He said that while FHIR (Fast Healthcare Interoperability Resources) had become essential for connecting digital health systems, its complexity had limited its use for analytics.
“We’re capturing more structured data than ever before, but we’re still struggling to make full use of it,” Mr Grimes said.
“FHIR is important not just to connect systems together, but also for health data analytics – because it gives us the potential to get a more complete view of a patient’s health data.”
He said that FHIR also had the potential to reduce the amount of time it takes to get patient data for analytic purposes, “which makes it more feasible to do things like clinical decision support at the point of care.”
SIMPLER VIEWS
The SQL on FHIR standard provided a way to define simple, flat views over FHIR data that can be easily queried with SQL. These views are then reusable and shareable.
“It tries to make it easier to make use of FHIR data without needing to have deep knowledge of the standard,” Mr Grimes said.
“Once you’ve created those views you can share them, other users can pick them up and use them with the tools they already have and they’re already familiar with.”
Rather than trying to build a universal SQL dialect, the group focused on a more flexible approach.
“Instead of actually trying to create an approach to query FHIR using a particular dialect of SQL, it’s instead a way of defining views that are then easy to query using lots of different dialects of SQL,” Mr Grimes said.
Work on the current version began almost two years ago.
“It was really just a group of interested people including people from Google, Microsoft, Health Samurai and others, who are part of the core team who develops the FHIR spec.
It was very much implementation led – so we built working software alongside the specification.”
Mr Grimes said that previously, many standards had fallen short because they were written by committee without hands-on testing.
“We also created a sort of common test suite, and we could run that against all the different implementations and see what coverage each of those had and make sure that we could get consistent results.”
He said one of the technical challenges was SQL dialect compatibility.
“Ideally, we’d just be able to talk SQL across any database and it would work – but that’s actually not the case – in practical terms, you can’t really just pass SQL queries around and just expect them to work anywhere.”
Mr Grimes said that limitation shaped how SQL on FHIR was designed, which led to the group ‘chopping the problem in half.’
“We asked: ‘What do people need to help them with FHIR to get it into a super simple form?” Once you have it in this super simple form it actually simplifies the SQL that you need to write to query it – making this SQL more portable.”
IMPLEMENTATIONS
Mr Grimes said the new standard was already being implemented in various settings.
“We have a reference implementation that we collaboratively built in JavaScript, we have an implementation in Aidbox, and another commercial product from Health Samurai. You can feed it a view definition and it will create and maintain that as a view within the database.”
Mr Grimes said CSIRO’s open-source platform Pathling was another key implementation, using Apache Spark.
“What Pathling does is translates a SQL on FHIR view definition into an executable query over Apache Spark. It can take big data sets, chop them up into slices and process them in parallel. It also has streaming support.”
Other implementations included FlatQuack built on DuckDB, Google’s FHIR Data Pipes, MedPlum, Sapphire (built in Rust), and support within HL7’s implementation guide builder.
Mr Grimes said U.S-based NCQA was also interested in using SQL on FHIR to supplement their goal of improving quality reporting.
Looking ahead, the working group was planning integrations with large language models (LLMs) to create natural-language SQL queries, and was drafting a new API to generate tailored data extracts for population health.
“We’ve got all sorts of things that we’re planning to leverage this work – including using SQL on FHIR views to surface tabular views to LLMs, to interrogate those views in a question-answer interface.
Mr Grimes said there were also plans to support cohort identification and to connect FHIR to other data standards like OMOP.
“We’re looking at whether we can use this as part of the toolset, in a predictable and reproducible way – and just trying to make it as easy as possible for people to use this data to do real-world things.”
