Data Engineer interview questions

Data Engineer interviews are the most systems-heavy in data: advanced SQL, data modeling, and pipeline design, and they probe failure modes rather than happy paths. Expect to be asked what happens when a job re-runs, when events arrive late, or when the data does not fit in memory. Real questions with worked answers below.

How a Data Engineer interview runs

How to prepare for a Data Engineer interview

What the loop actually tests

The most systems-heavy loop in data. It tests advanced SQL, Python for data processing, data modeling, and pipeline design, and in every round it probes the failure mode rather than the happy path. What happens when the job reruns. When events arrive late. When the file does not fit in memory. When two versions of a customer exist for the same day. Candidates who have only run pipelines that worked struggle here.

The rounds, in order

Recruiter screen, then a SQL screen that goes further than analyst SQL: window functions, gaps and islands, deduplication with a rule, slowly changing dimensions. The onsite adds a Python round (process a large file, sessionize events, parse and validate records), a data modeling round (design a warehouse for a business), a pipeline and system design round (build the ingestion for this source, make it idempotent, handle late data), and a behavioral session.

What interviewers score

SQL: correct results at the right grain, and whether you noticed the duplicates. Python: memory awareness, streaming rather than loading, clear handling of bad records. Modeling: a star schema with the grain stated per fact table, the dimensions chosen for the questions the business asks, and a plan for changing attributes. Design: idempotent reruns, backfills, late-arriving data, partitioning, and what the on-call engineer sees when it breaks.

Saying the word idempotent unprompted, and meaning it, is worth more than any tool name.

How to prepare, and what to do first

Start with the SQL patterns that distinguish engineer rounds from analyst ones: gaps in a sequence, consecutive-day streaks, SCD Type 2 joins on a date range, deduplication keeping the latest. Then pick two pipelines you have built and rehearse them as design answers with the failure modes named. Then modeling: design a warehouse for an e-commerce company out loud, grain by grain.

The Python round is usually about a file too big for memory. Practice chunked reading, generators, and the sessionization problem, and be ready to say when you would reach for Spark or DuckDB instead.

Where candidates lose the offer

An append-only pipeline that double-counts on the first retry. A warehouse design with no stated grain. A Python answer that starts with reading the whole file into a list. And answering a failure-mode question with the happy path again, more slowly.

12 Data Engineer interview questions

2. fact_orders has an order_ts. dim_customer is SCD Type 2 with valid_from/valid_to. Join each order to the customer attributes that were current when the order was placed.

A range join, not an equality join: ON o.customer_id = d.customer_id AND o.order_ts >= d.valid_from AND o.order_ts < d.valid_to. The half-open interval is what stops an order matching two versions on a boundary. Joining on the customer key alone, or filtering d.is_current = true, silently reports today's attributes for historical orders, the classic SCD mistake, and the reason last year's revenue-by-segment number quietly changes.

3. A daily batch job loads into a target table. Make re-running it for the same day safe, with no duplicates.

MERGE on a stable business key, or delete-then-insert scoped to that day's partition inside one transaction. The job has to be idempotent rather than append-only: keyed on something like (entity_id, load_date) so a re-run replaces its own previous output instead of stacking on top of it. INSERT-only pipelines look correct until the first retry, and then every downstream metric double-counts.

6. You need to process a 50GB CSV on a machine with 16GB of RAM. How?

Stream it instead of loading it: read_csv with chunksize, or hand it to an engine that spills to disk and reads only the columns you touch (DuckDB, Polars lazy, Spark). If it is a recurring job, convert once to Parquet, columnar plus compression usually cuts both IO and memory by an order of magnitude. The general move is to make the working set fit in memory, not the file.

10. A nightly pipeline fails halfway through. How do you design it so re-running is safe?

Make each step idempotent and partition-scoped: a run owns a date partition and fully replaces it, so re-running converges instead of accumulating. Checkpoint at partition boundaries rather than per row, recovery granularity should match the unit you can safely recompute. Side effects that cannot be repeated (incrementing a counter, sending an alert, charging something) either move outside the retryable path or get keyed and deduplicated.

11. Events can arrive up to 48 hours late. How do you handle that in a daily aggregation?

Partition by event time, not ingestion time, and reprocess a trailing window, re-run the last few days each night so late events land in the day they actually belong to. The alternative is a watermark plus published corrections. Either way, state the tradeoff out loud: you wait and are complete but late, or publish early and restate. Bucketing by ingestion time is what makes the warehouse and the source system disagree, and nobody can tell you why.

12. When would you denormalize a warehouse table, and what do you give up?

Denormalize when read patterns are stable and join cost dominates, wide fact tables or pre-joined marts, so analysts are not writing six joins for every question. You trade storage, which is cheap, for write complexity, which is not: one source change now has to update several places, so consistency becomes your problem instead of the database's. Normalize what changes often, denormalize what is read often.

Practice this for real