← All posts
general

Data Engineer Interview: SQL, Pipelines and System Design

Experienced engineers who fail a data engineer interview often fail it in the SQL round, and are surprised. They write SQL every day. They did not expect to be stuck on a window function with someone watching, or to be told the query was correct and then asked what it does when the table has duplicate rows.

That is the pattern across the whole loop. The material is familiar from the job, and the interview tests it faster, more precisely and with more follow-up than the job does. This guide takes the data engineer interview one round at a time (SQL, data modelling, pipeline and streaming design, coding, behavioural) with five example questions for each and a four-week plan. The questions are examples written for practice, not leaked questions from any company.

What the loop contains

The stages are the usual ones: a recruiter call, a technical screen heavy on SQL, then a final loop. What differs between companies is how the final loop is cut. Two prep guides built from candidate reports, read in November 2026, describe opposite arrangements.

Meta, as reported Amazon, as reported
Technical screen 60 minutes: five SQL questions, then five Python One or two interviews of 60 to 75 minutes; some teams add an online assessment
Final loop Four rounds of about 60 minutes, each blending product sense, data modelling, SQL and Python on one scenario Three to five interviews of 45 to 60 minutes; the hiring team sets the mix
Behavioural A separate 30-minute round on ownership Leadership principle questions in every interview, plus a Bar Raiser
Emphasis Data modelling carries the most weight SQL, modelling, pipeline design on AWS services, coding

Neither company publishes this, so treat both columns as reports. The useful point is the contrast: one loop mixes every skill into each hour around a product scenario, the other gives each skill its own interviewer. Ask your recruiter which kind yours is, because it changes how you practise. For Amazon, the Bar Raiser guide explains the interviewer from outside the team.

Smaller companies often replace part of this with a take-home pipeline exercise and a review conversation.

The SQL round

Expect three to six questions of rising difficulty against two or three tables, in a shared editor that may not run your query. Speed matters: at the pace the Meta screen is reported to run, you have about five minutes a question.

The topics are stable. Joins, including the self join and the anti join. Aggregation with GROUP BY and HAVING. Window functions, which decide most rounds: ROW_NUMBER, RANK, LAG and LEAD, running totals, moving averages. Deduplication. Date arithmetic. Conditional aggregation with CASE. Gaps and islands, such as consecutive days of activity.

An invented example of the middle difficulty. Given orders(order_id, customer_id, ordered_at, amount), find for each customer the number of days between their first and second order. The answer below is in Trino's dialect.

WITH ranked AS (
  SELECT
    customer_id,
    ordered_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY ordered_at, order_id
    ) AS n
  FROM orders
)
SELECT
  f.customer_id,
  DATE_DIFF('day', f.ordered_at, s.ordered_at) AS days_to_second
FROM ranked f
JOIN ranked s
  ON s.customer_id = f.customer_id AND s.n = 2
WHERE f.n = 1;

Writing that is half the answer. The interviewer then asks what happens to customers with one order (they vanish, because of the inner join; is that wanted?), why order_id is in the ordering (two orders in the same second would otherwise rank arbitrarily), what you would do if the same order had been loaded twice, and how the query behaves on a few billion rows. Date functions differ between engines (the argument order of DATE_DIFF is one example), so say which dialect you are writing.

Habits that score: restate the question and the grain of the output before writing, name the edge cases yourself, and read the finished query back against a three-row example.

Questions of this kind:

  • Find the top three products by revenue in each category, including ties.
  • For each user, find the longest run of consecutive days with at least one session.
  • Return customers who ordered in January and did not order in February.
  • Compute a seven-day moving average of daily active users.
  • Remove duplicate events, keeping the latest version of each.

Data modelling

Here you are given a product (a ride-hailing app, a streaming service, an online classroom) and asked to design the tables that analysts will query. One of the guides above says this carries the most weight in Meta's loop and usually starts from a product question: what would the business want to measure?

Start there. List the questions the model must answer, then the events that occur, then the grain of each fact table in one sentence: "one row per completed trip". Most weak answers trace back to a grain that was never stated. Add dimensions, decide what is stored and what is derived, and write one or two of the queries the model is meant to serve, to prove it does.

Be ready to discuss:

  • Facts and dimensions. Star schema against a wide denormalised table, and what each costs to query and to maintain.
  • History. Slowly changing dimensions: when to overwrite, when to keep versions with validity dates, and what a join against the versioned table looks like.
  • Keys and partitioning. Natural against surrogate keys; partitioning by date; what happens to a query that ignores the partition column.
  • Late and changing facts. A trip that is refunded three days later. A status that moves through five states.
  • Aggregates. Which rollups to precompute, and how they are kept consistent with the detail.

Questions of this kind:

  • Model the data for a food delivery app so that we can report delivery time by restaurant and hour.
  • A customer changes address. How does your model keep last year's orders in the right region?
  • Design tables to track a subscription through trial, paid, paused and cancelled.
  • The product team wants a daily retention chart. What table would you build for it?
  • Your fact table has reached a size where the dashboard times out. What are the options?

Pipeline and streaming design

This is the data engineer's system design round. A prompt might be: ingest clickstream events from mobile apps and make them queryable within five minutes and correct by the next morning. It is graded on whether the pipeline would survive real conditions, and the conditions are well known.

A sound answer covers, in order: the sources and their volume; batch, streaming or both, with the freshness the business needs as the reason; where raw data lands unchanged; the transformations and where they run; the serving layer; orchestration; and then the failure cases, which is where most of the marks are.

  • Idempotency. A job that runs twice must leave the same result as a job that runs once. Say how: overwrite by partition, merge on a key, deduplicate on an event identifier.
  • Late and out-of-order data. Event time against processing time, watermarks, how long you wait, and what happens to an event that arrives after the window closed.
  • Backfills. How you reprocess three months after a logic fix without doubling the numbers or stalling today's run.
  • Schema change. A new field, a renamed field, a type change upstream that nobody announced.
  • Data quality. Checks on volume, nulls, uniqueness and freshness; what blocks a publish and who is told.
  • Delivery guarantees. At-least-once delivery with idempotent writes is the usual honest answer. Be careful claiming exactly-once, and be able to say what it would require end to end.
  • Cost. File sizes, partition counts, the price of a full scan, and which stage dominates the bill.

Name tools only as examples. An interviewer at a company on a different stack wants the reasoning, and "a log-based queue such as Kafka" travels better than a product pitch. The exception is a team that has told you its stack: the Amazon guide above reports questions on choosing between AWS services. For the storage and messaging building blocks, backend systems architectural patterns is a compact reference.

Questions of this kind:

  • Design a pipeline that loads orders from a transactional database into a warehouse every hour.
  • Design real-time counting of video views that can be corrected later.
  • Yesterday's job ran twice. How do you find out, and how should it have been built?
  • An upstream team changes a column from integer to string. What happens in your pipeline?
  • When is a streaming pipeline the wrong choice?

Coding

Usually Python, and usually closer to data manipulation than to algorithm puzzles: parse a log file, group and aggregate records, flatten nested JSON, merge two sorted streams, deduplicate with a rule, implement a small sliding window. Dictionaries, sets, sorting and generators cover most of it.

Some companies do ask standard data structure problems at an easy to medium level, particularly where data engineers share a ladder with software engineers. The Amazon guide lists both kinds. Ask the recruiter which to expect, and do a few weeks of light algorithm practice either way.

What interviewers note: handling of empty and malformed input, whether you thought about data that does not fit in memory, clear names, and a test with a small example before you say you are done.

Questions of this kind:

  • Parse web server log lines and return the ten most common paths per hour.
  • Given two files of user records too large for memory, find the users present in both.
  • Flatten a nested JSON document into rows with dotted column names.
  • Implement a function that yields fixed-size batches from an iterator.
  • Given events with timestamps, group them into sessions separated by thirty minutes of inactivity.

Behavioural

Data engineers are asked the standard behavioural questions, with a slant toward the things that go wrong in data work: the number in the dashboard that was wrong for a month, the stakeholder who needed something by Friday, the upstream team that broke your pipeline, the migration nobody wanted to pay for.

Prepare five or six stories in the STAR form and make sure they include a data quality incident you owned, a disagreement with an analyst or product manager about a definition, and something you made cheaper or faster with numbers attached. At Amazon each story is heard against the leadership principles, in every interview and not only in a dedicated one, so prepare more stories there.

Questions of this kind:

  • Tell me about a time you shipped wrong data. How was it found, and what did you change?
  • Describe a pipeline you inherited in poor condition. What did you do first?
  • Tell me about a disagreement over the definition of a metric.
  • When did you push back on a request for real-time data?
  • Describe the largest migration you have carried out. What went wrong?

A four-week plan

At eight hours a week, for a working data engineer.

  • Week 1: SQL under time. Five timed questions a day, five minutes each, written without running them and then checked. Keep a list of every mistake by type. Window functions and gaps-and-islands until they are automatic.
  • Week 2: modelling. Model four products on paper: a marketplace, a subscription service, a messaging app, a logistics business. For each, state the grain of every fact table and write two queries against the model. Start drafting behavioural stories.
  • Week 3: pipelines. Four design prompts, 45 minutes each, aloud. For every design, answer the seven failure cases above before you stop. Do ten Python data manipulation exercises.
  • Week 4: rehearsal. One mock of each round with another person. If your loop is the blended kind, practise scenario hours that move from a product question to a model to SQL to Python without a break. Finish the stories.

Modelling and pipeline design are discussions, and practising them in silence hides the weak points. The fastest correction comes from someone who builds these systems asking why your grain is what it is; a mock with one of the data engineering coaches in week 3 or 4 does that in an hour.

Where coaching fits

Use a coach for the two open-ended rounds, modelling and pipeline design, where there is no answer key to check yourself against. On techinterview.coach, five of the 40 coaches list data engineering as a specialty as of November 2026, and the coaches' titles include data and analytics engineers; the data engineering coaches page shows who they are. For the behavioural stories, the practice interview runs a behavioural round with an AI interviewer and gives a written recap, and you can paste in the job ad to ground it. It is free; sign in to start.

Related guides