Real questions on Extract, Transform, Load-the core of data engineering. Covers pipeline design, ingestion patterns, batch vs streaming, and data quality.
ETL (Extract, Transform, Load) is fundamental to every data engineering role. These questions cover pipeline design, incremental vs full loads, idempotency, late-arriving data, batch vs streaming ingestion, transformation patterns, data quality checks, and SCD (slowly changing dimension) implementations. Prepare for the ETL deep-dives that interviewers consistently ask.
This collection contains 50 curated questions: 9 easy, 18 medium, and 23 hard. The distribution skews toward harder problems, reflecting the depth expected in senior-level interviews.
The most frequently tested areas in this set are partition (31), spark (23), join (17), optimization (10), etl (9), and python (9). Focusing on these topics will give you the highest return on your preparation time.
Start with the easy questions to warm up and solidify fundamentals. Medium-difficulty questions form the bulk of real interviews — spend the most time here and practice explaining your reasoning out loud. Hard questions often appear in senior and staff-level rounds; attempt them after you're comfortable with the basics. For each question, try answering before revealing the solution. Use our AI Mock Interview to simulate real interview conditions and get instant feedback on your responses.
Tell me about yourself and your experience.
What architecture are you following in your current project, and why?
What is the difference between narrow and wide transformations in Apache Spark? Explain with examples.
CDC During Migration - explain approaches for real-time Change Data Capture
Tell me about your family background
What strategies can you use to handle skewed data in Spark?
Briefly introduce yourself and walk us through your journey as a Data Engineer so far.
Explain the difference between Spark's map() and flatMap() transformations.
Explain the types of triggers in ADF, including schedule, tumbling window, and event-based triggers.
Architect incremental load in ADF + Databricks with idempotency, late-arrival handling, and cost/scalability implications of watermark vs. change data capture.
Architecturally, how do Job–Stage–Task boundaries in Spark's execution model impact cluster sizing, shuffle cost, and when would you deliberately collapse or split stages?
Architecturally, how would you justify or challenge Hadoop vs. a cloud-native data lake (S3 + EMR/Databricks) for a greenfield enterprise data platform? Discuss scalability ceilings, cost model trade-offs, and operational complexity.
Convert complex SQL (CTEs, window functions, subqueries) to production-grade PySpark. Discuss when to use spark.sql() vs. DataFrame API, and the implications for testability, partitioning, and execution predictability.
Describe the data pipeline architecture you've worked with.
Design a cost-aware resource strategy for a Databricks workload with spiky and batch jobs. Explain Dynamic Resource Allocation, when to disable it, and how min/max executors and spot instances affect cost and SLAs.
Design a Delta table layout for mixed workload: point lookups by user_id, range scans by date, and full partition scans. Compare partitioning vs. Z-ordering—when to use each, and the rewrite cost trade-off.
Explain Common Table Expressions (CTEs) and their benefits.
Explain strategies for managing schema changes in PySpark over time.
Explain the benefits of using DataFrames over RDDs.
Explain the concept of checkpointing in Spark and why it is important.
Explain the difference between Azure Data Factory (ADF) and Databricks.
Explain the difference between batch and streaming data processing in Data Fusion.
Explain the trade-offs between batch and real-time data processing. Provide examples of when each is appropriate.
Explain the use of the MERGE statement in SQL.
Explain wide vs. narrow transformations and how they drive shuffle cost, failure domains, and pipeline design. When would you intentionally add a wide transformation, and how do you minimize its impact?
Given a streaming dataset from Kafka, how would you ingest the data in real-time using Spark?
Have you worked on Data Warehousing projects?
How do you handle conflicts within a team? Provide an example.
How do you handle data skewness in Spark?
How do you handle exceptions in Python? Provide an example.
How do you handle memory management in Python?
How would you handle duplicate records in an SQL table?
How would you read data from a web API? What steps would you follow after reading the data?
Tell me about a time when you faced a challenging situation at work and how you handled it.
Triggers in ADF, especially tumbling window triggers.
What are the key components of AWS Glue, and how do they work together?
What are the key components of the Spark execution model (Job, Stage, Task)?
What challenges did you face, and how did you tackle them?
What is Azure Data Factory (ADF), and what are its main components?
What is normalization and denormalization? When would you use each?
What is the difference between a generator and a list in Python?
What is the difference between OLTP and OLAP?
What is the difference between S3 and HDFS?
What is the difference between Spark RDDs, DataFrames, and Datasets?
What is the purpose of the Bronze, Silver, and Gold layers in a data pipeline?
What is the role of AWS Lambda in a data engineering pipeline?
What is the role of the Integration Runtime (IR) in ADF?
What is the small-file problem in Spark, and how do you solve it?
What would you do if a pipeline failed and you couldn't find the reason?
When would you architecturally choose Dataset[T] over DataFrame in a Scala Spark pipeline, and what are the scalability and portability trade-offs? Include type-safety benefits vs. operational constraints.
ETL transforms data before loading it into the destination (common with on-prem warehouses and fixed schemas). ELT loads raw data first, then transforms inside the warehouse using its compute (the modern cloud pattern with Snowflake, BigQuery, Databricks, and dbt). ELT wins when storage is cheap and the warehouse is powerful, because it keeps raw data for reprocessing and pushes transformation into scalable SQL.
An idempotent pipeline produces the same result no matter how many times it runs for a given input. Achieve it with deterministic partition overwrites (overwrite a date partition instead of appending), MERGE/upsert on a stable business key, and delete-then-insert per batch window. This lets you safely retry failed runs and backfill without creating duplicates — a question interviewers ask constantly.
A full load reprocesses the entire source every run — simple and self-healing but expensive and slow at scale. An incremental load only processes new or changed records since the last run, tracked with a watermark (a high-water-mark timestamp or monotonic ID) or change data capture (CDC). Incremental is the default for large tables; keep a periodic full reconciliation to catch missed changes.
Late-arriving data is records that show up after their event window has been processed. Handle it by processing on event time (not ingestion time), keeping a reprocessing window that re-runs the last N days, using watermarks in streaming engines to bound lateness, and designing idempotent upserts so reprocessing corrects earlier results instead of duplicating them.
Slowly Changing Dimension Type 2 preserves full history by adding a new row each time a tracked attribute changes, rather than overwriting. Implement it with effective_from / effective_to dates and an is_current flag: when a change arrives, close the current row (set effective_to and is_current = false) and insert a new active row. A MERGE statement handles both the close and the insert in one step.
The Data Engineering Interview Answer Vault bundles 750+ reviewed answers into 7 focused PDF volumes — SQL, Spark, Python, System Design, Cloud, Behavioral, and Data Modeling. Study on any device, no subscription required.
800+ hands-on courses — Grokking System Design, Coding Patterns, and AI mock interviews for your DE loop.
Turn any topic or your own notes into an interactive, personalized course in 60 seconds.
The book that gets data engineers through system-design rounds. Essential reading.
Some links below are affiliate links. If you buy through them we may earn a small commission at no extra cost to you — it helps keep DataEngPrep free.
Reading answers is step one. Get instant AI feedback on your answers, run mock interviews, and track readiness — built specifically for data engineering interviews.