Data engineering interviews lean hard on SQL and on whether your pipelines survive being run twice, late, or out of order. Expect modelling questions with no single right answer. Below are the questions asked most often, each with a model answer. Middle: deeper understanding, optimization, and real-world situations.
1
What is the difference between row-oriented and columnar formats?
Answer
Row formats like CSV or Avro store a record together, which suits writing and reading whole records. Columnar formats like Parquet and ORC store each column together, so a query touching three columns of fifty reads only those three. Columnar also compresses far better, because a column holds similar values. The trade-off is that writing a single row is expensive, which is why columnar is for analytics and not for transactions.
2
What does it mean for a batch job to be idempotent, and how do you make an insert one?
Answer
Idempotent means running it twice leaves the same result as running it once — which matters because retries happen, whether from a scheduler, a failure or a human. For an insert, the usual answers are a merge or upsert on a business key, deleting the target partition before writing it, or a unique constraint that makes the duplicate fail harmlessly. A plain INSERT in a retried job silently doubles the data.
3
What is the difference between a star and a snowflake schema?
Answer
In a star, dimensions are denormalised into single tables joined directly to the fact. In a snowflake, dimensions are normalised into further tables. The star needs fewer joins and is faster to query; the snowflake saves storage and avoids update anomalies. Analytics usually chooses the star, because storage is cheap and analyst time is not.
4
What is lazy evaluation in Spark, and what is the difference between a transformation and an action?
Answer
Transformations — map, filter, join — build a plan and execute nothing. An action — count, collect, write — triggers the whole plan. Laziness lets the optimiser see the entire chain and rearrange it, pushing filters down and collapsing steps. The practical consequence is that timing a transformation measures nothing, and the error you expected at line three appears at the action.
5
What is the difference between a wide and a narrow dependency, and why does it matter?
Answer
In a narrow dependency each input partition feeds one output partition — a map or filter, computable on the node that holds the data. A wide dependency requires data from many partitions, which means a shuffle across the network. Shuffles dominate the runtime of most jobs, so a groupBy or a join is where you look first when something is slow.
6
When would you use a broadcast join?
Answer
When one side is small enough to fit in each executor's memory — a dimension table against a large fact table. Broadcasting it removes the shuffle entirely, which is usually the difference between minutes and hours. The limit is memory: broadcast something too large and every executor pays for it, so the threshold is a setting worth knowing rather than guessing.
7
What is Change Data Capture and when do you use it instead of a full reload?
Answer
CDC reads the source database's own change log and streams inserts, updates and deletes rather than re-reading the whole table. You use it when the table is large enough that a full reload does not fit the window, or when you need the changes themselves — deletes in particular, which a full snapshot comparison detects only by absence.
🦎
Reading answers is not enough
In a real interview you speak under pressure. Cam asks these same questions, scores every answer, and shows exactly what to fix.
Practice a Middle Data Engineer interview →Free · 3 interviews per month