Nightly Exports Are Too Slow
A medium 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
- medium
- Seniority
- L5
Problem
The claims, member and provider databases run on three different engines and take about 225K changes a day, and their owners will grant replication access to the transaction logs but nothing else. The platform lead needs a pipeline that retires the six-hour nightly export into the analytics warehouse: design it.
Healthcare claims change constantly. The warehouse cannot fall behind.
Business requirements
- The utilization management team makes same-day authorization calls; the six-hour nightly export has them missing the window.
- Every record in the analytics warehouse is regulated PHI; identifiers and clinical fields must be de-identified before they cross into the warehouse, or we fail a HIPAA audit.
- All three database teams have refused to sign off on anything that risks slowing or breaking the live applications, so no new query or write load on the source systems is acceptable.
Worked solution and explanation
Three constraints, one boundary
This reads like a latency complaint about a nightly export, but it is really a question about where two boundaries sit. Anyone can make claims arrive faster. The trap is doing it with incremental polling against production and masking PHI in warehouse views. That design beats tonight's export, yet it adds the query load the database teams already refused, and it leaves raw member identifiers in tables an auditor can read directly.
The natural first draft is hourly SELECTs that pull only changed rows. Utilization management is happier, the database teams see the new load on their dashboards and ask the data team to back off, and the masking views over raw tables are the first thing a HIPAA assessor queries around.
Walk the requirements
Step 1: Keep claims flowing all day, not once a night
Change events move from capture through de-identification into deidentified_warehouse as they happen, and the warehouse carries a '< 2h' freshness target because authorizations are decided the same day they are requested. A single overnight run, however fast, still misses that window.
Step 2: De-identify before the warehouse, not in views over it
phi_deidentify sits on the only path into the warehouse: member identifiers become stable pseudonymous tokens, names and SSNs are dropped, and clinical fields are scrubbed per policy. Masking views leave raw PHI in the base tables, and an assessor reads base tables. The boundary has to be a stage, not a permission.
Step 3: Capture from the log and watch its backlog
cdc_capture reads the WAL, redo log or SQL Server change tables, so the OLTP side does no extra work. The one way log capture can still hurt production is a stalled reader: the replication slot keeps WAL around until it is consumed. The backlog alert on cdc_capture pages someone long before the DBA's 5GB cutoff.
The shape that fits
- operational_dbs (source)
- cdc_capture (source, CDC); alerting: page if slot lag > 2GB
- phi_deidentify (transform, Spark)
- deidentified_warehouse (storage, warehouse); freshness: < 2h
- um_dashboard (consumer, dashboard)
- operational_dbs -> cdc_capture
- cdc_capture -> phi_deidentify
- phi_deidentify -> deidentified_warehouse
- deidentified_warehouse -> um_dashboard
Common follow-up questions
- An auditor asks for one specific patient's claim history. Where do they go, and where do they not? (Tests whether PHI scope stays tight: identifiable data lives only in the source systems under existing controls, and the warehouse holds only de-identified records.)
- A new identifier needs scrubbing. What in this design is redone, and what is not? (Tests seeing de-identification as the extension point: change its rules and reprocess affected recent data, while capture and the warehouse layout stay as they are.)