# System Design: A Daily Batch Pipeline, Step by Step

> A repeatable answer for the most common data engineering design question: requirements, architecture, data model, failure handling and monitoring.

- Source: https://akshaythakare.com/notes/system-design/designing-a-daily-batch-pipeline
- Topic: Data Engineering System Design
- Level: intermediate
- Reading time: 2 min
- Tags: system-design, batch, architecture, interview
- Updated: 2026-10-11

## 1. Clarify requirements (2 minutes)

- What metrics, for whom, by when? ("Store sales by 7 am")
- Sources, volumes and growth (rows per day, number of stores).
- Freshness, correctness and history needs.
- Can late or corrected data arrive?

## 2. Draw the architecture

```text
Sources (OLTP, APIs, files)
   → Ingest (incremental extract)      → raw zone (immutable, per run)
   → Validate (counts, schema, checksums)
   → Transform (clean, dedupe, join)   → curated zone (partitioned Parquet)
   → Load / MERGE                      → warehouse marts
   → BI / APIs
Orchestrator + run ledger + alerts around all of it
```

## 3. Data model

State the **grain** of each fact ("one row per order line"), name the dimensions, and say which need history (SCD2).

## 4. Correctness guarantees

| Risk | Design answer |
| --- | --- |
| Retries and replays | Idempotent writes: MERGE or partition overwrite |
| Duplicates | Dedupe on business key + version |
| Late data | Extract by arrival time; recompute affected partitions |
| Partial failure | Single commit per publish; watermark moves only on success |
| Bad data | Validate before transform; quarantine and alert |

## 5. Operations

- Monitoring: freshness, row counts against the source, failed runs.
- Backfills: the same code with a date range.
- Cost: partition pruning, right-sized compute, lifecycle on raw data.

## 6. Trade-offs to mention

Batch vs streaming, ETL vs ELT, build vs managed services. Pick one, justify it with the requirements, and say what would change your mind.

> **Tip: What interviewers listen for**
>
> Grain, idempotency, late data and how you know the numbers are right — not the list of tools.