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. Senior: architecture, trade-offs, mentoring, and decision-making.
1
What are window functions and where do they beat a GROUP BY?
Answer
A window function computes over a set of rows related to the current row without collapsing them, so you keep row-level detail alongside the aggregate. That makes them the right tool for running totals, ranking within a partition, comparing a row to the group average, and lag/lead comparisons between consecutive events. A GROUP BY would force you to aggregate and then join back to the detail — more code, more shuffling, and easier to get wrong.
2
What does it mean for a pipeline to be idempotent, and why does it matter?
Answer
Idempotent means running the same task for the same period twice produces the same result rather than duplicating data. It matters because retries are inevitable — a task fails halfway, someone reruns yesterday, a backfill overlaps a scheduled run. The usual implementations are deleting and rewriting the target partition, or a merge keyed on a business key rather than a blind insert. Pipelines that only work when run exactly once break the first time they are retried.
3
How do you design an Airflow DAG that is safe to backfill?
Answer
Each task should be parameterised by the logical execution date rather than "now", so a run for an old date reads and writes that date's data. Tasks must be idempotent, so a rerun overwrites its own partition. Set sensible retries with backoff, and use max_active_runs and pools so a backfill of two years does not saturate the warehouse and starve production runs. Dependencies between DAGs are safer expressed as data availability checks than as guessed schedules.
4
What is a shuffle in Spark and why is it expensive?
Answer
A narrow transformation like map or filter works within a partition, so it needs no data movement. A wide transformation like groupBy, join or repartition requires rows with the same key to end up on the same executor, which means writing intermediate data to disk and moving it across the network — that is a shuffle. It dominates job runtime, so tuning means reducing shuffles: broadcasting a small side of a join, filtering before joining, and choosing partitioning that matches how you query.
5
What is data skew and how do you deal with it?
Answer
Skew is when one key holds a disproportionate share of rows, so a single task processes most of the data while the rest of the cluster idles — the job looks 99% done for an hour. You spot it in the Spark UI as one task with far larger input than its peers. Remedies include salting the hot key to split it across partitions, broadcasting the smaller table to avoid the shuffle join entirely, and handling known hot keys separately from the long tail.
6
Why Parquet rather than CSV or JSON, and what does partitioning add?
Answer
Parquet is columnar and compressed, so a query reading three of forty columns reads only those columns, and it carries a schema and column statistics that let engines skip row groups entirely. CSV and JSON force a full scan and re-parse every time. Partitioning by a column you filter on, usually date, lets the engine skip whole directories, but over-partitioning creates millions of tiny files and the metadata overhead then costs more than it saves.
7
What does exactly-once processing really mean in streaming?
Answer
True exactly-once delivery is not achievable end to end; what systems provide is effectively-once processing, meaning duplicates may be delivered but the observable result is as if each message counted once. That is achieved with idempotent writes, deduplication on a message key, or transactional sinks that commit offsets and output atomically. Claiming exactly-once without saying which mechanism makes it true is a common interview red flag.
8
How do you handle late-arriving data?
Answer
First decide the semantics: is the record assigned to the time the event happened or the time you received it? Event time is usually correct for analytics but requires the pipeline to reopen and rewrite an already-published partition. Watermarks define how long you wait before treating a window as closed, and anything later goes to a correction path or a dedicated late-arrivals table. The design mistake is silently dropping late data, because the numbers then differ from source and nobody knows why.
9
How do you test data quality, and what do you check?
Answer
Tests run as pipeline steps that fail loudly rather than as dashboards nobody reads. The standard checks are freshness, row-count volume against expectations, uniqueness of keys, not-null on required columns, referential integrity between fact and dimension, and accepted ranges or enumerations. Tools like dbt tests or Great Expectations formalise this. The important design decision is which failures block downstream tasks and which only alert, because blocking on every anomaly trains people to ignore the alerts.
🦎
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 Senior Data Engineer interview →Free · 3 interviews per month