Oncology EHR data pipeline into an analysis ready database
A healthcare data company needed to get usable data out of an oncology EHR. We built the pipeline to move the exports, parse ten message types including HL7 v2 and CCDA, link every record to the right patient, and load the data into PostgreSQL for analysis and reporting.
What was the client trying to do?
The client works with clinical data from oncology practices. The data sits in the practice EHR. The EHR could export it, but the result was a collection of message files in different formats, with nothing organised for direct querying.
There was no process in place to move the exports on a schedule, stage them in the cloud, or connect a lab result with the related appointment or diagnosis. Each message type had its own identifiers.
The requirement was straightforward. One PostgreSQL database, one row per patient in a master index, structured fields for the message types that mattered most, and new data arriving without manual intervention. That is what we were engaged to build under our clinical data and study platforms practice.
Why is EHR data harder to land than it looks?
An oncology EHR does not hand over a ready made table. It sends messages. Some follow HL7 Version 2, the pipe delimited standard most US hospital interfaces still speak. Some are CCDA clinical summaries, which is XML, and the client's EHR produced two flavours of it, HITSP C32 and CCDA 2.0. Others are report exports with their own layout for diagnoses, drugs given by patient and regimen, and radiology orders.
Ten message types were in scope. The client ranked them, with four marked as priority zero for the first phase. The remaining types followed after the core pipeline was working.
| Message type | Data carried |
|---|---|
| HL7 v2 | Patient |
| MDM | Documents |
| SIU | Appointments |
| Lab results report | Lab results |
| Lab ORM | Lab orders |
| CCDA HITSP C32 | Clinical summary |
| CCDA 2.0 / CER | Clinical summary |
| Patient diagnosis report | Diagnosis |
| Drugs report | Oncology drugs, by patient and regimen |
| Radiology ORM | Radiology orders |
The difficult part is not any single parser. All ten need to agree on the patient, and the full process needs to keep working after the engineers are done. A pipeline that works once on a laptop is not enough. The real deliverable is one that runs every night without someone having to step in.
What did Sequoia build?
Five pieces, built in the same order the data moves. Each was tested separately before we tested the full flow end to end.
Scheduled export to an SFTP landing site
A job moves new data from the EHR to an SFTP landing site on a fixed schedule. This is the only step that touches the EHR. Its job is simply to move files, not interpret them.
Lambda into S3
An AWS Lambda function copies each new file from the landing site into an S3 bucket and starts the processing run. S3 keeps a record of every export received, which makes it possible to trace a value back to its source later.
Master patient index
We built this to the client's specification before writing the parsers. Every identifier from every message type resolves through it, so downstream tables do not have to work out whether two rows belong to the same patient.
ETL for each message type
We defined extraction rules for each message type, then wrote the transform and load programs to put the structured data into PostgreSQL. HL7 v2 segments, CCDA XML sections and the flat report exports each had their own extractor feeding the same schema.
End to end test and handover
We tested the full path from EHR export through to the PostgreSQL row. CCDA content and every prioritised message type were checked against the client's list. Technical specifications, user manuals and test results were written using the client's templates. Source code and executables were handed over as work for hire.
How were CCDA and patient identity handled?
CCDA needed separate handling. HITSP C32 and CCDA 2.0 summaries contain much of the same clinical information, but their XML structures differ. We normalised both into one set of tables so analysts do not have to work across two separate structures. The database user does not need to know which version a summary came in.
We dealt with identity first. It is tempting to parse the easier messages, load them, and handle patient matching later. That leaves you with tables full of rows that cannot be reliably joined. The index was built before the ETL, and every loader uses it.
We also kept the source data traceable. Every raw export stays in S3, and each processed row can be traced back to its source file. If someone needs to check a lab value, the path back to the original export is there. For a data organisation, that matters when the data needs to be explained or defended.
How was the engagement run?
The work was scoped as a fixed fee delivery against written acceptance criteria. There were four: the pipeline had to be fully functional and meet the technical requirements, the software had to be documented and maintainable, the project had to stay within the agreed timeline and budget, and the sponsor had to approve the final deliverables.
A team of eight worked on it, including software engineers and DevOps, with tech leads covering each stream. Invoices were sent every four weeks with a description of the work completed and the hours involved, giving the client a clear record of progress.
We used the client's templates for the documentation rather than our own. That matters when the people maintaining the pipeline later will be the client's engineers and the documents need to fit with the rest of their material. The delivery pattern is the same one we use across cloud and data platform work in regulated settings.
What does this mean if you are in a similar spot?
If you have an EHR that exports messages and a team that needs usable tables, the basic shape of this work will look familiar. The vendor, message list and priorities may change. The core setup remains: a landing site, cloud staging, a patient index, a parser for each message type, one relational target, and a schedule that runs without manual intervention.
There is one practical dependency to plan for. This kind of pipeline depends on what the EHR can export. Access to the source system and the landing site also needs to be in place before the engineering work can move very far. In this engagement, the client handled that dependency. It should be part of the plan for any similar project.
It is also worth agreeing early on who will own the code after delivery. Here, everything was work for hire and delivered with the source code, which also shaped the documentation. If that is what you need, it should be agreed during scoping. Our bioinformatics and genomics and regulatory and validation teams dovetail with this kind of build when the data is headed for a study or a submission.
Questions people ask about this work
What is an oncology EHR data pipeline?
It is the set of jobs that move exports from an oncology electronic health record, parse each message type, link every record to one patient identity, and load the structured data into a database that analysts can query. The pipeline described here used SFTP, AWS S3 and Lambda, custom ETL code, and PostgreSQL. The EHR itself was not modified.
Which EHR message types did the pipeline handle?
The pipeline handled HL7 v2 patient messages, MDM document notifications, SIU appointment messages, lab orders, lab results reports, radiology orders, patient diagnosis reports, drug and regimen reports, and CCDA clinical summaries in both HITSP C32 and CCDA 2.0 form. Patient, documents, appointments and lab results were the first four to go live.
Why does a master patient index matter in an EHR pipeline?
Each message type has its own identifiers. Without an index that resolves them to one patient, a lab result, an appointment and a diagnosis for the same person can end up as unrelated rows. The index was built to the client's specification before the ETL was written, so every table used it from the start.
What did the client receive at the end?
The export job, the Lambda function, the ETL programs, the master patient index and the PostgreSQL schema. The client also received technical specifications, user manuals and test results using its own templates, along with the full source code and executables. Everything was delivered as work for hire and is the client's property.
Can Sequoia build a similar pipeline from a different EHR?
The approach is not tied to one vendor. The export mechanism, message formats and priorities will vary, but the basic structure carries over: landing site, cloud staging, patient index, parsers for each message type and a relational target. We can scope the work around your system and message list.
Have an EHR feed you need in a database?
Send us the message list and the target database. We will come back with a scoped plan and a team.
Start a conversation