Design an analytics pipeline that produces a daily report for the leadership team by 8am. The data comes from a production database and a stream of app events.
What they are really testing: Whether you treat a deadline as a design constraint and build for late data and reruns, not just for a happy nightly job.
A real interview question
Design an analytics pipeline that produces a daily report for the leadership team by 8am. The data comes from a production database and a stream of app events.
What most people say
drag me
“I would run a nightly job that pulls the tables, joins them, and writes the numbers into a dashboard.”
It has no deadline math, no story for late events, and no way to rerun a bad day, so the first time a source is slow the leadership report is silently wrong.
The follow-ups they ask next
The upstream database dump is 3 hours late. What does the 8am report show?
Describe publishing the previous day's numbers with an explicit staleness banner, plus an alert, rather than silently showing empty charts.
A source adds a column and changes a type. What breaks?
Schema on read at the bronze layer, contract checks at silver, and a quarantine path for rows that fail rather than a job crash.
Finance says last Tuesday's number was wrong. How do you fix it?
Backfill by reprocessing that partition. This only works if writes are overwrite by partition, which is why you designed it that way.
What the interviewer is listening for
- Works backwards from the 8am deadline and leaves room for a rerun
- Handles late arriving events with a reprocessing window
- Makes the pipeline idempotent so a backfill is routine
- Fails the run on a data quality check rather than publishing a bad number
What sinks the answer
- No deadline math at all
- Appends rows so a rerun duplicates the day
- Assumes every event arrives before midnight
- Reaches for streaming for a daily report without justifying the cost
If you genuinely do not know
Say this instead of freezing. Reasoning out loud from what you do know beats silence every single time, and a good interviewer is listening for exactly that.
“I have not owned a reporting pipeline end to end, but here is how I would reason about it. The report is due at 8am, so I would work backwards from that time and see what has to happen when.”
Keep going with system design
Mid
Design a log and metrics ingestion pipeline for a platform with about 200 services. Engineers need to search logs and alert on metrics.
Mid
Design an image upload and processing service for a marketplace app. Users upload photos from their phone, and we need thumbnails and a moderation check before the listing goes live. Walk me through it.
Senior
Design an event-driven order processing system for an e-commerce site. Payment, inventory, and shipping are separate services.
Senior
Design a system that must never lose a message. It ingests payment notifications from a partner and each one must be processed exactly once.
Senior
We have about 50 product teams and every one of them has rolled their own pipeline. Design a CI/CD platform that all 50 can use. Take me through it.
Senior
We are moving from one shared cloud account to a proper multi-account setup. Design the landing zone.
Knowing the answer is not the same as recalling it under pressure
Sign in to send the questions you fumble to spaced recall, so they come back right before you would forget them, and learn the concepts behind them with hands-on labs.
Start free