ETL vs ELT in healthcare: should clinical data be transformed before or after it lands?
Every healthcare data pipeline decides where transforms run: before data loads into the warehouse (ETL) or inside it after loading (ELT). In healthcare that choice decides where untransformed PHI lands, who can read it, how HL7 v2, X12 and FHIR are parsed, and whether you can rebuild tables when a terminology map changes.
ETL vs ELT in healthcare: which fits each data source?
Quick answer
ELT usually fits healthcare pipelines when the raw zone sits inside your BAA and access controls, because untouched source data lets you replay transforms when terminology maps change. ETL fits when identified PHI must never land in the analytics platform, or when HL7 v2 and X12 need parsing before load. Agnotic Technologies builds both patterns.
| Criterion | ETL | ELT |
|---|---|---|
| HL7 v2 feeds | Parse segments in the interface engine or pipeline, load tables | Land each message raw, parse into tables in the platform |
| X12 835 and 837 files | Parse loops and segments before load | Land files raw, parse with platform jobs; keep the file for audit |
| FHIR Bulk NDJSON exports | Flatten resources before load | Load NDJSON as is, flatten inside the platform |
| EHR reporting database extracts | Transform extracts in the pipeline | Replicate tables raw, model them in the platform |
| Free-text notes | Run NLP or de-identification first, load results | Land notes in the restricted raw zone, run NLP inside |
| What lands as raw PHI | Nothing in the platform; raw data stays in the pipeline | Full source records, in a restricted raw zone |
| Terminology map changes | Re-extract from source, if the source still has the data | Replay transforms over the stored raw data |
| Lineage | Tracked in the pipeline tool | Tracked in platform jobs, from raw to curated tables |
What they are
What is ETL
Data is extracted from source systems, transformed in a separate pipeline service, and only the transformed result is loaded into the warehouse. Raw data lives briefly in the pipeline, so the warehouse never holds the original records.
What is ELT
Data is loaded raw into a restricted zone of the warehouse or lakehouse, then transformed there with SQL or jobs. The raw copy stays, so tables can be rebuilt when logic or terminology maps change.
Key differences
Where does untransformed PHI land, and who can read it?
With ELT, the raw zone holds full source records, so it is the most sensitive store you run. 45 CFR 164.502(b) requires covered entities and business associates to make reasonable efforts to limit PHI to the minimum necessary for each use. Restrict the raw zone to pipeline service accounts and a few named engineers, log every read, and keep analysts on curated tables.
Should HL7 v2 and X12 be parsed before load?
Often yes, or at least before analysts see them. HL7 v2 messages need segment parsing and site-specific mapping, usually in the interface engine. X12 remittances follow the 835 implementation guide that 45 CFR 162.1602 adopts, version 005010X221, with nested loops that SQL handles poorly. A common split is ETL for parsing into raw tables, then ELT for modelling.
How do you replay when a terminology map changes?
Keep the raw data unchanged and rebuild. When a local lab code map or a value set is corrected, ELT reruns the transforms over stored raw data and every table reflects the fix. That depends on the raw copy staying intact: 45 CFR 164.312(c)(1) requires policies and procedures that protect ePHI from improper alteration or destruction, so make the raw zone append-only.
Change data capture or FHIR Bulk export?
Change data capture from an EHR's reporting database gives near-real-time rows in the vendor's internal schema, which changes with upgrades. FHIR Bulk export gives standard resources, and its _since parameter returns only resources changed after a given time, which suits nightly or hourly incremental loads. Many teams use Bulk export for standard data and CDC only where latency demands it.
Where should de-identification run?
As late as possible, but before data leaves the identified zones. Run it as a transform from curated identified tables into a separate de-identified zone, so the logic can be replayed and audited. Our data warehouse vs data lake vs lakehouse guide covers how those zones are laid out.
When to use each
When to use ETL (transform, then load)
- Policy says identified PHI must not land in the analytics platform.
- Sources need heavy parsing first, such as HL7 v2 messages or X12 files.
- The warehouse charges heavily for compute, so transforms run cheaper elsewhere.
- Outputs are fixed and rarely need rebuilding from source.
When to use ELT (load, then transform)
- You need to rebuild tables when code maps, measures or logic change.
- Sources arrive in formats the platform can already read, such as FHIR Bulk NDJSON.
- Analysts write transforms in SQL and want them versioned with the data.
- The raw zone can sit inside your BAA with tight access and logging.
How do you move an ETL pipeline to ELT without exposing PHI?
1. Build the raw zone first, with access limited to pipeline service accounts, encryption with your keys and read logging switched on.
2. Land one source raw in parallel with the existing ETL feed, and compare the tables each path produces.
3. Move transforms into versioned SQL or jobs, with tests for row counts, required fields and code mappings.
4. Switch reports to the new tables once results match, then retire the old ETL job.
5. Set retention for the raw zone: keep what you need for replay and audit, and delete the rest on schedule.
Our healthcare data validation checklist lists the data checks to run at each step.