How to Prepare for a Data Engineer Interview

“How would you design a pipeline to ingest data from a production database into the warehouse?”

What they're really asking: Do you reach for a nightly full dump by default, or do you reason about freshness, load on the source, and change capture?

How to answer: Start by asking who consumes the data and how fresh it must be, and how large the tables are. Then present the options honestly: full snapshot for small slowly changing tables, incremental by updated_at where you trust it, log-based change data capture where you need low latency or hard deletes. Name the failure cases you are covering — missed deletes, clock skew, schema changes — and say how you would verify correctness with row counts and checksums.

“A daily job failed overnight and no one noticed until an executive dashboard was wrong. Walk me through what you do.”

What they're really asking: Do you distinguish immediate remediation from the systemic gap, and do you think about communication to consumers?

How to answer: Sequence it: confirm the blast radius and which downstream tables are stale, tell the affected consumers before they discover it themselves, then restore correct data via a rerun or backfill. Only then move to root cause. Finish with the systemic fix — a freshness check that alerts on the table rather than the job, since the job succeeding is not the same as the data being right.

“This query returns more rows than expected after a join. How do you diagnose it?”

What they're really asking: Do you reason about grain, or do you add DISTINCT and move on?

How to answer: State the expected grain of the result, then check the cardinality of the join key on each side with a group-by count. Identify which side fans out and decide whether the fan-out is legitimate — in which case you aggregate before joining — or a data quality problem such as duplicate keys. Explicitly say that adding DISTINCT hides the problem rather than fixing it.

“How do you make a pipeline safe to rerun?”

What they're really asking: Whether you understand idempotency, which is the single most useful property in day-to-day operations.

How to answer: Define idempotency plainly, then give the mechanisms: deterministic partitioning so a rerun overwrites exactly the affected partition, merge on a stable business key rather than blind inserts, and deduplication on an event id where the source can deliver twice. Mention what breaks it — appending without keys, side effects like sending emails inside a transform, and non-deterministic timestamps generated at run time.

“How do you decide between batch and streaming?”

What they're really asking: Whether you choose based on requirements or on what sounds impressive.

How to answer: Anchor on the consumer's decision latency: if nothing changes because data is five minutes newer, batch is the right answer. Then weigh operational cost — streaming systems carry state, ordering and replay complexity that a small team may not want to carry. Give a case where you would genuinely choose streaming, such as fraud checks or operational alerting, and note the micro-batch middle ground.

“How do you know your data is correct?”

What they're really asking: Whether data quality is a discipline you practise or a word you use.

How to answer: Describe layered checks rather than one tool: schema and not-null constraints at ingestion, uniqueness and referential tests on the models, freshness and volume anomaly checks on the outputs, and reconciliation against the source system for the numbers that matter most. Say who receives the alerts and what happens when one fires, because checks nobody owns are decoration.