Data Engineer Interview Questions and Answers

What interviewers look for

SQL fluency

You write joins, aggregations and window functions live, and you explain how NULLs and duplicate keys change the result before the interviewer has to ask.

Data modeling

Given a business process, you state the grain of each table, separate facts from dimensions, and handle history such as a customer changing address.

Pipeline design

You design ingestion and transformation that can be rerun safely, cope with late or duplicate records, and recover from a failed night without manual cleanup.

Data quality and reliability

Interviewers look for tests, freshness and volume checks, and alerting that catch bad data before it reaches a dashboard or a model.

Performance and cost

You can explain partitioning, file formats, skewed joins and warehouse costs, and you have made a slow or expensive job faster or cheaper.

SQL

Live SQL against a small schema, testing joins, window functions and the edge cases that silently corrupt numbers.

1. 01

   Write a query that returns the second-highest salary in each department.

   Why they ask it: It is a quick test of window functions and whether you think about ties and departments with only one employee.

   How to answer

   • Ask whether 'second-highest' means the second distinct salary or the second person, since ties change the answer.
   • Use DENSE_RANK() partitioned by department and ordered by salary descending, then filter to rank 2.
   • Explain why ROW_NUMBER() would be wrong if two people share the top salary.
   • Say what happens to a department with a single salary (it returns no row) and whether that is acceptable.

   I'd first ask whether ties should count as one salary. Assuming we want the second distinct salary, I'd write a CTE that selects department, employee and salary plus DENSE_RANK() over a window partitioned by department and ordered by salary descending. The outer query keeps rows where that rank equals 2. I'd use DENSE_RANK rather than ROW_NUMBER because if two people both earn the top salary, ROW_NUMBER would label one of them second and give the wrong answer, while DENSE_RANK gives them both rank 1 and the next salary rank 2. RANK would skip to 3 after a tie, so it also fails. One thing to flag: departments with only one distinct salary return nothing. If the report needs every department listed, I'd left join the result back to the departments table and show NULL.

   Avoid: Using ORDER BY with LIMIT 1 OFFSET 1, or ROW_NUMBER(), which both return the top salary again when two people tie for first.

2. 02

   Given a table of user logins, find users who logged in on three or more consecutive days.

   Why they ask it: It is the gaps-and-islands pattern, which shows up constantly in real reporting such as streaks and sessions.

   How to answer

   • Reduce to one row per user per day first, because multiple logins on the same day break the logic.
   • Subtract ROW_NUMBER() (ordered by date within each user) from the date; consecutive days share the same result, which identifies each streak.
   • Group by user and that streak key, and keep groups with a count of three or more.
   • Mention LAG() as an alternative and which one you find easier to read.

   Avoid: Skipping the deduplication step and getting streaks that are too long.

3. 03

   Why might a LEFT JOIN return more rows than the left table has?

   Why they ask it: Join fan-out is the most common way pipelines quietly double-count revenue, so interviewers check that you know it.

   How to answer

   • Explain that every match on the right side produces a row, so a key that appears three times on the right triples the left row.
   • Give a realistic case: joining orders to a payments table where some orders had two payment attempts.
   • Say how you detect it: compare row counts before and after the join, or check key uniqueness on the right table first.
   • Fix it by aggregating or deduplicating the right side to the grain you need before joining.

   Avoid: Reaching for SELECT DISTINCT to hide the duplicates instead of fixing the join grain.

4. 04

   Compute a 7-day rolling average of daily revenue.

   Why they ask it: It tests window frames and whether you notice that missing days make a row-based window wrong.

   How to answer

   • Aggregate to one row per day first.
   • Use AVG() over a window ordered by date with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
   • Point out that if some days have no sales, a ROWS frame spans more than seven calendar days. Join to a date spine (a table of every date) and COALESCE missing days to 0, or take the SUM over a seven-calendar-day RANGE frame and divide it by 7; an AVG over that RANGE frame divides only by the days that have rows, so it comes out too high.
   • Decide how to treat the first six days, where fewer than seven days exist.

   Avoid: Assuming every calendar day has a row in the data.

Data modeling

Designing tables that analysts can query correctly, with a clear grain and an honest record of history.

1. 05

   Design the analytics tables for an online store's orders.

   Why they ask it: This is the classic modeling exercise; it shows whether you start from the questions the business asks and define grain before columns.

   How to answer

   • Ask what questions the model must answer: revenue by product and day, repeat purchase rate, refunds.
   • Declare the grain of the fact table out loud, such as one row per order line.
   • Lay out dimensions (customer, product, date, maybe promotion) and the measures on the fact (quantity, price, discount, tax).
   • Explain how refunds and cancellations are recorded and how late changes to an order are handled.

   I'd start by asking what people will query: revenue by product category per day, average order value, and how many customers come back within 90 days. Then I'd set the grain of the central fact table at one row per order line, because product-level revenue needs it and order totals can be rolled up from it. That fact table holds order ID, line number, foreign keys to customer, product and date dimensions, plus quantity, unit price, discount and tax. Product attributes like category and brand live in the product dimension so they're not repeated on every line. Refunds would go in a separate fact at the refund-line grain instead of overwriting the original sale, so revenue on a past date doesn't silently change. I'd also keep a small order-level table for things like shipping fees that don't belong to a single line.

   Avoid: Listing columns before stating the grain, which is how double counting gets designed in.

2. 06

   A customer's address changes. How do you handle that in your customer dimension?

   Why they ask it: Slowly changing dimensions test whether you think about historical accuracy, not just current state.

   How to answer

   • Ask whether reports need history: should last year's sales stay attributed to the old region?
   • Type 1 overwrites the value (simple, loses history); Type 2 adds a new row with valid-from and valid-to dates and a current flag.
   • With Type 2, facts reference a surrogate key so each sale points to the version that was true at the time.
   • Mention the cost: Type 2 tables grow and joins need care.

   Avoid: Answering only 'update the row' without asking whether history matters.

3. 07

   When would you denormalize data, and when would you keep it normalized?

   Why they ask it: It checks that you know transactional and analytical systems are designed for different jobs.

   How to answer

   • Normalized schemas suit transactional systems: many small writes, no duplicated data to keep in sync.
   • Analytical warehouses, which store data by column, handle wide denormalized tables well and they make queries simpler for analysts.
   • Name the cost of denormalizing: duplicated attributes that must be refreshed when the source changes.
   • Give an example of a choice you made and why.

   Avoid: Treating normalization as always right or always wrong.

Pipelines and performance

How data moves from source systems to tables people trust, and what you do when it is slow, late or wrong.

1. 08

   Design a pipeline that ingests website click events and makes them available for analysis.

   Why they ask it: It covers ingestion, storage, transformation and failure handling in one prompt, and shows how you trade freshness against cost and complexity.

   How to answer

   • Ask about volume, how fresh the data must be, and who uses it.
   • Describe the path: events into a message queue, landed raw in object storage in a columnar format partitioned by date, then transformed into warehouse tables.
   • Handle duplicates (deduplicate on an event ID) and late events (reprocess recent partitions).
   • Cover orchestration, schema changes from the app team, and monitoring for volume drops.

   I'd ask how fresh the data needs to be first. If analysts need hourly numbers, not seconds, I'd keep it simple. The app sends events to a message queue such as Kafka. A consumer writes them in small batches to object storage as Parquet files, partitioned by event date, so we always keep the raw data and can replay it. An hourly orchestrated job, in Airflow for example, loads new files into the warehouse and runs transformations that deduplicate on event ID, since delivery is at-least-once, and builds session and page-view tables. Events can arrive late from mobile devices, so each run reprocesses the last two days of partitions and overwrites them, which keeps the job idempotent. I'd add checks for row volume against the same hour last week and alert if events drop sharply, because a broken tracking release usually shows up as missing data, not an error.

   Avoid: Choosing real-time streaming by default when nobody needs data faster than hourly.

2. 09

   What does it mean for a pipeline to be idempotent, and why does it matter?

   Why they ask it: Pipelines fail and get rerun; non-idempotent jobs duplicate data every time that happens.

   How to answer

   • Define it: running the same job for the same input twice leaves the same result as running it once.
   • Show how: overwrite a date partition instead of appending, or MERGE on a unique key.
   • Connect it to backfills: you can rerun last month safely.
   • Mention a case where you fixed or built this.

   Avoid: Relying on someone to remember to delete rows before a rerun.

3. 10

   In a streaming pipeline that counts orders per minute, how do you handle events that arrive late?

   Why they ask it: Late and out-of-order data is the central problem in stream processing, and the answer shows whether you understand event time, watermarks and their trade-offs.

   How to answer

   • Separate event time (when the order happened) from processing time (when the pipeline saw it), and window on event time so a delayed event lands in the right minute.
   • Explain watermarks: the pipeline's running estimate of how far event time has progressed, which decides when a window can be finalized and its state dropped.
   • Name the trade-off: waiting longer for late events gives more complete counts but later, more memory-hungry results.
   • Say what happens to events that arrive after the watermark: drop and count them, route them to a side output, or correct the totals in a later batch job, and make downstream writes upserts so corrections are safe.

   Avoid: Windowing on processing time, which silently shifts late events into the wrong minute.

4. 11

   Your Spark job is slow, and one task runs far longer than all the others. What is going on?

   Why they ask it: Data skew is one of the most common Spark performance problems, and diagnosing it shows you understand how work is distributed.

   How to answer

   • Name the likely cause: skew, where one join or group key (often NULL or a default value) holds a huge share of rows, so one task gets most of the work.
   • Say how you confirm it: the Spark UI's task durations and shuffle sizes, or a count by key.
   • List fixes: broadcast the smaller table, filter or separately handle the hot key, salt the key, or rely on adaptive query execution's skew handling in recent Spark versions.
   • Check simpler causes too: too few partitions, or spilling to disk.

   Avoid: Adding more executors, which does not help when one task holds most of the data.

5. 12

   How do you make sure the data your pipelines produce is correct?

   Why they ask it: Stakeholders lose trust after one wrong dashboard, so teams want engineers who build checks into the pipeline.

   How to answer

   • Name specific tests: not-null and uniqueness on keys, accepted values, referential integrity between facts and dimensions.
   • Add freshness and volume checks that compare against recent history.
   • Decide which failures block downstream tables and which only warn.
   • Describe how you agree on expectations with source-system owners.

   I think in layers. On every model I add basic tests: primary keys are unique and not null, foreign keys exist in the dimension, and status fields only contain values we expect. In dbt those are a few lines of configuration each. Then I add checks on the data as a whole: did today's load arrive on time, and is the row count within a reasonable range of the same day last week? Those catch the problems tests miss, like an upstream service silently sending half its events. Failures on key tables block the downstream models so a broken load never reaches a dashboard, while softer checks just alert the channel. The hard part is tuning: my first volume alerts fired every holiday until people started ignoring them, so now I compare against the same weekday and mute known holidays, because an alert nobody trusts is worse than none.

   Avoid: Saying analysts will tell you if something looks off.

Behavioral and motivation

How you handle incidents, stakeholders and your own work, told through specific stories.

1. 13

   Tell me about a time bad data reached a dashboard or a stakeholder. What did you do?

   Why they ask it: Every data engineer has one; interviewers want to hear how you communicated, fixed it and prevented a repeat.

   How to answer

   • Say how the problem was discovered and how quickly you told the people relying on the data.
   • Explain the root cause plainly.
   • Describe the fix, including correcting the historical data, not just future loads.
   • End with the check or process you added afterward.

   A sales manager messaged me that the weekly pipeline report showed revenue up 40 percent, which didn't match what her team saw. I posted a note on the dashboard that the numbers were under review, then traced it. An upstream table had started including test accounts after a schema change, and our filter relied on a column that had been renamed, so it silently matched nothing. I fixed the filter, reran the affected three weeks of partitions, and confirmed the totals against the billing system. Then I told the stakeholders what had happened and which numbers had changed. To prevent a repeat, I added a test that fails if any test-account IDs reach the reporting tables. The bigger fix was the process gap: I asked the upstream team to tag us on schema changes to that table, and now we review renames before they ship instead of finding them in a dashboard.

   Avoid: Fixing it quietly without telling the people who had already used the wrong numbers.

2. 14

   Walk me through a pipeline you built from start to finish.

   Why they ask it: Going deep on one project shows what you personally designed and whether you understand why it works.

   How to answer

   • Name the business need and the consumers first.
   • Walk the path: source, ingestion, storage, transformations, orchestration, checks.
   • Explain one decision you weighed and what you chose.
   • Give a result: runtime, freshness, cost, or hours saved.

   Avoid: Listing tools without saying what problem each one solved.

3. 15

   Tell me about a time a stakeholder asked for something you thought was the wrong solution.

   Why they ask it: Data engineers constantly receive requests for one-off tables and extracts; the question tests whether you find the real need.

   How to answer

   • Show that you asked what decision the request was meant to support.
   • Explain the alternative you proposed and why it served them better.
   • Describe how it turned out, including compromises.

   Avoid: A story where you simply refused the request.

4. 16

   What pulled you toward data engineering rather than analytics or software engineering?

   Why they ask it: Interviewers want to know you understand what the job is day to day and actually want it.

   How to answer

   • Describe the part of the work you enjoy: building reliable systems that other people's analysis depends on.
   • Back it with something you did: a pipeline in a class project, internship or previous analyst job.
   • Connect it to this team's data and problems.

   I started as an analyst, and I kept noticing that most of my week went to fixing the inputs, not the analysis. Our marketing data came from three tools with different customer IDs, and every report started with an hour of cleanup. I wrote a Python job that pulled all three nightly, matched customers on email, and loaded one clean table into our warehouse. After that the team's weekly reporting went from most of a day to under an hour, and I realized I enjoyed building that more than presenting the charts. I've since learned SQL modeling properly, set up Airflow for my own projects, and taken on our team's pipeline work. Your posting mentions consolidating several product databases into one warehouse, which is exactly the kind of problem I want to work on full time.

   Avoid: Saying you chose data engineering because it pays well or because data science seemed too crowded.

Questions to ask them

The end of the interview is yours. Each of these tells you something about the job that a posting won't.

1. Who are the main consumers of the data this team produces, and how do they report problems?

   It tells you who you will serve and whether there is a working feedback loop.

2. What does on-call look like for the pipelines, and what breaks most often?

   You learn the operational load and the weakest parts of the platform.

3. What is the current stack, and is any migration underway?

   A warehouse or orchestration migration can take up much of your first year.

4. How do you test pipelines and catch data problems before stakeholders do?

   The answer shows how mature the team's data quality practice is.

5. How are metric definitions such as 'active customer' agreed and documented?

   It reveals whether you will spend your time reconciling conflicting numbers.

How to prepare

1. Drill SQL without autocomplete: joins, GROUP BY with HAVING, window functions (ROW_NUMBER, DENSE_RANK, LAG, running totals), CTEs, deduplication and gaps-and-islands.
2. Practice Python data manipulation: parsing files, dictionaries, merging and deduplicating records, and writing a small, tested transformation function.
3. Sketch a star schema for a business you know (orders, rides or subscriptions) and explain its grain out loud.
4. Prepare one pipeline design walk-through: source, ingestion, storage format and partitioning, transformation, orchestration, quality checks and how you would backfill.
5. Review how the tools named in the posting work, not only what they are: partitions and shuffles in Spark, DAGs and retries in Airflow, models and tests in dbt.
6. Prepare stories with numbers: a pipeline you built, a data-quality incident, a runtime or cost you cut.
7. If you have a portfolio project, put it on GitHub with a short README describing the architecture and the decisions behind it.

Sources

1. Database Architects (15-1243.00), O*NET OnLine
2. Database Administrators and Architects, U.S. Bureau of Labor Statistics, Occupational Outlook Handbook