In Part 1 of this series, I argued that reverse ETL is defined by intent, not by a tool. Part 2 turned that into four transport mechanisms and a decision table, using a generic example because the pattern is industry-agnostic. Now, in Part 3, the series takes a turn for the specific, starting with the payload I spend most of my time in: FHIR.
Here is the clinical version of the Part 1 problem. Your data team computes a readmission risk score, LACE, for every admitted patient. It is a genuine data product in the gold layer, one definition derived from length of stay, ED admissions, comorbidity, and prior visits.
It is also invisible to the one person who could act on it. The clinician works in the EMR, not in your BI tool, so the score that could flag a risky discharge never reaches the chart where the discharge decision gets made. Reverse ETL for FHIR is how that score gets into the chart.
Why FHIR is the Perfect Test of the Coupling Spectrum
FHIR hands you two delivery modes at opposite ends of the coupling spectrum, both blessed by the spec. At the tight end, you POST a transaction Bundle to a FHIR server’s REST API and the server returns a response Bundle telling you, per entry, exactly what happened.
That is Part 2’s API push speaking FHIR, atomic, so every entry commits or none do. At the loose end, you generate NDJSON, one resource type per file, and land it on an external stage for the consumer to pull on their own schedule. That is Part 2’s COPY to an external stage speaking FHIR, no live connection, no per-resource acknowledgment.
Same resources, two packagings. The LACE Observation is identical whether it leaves in a transaction Bundle over REST or as a line in an NDJSON file on S3. What differs is the coupling, and everything the spectrum taught you carries over: the acknowledgment you get, the availability you depend on, the volume you can move.
One precision note. “Bulk FHIR” usually means the $export operation, where a server dumps its own data as NDJSON. That is ingestion. We are doing the reverse, generating FHIR NDJSON from curated warehouse data and delivering it outward. Same format, opposite flow.
The Hard Part isn’t the Transport, it’s the Resource
Part 1 argued that choosing the transport is the easy half, and FHIR makes that concrete. Getting a LACE score onto a stage is one COPY INTO. Getting that same score into a shape a FHIR server accepts as a valid Observation is the real work, and no managed connector will do it for you, because it is specific to your data, your codes, and your target profile. The flow has three steps: an ML or analytic workflow computes the score in the gold layer, that record is translated into a conformant FHIR resource, and the resource is delivered by REST call or NDJSON file, which is the Part 2 coupling decision.
Step 2 is the one worth an accelerator, which is why I built the FHIR Escape accelerator as part of Hakkoda’s FHIR Data Transformation solution. It turns a structured data product in Snowflake into a conformant FHIR resource using templates tuned to the client’s target profile, so a LACE Observation for one client and an ExplanationOfBenefit for another are the same machinery pointed at different templates. Under the hood it is more approachable than people expect. A UDF computes the metric once as a single reusable definition, Part 1’s one-source-of-truth principle in code, and a table function with a lateral join shapes each patient row into the nested structure a resource requires.
Because the resource is the hard part, three things make one valid rather than merely well-formed JSON.
- Terminology mapping: your warehouse speaks your codes, but FHIR profiles demand specific ValueSets, so normalizing local codes to ICD-10, LOINC, or SNOMED is tedious, unavoidable, and the most common reason a resource gets rejected.
- Reference resolution: resources do not stand alone, so generating them in dependency order with references that resolve is real work.
- Validation: run the target profile through a validator like Inferno before anything leaves, catching conformance gaps in your pipeline instead of in the EMR’s rejection log.
Get all that right and you have valid FHIR resources in Snowflake. Now, and only now, does the coupling decision come back: how do they leave?
The Tight Path: Writing to the FHIR API
For a score like LACE the ideal case is vivid. A patient hits discharge, you POST their LACE Observation to the EMR’s FHIR endpoint, and it lands in the chart in near-real-time, right where the decision is being made.
Reach for a transaction Bundle when resources belong together, because it is atomic, so you never leave the chart with an Observation referencing a Patient that failed to write. The server hands back a response Bundle with one entry per resource, each carrying a status and, on failure, a structured OperationOutcome, the richest acknowledgment in this series. A batch Bundle is the looser option, allowing partial success when resources are unrelated and you would rather land nine of ten than none.
Mechanically, this is Part 2’s external access integration pointed at a FHIR endpoint: a network rule allowlisting the server, an OAuth secret, and a procedure that authenticates and POSTs. The hard part is being allowed to write at all. That means SMART Backend Services: register a client, publish a public key, mint a signed JWT assertion, and exchange it for a short-lived token scoped to exactly what you can touch, something like system/Observation.write. A lot more than “POST some JSON.”
Granting write scopes to a production EMR is often an intensive, manual process across multiple teams and sometimes the vendor, so it frequently does not happen at all. Read access is routine. Write access is heavily gated and usually routed through vendor review. Add per-endpoint rate limits, the constraint Part 2 flagged as making tight coupling a poor fit for volume, and the tight path resolves to a narrow, high-value use: single-patient, near-real-time writes into a system that has actually granted the scope. When you have that, it is the best delivery in healthcare. When you do not, the loose path is not a consolation prize.
The Loose path: Generating NDJSON to a Stage
When the write scopes never materialize, and often they will not, you point the same generated resources at the other end of the spectrum: write NDJSON, land it on an external stage, and let the consumer ingest on their own schedule. For FHIR this is frequently the default, not the fallback. Each file holds one resource type, one resource per line, no wrapping Bundle, the convention the FHIR Bulk Data spec uses, so a downstream system already knows how to consume it even though you generated it rather than exported it.
Two FHIR-specific wrinkles make this more than a plain unload. First, references: with no atomic Bundle holding them together, you emit resource types in dependency order with stable, resolvable identifiers, so an Observation’s reference to a Patient still lands when they arrive as separate files. That is the loose-coupling tax, engineering referential integrity yourself. Second, no acknowledgment: a file landing in a bucket is not proof of ingestion, so proof of receipt lives out of band in a manifest or control report, exactly as Part 2 warned.
Here is why this is a genuine architecture, not a workaround. Everything up to the unload happens inside Snowflake, the score, the generation, the validation, the assembly. The data does not leave your governed environment until the COPY INTO fires, and when it does, it leaves as exactly the minimum the use case needs. That is Part 1’s minimization-at-the-egress-point made real, and in a regulated setting it is often the deciding factor. A nightly file of curated Observations on a stage the client controls is a far easier compliance conversation than standing write access into a production EMR.
Choosing, and What Comes Next
The choice is Part 2’s decision table specialized for FHIR. Take the tight path, a transaction Bundle to the API, when you have write scopes and need a single patient’s resource in the chart in near-real-time with a per-resource acknowledgment. Take the loose path, generated NDJSON on a stage, when you are moving population-scale data, when write access is not on the table, or when the consumer would rather receive files. The generation work in the middle is identical either way. Only the coupling changes.
One thing to design in regardless of path. The LACE Observation is a computed value, not something a clinician measured, and when what you write into the chart is generated, the receiving system and the humans reading it deserve to know. FHIR has a resource for exactly this: attach a Provenance recording how the resource was derived and by what, so a generated Observation carries its own lineage into the EMR. As reverse ETL becomes the delivery layer for AI-produced data products, that provenance is the difference between a clinician trusting the number and ignoring it.
That is reverse ETL for FHIR: the same curated data product, generated once and delivered by whichever coupling your constraints allow. Next in the series is HL7v2, where the warehouse gets no friendly REST API or blessed file format and instead generates a pipe-delimited message for an interface engine. The resource stops being JSON and becomes a message with segments and a control ID, but the pattern, and the spectrum, hold.
If you have a data product trapped in a dashboard that clinicians or partners need in FHIR, that is the conversation we have every week. Reach out. We would be glad to help you think it through.
