High-Volume Healthcare Data Ingestion Pipeline

0) Problem Restatement

Oracle asked: design a pipeline that ingests large volumes of healthcare data from many partners (hospitals, labs, insurers). The data is sensitive (patient health information), arrives in different formats (HL7 v2 messages, FHIR JSON, CSV files), and is messy: it can be duplicated, malformed, corrected later (new versions of the same record), or late. The pipeline must validate it, keep a full audit trail, support replay, and follow privacy laws like HIPAA.

1) Requirements

• Accept data via SFTP/file drops, APIs and message feeds from hundreds of partners.
• Validate against schemas and business rules, and quarantine bad records with clear errors back to partners.
• Deduplicate, and handle versions (the latest correction wins, and history is kept).
• Normalize to a common model (e.g., FHIR resources).
• Replay or backfill any time range. Meet per-partner freshness SLAs.
• Encryption, access control, audit logs and minimum-necessary access.

2) Architecture

flowchart LR
    P["Partners - SFTP, API, HL7 feeds"] --> GW["Ingestion gateway - auth, checksum"]
    GW --> RAW[("Raw zone - immutable, encrypted")]
    RAW --> Q[("Kafka - raw records")]
    Q --> PARSE["Parse + map to common model"]
    PARSE --> VAL["Validate - schema, rules"]
    VAL -->|"invalid"| QUAR[("Quarantine + partner error report")]
    VAL --> DEDUP["Dedupe + version resolution"]
    DEDUP --> CUR[("Curated store - FHIR / warehouse")]
    DEDUP --> LIN[("Lineage + audit log")]
    OPS["Ops dashboard - per-partner SLAs"] --> LIN

3) Key Stages

1. Land raw data first, unchanged: every file or message is stored immutably in an encrypted raw zone with metadata (partner, received_at, checksum). This is the basis for replay and audits, since we can always reprocess from raw.
2. Parse and map: partner-specific adapters convert formats into a common model (e.g., FHIR Patient, Observation). Mapping rules are versioned per partner.
3. Validate: schema checks (required fields, types), code checks (valid lab codes, like LOINC), and business rules (the date isn't in the future). Invalid records go to quarantine with reasons. Partners get error reports and can resend.
4. Deduplicate and version:

• Each record has a business key (partner + source record ID) and a version (a source timestamp or sequence).
• Exact duplicates (same key + same content hash) are dropped.
• Corrections (same key, newer version) replace the current value, and older versions are kept in history.
• Out-of-order: an older version arriving late must not overwrite a newer one, so compare versions in the upsert.

4) Replay, Backfill and Patient Matching

• Replay: re-run the pipeline from the raw zone for a partner and date range (e.g., after fixing a mapping bug). Idempotent upserts make this safe.
• Patient matching: records from different partners must be linked to the same person (a master patient index), using deterministic and probabilistic matching on name, date of birth and identifiers, with manual review for uncertain matches.

5) Security and Compliance

• Encryption in transit (TLS/SFTP) and at rest (KMS keys), and field-level encryption or tokenization for identifiers.
• Access control: least privilege, with engineers seeing de-identified data by default.
• Audit logs of every access and change, and lineage (which raw file produced which curated record).
• Retention and deletion policies per regulations and contracts.

6) Operations

• Per-partner dashboards: volume vs expected, error rate, lag vs SLA. Alert when a partner goes silent or its error rate spikes.
• Dead-letter handling and reprocessing tools for the support team.

7) Wrap-Up

Land every partner file or message immutably and encrypted in a raw zone, then stream it through partner-specific parsing into a common model, validation with quarantine and error reports, and deduplication and version resolution by business key (never letting older versions overwrite newer ones). Load with idempotent upserts, which makes replay and backfill from raw safe, and wrap it all in HIPAA-grade encryption, least-privilege access, audit logs, lineage and per-partner SLA monitoring.