Problem RestatementProblem
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.
RequirementsRequirements
- 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.
ArchitectureArchitecture
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
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"] --> LINKey Stages
- 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.
- Parse and map: partner-specific adapters convert formats into a common model (e.g., FHIR Patient, Observation). Mapping rules are versioned per partner.
- 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.
- 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.
Deep Dive — Deciding that two records are the same patientDeep dive
A lab result arrives from one hospital and a claim from an insurer. Both concern Maria Gonzalez. Linking them correctly is the hardest problem in the pipeline, and being wrong in either direction causes harm.
Match on name and date of birth
Treat records with the same name and date of birth as the same person.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
R1["Maria Gonzalez, 1984-03-02"] --> M["Same name + DOB"]
R2["Maria Gonzalez, 1984-03-02 - a different person"] --> M
M --> MERGE["Records merged"]
MERGE --> HARM["One patient's allergies attached to another's chart"]
R3["Maria Gonzales - one letter different"] --> SPLIT["Treated as a new patient"]
SPLIT --> MISS["Existing history invisible to the clinician"]Both errors are dangerous and they pull in opposite directions. A false merge puts one person's clinical data in another's record; a false split hides a history a clinician is relying on. Names are misspelled, transliterated, hyphenated and changed, and common name plus common birthday collides constantly.
Match on strong identifiers
Use a member id, an MRN or a national identifier when present, and only fall back to demographics when it is not.
Where an identifier exists this is exact and should be the first rule. But identifiers are partner-scoped — one hospital's MRN means nothing at another — they are frequently absent, and they are sometimes entered wrong. The fallback still has to handle the majority of cross-partner links.
Deterministic first, probabilistic second, humans for the middle
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
REC["Incoming record"] --> DET{"Strong identifier matches?"}
DET -->|"yes"| LINK["Link - high confidence"]
DET -->|"no"| PROB["Probabilistic score: name similarity, DOB, sex, address, phone"]
PROB --> T{"Score"}
T -->|"above upper threshold"| LINK
T -->|"below lower threshold"| NEW["Create a new patient record"]
T -->|"in between"| REV[("Review queue - a human decides")]
LINK --> MPI[("Master patient index - links, never overwrites")]
NEW --> MPI
REV --> MPI- Two thresholds, not one. Auto-link above, auto-separate below, and route the uncertain band to people. The band is small in volume and it is where every dangerous error lives.
- The index links, it never merges destructively. The master patient index records that two source records refer to one person while both originals remain intact, so a wrong link can be undone. An irreversible merge cannot.
- Record why. Each link stores the rule or score that produced it, which is what makes a mistaken link findable when a clinician reports one.
This is also what makes replay safe: re-running the pipeline from the raw zone after fixing a mapping bug uses idempotent upserts, so a backfill corrects records instead of duplicating them — and the linking decisions are re-derived from the same evidence rather than re-guessed.
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.
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.
Wrap-UpWrap-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.