Data engineering interview questions for freshers (with what a good answer covers)

Below are seventeen questions that come up again and again, grouped by area. For each one we list what a good answer covers. Use these as a checklist, not a script: an interviewer will follow up on whatever you say, so you need to understand the point, not memorise the sentence.

SQL

SQL is the one area every data engineer interview tests, and usually first. Expect to write queries, not just describe them.

1. What happens to NULLs and duplicate keys in a join?

What a good answer covers:

• NULL = NULL is not true, so rows with a NULL join key never match. An inner join drops them; a left join keeps left-side rows with NULLs in the right-side columns.
• Duplicate keys multiply rows. If a key appears 3 times on the left and 2 times on the right, the join returns 6 rows for it. This silently inflates a SUM or COUNT later.
• A filter on a right-table column in the WHERE clause turns a left join into an inner join. Put that condition in the ON clause if you want to keep unmatched rows.

2. Find the second-highest salary in each department

SELECT department_id, employee_id, salary
FROM (
  SELECT department_id, employee_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

What a good answer covers:

• Why DENSE_RANK: if two people share the top salary, both get rank 1 and the next distinct salary gets rank 2. RANK would skip to 3, and ROW_NUMBER would wrongly call one of the tied people "second".
• A department with only one distinct salary returns no row, and you should say so.
• Window functions cannot go in WHERE directly, which is why the subquery exists.

3. Deduplicate a table, keeping only the latest row per key

SELECT *
FROM (
  SELECT c.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY updated_at DESC, ingested_at DESC
         ) AS rn
  FROM customers_raw c
) t
WHERE rn = 1;

What a good answer covers:

• ROW_NUMBER here, because you want exactly one row per key, even with ties.
• A tie-breaker column in the ORDER BY. Without one, two rows with the same updated_at give a different "latest" row on different runs.
• Bonus: in PostgreSQL, DISTINCT ON (customer_id) with the same ORDER BY does this in one step.

4. When do you use GROUP BY and when a window function?

What a good answer covers:

• GROUP BY collapses rows: one output row per group. A window function keeps every row and adds a computed column alongside it.
• An example of each: total sales per city is a GROUP BY; each order's share of its city's total, or a running total by date, is a window.

Data modelling

5. What is the difference between OLTP and OLAP?

What a good answer covers:

• OLTP systems run the application: many small reads and writes of single rows, normalised tables, strict consistency.
• OLAP systems answer questions: fewer, heavier queries that scan and aggregate many rows, usually on denormalised or columnar data.
• Why you do not run heavy reports on the production database: they compete with live traffic.

6. Fact vs dimension tables, and star vs snowflake schema

What a good answer covers:

• A fact table holds events or measurements at a stated grain ("one row per order line"), with numeric measures and foreign keys. Dimensions describe the who, what, where and when: customer, product, store, date.
• Star: the fact table joins directly to flat, denormalised dimensions. Snowflake: the dimensions are normalised further (product → category → department), which saves some storage but adds joins.
• Always naming the grain. Interviewers listen for it.

7. Slowly changing dimensions: type 1 vs type 2

What a good answer covers:

• Type 1 overwrites the old value. Simple, but history is lost.
• Type 2 adds a new row for each change, with valid_from, valid_to and an is_current flag, and a surrogate key so old facts still point at the version that was true at the time.
• A concrete case: a customer moves from Pune to Bengaluru. Should last year's sales report under Pune or Bengaluru? That business question decides the type.

Pipelines

8. ETL vs ELT

What a good answer covers:

• ETL transforms data before loading it into the warehouse. ELT loads raw data first and transforms it inside the warehouse with SQL.
• Why ELT became common: warehouse compute is cheap enough, and keeping raw data lets you rebuild transformations when the logic changes.
• When ETL still makes sense, for example masking sensitive fields before they land anywhere.

9. Batch vs streaming, and when each fits

What a good answer covers:

• Batch processes a bounded chunk on a schedule; streaming processes events continuously as they arrive, for example from Kafka.
• Choose by how fresh the data needs to be. A daily finance report is batch; fraud alerts or live inventory need streaming.
• Streaming costs more to build and operate, so you need a reason to pick it.

10. What does it mean for a pipeline to be idempotent, and how do you backfill?

What a good answer covers:

• Idempotent: running the same job for the same input twice gives the same result, not duplicates.
• How: overwrite a whole partition (delete-then-insert for that date) or MERGE/upsert on a key, instead of blind appends.
• A backfill reruns the job for past dates, for example after a bug fix. It is only safe if the job is idempotent and parameterised by date rather than reading "today".

11. How do you handle late-arriving data?

What a good answer covers:

• The difference between event time (when it happened) and processing time (when you received it).
• In batch: reprocess a trailing window, say the last three days, on every run. In streaming: watermarks that decide how long to wait before closing a window.
• A clear rule for data that arrives after the cutoff: drop it, or route it to a correction path.

Spark and distributed processing

12. What are partitions, and what is a shuffle?

What a good answer covers:

• A partition is a chunk of the data processed by one task; partitions are the unit of parallelism.
• Narrow transformations (filter, map, select) work within a partition. Wide transformations (groupBy, join, distinct) need rows with the same key together, which forces a shuffle across the network.
• Shuffles are usually the expensive part of a job, and each one marks a stage boundary.

13. What is data skew and how do you fix it?

What a good answer covers:

• Skew is when a few keys hold most of the rows, so one task runs for ages while the rest finish quickly. The symptom: a stage stuck at its last few tasks.
• Broadcast join: if one side is small, send a copy to every executor and skip the shuffle entirely.
• Salting: add a random suffix to the hot key so its rows spread over several partitions, then aggregate again to remove the salt.
• Mentioning that Spark's adaptive query execution can split skewed partitions in joins is a plus.

Storage

14. Row vs columnar storage, and why Parquet?

What a good answer covers:

• Row storage keeps each record together, good for reading or writing whole rows. Columnar keeps each column together, good for analytics that read a few columns across many rows.
• Parquet is columnar, compresses well because similar values sit together, stores the schema, and keeps min/max statistics so engines can skip blocks that cannot match a filter.

15. Why partition by date, and what is the small-files problem?

What a good answer covers:

• Partitioning by date (dt=2026-10-01/) lets a query for one day read one folder instead of the whole table. It also makes daily overwrites and backfills clean.
• Partitioning on a high-cardinality column, or writing too often, creates thousands of tiny files. Each file has overhead to list and open, so queries slow down.
• Fixes: compaction jobs, coalescing before writing, and choosing a coarser partition key.

Orchestration

16. What is a DAG, and why should tasks be idempotent?

What a good answer covers:

• A DAG is a directed acyclic graph of tasks: each task runs after its dependencies, and there are no cycles. In Airflow, each DAG run is tied to a logical date.
• Retries handle temporary failures such as a network timeout. Retries are only safe if a task that half-finished can run again without double-writing.
• The link back to question 10: idempotent tasks make retries and backfills boring, which is the goal.

Data quality

17. What checks would you put on a table, and what if the upstream schema changes?

What a good answer covers:

• Basic checks: not-null on required columns, uniqueness on the key, accepted values, row counts against the previous run, and freshness (has today's data arrived?).
• Where checks run: after load, and failing the pipeline before bad data reaches dashboards.
• Schema change: detect it by comparing incoming columns and types to an expected schema. Additive changes (a new nullable column) can often pass through; renamed, dropped or retyped columns should stop the load and alert the owner. Agreeing a data contract with the upstream team prevents surprises.

How to talk about your college or personal projects

Most freshers have one or two projects, usually a pipeline that pulls from an API, loads into a database and draws a dashboard. That is fine. What matters is how you describe it.

• Start with the data, not the tools. Where it came from, how much of it, how often it changed, and what question it answered.
• Name one decision and its tradeoff. "I chose daily batch because the source only updates once a day" beats a list of six technologies.
• Say what broke. Duplicate rows after a rerun, a column that changed type, a job that slowed down. Then say how you fixed it, using the ideas above.
• Know your numbers. Row counts, run time, file sizes. If you do not know them, say roughly and say how you would check.

How answers lose marks

• Definitions with no example. "A fact table stores facts" tells the interviewer nothing. Give a grain and a column.
• SQL that only works on clean data. If you never mention NULLs, ties or duplicates, expect the follow-up that breaks your query.
• Tool names instead of reasoning. Saying "I'd use Spark" without saying why the data needs a distributed engine sounds like guessing.
• Stopping at the first answer. Interviewers almost always ask "and what if it runs twice?" or "what if the data is ten times bigger?". Get there before they do.

Practise saying it out loud

Reading these answers is not the same as giving them under follow-up questions. On mangoose.tech you can sit a live mock interview for a Data Engineering role and speak your answers to Meera, who asks follow-ups and pushes one level past each answer, the way a panel does. Technical rounds can include writing SQL that gets assessed, and the written gap report afterwards marks where your answer broke down and what to do differently next time.