Thousands of Practices, One Dataset

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

We run a cloud EHR platform for thousands of medical practices, all sharing one database where tenant isolation lives only in the application layer. Design the pipeline that serves both cross-practice population health analytics and each practice's own performance reporting from that database.

Patient records in, operational insights out.

Business requirements

• One practice's data being visible to another is a HIPAA breach; the boundary has to hold once data leaves the application.
• The epidemiology team runs cross-practice research; they have to do it without seeing real patient identifiers.
• A handful of huge practices generate most of the volume; a smaller practice can't be left waiting because a big one is backed up.
• Every practice manager opens dashboards each morning for the prior day; T+1 freshness has to hold across all of them, not just the largest.

Worked solution and explanation

The real problem

This is multi-tenant isolation wearing a healthcare costume. Anyone can draw a pipeline that lands the data. What separates candidates is whether the boundary survives once the data leaves the application. A WHERE practice_id = ... filter in the BI tool is one forgotten clause from a HIPAA breach. A single nightly job into one table fails on three fronts at once: the biggest practice runs long and starves the small ones, research reads real identifiers, and one unfiltered query returns another practice's patients.

Walk the requirements

Step 1: Capture changes per practice, in near real time

Stream changes out of the shared database with a sub-minute capture stage and partition the stream by practice. Large tenants generate most of the volume, and partitioning means their lag stays in their own lane. Also, no big nightly extract hammers the production database.

Step 2: Ingest each practice independently

The ingest job runs in parallel partitions, one practice per partition. A backed-up large practice delays only itself. The prior day lands for every tenant before the morning dashboards, not just for the big ones.

Step 3: Put the boundary in the warehouse

The practice warehouse sits behind a row-level security policy keyed on the practice, so a query for one practice cannot return another's rows. Without a warehouse tier there is nowhere to enforce it, and a BI filter is only a convention.

Step 4: De-identify before research data lands

A pseudonymizing transform replaces MRN, name and date of birth with tokens on the way to the population warehouse. Epidemiology queries a store that never held real identifiers. Real PHI stays only on the per-practice path.

The shape that fits

Common follow-up questions

• A small practice is stuck for hours behind a large practice's onboarding backfill. What prevents this, and what would you change if it happened anyway? (Tests whether they isolate backfill capacity from steady-state ingest instead of sharing one worker pool.)
• Prove a researcher cannot reconstruct a patient's identity from the population warehouse. What in this design backs that up? (Tests whether they reason about quasi-identifiers and where re-identification keys live, not just removed columns.)