December 4, 2025
Data Engineering Interview Prep Series — Part 3: ETL & Pipeline Design Questions You Must Master
Real business scenarios testing how you think, design, and deliver scalable pipelines.
By Ankur Gupta
2 min read
Why ETL & Pipeline Design Rounds Are Crucial
Most interviews go beyond SQL + coding. They evaluate whether you can:
- Design scalable systems
- Handle dirty & late data
- Build incremental pipelines
- Ensure data quality & lineage
- Optimize performance and cost
Strong pipeline design ability = hire.
Below are 10 real interview scenarios with guidance on how to answer.
Q1 — Design an Incremental Load for a Daily Orders Feed
Millions of new transactions arrive every day.
What to cover in your answer: ✔ Unique keys for change tracking ✔ Detect new vs updated vs deleted records ✔ UPSERT logic using MERGE ✔ Maintain audit metadata (load timestamp, batch_id)
Mention: Spark or dbt incremental strategy.
Q2 — How to Handle Late-Arriving Data?
Streaming events arrive after their actual event time.
Expected techniques: ✔ Watermarking + windowing (Kafka + Spark Streaming) ✔ Event-time ordering instead of ingestion-time ✔ Reconciliation and backfill strategy
Bonus point: Prevent double-counting using idempotent writes.
Q3 — Design a CDC (Change Data Capture) Pipeline
Source: operational DB continuously changing.
Discuss: ✔ Log-based capture (Debezium, AWS DMS) ✔ Soft deletes & before/after image ✔ Schema evolution handling ✔ Replay & recovery from checkpoints
Interviewer checks event-driven mindset.
Q4 — How to Prevent Duplicate Records in ETL?
Duplicates can appear from retries and source anomalies.
Solutions to mention: ✔ Primary key checks before insert ✔ Dedup logic with windowing ✔ Idempotent load process ✔ Versioning of fact records (SCD approach)
Show awareness of root cause prevention.
Q5 — S3 → Data Lake → Warehouse Pipeline Design
(Cloud data platform question)
Highlight: ✔ Landing zone → Raw → Curated zones ✔ Columnar formats like Parquet ✔ Partitioning by ingestion_date ✔ Orchestration via Airflow/Dagster ✔ Schema registry for consistency
Always connect with lineage + governance.
Q6 — Scaling a Slow PySpark Job
Given: Job taking hours to complete.
Clear strategy: ✔ Reduce shuffle (broadcast, salting for skew) ✔ Optimize partitions (size matters!) ✔ Cache reused datasets ✔ Push filters down ✔ Use Auto-Optimize if in lakehouse
Mention measurement: job execution plan review.
Q7 — How Do You Make ETL Fault-Tolerant?
Failures must not corrupt the data.
Expected patterns: ✔ Retry with exponential backoff ✔ Dead-letter queue for bad messages ✔ Transaction boundaries ✔ Checkpointing for streaming jobs ✔ Alerts + rollback strategies
Bonus: include data quality gates.
Q8 — Schema Evolution Strategy
Data source adds new column tomorrow.
Talk about: ✔ Forward- and backward-compatible schemas ✔ Schema inference vs enforcement ✔ Alerting when schema drift occurs ✔ Column mapping documentation
Tools to mention: Glue schema registry, Iceberg/Delta auto evolution.
Q9 — Real-Time vs Batch: When & Why?
Explain using business value:
Real-time: ✔ Fraud alerts ✔ Personalized offers ✔ Logistics visibility
Batch: ✔ Historical analytics ✔ Cost-efficient bulk loads
Best answer: hybrid (lambda or kappa architecture).
Q10 — Data Quality Automation in Pipelines
Not a one-time check!
Explain: ✔ Null + integrity validation rules ✔ Duplicate + range checks ✔ Data freshness & anomaly detection ✔ Automated pipeline stop on critical failures ✔ Reporting on SLA breaches
Mention Great Expectations or Soda for bonus.
Interviewer Evaluation Criteria
How they score your answers:
✔ Ability to break down the architecture in stages ✔ Performance + cost awareness ✔ Data quality enforcement ✔ Observability and alerts ✔ Clear business impact justification
A strong answer sounds like you're already working there.
Coming Next — Part 4
Streaming + Kafka Interview Questions — Real Coding Tasks & Design Scenarios