The Analysts Cannot Touch Production

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

Morning reports no longer reconcile for the analytics team, because the same customers and orders sit across several production tables under mismatched keys. Design a pipeline that carries every insert, update and delete from that database (about 2,000 writes a second at peak) into a separate warehouse where records are normalized and combined.

Production is the source. Analytics needs its own copy.

Business requirements

• The product team has refused to let analytics queries hit production; whatever copies the data over can't itself add read load.
• Analysts open dashboards at 4am; the overnight pipeline has to finish before then, so a missed or lagging stage has to page someone, not fail silently.
• Production holds raw customer email and phone; we can't fail a privacy audit on what's stored in the analytics warehouse.

Worked solution and explanation

What this really is

This is continuous change capture dressed up as 'give analytics its own warehouse.' The skill being probed: can you get every insert, update and delete out of a busy transactional database without adding a single read to it? Everyone draws a warehouse and a dashboard. What separates candidates is the arrow on the far left. A scheduled extract that SELECTs from production is still a query, and queries are exactly what the owning team refused. Get that arrow wrong and the DBA blocks the data team at 2am, the extract slides past 4am so analysts open empty dashboards, and a privacy review finds raw email in the warehouse because masking was bolted onto the BI layer.

Nightly extract

A scheduled job runs SELECT * against every table. It is read load by definition, it cannot see a hard-deleted row because the row is gone, and its runtime grows with table size until it no longer fits before 4am.

Log-based capture

A connector tails the WAL the database already writes for replication. Zero queries, every insert, update and delete arrives as an event, and the volume tracks the day's changes rather than the 500M-row table.

Walk the requirements

Step 1: Capture changes off the log, not the tables

The database already writes a change log for its own replicas. A CDC connector reads that log as a separate process and publishes each change to a stream, so the only thing touching production is a log reader the DBA already runs for replication. This is also the only path that carries deletes: a canceled order hard-deleted from customer_orders shows up as a delete event instead of silently vanishing.

Step 2: Mask before anything is stored

Strip or hash email and phone in the stream consumer before anything is written down. Emails become a stable hash so analysts can still count unique customers; phones become null or last four. If raw values land even in staging, the audit covers them, so the boundary sits upstream of every storage tier.

Step 3: Stage raw, then normalize and combine

Masked change events land in a staging lake that is never rewritten. A separate transform then reconciles the mess: collapse the two order tables on external_order_id, map product_sku onto product_catalog, and coerce three date encodings into one. Keeping staging intact means a bad transform is rerun from staging, not re-captured from production.

Step 4: Orchestrate the build and page on a miss

The orchestrator sequences the build and a freshness check runs after the warehouse load. If the check fails or a task overruns, it pages on-call hours before 4am, not when an analyst files a ticket. Without that edge into an alerting consumer, a late load looks exactly like a healthy one until someone opens a dashboard.

The reference design

Common follow-up questions

• A NOT NULL column appears on a production table with no warning. What does the connector do, and what breaks downstream? (Tests whether additive columns flow into `staged_lake` untouched and whether `normalize_transforms` is written to tolerate them instead of failing the 4am build.)
• `customer_orders_legacy` stops receiving rows and is later dropped. How do historical legacy-only orders survive? (Tests whether the candidate leans on the preserved staging tier as the permanent record rather than the source table.)
• An analyst needs a raw email to debug one customer. Where do they go? (Tests whether the privacy boundary holds: the lookup goes through an audited path to production, never the warehouse.)