Cheat sheetsSheet 3 of 10
Cheat sheet · Sheet 3 of 10
Data Engineering System Design Cheat Sheet
A one-page framework for data engineering system design interviews: requirements, estimates, architecture, storage, processing, reliability, quality and trade-offs.
On this page
The order to talk in
- Clarify requirements: users, questions to answer, freshness, correctness, retention.
- Estimate scale: events/day, bytes/event, peak rate, history size, query concurrency.
- Sketch end to end: sources → ingestion → storage layers → processing → serving.
- Go deep on risk: late data, duplicates, schema change, backfills, skew.
- Reliability and quality: idempotency, checks, alerting, recovery.
- Security and cost.
- Trade-offs and alternatives.
Quick estimates
| Quantity | Rule of thumb |
|---|---|
| Seconds per day | ~86,400 (use 10⁵ for mental maths) |
| 1 M events/day | ~12 events/second on average; plan for peaks of several times that |
| 1 KB × 1 B events | ~1 TB raw before compression |
| Columnar compression | Often several times smaller than raw JSON; measure for your data |
Building blocks
| Need | Options |
|---|---|
| Event transport | Kafka, managed streaming services |
| Database changes | Log-based CDC connectors |
| Raw storage | Object storage |
| Tables | Delta Lake, Apache Iceberg, Apache Hudi, warehouse tables |
| Batch processing | Spark, warehouse SQL, dbt |
| Stream processing | Spark Structured Streaming, Flink |
| Orchestration | Airflow, platform job schedulers |
| Serving | Warehouse/lakehouse SQL, OLAP stores, caches |
Reliability checklist
- Each step rerunnable for a given interval (overwrite or merge).
- Late data policy (watermarks, reprocessing windows).
- Duplicate handling (event ids, upserts).
- Schema evolution policy (additive by default).
- Data-quality gates before publishing.
- Freshness and volume monitoring with alerts.
- Backfill path that does not disturb daily runs.
Trade-offs to name
| Decision | Alternatives |
|---|---|
| Batch vs streaming | Latency vs simplicity and cost |
| ETL vs ELT | Compliance and compute location |
| Normalised vs star schema | Write simplicity vs query simplicity |
| Partition key choice | Pruning vs small files |
| Exactly-once vs at-least-once + idempotency | Complexity vs practicality |
| Managed vs self-hosted | Operations vs control and cost |
Closing
Summarise the design in three sentences, restate the main risk and how you handled it, and say what you would add with more time.