Every Format Imaginable

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

hard

Seniority

L5

Problem

Patient records arrive in four formats, all carrying PHI: structured insurance claims, semi-structured HL7 hospital messages at about 2 million a day, PDF lab reports at about 200,000 a day, and free-text clinical notes. The originals must be kept unchanged for audit and re-parsing. Design the data lake pipeline.

PDFs, HL7, JSON. All of it lands in the same lake.

Business requirements

• Analysts need to ask questions across claims, hospital records, lab reports, and clinical notes; today they have to work with each format separately.
• Population health research has to use de-identified data; raw PHI is reserved for the clinical team only.
• Hospital events feed clinical decision support and have to be queryable within minutes on their own serving path; the other three sources tolerate up to a day.

Worked solution and explanation

What this problem really is

This is a privacy boundary dressed up as a format-parsing problem. Anyone can draw four parsers feeding a warehouse; the parsing is the easy half. What separates candidates is where the identifiers come off and which path is allowed to skip the warehouse. Leave PHI in the analytical store behind a column grant and it is one misconfigured role away from a researcher. Hash it carelessly, per source, and the same patient becomes four strangers, so the one canonical record analysts asked for can no longer be joined. Route decision support through the daily store and the alert lands after the patient has gone home.

The default draw is one ETL per format into one big table, with a column policy keeping researchers off the PHI columns. It answers the format question and fails the rest. Decision support reads a daily table. Analysts sit on identified rows reserved for clinicians. And when the PDF parser improves, there are no originals left to re-run it on.

Build it in four moves

Step 1: Land every original in the lake first

All four sources, hospital messages included, write unchanged into raw_lake, plain object storage. Regulators want the originals for seven years. Every parser improvement re-runs over these files, not over a table an earlier parser already flattened.

Step 2: Parse each format into one patient-event shape

format_parsers reads the lake on a daily cadence. Claims become rows, HL7 segments become rows, scanned PDFs go through text extraction, and notes become structured fields plus text. All four emit the same canonical schema, so analysts write one query instead of four.

Step 3: Strip identifiers before the analytical store

phi_deidentification replaces names, record numbers and addresses with a keyed pseudonymous token and coarsens dates and postcodes. One key across every source keeps a patient's claims, labs and notes joinable as one person. canonical_warehouse never holds raw PHI, so analysts and researchers cannot reach it however the query is written.

Step 4: Give hospital events their own minutes path

Hospital messages also flow through hospital_stream (Flink) into cds_serving_store, which is queryable within 15 minutes, and on to decision support. That path never waits on the daily batch. It is also the only path that carries identities, and it reaches the only team allowed them.

The reference design

Column policy on an identified store

PHI sits in canonical_warehouse and a grant hides the columns. Every new role, view or export is a fresh chance to leak, and the next privacy review audits all of them.

De-identify before the store

phi_deidentification runs before the load, so the store holds tokens only. The boundary is a property of the data, not of permissions, and there is nothing in the store to leak.

Common follow-up questions

• A new extraction model improves PDF parsing. What lets you reprocess two years of lab reports, and what changes downstream? (Tests whether the candidate re-runs `format_parsers` over `raw_lake` and replaces the PDF-derived rows in `canonical_warehouse` for those periods, leaving the hospital stream untouched.)
• Researchers want a cohort joined on provider and date of service. Is the de-identified store still safe? (Tests whether the candidate sees quasi-identifiers re-identifying small cohorts, and adds minimum cohort sizes or further generalisation inside `phi_deidentification`.)