Problem statement

[johngrimes]

I had a go at writing a succinct, high-level statement of the problem, let me know what you think:

Problem

As the availability of FHIR data increases, there is a growing interest in utilising it for analytic purposes. However, to use FHIR effectively analysts require a thorough understanding of the specification, including its conventions, semantics, and data types.

FHIR is represented as a graph of resources, each of which includes nested data elements. There are semantics defined for references between resources, data types, terminology, extensions, and many other aspects of the specification.

Most analytic and machine learning use cases require the preparation of FHIR data using some sort of projection or transformation from its original form. The task of authoring these transformations is not trivial, and there is currently no standard mechanism for reuse.

Solution

A standard format can be provided for defining use case-specific views of FHIR data. Tools can be developed that transform these definitions into queries that are capable of being executed by a wide variety of different query engines.

These views can be made available to users as an easier way to consume FHIR data - simpler to understand and easier to process with generic analytic query tools.

FHIR implementation guides could include definitions of simple, flattened views that comprise essential data elements. The availability of these view definitions will greatly reduce the need for analysts to perform repetitive and redundant transformation tasks for common use cases.

[csenn]

@johngrimes thanks a lot for this summary. Would you be open to elaborating a bit more on the "transform definitions into queries that are capable of being executed by a wide variety of different query engines"?

Like where does the initial FHIR data live? Does it get transformed into SQL tables, or are you suggesting there could be other stores that would be able to use the same "transform definitions"? Or do you mean these should work on a variety of different SQL engines?

Maybe it would make sense to elaborate a few different stack options explicitly? For instance.

1. Customer has 10 million patients in a FHIR server
2. A spark cluster in parallel pulls each Condition record into memory and transforms it to a flat array of data using a Java program with approx API doTransform(resource: FHIR.Condition, definition: SqlOnFhirDef )
3. that data is inserted into an SQL table which can then be queried for analytics purposes.
4. If more data is added to the FHIR Server, this process needs to either be re-run on the whole set of data, or some type of lastUpdated key needs to be tracked somewhere

Is this the type of process you are thinking? What are the variable possible types of input resources provided to the "doTransform" function? I imagine a TS object or a HAPI/Firely Condition class depending on language, is there anything else?

[johngrimes]

Like where does the initial FHIR data live? Does it get transformed into SQL tables, or are you suggesting there could be other stores that would be able to use the same "transform definitions"? Or do you mean these should work on a variety of different SQL engines?

@csenn The idea is that the view definition layer is agnostic of the storage that it operates over. Different implementations can be developed that are capable of operating on different representations of the FHIR data.

To paraphrase your examples, here are some different scenarios that I can imagine:

• FHIR data is in a normalised database (similar to HAPI), view executor generates SQL (or maybe ORM queries)
• FHIR data is in a database but stored as a JSON document, view executor generates JSONPath or similar to extract data from FHIR resources (this is how Nikolai's demo works)
• FHIR data is in a set of NDJSON files in an S3 bucket, view executor builds a Spark query to transform it into a dataframe
• FHIR data is in a columnar data store such as Parquet or BigQuery, view executor builds a SQL query to transform it into a view
• FHIR data is consumed from FHIR Subscriptions or a Kafka topic into a Spark stream, view executor builds a Spark query to transform it into another stream

The logic around incremental update is outside of the scope of the specification and depends upon the implementation. Spark can handle streaming data and could deal with the simple form of this by either assuming additive-only updates, or merge on ID. I can imagine that the method you describe might be useful in other scenarios or implementations.

Regarding the doTransform API - this is basically what Pathling does, except maybe that the first argument is not a single resource, it is a collection of resources. FHIR JSON or XML resources are "encoded" into a Spark dataframe, a columnar structure similar to the columnar schema guidance. Then the view executor builds a query that transforms it from this form to the flattened view requested.

[dkapitan]

@johngrimes thanks for clarifying. The way I see it, the 2nd, 3rd and 4th bullet are very similar in terms of the logic that the view definition layer needs to be able perform. Would it be helpful for this project to focus on one implementation first, because it is easy to work with intermediate storage transforms.

For example, take Nikolai's implementation that generates JSONPath. It should be relatively easy to execute that JSONPath for NDJSON and Parquet by transforming the storage format.

In my thinking, (ND)JSON and Parquet formats are completely interchangeable and you can roundtrip the data to and from either format with no loss of information.

On the other side, the code of the view executor can be generated JSONPath, SQL (I would suggest using duckdb as the default engine) or Spark.

I have been following the developments in the Arrow ecosystem, particularly the work done by Voltron Data c.s. We could consider stepping into that ecosystem and standardize on the dataformat first, namely arrow in memory and parquet on disk. This also allows you to pass an arrow table in memory across execution engines without the cost of deserialization - serialization. There is quite a buzz going on in the pydata community, where this solution pattern is now implemented in polars and duckdb with significant performance gains in data pipelines (see this blog for example).

Not sure whether this makes sense and is at all relevant, but I thought I'd share it just in case.

[csenn]

@johngrimes I think it's great that your experience with pathling is helping to inform the needs of this specification. I'm always a little hesitant when it comes to creating a new DSL (even if it's a json spec mixed with FHIRPath), in my experience they just end up having as much potential to complicate things as to simplify them. The problem of transforming nested data into flat tables is far from a new one, and many solutions have been tried and exist. I think the essence of the sql-on-fhir-2 project is to prove that the new spec, along with one/several of the implementations you stated above, will have a significantly better developer experience that fosters more reusability than someone using a more standard transform pipeline.

[dkapitan]

I just wanted to point to this marvelous piece of engineering: https://voltrondata.com/codex

The solution patterns and approach taken in the so-called Composable Data Stack looks very similar to ours. Perhaps we can re-use parts of the documentation/concepts.

[csenn]

This looks like a really interesting project @dkapitan, how do you envision this could be used for FHIR analytics workloads?

[dkapitan]

@csenn I am still trying to wrap my head around it, so what follows is still very handwaving.

These diagrams are the starting point of the Composable Data Stack (CDS):

Starting from the top, 1. User Interface, the aim of SQL-on-FHIR is to provide FHIR data in an accessible, data format. So in an ideal world, it should not matter how that tabular data is stored and which execution engine is used under the hood. The Ibis project is an example that aims to implement this: as a data consumer, I can use one standard dataframe API, and abstract away the backend. I no longer need to worry whether it is stored in BigQuery, Apache parquet/spark, Snowflake etc. My expectation is that the CDS community will continue to abstract away the backend for end-users, ultimately replacing ODBC/JDBC with the Arrow equivalent.

Now, in our case, the purpose of SQL-on-FHIR is to define a specification to generate tabular views in the first place. At this point it is still unclear to me how this relates to CDS:

• In the scenario that the tabular views are persisted, then CDS can help by standardizing the UI (e.g. with Ibis). It no longer matters which runner and which storage engine has been used to generate the tabular view
• In the scenario that the tabular views are ephemeral, i.e. not persisted, then the actual implentation of the runner becomes part of 2. Execution Engine. This sounds doable, I how exactly I don't know.

So handwavingly, my thinking would be: how could I combine Ibis and existing libraries like Pathling, if I wanted to go for the tabular views on the fly. That way, users could do all of there work in a dataframe API. Or put differently: given that the original FHIR data is in parquet, could I implement a Runner in Ibis?

Another thing that comes to mind: how does our View specification relate to Substrait? Could we perhaps use Substrait?

My dreamscenario would be that I could work in Ibis on any FHIR datastore in parquet ... 😄

[csenn]

@dkapitan this is a great summary, and the projects and technologies you mentioned definitely look interesting. I think your comment about "defining a specification to generate tabular views in the first place" is really the crux of the issue, as once the data is tabular you could add it to the variety of backends that are abstracted over with the projects you mentioned. You may have seen this before, but a project I stumbled on that seems to be working on a similar problem is: https://developers.google.com/open-health-stack/fhir-analytics . This may even use SQL-on-FHIR (or something similar) actually.

[dkapitan]

@csenn indeed, open health stack is one of the core libraries that we use for our use cases at PharmAccess Foundation.