SQL on FHIR: Improving Medical Data Analytics & Interoperability between Healthcare Systems

The Fast Healthcare Interoperability Resources (FHIR®) standard, developed by Health Level Seven (HL7®) International, has emerged as a pivotal force in modern healthcare. It provides a flexible, web-centric framework for exchanging electronic health information, aiming to break down data silos and foster seamless interoperability across disparate systems. FHIR’s resource-based approach, utilizing common web technologies like RESTful APIs, JSON, and XML, has significantly improved the ability to access and share patient data for clinical care, patient engagement, and operational workflows.

However, while FHIR excels at transactional data exchange—retrieving a patient’s record or a specific observation—its inherent structure presents challenges for complex analytical tasks. FHIR data is often deeply nested and organized in a graph-like manner, which is not natively conducive to the relational queries typically used in data analytics and business intelligence. Standard FHIR REST APIs, while powerful for specific lookups, can be cumbersome and inefficient for large-scale data aggregation, cohort analysis, or sophisticated reporting that requires joining information across multiple resource types or performing complex calculations.

This is where SQL on FHIR enters the picture. It offers a mechanism to bridge the gap between the rich, structured data available in FHIR repositories and the powerful, familiar world of Structured Query Language (SQL). By enabling SQL-based access to FHIR data, often by projecting it into tabular or relational views, SQL on FHIR empowers a broader range of healthcare professionals, data analysts, and researchers to unlock valuable insights from health information. 

The evolution of this approach, particularly with the SQL on FHIR v2 specification, further refines this capability by introducing standardized mechanisms like ViewDefinitions, enhancing portability and simplifying the path to robust healthcare analytics. This article delves into the intricacies of SQL on FHIR, its evolution, core concepts, use cases, implementation considerations, and its promising future in shaping a data-driven healthcare landscape.

Revolutionize your healthcare analytics and AI capabilities. SPsoft offers tremendous expertise in developing and integrating healthcare AI solutions!

Get in Touch

Understanding SQL on FHIR: Core Concepts and Architecture

SQL on FHIR is an approach that allows users to query data stored in FHIR repositories using standard SQL commands. The fundamental idea is to present the inherently hierarchical and nested FHIR data in a format more amenable to SQL, typically as relational tables or views. This transformation enables data analysts, researchers, and business intelligence tools to leverage their existing SQL skills and platforms to perform complex queries, aggregations, and analyses on rich clinical datasets.

What is SQL on FHIR?

At its core, SQL on FHIR provides a way to view FHIR data in a relational format and access it via standard SQL connections. FHIR resources, which are the fundamental units of interoperability (e.g., Patient, Encounter, Observation), are mapped to tables. Each row in such a table typically represents a single instance of that FHIR resource type. For example, Patient resources might be found in a patient table, and Encounter resources in an encounter table. The columns within these tables correspond to the elements defined in the FHIR specification for each resource, with mechanisms to handle FHIR’s complex data types, arrays, and nested structures, often through unnesting or flattening operations.

Why Use SQL Instead of Standard FHIR REST APIs for Analytics?

While FHIR REST APIs are excellent for transactional operations, they have limitations for large-scale analytics. Performing complex aggregations or joining data from multiple resource types can require numerous, inefficient API calls. SQL, designed for set-based operations, handles this more effectively. Furthermore, SQL is a ubiquitous language with a vast ecosystem of Business Intelligence (BI) tools (like Tableau, Power BI) that natively support it, allowing these tools to connect directly to SQL on FHIR data sources. 

For queries scanning large data volumes, an optimized SQL engine often outperforms repeated API calls. SQL also offers a richer syntax for complex filtering and calculations than FHIR search parameters alone. Aidbox, for instance, implements an SQL on FHIR specification precisely to address the cumbersomeness of extracting data from deeply nested FHIR structures for analysis.

The SQL on FHIR v2 Architecture: A Layered Approach

The SQL on FHIR v2 specification, an HL7 FHIR Implementation Guide, introduces a more formalized and standardized architecture to promote portability and ease of use. This architecture is conceptualized in three layers:

Key components of the SQL on FHIR v2 architecture:

• Data Layer. This optional first layer provides access to a lossless representation of the FHIR data (e.g., FHIR NDJSON files, JSON-typed database columns, Parquet files). It can be annotated to improve performance.
• View Layer. Central to SQL on FHIR v2, this layer defines and transforms FHIR data into tabular form. It includes:

1. ViewDefinitions. Portable, datastore-independent artifacts defining how FHIR resource elements are projected into a tabular view. They are crucial for consistency and reusability.
2. ViewRunners. System-specific tools that interpret ViewDefinitions and apply them to the Data Layer, creating tabular views.

• Analytic Layer. This is where analytical tools (SQL clients, BI platforms) query the tabular data produced by the View Layer. SQL on FHIR v2 is agnostic to the tools used here.

This layered architecture, especially standardized ViewDefinitions, allows for predefined, shareable views tailored to specific analytical use cases, reducing redundant data preparation. ViewDefinitions deliberately avoid complex operations like joins or aggregations, deferring these to the Analytic Layer to ensure simplicity

The Evolution and Standardization: SQL on FHIR v1 to v2

The journey towards effective SQL-based analytics on FHIR data has been evolutionary. Early approaches, often termed “SQL on FHIR v1,” showed potential but also highlighted challenges that the SQL on FHIR v2 specification aims to address.

Limitations of Early “SQL on FHIR v1” Approaches

Initial efforts to query FHIR data using SQL often involved direct mapping of FHIR resources to relational tables or required complex, database-specific SQL syntax. Key limitations included a lack of standardization, leading to non-portable queries. Handling nested elements often required SQL dialect-specific functions (e.g., CROSS JOIN UNNEST), making queries non-portable. Analysts needed deep knowledge of both FHIR structures and specific SQL dialect capabilities. Implementations also faced tradeoffs between JSON-based storage simplicity and columnar storage scalability. These challenges underscored the need for a standardized way to project FHIR data into tabular views.

Key Enhancements in SQL on FHIR v2

The SQL on FHIR v2 specification represents a significant advancement by introducing a standardized framework centered around ViewDefinitions. This iteration aims to enhance portability and simplify usage. Standardized ViewDefinitions are portable, datastore-independent artifacts describing how to project FHIR resources into tabular views using FHIRPath expressions for data extraction. This provides a common language for defining views, irrespective of the underlying database. The use of FHIRPath allows for powerful and standardized navigation and extraction of data.

Consequently, a ViewDefinition created for a particular use case can theoretically be used by any SQL on FHIR v2-compliant ViewRunner, greatly enhancing portability. ViewDefinitions abstract away the complexities of how FHIR’s nested data is stored or unnested, allowing analysts to query resulting flat tables using standard SQL. Being an HL7 Implementation Guide, SQL on FHIR v2 benefits from community input and aims to be an open standard. The specification deliberately limits ViewDefinition scope (e.g., no joins between resource types within the definition) to ensure broad implementability, deferring complex operations to SQL engines.

SQL on FHIR in Action: Use Cases and Benefits

The ability to query FHIR data using SQL opens up a wide array of use cases across the healthcare spectrum, from clinical research to operational analytics.

Real-World Applications of SQL on FHIR

• Advanced Analytics and Reporting. SQL on FHIR allows direct connection of BI tools (Tableau, Power BI) to FHIR data sources, enabling interactive dashboards and complex reports. Analysts can track KPIs and explore trends using SQL’s full power for aggregation and joining.
• Population Health Management. SQL on FHIR can query patient data to identify cohorts based on diagnoses, demographics, lab results, and medications, crucial for managing population health. InterSystems FHIR SQL Builder lists this as a key use case.
• Cohort Identification for Clinical Trials and Research. Researchers use SQL on FHIR to define complex inclusion/exclusion criteria to select precise cohorts for clinical trials or retrospective studies. A study on oxygen supplementation used SQL on FHIR views on MIMIC-IV FHIR data.
• Quality Measure Tracking and Regulatory Reporting. SQL on FHIR facilitates calculating quality measures by querying relevant clinical data. NCQA uses SQL on FHIR for digital quality measurement, supporting measures like HEDIS.
• Data Quality for FHIR. SQL on FHIR can run data quality checks and identify inconsistencies. NCQA leverages fully flattened FHIR resources in SQL databases for data quality assessments.

Summarizing the Benefits

The adoption of SQL on FHIR offers numerous advantages:

• Simplified Data Access. Provides a familiar SQL interface, lowering the barrier for data analysts.
• Enhanced Analytical Capabilities. Enables complex queries, aggregations, and joins across FHIR resources.
• Leveraging Existing Tools. Allows use of existing SQL-based BI tools and data science environments.
• Improved Performance. Can offer better performance for bulk data analysis compared to numerous API calls.
• Scalability. Solutions on scalable SQL engines can handle large FHIR datasets.
• Standardization with SQL on FHIR v2. Promotes consistency and reusability of analytical views.

By bridging FHIR’s semantic capabilities with SQL’s analytical power, SQL on FHIR transforms raw healthcare data into actionable knowledge.

Navigating SQL on FHIR Implementation: Key Considerations

Implementing SQL on FHIR involves several important considerations, from its official standing and maturity to security, performance, and integration.

Official Standing, Maturity, and Production-Readiness

SQL on FHIR is an HL7 FHIR Implementation Guide (IG), not part of the core FHIR standard. The SQL on FHIR v2 IG is a formal specification defining portable, tabular projections of FHIR data. Its maturity varies; the FHIR Maturity Model (FMM) assesses artifacts from FMM0 to FMM5. SQL on FHIR v2 with its standardized ViewDefinitions is a step forward. Several implementations exist (NCQA, Aidbox, Pathling), though some components might be in preview or experimental stages (e.g., Aidbox’s engine, HAPI FHIR’s HFQL). Organizations should evaluate specific solutions carefully.

ETL Processes: Necessity and Approaches

The need for an Extract, Transform, Load (ETL) process with SQL on FHIR is nuanced. Some solutions minimize traditional ETL by creating SQL projections directly on FHIR repositories (e.g., InterSystems FHIR SQL Builder). However, transformation (flattening, mapping) is inherent. This can result in virtual views (generated on-the-fly, offering fresh data but potential performance overhead) or materialized views (pre-processed and stored, faster queries but data latency). Google’s FHIR Data Pipes can generate materialized Parquet views using ViewDefinitions or support SQL queries for virtual views. The choice depends on data freshness, query performance, and storage needs. The SQL on FHIR v2 API specification also describes bulk export operations (e.g., to CSV, Parquet) which constitute an ETL-like workflow.

C. Database and Engine Compatibility

SQL on FHIR implementations work with various SQL databases and engines, including Amazon Redshift (1upHealth), PostgreSQL (Aidbox), BigQuery (Google Cloud Healthcare API), and Apache Spark (CSIRO’s Pathling). The SQL on FHIR v2 IG aims for ViewDefinition portability across engines like Snowflake, Oracle, and MySQL. While ViewDefinitions abstract SQL dialect differences, understanding target engine capabilities remains important for optimization.

FHIR Server Integration

The level of SQL on FHIR support varies across FHIR server platforms.

FHIR Server Platform	Native/Extension SQL on FHIR Support (v2 ViewDefinition focus)	Key Features/Mechanisms for SQL Access to FHIR Data
HAPI FHIR	No direct SQL on FHIR v2. Has experimental HFQL.	HFQL module, JDBC driver for HFQL.
Microsoft Azure API for FHIR	No direct native SQL on FHIR v2. Analytics via integration.	Integration with Azure Synapse Analytics. Data export (e.g., to Parquet using $export then Synapse Link) for analytics.
Google Cloud Healthcare API	Integrates with BigQuery. Open Health Stack uses ViewDefinitions.	Direct export/link to BigQuery for SQL analytics. FHIR Data Pipes can use ViewDefinitions for materialized Parquet views.
Aidbox FHIR Server	Yes, implements SQL on FHIR specification with ViewDefinitions.	SQL API, ViewDefinition builder, ability to query materialized flat views using plain SQL.
InterSystems IRIS for Health	Has “FHIR SQL Builder” (proprietary projection tool).	FHIR SQL Builder creates custom SQL projections on the FHIR repository without moving data, enabling SQL queries via familiar tools.
Other (e.g., Firely Server)	May have its own SQL access methods or evolving support.	Firely Server has made improvements to its SQL query performance and offers some SQL access capabilities.

This highlights that some platforms align closely with SQL on FHIR v2 (emphasizing ViewDefinition portability), while others offer analytics through proprietary integrations.

Managing Complex Queries, Nested Data, and Performance

A primary motivation for SQL on FHIR is enabling complex analytical queries. FHIR’s nested structures are a core challenge. SQL on FHIR v2 ViewDefinitions, using FHIRPath, provide a standardized way to flatten these structures, simplifying query writing.

Performance optimization strategies include:

1. Slimming source data by projecting only necessary elements.
2. Using efficient storage formats like Apache Parquet for materialized views.
3. Indexing frequently queried columns.
4. Choosing virtual vs. materialized views appropriately.
5. Optimizing FHIR data ingestion (e.g., large files for bulk import).

While SQL on FHIR can be performant for analytics, it may not suit high-throughput transactional operations where native FHIR APIs excel.

Ensuring Security and Compliance (HIPAA)

Security and compliance are paramount. SQL on FHIR solutions must be implemented with robust security. Several implementations (1upHealth, InterSystems, Aidbox) are designed to be HIPAA-compliant, but overall compliance depends on deployment and management.

Core security and access control mechanisms for SQL on FHIR environments:

• Robust Authentication. Ensuring only authorized users connect (e.g., user accounts with access keys).
• Granular Authorization. Defining data access for users/roles (e.g., specific views, row/column-level security, RBAC).
• Network Security. Restricting access via VPNs and IP allow-listing.
• Data Encryption. Protecting data in transit (SSL/TLS) and at rest (server-side encryption).
• Audit Trails. Logging SQL queries and data access attempts.
• Read-Only Access. Many implementations provide read-only access to prevent source data modification.

Google Cloud’s FHIR access control, for example, allows fine-grained, resource-level policies, illustrating relevant principles.

SQL on FHIR and the Future of Health Data Intelligence

The healthcare landscape’s rapid digitization emphasizes data-driven decisions. SQL on FHIR, especially v2, is poised to be crucial by making FHIR data more accessible for analytics.

Trends in FHIR Adoption and Healthcare Interoperability

FHIR adoption is experiencing significant global momentum. A substantial percentage of U.S. healthcare providers use FHIR, and over 90% of major EHRs offer FHIR APIs. Globally, adoption sees annual growth around 38%. The 2024 State of FHIR Survey indicates 84% expect increased adoption, with FHIR R4 being widely used. Regulatory drivers like the CMS Interoperability Rule compel adoption. Analysts like Gartner highlight foundational interoperability technologies. FHIR is expanding beyond EHR integration to patient apps, public health, and research, with 73% of digital health companies using FHIR for EHR integration. This growth creates fertile ground for SQL on FHIR.

The Role of SQL on FHIR and SQL on FHIR v2 in Shaping Future Analytics

As FHIR data volume grows, SQL on FHIR will be indispensable.

Key trends shaping the future of SQL on FHIR:

• Increased adoption of FHIR as the primary standard for health data exchange.
• Growing demand for advanced analytics and AI/ML in healthcare.
• Maturation of SQL on FHIR v2 implementations and ViewRunner ecosystems.
• Integration of SQL on FHIR capabilities directly into mainstream FHIR server platforms and cloud analytics services.

SQL on FHIR democratizes access, empowering a broader audience proficient in SQL. SQL on FHIR v2, with standardized ViewDefinitions, further abstracts complexities. This is crucial for advanced analytics and AI/ML, serving as a data preparation layer. The reusability of ViewDefinitions fosters collaboration and consistency. The success of SQL on FHIR v2 depends on robust ViewRunner development and integration into major platforms.

Alignment with Global Digital Health Standards

SQL on FHIR principles align with global digital health initiatives. The World Health Organization (WHO) champions digital health technologies and open standards, collaborating with HL7 International to support FHIR adoption. WHO’s SMART Guidelines emphasize standards-based, interoperable digital health components. While not explicitly named in all WHO strategies, SQL on FHIR’s function—making standardized FHIR data more usable for analytics and evidence-based decision-making—directly supports WHO’s objectives of strengthening health systems.

Conclusion

SQL on FHIR represents a critical technological advancement, bridging FHIR’s semantic richness with SQL’s analytical power. It addresses the challenge of efficiently querying complex FHIR data for purposes beyond direct patient care. The evolution to SQL on FHIR v2, with its standardized ViewDefinitions, marks significant maturation, promising enhanced portability, reusability, and simplified development of analytical applications. This journey from ad-hoc “v1” methods to the formalized v2 specification reflects a move towards greater interoperability.

Ultimately, SQL on FHIR’s value lies in making complex healthcare data more accessible and actionable. This enhanced data utilization is key to improving patient outcomes, optimizing operations, advancing research, and fostering a more data-driven healthcare future. Continued development of robust ViewRunner implementations and integration of SQL on FHIR v2 capabilities will be crucial.

Ready to transform your medical analytics with FHIR and AI? Partner with SPsoft to leverage our deep experience in AI-driven healthcare solutions and FHIR integration!

FAQ