Real Data, Fake Patients
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
- L6
Problem
Design a scheduled pipeline that de-identifies PHI from production into a dev object store, replacing stable ids with synthetic tokens from a consistent mapping. The mapping sits in a vault feeding only the de-identification step; an orchestrator runs tables in dependency order through a classification check, writing a per-run audit log retained for years.
Dev needs production data. HIPAA says absolutely not.
Business requirements
- HIPAA forbids real PHI in development; engineers have to work with realistic data that isn't a real person.
- Engineers run tests in dev; if joins don't behave like prod, the tests pass on broken code.
- Production schema changes weekly and a new sensitive field could quietly land in dev; that has to be blocked.
- HIPAA compliance requires that every de-identification run is auditable for years.
Worked solution and explanation
The trap
This is a referential-integrity problem dressed up as compliance. Most candidates draw a copy job with masking in the middle. The real question is whether the fake data still joins like the real data. Random replacement scrubs PHI, but it hands the same patient a different token in every table, so joins on patient_id come back empty and dev tests pass on code that breaks in prod. The second trap is time. The schema changes weekly, and a masking job that passes unknown columns through will eventually ship a new sensitive field to dev without anyone noticing.
Walk the requirements
Step 1: Tokenize from one mapping, in dependency order
Real patient_id 12345 becomes 'pat_abc' in patients, encounters, labs and every other table, because a single tokenize step reads one mapping. The orchestrator processes parents before children, so a child row never references a token that has not been minted yet.
Step 2: Wall off the vault
The original-to-token mapping is the one artifact that can re-identify a patient. It feeds the tokenizer and nothing else: no edge to the dev dataset and no edge to any consumer. Dev only ever holds tokens.
Step 3: Gate every column before it moves
A classification gate checks each column against its rules. It is marked safe, marked sensitive with a scrub rule, or blocked. An unclassified column halts the run and alerts. A PHI scan after tokenizing catches anything the rules missed before anything lands in dev.
Step 4: Audit each run without PHI
Each run writes the tables processed, row counts, rules applied and scan results to an archive retained for years. The record holds counts and rule ids, never patient values, so the audit log is not a second PHI store.
The shape that fits
- production_db (source, PostgreSQL)
- orchestrator (transform, Airflow); on error: alert; alerting: Unclassified column blocked or run incomplete
- classification_gate (quality gate, Great Expectations); on error: alert
- tokenization_vault (storage, PostgreSQL)
- tokenizer (transform, Spark); idempotency: staging_table
- phi_scanner (quality gate, custom); on error: alert
- dev_dataset (storage, object storage); backfill: partition_overwrite
- audit_archive (storage, object storage); backfill: incremental
- engineers (consumer, Jupyter); freshness: < 24h
- compliance_team (consumer, dashboard); freshness: < 24h
- production_db -> orchestrator
- orchestrator -> classification_gate
- classification_gate -> tokenizer
- tokenization_vault -> tokenizer
- tokenizer -> phi_scanner
- phi_scanner -> dev_dataset
- phi_scanner -> audit_archive
- dev_dataset -> engineers
- audit_archive -> compliance_team
- Source + orchestration: production_db, orchestrator, classification_gate
- Tokenize + scan: tokenization_vault, tokenizer, phi_scanner
- Outputs: dev_dataset, audit_archive
- Consumers: engineers, compliance_team
- Random replacement
- Each table picks its own fake
patient_id. PHI is gone, and so are the joins. Dev tests run against relationships that do not exist. - Vaulted deterministic tokens
- One mapping, one tokenize step. The same real id gets the same token in every table, so joins in dev return the same shape they return in prod.
Common follow-up questions
- An engineer needs the dev row for one specific real patient. What allows it, and what prevents it? (Only the vault maps tokens back to people, and engineers have no path to it.)
- A regulator asks you to prove the dev dataset from a given date held no PHI. What answers that? (The audit record for that run: scan results, rules applied and the dataset id.)