Healthcare Data Lakehouse: Build a FHIR to Databricks ETL Pipeline

A healthcare data lakehouse keeps raw clinical files and governed analytics tables in one store, so the same FHIR and EHR data can feed reporting and AI. To build a FHIR to Databricks ETL pipeline, land every export untouched in bronze, apply identity and version rules in silver, reconcile, and only then release gold tables. Our healthcare data engineering team scopes this work one pipeline at a time.

Updated October 2026.

Ingestion is the easy part. Consider a synthetic example: a dashboard refreshes without errors, yet its encounter count drops sharply. The source export completed and the scheduler is green. The table holds only the latest increment, because it was recreated after earlier history disappeared. The job processed what it saw and the dataset still failed the business requirement. This guide covers the route in, the table design, and the replay, reconciliation and recovery rules that catch that failure. The scenario is illustrative and is not a report of a customer incident.

If you already run a lakehouse and want its recovery behavior checked, our Databricks healthcare implementation service starts with a scoped review of one source-to-consumer pipeline.

If your need is mainly SQL reporting, compare this with our healthcare data warehouse services.

What is a healthcare data lakehouse?

A lakehouse is, in the definition Databricks uses, "a data management system that combines the benefits of data lakes and data warehouses." In healthcare that means NDJSON from FHIR exports, HL7 v2 messages, claims files and notes can sit beside curated tables under one set of access rules.

Our warehouse vs lake vs lakehouse vs mesh comparison goes deeper on that choice. The rest of this guide assumes you picked a lakehouse on Databricks.

How do you build an ETL pipeline from FHIR to Databricks?

Agree what the source can deliver, keep every raw delivery, then apply validation and version policy before anything reaches a consumer. These eight steps are the build order, and steps 5 to 7 are where pipelines most need testing.

This is a proposed reference architecture, not a deployed customer topology. Bronze keeps the original payload and the ingestion context. Silver establishes valid structure, source identity and the update policy. Gold states its grain: resource version, current resource, encounter or an agreed aggregate. For layer detail, see our healthcare medallion architecture guide.

A minimal bronze load with Auto Loader reads each NDJSON line as text, so nothing is reshaped before you have stored it. Silver then parses it. Both samples are sketches with example catalog names, written for this guide and not run against your workspace, so test them on synthetic data first. Keep manifests in a separate folder from the NDJSON.

from pyspark.sql.functions import col, current_timestamp, lit

(spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "text")   # keep each NDJSON line exactly as delivered
    .load("/Volumes/phi/fhir_raw/exports/ehr_prod/")
    .select(col("value").alias("raw"),
            col("_metadata.file_path").alias("source_file"),
            current_timestamp().alias("ingested_at"),
            lit("ehr_prod").alias("source_system"))
    .writeStream
    .option("checkpointLocation", "/Volumes/phi/fhir_raw/checkpoints/bronze_ehr_prod/")
    .trigger(availableNow=True)
    .toTable("phi.bronze.fhir_raw"))

-- One row per resource version, however many times it was delivered
CREATE OR REPLACE TABLE phi.silver.fhir_versions AS
SELECT source_system,
       raw:resourceType::string        AS resource_type,
       raw:id::string                  AS resource_id,
       raw:meta.versionId::string      AS version_id,
       raw:meta.lastUpdated::timestamp AS last_updated,
       raw, source_file, ingested_at
FROM phi.bronze.fhir_raw
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY source_system, resource_type, resource_id, version_id
  ORDER BY ingested_at) = 1;

-- Current state, built separately so history survives
CREATE OR REPLACE VIEW phi.silver.fhir_current AS
SELECT * FROM phi.silver.fhir_versions
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY source_system, resource_type, resource_id
  ORDER BY last_updated DESC) = 1;

Databricks EHR integration: which route fits?

Pick the route from what your EHR can deliver and how fresh the consumer needs the data. Most Databricks EHR integration work combines a batch route for history with a faster route for changes.

Can FHIR reach Databricks in real time?

Yes, close to it, but freshness depends on what the source supports, and Bulk export is a batch tool. For real-time or streaming FHIR to Databricks integration, the tools below move change events, and you still need a batch path to prove history.

• FHIR Subscriptions, where the EHR supports them. A notification tells you something changed, and your service fetches the resource and lands it in bronze.
• HL7 v2 messages through an interface engine and a broker such as Kafka, with a streaming job reading the topic into bronze. Our Kafka and FHIR streaming guide covers ordering, replay and dead-letter handling.
• Zerobus Ingest, a push API that writes straight into Unity Catalog Delta tables with no message bus to run. Check its current release status for your cloud before you plan around it.

Use the same bronze and silver contract on every path, so the replay and reconciliation rules below apply to all of them.

What is the best architecture to land HL7 FHIR data into Databricks with the Rhapsody integration engine and Delta Live Tables?

Let Rhapsody handle the connections and message routing, write batched output to storage, and let Auto Loader and a Lakeflow pipeline do the rest on Delta Lake. Delta Live Tables is now called Lakeflow pipelines, and existing DLT code keeps running.

Rhapsody's 2026 brochure names SFTP, FTP and TCP plus a development kit for other connections. We did not find a documented native Databricks or Kafka connector in its public material, so ask the vendor before designing around one. In the pipeline, use expectations with care: warn keeps invalid records, drop removes them and fail stops the update. For clinical data, keep the rejected record in a quarantine table, because a dropped row leaves only a count behind.

Agree what the source can actually deliver

Write down the export contract before creating tables: resource types, authorization scope, export mode, update behavior, deletion handling and how failed exports are reported. Without it, nobody can later say which source delivery produced a dataset.

FHIR Bulk Data export (IG v3.0.0) is asynchronous. A kick-off returns 202 with a polling URL, and completion returns 200 with a manifest. Three details in that manifest catch teams out:

• A 200 can be a partial success, with problems listed in the error array as OperationOutcome files. Load them as evidence.
• With _since, deletions can appear in a separate optional deleted array, and those resources do not appear in output. A pipeline that reads only output keeps deleted records alive.
• Output files can expire, so download and verify them before you mark the run complete.

Keep a run record with the source, requested interval, export identifier, output files, download results and acceptance status. Store credentials separately. Confirm the implementation guide version and capabilities your source server actually supports.

Preserve corrections without guessing version order

Never sort FHIR version IDs to decide which record is newest. The FHIR R4 specification says "there is no fixed order for version ids", so a numeric-looking value proves nothing.

A file checkpoint and a business key solve different problems. Two files can contain the same resource version, the same resource can arrive with a legitimate correction, and a record from one source can share an identifier string with a different record from another. That last case is why the silver sample partitions by source system. A synthetic sequence shows the risk:

The third arrival should not overwrite the correction because it arrived later. The specification says meta.lastUpdated can be used to judge currency, which is why the current-state view orders on it and not on arrival time. Check that your source maintains it properly, for example after a server migration, and send ties or missing values to an explicit conflict policy. Keep historical versions if the analysis needs them, and build current state separately so audit history and "latest record" use do not share one destructive update rule.

Patient identity is a separate decision. If several sources feed the lakehouse, decide how a master patient index or matching rule links their patients, and record which rule produced each link. Treat a match as a candidate until your data owner has confirmed the rule.

Treat replay as a normal operating condition

Retries happen after timeouts, deployments and partial failures, so processing the same delivery twice must not create an extra accepted version or double an aggregate. A legitimate correction must stay distinguishable from a duplicate.

A streaming checkpoint alone does not give you this. Auto Loader tracks discovered files for exactly-once processing when writing to Delta Lake, which says nothing about the same resource version arriving in two different files. For custom writers, Databricks states that foreachBatch provides only at-least-once write guarantees. On Delta tables, its guidance is to set txnAppId and txnVersion, with the version bound to batchId. For external APIs and databases, you implement idempotency yourself.

Test an interruption after the write and before the job records completion. A clean-looking retry at that point is where duplicates appear.

Check destination coverage before advancing recovery state

Before each run, confirm the destination exists and holds the required history, because a persisted cursor can say the source is up to date when the table it describes has lost its data. A missing destination and a missing checkpoint are different failures, and they need different responses.

if required destination is missing or coverage is incomplete:
    reconstruct the required history from retained raw data or a new export
    reconcile the rebuilt result
    establish the new recovery baseline
else:
    process the next supported increment
    reconcile that increment
    advance processing state after the required writes succeed

This is design pseudocode, not runnable Databricks code. The rebuild window follows the dataset requirement and the available source history, so "reload the last seven days" fails a dashboard that needs a year. If the history cannot be recovered, report that limit and do not call the dataset complete.

Delta time travel will not cover this for you. Databricks documents a default of 7 days for deleted file retention and 30 days for log retention, and once VACUUM removes a version's data files you cannot return to that version. Your long-term recovery source is retained raw data, and vendor limits make that urgent: a file deleted by the EHR after its retention period can only come back through a new export, which holds current state and may not hold the older versions.

For pipelines with several derived tables, record which outputs completed. One table's successful commit does not prove the workflow finished.

Reconcile data before releasing it to consumers

For every resource type, input versions must equal accepted plus duplicate plus rejected plus pending, and the run is held until that equation holds and the pending count is explained. Counts alone are incomplete, so define the check at the right grain.

Those numbers are synthetic. A flattened table can hold several rows per resource, so do not compare its row count with the source count without a transformation rule. Other useful checks are missing referenced resources, unexpected nulls, source-to-curated key coverage, unexplained changes by resource type, and freshness by source. Store rejection reasons and source references so a correction can be replayed deliberately.

Can you run PHI on Databricks under HIPAA?

Yes, with the setup Databricks requires, and the configuration stays your responsibility. Its HIPAA page (AWS version, updated 18 September 2026) requires an active BAA before any PHI is processed and the compliance security profile on each workspace that handles it. No platform makes a pipeline compliant by itself, so take this list to your compliance lead.

Requirements can differ on Azure and Google Cloud, so read the page for your cloud. Before data leaves the clinical boundary for AI or research, de-identify it under your own reviewed method and test that the masking holds.

What to compare when choosing a healthcare data lakehouse

For interoperability and clinical AI in the US, compare how each platform handles raw clinical formats, PHI controls, and the cost of running your pipelines. There is no single best data lake platform for healthcare, because the answer depends on your cloud, team skills and the sources you already run.

Databricks suits teams with Spark engineers and mixed data. Microsoft Fabric's healthcare data solutions are an option for Azure-based teams, with one recent change to plan for: Microsoft says new customers use a downloadable, customer-managed package from 1 October 2026, and support for the managed version ends on 31 December 2027. A warehouse such as Snowflake can be enough when your work is mostly SQL over structured data.

Prove the failure paths before adding AI

Run this acceptance suite on synthetic data before connecting a reporting or AI consumer. Each test has evidence a reviewer can check.

The FHIR-to-AI pipeline guide covers the next stage of that architecture.

Scope a pipeline review around one decision

Bring one source feed, one destination dataset and the report or workflow that depends on it. The review sets out the source contract, correction policy, reconciliation checks and recovery gaps, and gives your team a bounded scope to evaluate.

We are an engineering team and do not sell a connector or a platform, so if you want a packaged product, a vendor from the route table is the better fit. We are ISO 27001:2022 certified and sign BAAs, and our healthcare integration work includes 350+ Mirth Connect interfaces and 20 public healthcare repositories.

Scope a Databricks pipeline review or talk to our team about the feed you need to make recoverable.