System design interview

Batch ETL Pipeline for E-Commerce (2M Orders/Day)

MediumLocked55 min read

Design a batch pipeline that processes 2M orders/day and lands warehouse tables by 6 AM for daily reports.

system-designbatchetlwarehouseestimation

Start here

The problem in plain words

Think of a shop that closes at night. After closing, someone totals the till, checks the receipts against the card machine, and writes up the day for the owner. The owner reads that report at 6 in the morning. If the totals are missing or half-finished, the owner makes bad decisions all day.

A batch data pipeline is that closing routine, done by machines. Overnight it copies the day's orders out of the live shop database, tidies and reshapes them, and loads them into a warehouse that reports are built on. The promise is simple: by 6 AM, yesterday is complete, or it is clearly marked as failed.

The hard part is not the computing. It is what happens at 4 AM when one step fails. Can you re-run it safely, without counting orders twice? Will the dashboard ever show half a day? That is what this problem tests.

Words you will meet

OLTP database
The live database that runs the shop, taking orders and payments all day. It is built for many small, quick writes, not for big reports.
Data warehouse
A separate database built for reports, where analysts ask big questions across millions of rows.
ETL
Extract, Transform, Load: copy data out of the source, reshape it, then load it into the warehouse.
Batch
Processing a whole chunk of data at once on a schedule, such as every night, instead of one event at a time.
SLA
A promise about service. Here: yesterday's numbers are ready by 6 AM.
Partition
A slice of a table stored separately, here one slice per order date. You can replace one day without touching the others.
Idempotent
Running the same job twice gives the same final result as running it once. It makes retries safe.
Staging area
A private workspace where data is prepared and checked, hidden from reports until it is ready.
Business day
An agreed definition of which orders belong to which date, for example by the customer's local time. Without it, finance and engineering disagree on totals.
The nightly promise, and the thing that can break it
ordersload6 AMLive orderdatabasebusy all dayNightly jobcopy, tidy, loadWarehousebuilt for reportsOwner at 6 AMopens the report
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.After the day closes, the job copies the day's orders, items, payments and refunds out of the live database.
  2. 2.It reshapes them into report-friendly tables and loads them into the warehouse.
  3. 3.At 6 AM the owner opens the report. If a step failed at 4 AM and was retried badly, the numbers could be missing or counted twice.

The numbers, as things you can picture

  • 2 million orders a dayWith about 5 items each, that is roughly 10 million item rows a day. Large for a spreadsheet, small for a real data engine.
  • About 10 GB a day of raw ordersIf an order record is around 5 KB. A laptop-sized amount of data, so computing power is not the worry.
  • A window of about 5 hoursFrom the day closing to 6 AM. The risk is a slow source or a failed step with no time left to retry.

What the interviewer wants

Design the overnight pipeline, estimate its size and time, show how steps depend on each other, and explain exactly what you do when a job fails at 4 AM.

Interview framing

You are designing a batch ETL system in a data engineering interview. Time box is about 45 minutes. The interviewer cares about the clock, correctness under retries, and whether BI can trust what they see at 6 AM. They do not care whether you memorized every Airflow operator name.

The prompt:

Design a batch pipeline that processes about 2 million orders per day for an e-commerce company and lands curated warehouse tables by 6:00 AM local time for daily reports.

Background from first principles

Batch ETL means you process data in scheduled chunks (often one business day), not as each row arrives. That is different from streaming. At 2M orders/day you are not in firehose territory. If an order JSON is about 5 KB, raw orders alone are near 10 GB/day. A modest Spark or warehouse ELT job can transform that in minutes. The hard parts are dependencies, the definition of yesterday, idempotency, and atomic publish.

Think of three promises:

1. Completeness: for business date D, the warehouse has all orders that belong to D within an agreed late window. 2. Correctness: rerunning the job does not double-count. 3. Visibility: either date D is published and safe for BI, or it is clearly not published and someone is alerted.

Finance treating 6 AM as hard is normal. A missing day with an alert beats wrong revenue that leadership trusts.

Also clarify extract versus load versus transform. Extract copies source data into a raw zone you control. Transform builds analytics shapes. Load or publish makes those shapes visible to BI. Mixing those stages without clear boundaries is how half-written mornings happen.

Expanded problem and constraints

  • Volume: 2M orders/day; assume about 5 line items average so about 10M item rows/day.
  • SLA: critical marts ready by 06:00; page by 05:00 if the run is behind.
  • Sources: orders, order_items, payments, refunds for the completed business day.
  • Load pattern: prefer incremental by date with ability to re-run a single day safely.
  • Quality: checks must pass before BI is allowed to read the new day.
  • Publish: atomic. No half-visible days.
  • Cost: ephemeral compute / spot where reasonable; partition by order_date as history grows.

What good looks like

You define business day and timezone first. You estimate GB and runtime so you do not overbuild a streaming stack. You draw raw zone to transform to staging to atomic publish. You explain what happens when a job fails at 4 AM. You name idempotent keys and a success signal BI can trust.

Strong candidates also name which tables are on the critical path and which can finish later without blocking finance.

Clarifying questions

  • Source of truth: OLTP primary, read replica, CDC topic, or nightly dump?
  • Timezone for business day, and how late orders after midnight are handled?
  • Which reports sit on the critical 6 AM path vs can land later?
  • SCD needs for customers and products?
  • Full refresh vs incremental by date?
  • Who owns schema changes on source tables?
  • Are refunds same-day or can they adjust prior days?

Scale and estimation prompts

  • 2e6 orders x 5 KB is about 10 GB/day orders payload (plus items, payments).
  • Transform runtime: minutes to low tens of minutes on a modest cluster if designed well. The risk is waiting on source readiness and retries, not raw compute.
  • Batch window: if day closes late (stores until 2 AM), extract cannot start at midnight. Count remaining hours to 6 AM.
  • Historical growth: partition facts by order_date; avoid rewriting all history nightly.
  • Rough cost: object storage cents per GB; warehouse storage grows with years of partitions; compute is the dial you turn for retries.

Out of scope for v1

  • Sub-minute freshness for every metric (that is a later CDC or streaming add-on).
  • Rebuilding the entire multi-year history every night.
  • Perfect SCD2 for every attribute on day one.
  • Multi-region active-active warehouses.
  • Replacing the finance mart with a real-time dashboard as system of record.