Analysts Are Slowing the Store Down
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
Analytics queries against the production database are slowing the live application. Move analytics onto its own warehouse fed from the database's change log, while a merchant dashboard shows new orders within fifteen minutes on a path of its own.
Orders placed. Data warehouse hungry.
Business requirements
- Analytics queries on the read replica have been spiking lag and breaking customer-facing features that depend on the same replica.
- Merchants watch their dashboards throughout the day; they expect a new order to show up within minutes.
- A small fraction of orders arrive with broken merchant references or totals that don't add up; these must be caught before they reach the reporting tables, not after finance finds them.
Worked solution and explanation
Why this problem exists in real interviews
Analytics is hitting the read replica that the application also depends on, lag spikes, customers feel it. The fix sounds obvious: move analytics off the replica. The trap is that the obvious fix is incomplete: a warehouse fed by a nightly batch satisfies the analysts but leaves the merchant dashboard a day stale, and a pipeline that trusts every row lets the small fraction of broken orders poison the reporting tables.
The natural shape is a nightly batch from the replica into a new warehouse. Analytics is happy, the replica recovers, and merchants discover their dashboards are showing yesterday's orders. Somebody bolts on a 'realtime view' over the replica, which puts the load right back. Meanwhile the orders with broken merchant references or totals that do not reconcile flow straight into the marts, and finance opens a ticket when the numbers fail to tie out.
Walk the requirements
Step 1: Move analytics off the replica with log-based CDC
Replication has to add zero load to the production system. Log-based CDC (Debezium or a database's built-in connector) reads the binlog or WAL that the database is already writing, runs as a separate process, and emits change events to a stream. Analytics reads from the warehouse fed by that stream; nothing analytics-shaped touches the OLTP or the replica. Without a CDC capture path the replication is a query against production, which is the named cause of the problem.
Step 2: Streaming path serves merchant dashboards in minutes
Merchants expect their orders within minutes. The same change stream feeds a streaming consumer that updates a merchant-facing serving store; the dashboard reads from there. Analytics runs on a slower path into the warehouse. One stream, two consumer paths sized to the consumer; merchants do not pay the latency of the analytics load and analytics does not pay the cost of a streaming serving tier. A 'we'll just make analytics fast and let merchants read it too' design ends up paying streaming prices for analytics.
Step 3: A data quality gate guards the reporting tables
About 1 to 2 percent of orders arrive with a merchant_id that is not in the merchant table yet, or a total that does not equal the sum of its line items. A data quality gate validates each order before it lands in the reporting tables; failures go to a dead-letter queue where the data team reviews them and reprocesses the ones they fix. Many referential gaps are race conditions that resolve within minutes. Without the gate, broken rows flow straight into the marts and finance finds them when the daily totals do not reconcile.
The shape that fits
- order_source (source, CDC)
- change_stream (queue, Kafka)
- merchant_streamer (transform, Flink)
- merchant_store (storage, PostgreSQL)
- dq_gate (quality gate, Great Expectations)
- dead_letter (queue, Kafka)
- warehouse_loader (transform, Spark)
- analytics_warehouse (storage, warehouse)
- merchant_dashboard (consumer, Grafana)
- analytics_team (consumer, Metabase)
- order_source -> change_stream
- change_stream -> merchant_streamer
- change_stream -> dq_gate
- merchant_streamer -> merchant_store
- dq_gate -> warehouse_loader
- dq_gate -> dead_letter
- warehouse_loader -> analytics_warehouse
- merchant_store -> merchant_dashboard
- analytics_warehouse -> analytics_team
- Source + CDC: order_source, change_stream
- Streaming path: merchant_streamer, merchant_store
- Quality + warehouse: dq_gate, dead_letter, warehouse_loader, analytics_warehouse
- Consumers: merchant_dashboard, analytics_team
Common follow-up questions
- An order fails the referential check because its merchant was created a second after the order, while another fails because its total genuinely does not match its line items. What should the pipeline do differently with each? (Tests whether the candidate separates transient from permanent failures: a referential miss from a merchant-creation race is reprocessed from the dead-letter path and usually resolves in minutes, while a total that genuinely does not add up is a data bug that needs review rather than endless retries.)
- The CDC stream falls behind during a maintenance window on the production database. What does the merchant dashboard show, and what does the analytics warehouse do? (Tests whether the candidate has thought about staleness: the dashboard either freezes at the last update or surfaces a 'stale' indicator; the warehouse keeps catching up when the stream resumes; a lag alert fires before either consumer is meaningfully impacted.)