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.
Data Engineering System DesignIntermediate2 min readUpdated 11 Oct 2026
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
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 it3. 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.
Finished this note?