Case Study: Design a Batch Analytics Platform

"Design the pipeline that gets data from our production database into the dashboards the executive team looks at every morning" is one of the most common system-design prompts in a data engineering interview loop, precisely because it is not a trick question — it is the job. Nearly every mid-size company runs some version of this system: OLTP sources feeding ingestion, ingestion landing raw data somewhere, a transformation layer turning raw data into trustworthy tables, an orchestrator gluing the steps together on a schedule, and a BI tool sitting on top. The interview is not testing whether you know these five words exist. It is testing whether you can turn "some version of this system" into concrete numbers, concrete trade-offs, and concrete failure modes for one specific company.

A weak answer names the components — "we'd use CDC into a data lake, then dbt, orchestrated by Airflow" — and stops, as if listing the right nouns were the design. It never states a data volume, never explains why CDC was chosen for one source and batch extract for another, never says what happens when the pipeline runs late or a table is wrong at 7am, and never says who is paged. A strong answer does the opposite at every step: states assumptions about scale out loud, does arithmetic that would change the answer if the numbers were different, makes a different ingestion call for a 9-million-row mutable OLTP table than for a 40,000-row slowly-changing SaaS object, and treats SLAs, backfills, cost and ownership as first-class design constraints rather than an afterthought tacked onto the end.

The sentence to keep coming back to across every step below: the freshness, correctness and cost of every number on the dashboard is the sum of decisions made much earlier in the pipeline — how a source was extracted, how a file was partitioned, how a model was materialized — and a strong design makes each of those decisions on purpose, with numbers, rather than by default.