Data Engineer Interview Questions and Answers (2026): SQL, Pipelines, and Spark

Data engineer interviews test three things: SQL, how you design and run data pipelines, and how you reason about scale and failure. Below are 20 questions with answers you can adapt, in the order candidates usually meet them. The SQL examples were run against a small sample database before publishing, and the results shown are the real output.

SQL questions

1. Find the second highest salary in each company or overall

Use DENSE_RANK, so ties share a rank and "second highest" means the second distinct value.

SELECT name, salary
FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r = 2;

On a table with salaries 150, 130, 130, 110, 90, 70 this returns the two people paid 130. Say why you chose DENSE_RANK over RANK (which skips numbers after ties) and ROW_NUMBER (which breaks ties arbitrarily).

2. Top two earners in each department

SELECT dept, name, salary
FROM (
  SELECT dept, name, salary,
         RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r <= 2;

PARTITION BY restarts the ranking for each department. Mention that tied salaries can return more than two rows with RANK.

3. Running total per customer

SELECT customer, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer ORDER BY order_date) AS running_total
FROM orders;

For a customer with orders of 10, 20, and 30 on increasing dates, the running totals are 10, 30, and 60.

4. Remove duplicate rows, keeping the first

SELECT id, user_id
FROM (
  SELECT id, user_id,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id) AS rn
  FROM events
) t
WHERE rn = 1;

Here ROW_NUMBER is right, because you want exactly one row per group. Be ready to say how you'd decide which duplicate to keep in a real table (latest timestamp, most complete record).

5. Change from the previous row

SELECT order_date, amount,
       amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders
WHERE customer = 'A';

The first row's change is NULL, because there's no previous row. Say that out loud, since it's the usual follow-up.

6. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, and HAVING filters groups after aggregation. You can't use an aggregate such as COUNT(*) in WHERE.

7. How do you speed up a slow query?

1. Look at the query plan before changing anything.
2. Check that filters can use an index, and that functions on the column aren't blocking it.
3. Reduce the data scanned: select only needed columns, filter early, and partition large tables by date.
4. Replace repeated subqueries with joins or common table expressions, and check for accidental row multiplication in joins.
5. For warehouses, check clustering or sort keys and how much data each query reads.

Pipeline design questions

8. ETL or ELT?

ETL transforms data before loading it. ELT loads raw data first and transforms it inside the warehouse. ELT is common with cloud warehouses because storage is cheap, raw data stays available for reprocessing, and transformations can be versioned as SQL. ETL still makes sense when data must be cleaned or masked before it lands, for example to remove personal data.

9. Batch or streaming?

Choose by how fresh the data must be. Batch is simpler and cheaper and fits daily reports. Streaming fits cases where minutes or seconds matter, such as fraud checks or live dashboards. Many systems use both. Say what the business decision is, not just the technology.

10. What is an idempotent pipeline, and why does it matter?

Running it twice produces the same result as running it once. It matters because jobs fail and get retried. To make a load idempotent, write by partition and overwrite that partition, or use an upsert with a stable key, so a retry doesn't create duplicates.

11. How do you handle late or out-of-order data?

Use event time rather than arrival time, and define how late data can arrive. In streaming systems, a watermark tells the job how long to wait before closing a window. For batch tables, reprocess recent partitions on a schedule, or keep a correction process that rewrites the affected days.

12. How do you design for failure and retries?

Make steps small, idempotent, and independently re-runnable. Store state outside the job. Alert on missing or late data as well as errors. Put a limit on retries, and send repeated failures to a place where a person looks.

13. How do you check data quality?

Run checks inside the pipeline and fail early, so bad data doesn't reach reports.

Modeling and storage questions

14. What is a star schema?

A central fact table holds measurable events, such as orders, and links to dimension tables that describe them, such as customer, product, and date. It's easy to query and fast to aggregate. A snowflake schema normalizes the dimensions further, which saves space but adds joins.

15. What are slowly changing dimensions?

They are dimension attributes that change over time, such as a customer's address. Type 1 overwrites the old value and loses history. Type 2 adds a new row with start and end dates, so history is kept. Say which you'd choose for which attribute and why.

16. How do you partition a large table?

Partition on a column that most queries filter on, usually a date. Queries that filter on it read only the matching partitions. Avoid a column with too many distinct values, which creates a huge number of tiny partitions.

17. What file formats do you use for analytics, and why?

Columnar formats such as Parquet store each column together, so queries that read a few columns skip the rest and compress well. Row formats such as CSV or JSON are easy to produce and read but slow and large at scale.

Distributed processing questions

18. What is a shuffle in Spark, and why is it expensive?

A shuffle moves data between machines so that rows with the same key end up together, as in joins and group-bys. It costs network transfer, disk writes, and sorting. To reduce it, filter early, pick sensible partition counts, and broadcast a small table in a join so the large one doesn't have to move.

19. How do you handle data skew?

Skew means a few keys hold most of the data, so one task runs far longer than the rest. Options include salting the hot key by adding a random suffix and aggregating twice, broadcasting the smaller side of a join, and filtering or handling the hot keys separately.

20. What does an orchestrator like Airflow do?

It schedules and runs tasks in order, defined as a directed graph where each task depends on others. It handles retries, backfills, and alerts, and shows what ran and what failed. It coordinates the work. It isn't meant to do heavy processing itself, which should run in a warehouse or a processing engine.

How to prepare

1. Practice the window function patterns above until you can write them without looking.
2. Prepare one pipeline you built, with the volume, the failures you hit, and what you changed.
3. Be ready to draw a simple architecture on a whiteboard and say what happens when a step fails.
4. Review your resume: every tool on it can be asked about.

For related practice, see our guides to Kafka interview questions, Databricks interview questions, Snowflake interview questions, and system design interview questions.

Practice aloud and in your own words. A transcript afterward shows where you rambled or skipped the point. Follow the employer's rules on AI tools in any live interview or assessment.