Data Engineer Interview Questions and Answers
Last updated 2026-11-19
Data engineer interviews test whether you can move data reliably at scale. Expect SQL that goes beyond joins (gaps and islands, sessionization, deduplication), questions on pipeline design and failure handling, and tool knowledge such as Spark, Kafka and Airflow. Coding is usually Python and SQL.
The answers below give precise reasoning and code you can adapt. If your SQL is the weak spot, work through how to crack the SQL interview and the SQL window functions questions first, then return here.
| Typical rounds | SQL and Python test, pipeline design, tools round, behavioural |
|---|---|
| Core skills | SQL, Python, Spark, Kafka, Airflow, warehousing |
| Questions here | 13 worked questions |
What interviewers listen for
- Idempotency: can the job be re-run without duplicates?
- Failure handling: retries, partial writes, backfills and late data.
- Cost and scale thinking: file sizes, partitions, shuffles and data skew.
- Data quality: checks at the boundaries, not only at the end.
- Trade-offs stated plainly, for example latency versus cost.
A pipeline design answer in five steps
State the source, volume and freshness need. Choose batch or streaming from the freshness need, not from habit. Describe storage layout (raw, cleaned, modelled layers) and partitioning. Describe orchestration, retries and monitoring. Finish with how you would backfill and how you would detect bad data.
Data Engineer Interview Questions and Answers: questions with worked answers
1 What is the difference between OLTP and OLAP systems?
easyOLTP systems run the live application: many small reads and writes on a few rows, normalised tables, strict transactions, for example an orders database. OLAP systems serve analysis: few large read-heavy queries scanning many rows and aggregating a few columns, usually denormalised or star-schema tables in columnar storage. Data engineers move data from OLTP sources into OLAP stores, and running heavy analytics directly on the OLTP database risks slowing the application.
2 ETL versus ELT: when would you choose each?
easyETL transforms data before loading it, which suits small volumes, strict masking requirements before data lands, or a target with limited compute. ELT loads raw data first and transforms inside the warehouse or lakehouse, which suits cloud warehouses with elastic compute, keeps the raw data for reprocessing and lets analysts own SQL transformations. Most modern stacks use ELT with tools like dbt, but sensitive fields may still be masked on the way in.
3 What does it mean for a pipeline to be idempotent, and how do you achieve it?
mediumRunning the job twice for the same input gives the same final state as running it once. Achieve it by writing to a partition with overwrite instead of append, by upserting on a natural or surrogate key with MERGE, or by deleting the target date range inside the same transaction before inserting. Avoid using the current timestamp inside the transformation logic, pass the run date in as a parameter. This is what makes retries and backfills safe.
sqlMERGE INTO dim_customer t USING staging_customer s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET email = s.email, updated_at = s.updated_at WHEN NOT MATCHED THEN INSERT (customer_id, email, updated_at) VALUES (s.customer_id, s.email, s.updated_at);4 Remove duplicate rows from an events table, keeping the latest row per event_id.
mediumNumber the rows within each event_id by ingestion time descending and keep row 1. This works on any warehouse that supports window functions. Mention the tie-break: if two rows share the same timestamp, add another column to the ORDER BY so the result is deterministic.
sqlSELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC, batch_id DESC) AS rn FROM events e ) t WHERE rn = 1;5 Assign session ids to click events, where a gap of more than 30 minutes starts a new session.
hardCompare each event with the previous event of the same user using LAG, flag a new session when the gap exceeds 30 minutes or there is no previous event, then take a running sum of the flags per user. The running sum is the session number. The syntax below is PostgreSQL; other databases use DATEDIFF or TIMESTAMPDIFF for the gap.
sqlWITH flagged AS ( SELECT user_id, event_time, CASE WHEN LAG(event_time) OVER w IS NULL OR event_time - LAG(event_time) OVER w > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS new_session FROM clicks WINDOW w AS (PARTITION BY user_id ORDER BY event_time) ) SELECT user_id, event_time, SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_no FROM flagged;6 Find each user's longest streak of consecutive login days (gaps and islands).
hardTake distinct login dates, number them per user with ROW_NUMBER and subtract that number of days from the date. Consecutive dates give the same result, so the result works as a group key for each streak. Group by it and count. For dates 1, 2, 3, 5, 6 the row numbers are 1 to 5 and the date minus row number is 0, 0, 0, 1, 1 days (day 1 minus 1, and so on), giving two groups of size 3 and 2. The SQL is PostgreSQL style.
sqlSELECT user_id, MIN(login_date) AS start_day, MAX(login_date) AS end_day, COUNT(*) AS days FROM ( SELECT user_id, login_date, login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS INT) AS grp FROM (SELECT DISTINCT user_id, login_date FROM logins) d ) g GROUP BY user_id, grp ORDER BY days DESC;7 Why is Parquet preferred to CSV for analytics?
easyParquet stores data by column, with compression and per-column statistics. A query that reads three columns out of fifty scans only those columns, and row groups can be skipped using min and max statistics (predicate pushdown). It also stores a schema and types, so there is no ambiguity about dates or numbers. CSV is row based, has no types, no built-in compression and must be fully parsed. Parquet is slower to write and not human readable, which is acceptable for analytical storage.
8 How should you choose partition columns, and what is the small-files problem?
mediumPartition by a column that queries filter on and that has moderate cardinality, usually a date. Too many partitions, for example one per user id, create millions of tiny files. Each file adds metadata and task overhead, and reads become slow. As a guide, aim for files of roughly 128 MB to 1 GB. A 500 GB dataset at 128 MB per file is about 500 x 1024 / 128 = 4,000 files. Fix small files by compaction jobs or by repartitioning before the write.
9 How does Kafka preserve ordering and scale consumers?
mediumA topic is split into partitions, and ordering is guaranteed only within a partition. Messages with the same key go to the same partition, so all events of one user stay in order. Within a consumer group, each partition is read by exactly one consumer, so parallelism is capped by the partition count: with 6 partitions and 8 consumers, 2 consumers sit idle. Each consumer tracks an offset per partition; committing it after processing gives at-least-once delivery, so consumers must be idempotent.
10 Explain at-least-once, at-most-once and exactly-once delivery.
mediumAt-most-once never retries, so messages can be lost. At-least-once retries until acknowledged, so messages are never lost but may be duplicated. Exactly-once means each message affects the result once. In practice you get it by at-least-once delivery plus idempotent writes or transactional writes, such as Kafka transactions with an idempotent producer, or a sink that upserts by key. Claiming exactly-once without naming the mechanism is a red flag in an interview.
11 Write a minimal Airflow DAG that runs a daily extract, transform and load.
mediumDefine the DAG with a daily schedule, a start date and catchup behaviour, create one task per step and chain them. Use the logical date passed by the scheduler so a rerun for a past day processes that day, not today. Set retries in default arguments. Keep the heavy work in the external system and keep tasks small and idempotent.
pythonfrom datetime import datetime, timedelta from airflow import DAG from airflow.operators.python import PythonOperator def extract(ds, **_): print('extract', ds) def transform(ds, **_): print('transform', ds) def load(ds, **_): print('load', ds) with DAG('daily_sales', start_date=datetime(2026, 1, 1), schedule='@daily', catchup=False, default_args={'retries': 2, 'retry_delay': timedelta(minutes=5)}) as dag: t1 = PythonOperator(task_id='extract', python_callable=extract) t2 = PythonOperator(task_id='transform', python_callable=transform) t3 = PythonOperator(task_id='load', python_callable=load) t1 >> t2 >> t312 How do you handle late-arriving data in a streaming job?
hardUse event time, not processing time, and a watermark that says how long to wait for stragglers, for example 10 minutes. Events later than the watermark are either dropped, routed to a side output, or corrected by a periodic batch reprocess of the affected partitions. The choice depends on the accuracy requirement. State a trade-off: a larger watermark gives more accurate windows but higher latency and more state to hold.
13 What data quality checks would you add to a pipeline?
mediumCheck at each layer boundary: row counts against the source within a tolerance, not-null and uniqueness on keys, referential integrity between facts and dimensions, accepted value ranges, freshness (latest timestamp not older than the SLA) and schema checks for added or dropped columns. Fail loudly on critical checks and only warn on soft ones. Store check results so trends are visible, because a slow decline in volume is often the first sign of a broken source.
Frequently asked questions
Which language should I prepare for data engineering interviews?
Python and SQL are near universal. Scala or Java appear for Spark-heavy roles. Be able to write clean SQL by hand.
Do data engineers face DSA rounds?
Often a light round with arrays, hash maps, strings and sorting, plus SQL. Heavy graph or dynamic programming problems are uncommon but possible at product companies.
How do I prepare for system design for pipelines?
Practise designing a clickstream pipeline and a daily warehouse load end to end, naming storage, partitioning, orchestration, failure handling and monitoring.
Is Spark knowledge compulsory?
For most data engineering roles in India, yes at a basic to intermediate level. See the PySpark questions page for joins, shuffles and skew.
Related guides
More in Role-wise Interview Questions by Job and Tech Stack
- Data Scientist Interview Questions and Answers
- Deep Learning Interview Questions and Answers
- DevOps Interview Questions and Answers
- Django Interview Questions and Answers (Django 5)
- Docker Interview Questions and Answers
- Ethical Hacking Interview Questions and Answers
- ETL and Data Warehousing Interview Questions and Answers
- Excel Interview Questions and Answers
- Express.js Interview Questions and Answers (Express 5)
- FastAPI Interview Questions and Answers
- Flask Interview Questions and Answers
- Flutter Interview Questions and Answers (Flutter 3, Dart 3)