The Care Stack

Getting FHIR data into Snowflake in a form people can use

Victor Wilson · · 6 min read

If your health system runs Epic or Oracle Health, you have a FHIR R4 API. The 21st Century Cures Act made sure of that. What the law didn’t cover is how to analyze the data that comes out of it.

Having the API is not the same as having data you can query. I’ve seen teams build ingestion pipelines that produce data nobody can use, because they treated FHIR like any other JSON feed. It isn’t. FHIR is deeply nested, lets one field hold several data types, and is full of extensions. It was designed for exchanging data between systems, and analytics was not the goal.

Here is an approach that works, and the traps to avoid.

Why FHIR is hard to query

FHIR is good at moving clinical data between systems that don’t share a data model. That same flexibility makes analytics hard.

Take a patient’s name. In FHIR it’s a list of name entries, each marked with a use such as official, nickname or maiden. To get the legal name, you filter the list for the official entry, then join the given names to the family name. That’s several steps for what an analyst expects to be one column.

The same pattern shows up everywhere:

  • Choice types. An Observation’s value might be a quantity, a string, a true/false or a coded concept, and only one is filled in per record.
  • Extensions. Vendor-specific fields live in a nested list, keyed by URL, each with its own value type.
  • Coded concepts. Diagnoses, medications and results arrive as lists of codes from several vocabularies (LOINC, SNOMED, RxNorm, ICD-10, local codes), with no promise about which will be present.
  • References. A link to a patient might be relative, a full URL or a UUID, depending on the source system.

None of this is a flaw in FHIR. It just means raw FHIR JSON is not something analysts can query.

Load it raw, transform it in layers

For analytics, start with the FHIR Bulk Data Access specification, not the regular read API. Its $export operation gives you every resource of a type as NDJSON files, one resource per line, instead of paging through records one at a time.

The flow:

  1. Start a group export: GET [base]/Group/[id]/$export with the header Accept: application/fhir+ndjson.
  2. Poll the status URL until the job finishes. Large exports can take hours.
  3. Download the output files, one per resource type, to S3 or Azure Blob storage.
  4. Load each file with COPY INTO a Snowflake staging table that has a single VARIANT column.

A VARIANT column accepts raw JSON with no schema and no upfront mapping. You load everything first and decide what to extract later. Your sense of what you need will change, and you want to pull out a new field without going back to the source.

For daily loads, $export accepts a _since parameter that limits the export to resources changed after a given time. Run a daily job, record the last successful time, and you have a simple incremental load with no change data capture or triggers on the source system.

From there, dbt does the transformation. Don’t try to flatten everything in one large model. Build three layers instead.

Staging models pull out the simple fields that are always in the same place. For Patient, that’s id, gender and birth date, using Snowflake’s path syntax such as src:gender::string. Keep the raw VARIANT column here too for later models.

Intermediate models handle the hard parts: lists, choice types and extensions. Snowflake’s LATERAL FLATTEN does most of the work. For the patient name, you flatten the name list, filter for the official entry, then flatten again to get the given names. For coded concepts, you flatten the coding list and pick your preferred vocabulary. For Observations, that might be LOINC first, then SNOMED, then the local code. Put that order in one intermediate model, and every table downstream gets consistent codes.

Mart models join everything into the wide tables analysts want: a dim_patient with one row per patient, an fct_encounter with one row per encounter, an fct_observation_lab with lab values in typed columns. By the mart layer there’s no JSON left, only ordinary columns with real data types.

What to pull from the main resources

The spec tells you what’s possible. Your EHR vendor decides what you actually get.

Patient. The MRN lives in the identifier list. Find it by its type code (MR) or by your health system’s known identifier system, not by position, since it isn’t always first. Race and ethnicity usually sit in the US Core extensions, which means flattening the extension list and then flattening again to reach the codes.

Observation. Write one expression that checks each possible value type in turn, using COALESCE across quantity, string and the rest. Pull the unit separately, because the display unit and the UCUM code aren’t always the same. Labs and vitals usually carry an effective date and time, but observations taken from documents may use a period instead, so check both.

Encounter. The class field tells you inpatient, outpatient or emergency, with codes like IMP, AMB and EMER. Admit source and discharge disposition sit under hospitalization as coded concepts, and the codes vary by vendor. Build a mapping table to your own categories early, or you’ll end up with dozens of versions of “routine discharge.”

A few smaller traps cost teams real time:

  • Partial dates. FHIR dates are text, and “2026”, “2026-02” and “2026-02-23” are all valid. Snowflake’s TRY_TO_DATE quietly returns null for the short forms. Pad them first.
  • Reference formats. Write one function that strips any reference down to the resource ID, and use it everywhere.
  • Empty codes. Some vendors fill in the text of a coded concept but leave the codes empty. If your logic needs a LOINC code, you’ll get silent nulls. Add completeness tests in dbt from the first day.
  • Observation status. A result can be registered, preliminary, final, amended, corrected, cancelled or entered in error. Most analytics only want final and amended. If you don’t filter, preliminary and retracted results end up in your cohorts.

Why this holds up

Never change the raw layer. Every transformation lives in dbt, under version control, with tests, and can be rebuilt from scratch. If you need a field you skipped the first time, it’s already in the raw JSON. That’s your insurance.

This design also handles change well. When your vendor moves to a new US Core version and starts sending new fields, the raw layer takes them in without complaint. Your models keep running and ignore the new fields until you choose to use them. A load mapped to fixed columns can break on the first unexpected field.

It also lets you ship in pieces. Start with Patient, Encounter and Observation, and get those marts into your analysts’ hands. Add Condition, MedicationRequest and DiagnosticReport next. The raw data is already loaded, so you’re only writing new models.

AI agents are starting to help here too. Given a FHIR profile from an implementation guide, an agent can draft the staging and intermediate models, including the flattening and code handling. For resources that follow common patterns, that’s faster and less error-prone than a developer new to FHIR writing it by hand. Agents can also watch for quiet failures, like an extension that changed shape after a vendor upgrade or a field whose completeness suddenly dropped. Those used to go unnoticed for weeks.

To start, run one bulk export of Patient, Encounter and Observation into VARIANT tables this month, and build only the patient and encounter marts. You’ll hit most of the traps above at small scale, and analysts will have something real to use while you plan the rest.

Discussion

    Join the discussion