Top 15 Data Engineering System Design Mistakes
Most interviews are not lost on the clever part. They are lost on the boring part: a candidate designs a slick streaming pipeline and never says the word "replay," or draws a beautiful diagram whose state grows forever. Interviewers are not hunting for genius. They are checking whether you have been on call for a pipeline at 3 a.m. These fifteen are what they probe for.
Read this as a pre-interview checklist. If your design does not have an answer for each, you have a gap the interviewer will find.
Ingestion and correctness
1. Polling the source instead of reading the log. Running SELECT * WHERE updated_at > ... every few minutes hammers the primary, misses deletes, and races with in-flight writes. Fix: log-based CDC reads the database's own write-ahead log, at near-zero query cost. See Design a CDC Pipeline.
2. No idempotency. Any retry, replay, or at-least-once delivery will deliver the same record twice, and your totals double-count. Fix: make writes idempotent on a stable key (upsert, dedup window, or idempotent merge) so reprocessing is safe by construction.
3. Treating exactly-once as free. "Exactly-once" is not a checkbox; it is a budget of coordination (transactions, offsets committed with output, or idempotent sinks). Fix: say which mechanism you are buying it with, or design for at-least-once plus idempotency and get the same result more cheaply.
4. Dual writes. Writing to the database and the message queue in application code means one can succeed while the other fails, and now they disagree forever. Fix: one source of truth plus CDC or the transactional outbox pattern, so the second write is derived, not independent.
State, scale, and skew
5. Unbounded state. A streaming join or aggregation that keeps every key forever runs fine in the demo and OOMs in production. Fix: bound state with windows, TTLs, or watermarks, and say what happens to late data past the bound.
6. Ignoring data skew and hot keys. One celebrity user, one popular product, or one null key sends 90% of traffic to one partition, and that partition becomes the whole system's latency. Fix: name the hot-key risk and a mitigation (salting, two-phase aggregation, key isolation).
7. Designing for the average, not the peak. Average QPS is a comfort number. The system falls over at peak, which is 3-10x higher and spikier. Fix: size partitions, buffers, and autoscaling for peak, and state your peak-to-average multiple.
Operability
8. No backfill or replay plan. The first question after "does it work" is "what happens when it was broken for six hours, or the logic had a bug last week?" Fix: design reprocessing from the start: idempotent writes plus a retained, replayable log make backfill a rerun, not a rescue.
9. No dead-letter path. One malformed record blocks the stream, or worse, gets silently dropped. Fix: route unparseable or failing records to a dead-letter queue with enough context to fix and replay them.
10. Silent data loss. A pipeline that drops records without counting them is worse than one that crashes, because nobody notices until finance does. Fix: count in and count out, alert on the gap, and reconcile against the source.
11. No schema evolution story. Producers add a column and every downstream consumer breaks, or an incompatible change ships and corrupts history. Fix: a schema registry with compatibility rules; additive changes flow, breaking changes are coordinated.
Modeling and process
12. The small-files problem. Writing millions of tiny files makes column statistics useless and every query pay per-file overhead. Fix: a compaction strategy and a target file size of 128 MB-1 GB. See Key DE Numbers.
13. No data quality or reconciliation check. "The pipeline ran" is not "the data is right." A green DAG can produce wrong numbers. Fix: freshness, volume, and null/uniqueness checks, plus a reconciliation that proves the output matches the source.
14. Skipping requirements gathering. Jumping to a diagram before pinning down freshness, volume, correctness, and access load means you design the wrong system precisely. Fix: spend the first few minutes on scope and functional/non-functional requirements. See the design framework.
15. Over-engineering. Reaching for Kafka, Flink, and a lakehouse when a scheduled batch job and a warehouse would do. Complexity you cannot operate is a liability, not a signal of seniority. Fix: start with the simplest thing that meets the freshness and scale requirement, and add machinery only when a requirement forces it.
Notice the pattern: almost every mistake is a failure to plan for the second time the data flows through, whether that is a retry, a replay, a backfill, or a bad record. Senior data engineers design for reprocessing first. If your answer to "what happens when this breaks and we rerun it" is clean, you have avoided half of this list at once.
Ready to apply the checklist? Work any problem in the catalog, starting with the free ones.