Short Description

As more healthcare data is available in FHIR format, there is increasing value in being able to work with this data using off-the-shelf, low cost and scalable tooling for reporting, analytics, machine learning and other applications. Participants in this track will collaborate on updating the SQL-on-FHIR v1 spec to incorporate what we've learned using this approach, as well as take advantage of native JSON support in modern database engines.

Long Description

As more healthcare data is available in FHIR format, there is increasing value in being able to work with this data using off-the-shelf, low cost and scalable tooling for reporting, analytics, machine learning and other applications. The SQL-on-FHIR v1 spec defines a logical representation of FHIR for SQL users. Updating the spec to v2 will enable the community to incorporate what we've learned over a number of years of experience with this approach, as well as take advantage of native JSON support in modern database engines (e.g., BigQuery, Snowflake, Postgres, Oracle, MySql, etc.). 

The track will focus on defining and prototyping a standard set of data transformations and projections to simplify working with FHIR data using SQL and ensure that queries are portable between implementations.

Type

Author and test a FHIR associated specification

Related Tracks?


Call for participants

Anyone interested in working with FHIR in a data warehouse or data lake environment

Track Prerequisites


Track Lead(s)

Dan Gottlieb

Nikolai Ryzhikov

Track Lead Email(s)

Dan@CentralSquareSolutions.com ; niquola@health-samurai.io

Specification Information

SQL-on-FHIR v1

SQL-on-FHIR v2 (initial work)

Zulip stream

https://chat.fhir.org/#narrow/stream/179219-analytics-on-FHIR

Track Kick off Call

Please join our weekly calls on Friday at 10am ET

Testing Scenario:

Sample data: https://github.com/smart-on-fhir/sample-bulk-fhir-datasets 

Areas to explore:

Level 1 transformations - improve queryability of raw FHIR JSON through SQL transforms or pre-load transforms:

  • Extract contained resources
  • Update resource ids to be unique (if necessary)
  • Extract ids from references and store alongside url version
  • Standardize date representation (details tbd, possibly make date into ranges and store original precision)
  • Standardize units (details tbd, look at other implementations)

Level 2 transformations - flatten FHIR data into rectangular tables:

  • Convert existing queries into declarative FHIRPath rule syntax
  • Generate SQL queries from FHIRPath rules

Level 3 transformations - calculate analytics and measures based on flat tables:

  • Test approach with existing queries
  • Explore query portability between systems through SQL conversion tools like sqlglot and/or macro solutions like DBT