US Healthcare ETL/ELT Architecture

The Shift from Batch to Streaming Ingestion

Historically, US healthcare data ingestion has relied heavily on nightly batch processing. Electronic Health Record (EHR) systems like Epic or Cerner would export massive flat files or relational dumps at midnight, which were then processed by traditional ETL (Extract, Transform, Load) tools by morning.

Some organizations explore near-real-time data flows to support operational analytics, reporting, or workflow coordination. While batch processing remains relevant for historical analytics and monthly reporting, streaming ingestion using event-driven architectures is one architectural option for selected use cases — it should be evaluated against batch processing, governance needs, operational maturity, and compliance requirements.

HL7 v2 Ingestion Pipelines

HL7 v2 remains the workhorse of hospital interoperability. An effective HL7 ingestion pipeline must handle the chaotic nature of legacy HL7 messages. A modern architecture typically involves:

• Mirth Connect or similar integration engine acting as the secure TCP listener.
• Message queues (e.g., Kafka or AWS SQS) to buffer incoming ADT, ORU, and SIU messages.
• Serverless parsers that convert the pipe-delimited HL7 format into a structured JSON payload.

Instead of transforming the data immediately (ETL), the modern approach is ELT (Extract, Load, Transform). Raw HL7 messages are dumped directly into a data lake (e.g., S3 or Azure Data Lake), preserving the original message for auditability and replayability.

The Transition to FHIR-based Data Lakes

Fast Healthcare Interoperability Resources (FHIR) is being explored as a foundation for data lake patterns. By storing data in FHIR JSON format, healthcare organizations may be able to work toward a more unified schema that is compatible with SMART on FHIR applications and third-party APIs.

Building a FHIR-based data lake may involve services like AWS HealthLake or Google Cloud Healthcare API to ingest, validate, and index FHIR resources. This may allow data engineers to run complex SQL queries over unstructured clinical notes and structured lab results — though the analytical capabilities depend on implementation quality, data governance, and organizational requirements.

Operational Tooling for Source Inspection

Before a pipeline runs at scale, teams should inspect representative sources with tools matched to the data format. Use the parse HL7 v2 messages workflow for interface messages, the CCDA viewer for clinical document XML, the FHIR validator for JSON resources, and the flat file viewer for CSV or delimited extracts. For private desktop review before ingestion, Forge offers a raw file inspection desktop app for validating CSV, delimited, and fixed-width source files without sending operational data to online tools.