The Data Engineering Interview

Data engineering interviews test something narrower than software engineering ones and broader than analytics ones. This lesson is what the rounds actually assess, and one warm-up question worked twice — badly, then well.

The five rounds

Nobody is checking whether you memorised a dynamic programming template. They are checking whether the table you build will still be correct after the third re-run of a backfill.

A warm-up question

“Here are two CSV files — orders and customers. Give me daily revenue by country for completed orders.”

It sounds like a five-minute task. It is a filter for how you think.

import duckdb, textwrap
con = duckdb.connect()

con.execute("""
    create table customers as select * from (values
        (1, 'Ada Lovelace', 'GB'), (2, 'Grace Hopper', 'US'),
        (3, 'Alan Turing', 'GB'),  (4, 'Katherine Johnson', 'US')
    ) t(customer_id, full_name, country)
""")

con.execute("""
    create table orders as select * from (values
        (1001, 1, date '2026-01-04', 'completed', 25.50),
        (1002, 2, date '2026-01-04', 'completed', 12.00),
        (1003, 1, date '2026-01-04', 'returned',  40.00),
        (1004, 3, date '2026-01-05', 'completed',  8.75),
        (1005, 2, date '2026-01-05', 'pending',   63.20),
        (1006, 9, date '2026-01-05', 'completed', 19.99),
        (1007, 4, date '2026-01-06', 'completed', 31.20),
        (1007, 4, date '2026-01-06', 'completed', 31.20)
    ) t(order_id, customer_id, ordered_at, status, amount)
""")
print(con.execute("select count(*) as orders from orders").df().to_string(index=False))

 orders
      8

The answer that gets marked down

answer_1 = """
    select o.ordered_at, c.country, sum(o.amount) as revenue
    from orders o
    join customers c on o.customer_id = c.customer_id
    where o.status = 'completed'
    group by 1, 2
    order by 1, 2
"""
print(con.execute(answer_1).df().to_string(index=False))

 ordered_at country  revenue
 2026-01-04      GB    25.50
 2026-01-04      US    12.00
 2026-01-05      GB     8.75
 2026-01-06      US    62.40

It runs. It is syntactically fine. It is also wrong in two ways and unverified in a third, and an interviewer will not tell you — they will ask “are you happy with that?” and watch what you do.

Finding your own bugs

The habit being tested is whether you reconcile before you present. Three checks, none of which take longer than the query itself.

Does the join lose rows?

print(con.execute("""
    select count(*) as orphaned_orders
    from orders o left join customers c using (customer_id)
    where c.customer_id is null
""").df().to_string(index=False))

 orphaned_orders
               1

Order 1006 belongs to customer 9, who does not exist. The inner join silently dropped £19.99 of completed revenue. An inner join is a filter, and unexplained row loss is the single most common mistake in this round.

Is the grain what you think?

print(con.execute("""
    select order_id, count(*) as n
    from orders group by 1 having count(*) > 1
""").df().to_string(index=False))

 order_id  n
     1007  2

Order 1007 appears twice. The 2026-01-06 figure of £62.40 is double-counted — a duplicate in the source, which is the second most common trap.

Do the totals reconcile?

print(con.execute("""
    select
      (select round(sum(amount), 2) from orders where status = 'completed') as source_total,
      (select round(sum(o.amount), 2) from orders o
        join customers c using (customer_id) where o.status = 'completed') as joined_total
""").df().to_string(index=False))

 source_total  joined_total
        128.6        108.61

£19.99 missing, matching the orphan exactly. Stating that number out loud is worth more than the query.

The answer that gets an offer

answer_2 = """
    with deduped as (
        select * exclude (rn) from (
            select *, row_number() over (
                partition by order_id order by ordered_at
            ) as rn
            from orders
        ) where rn = 1
    )
    select
        d.ordered_at,
        coalesce(c.country, 'UNKNOWN')       as country,
        count(*)                             as orders,
        round(sum(d.amount), 2)              as revenue
    from deduped d
    left join customers c using (customer_id)
    where d.status = 'completed'
    group by 1, 2
    order by 1, 2
"""
print(con.execute(answer_2).df().to_string(index=False))

 ordered_at country  orders  revenue
 2026-01-04      GB       1    25.50
 2026-01-04      US       1    12.00
 2026-01-05      GB       1     8.75
 2026-01-05 UNKNOWN       1    19.99
 2026-01-06      US       1    31.20

Three differences, and each one is a sentence you say while writing it:

1. Deduplicated on the natural key, because order_id was not unique. 2026-01-06 is now £31.20, not £62.40.
2. Left join with coalesce, so the orphaned order is visible as UNKNOWN rather than silently deleted. Revenue now reconciles to the source total.
3. count(*) alongside the sum, so anyone reading the output can see the grain.

print(con.execute(f"""
    select round(sum(revenue), 2) as reported,
           (select round(sum(amount), 2) from (
               select * exclude (rn) from (
                   select *, row_number() over (partition by order_id order by ordered_at) rn
                   from orders) where rn = 1)
            where status = 'completed') as expected
    from ({answer_2}) t
""").df().to_string(index=False))

 reported  expected
    97.44     97.44

Reported equals expected. That line is the answer to “are you happy with that?”

What the interviewer wrote down

Note that the junior and senior answers contain almost the same SQL. The difference is entirely in the checking, which is also the difference in the job.

Clarifying questions that score

Ask these before writing anything. They are not stalling — each one changes the answer:

• “Is order_id unique in this file, or can it repeat?” — decides whether you dedupe.
• “Can an order reference a customer that is not in the customer file?” — decides inner versus left join.
• “Should returns and refunds net off revenue, or is ‘completed’ the whole definition?”
• “Is ordered_at in UTC or local time?” — decides whether the daily grain is stable.
• “How much data — thousands of rows, or billions?” — decides whether this is one query or a partitioned job.

print(con.execute("""
    select status, count(*) as n, round(sum(amount), 2) as total
    from orders group by 1 order by n desc
""").df().to_string(index=False))

    status  n  total
 completed  6 128.60
  returned  1  40.00
   pending  1  63.20

Profiling the input before answering takes ten seconds and often answers your own clarifying questions. Interviewers notice when you look at the data first.

Scaling the same question

“Now it is 40 billion rows a day in S3. Same output.”

The SQL barely changes; everything around it does. A strong answer names the four decisions:

plan = textwrap.dedent("""
    Format      Parquet, zstd — typed, columnar, ~5x smaller than CSV
    Layout      partition by ordered_at (day); sort by customer_id within
    Compute     push the aggregate down (Spark / warehouse SQL), never collect raw
    Idempotency delete-insert the partition for the run date, in one transaction
    Late data   3-day lookback window + merge on order_id
    Checks      row count vs source, orphan count, revenue reconciliation
    Output      one row per (day, country); ~200 rows/day
""").strip()
print(plan)

Format      Parquet, zstd — typed, columnar, ~5x smaller than CSV
Layout      partition by ordered_at (day); sort by customer_id within
Compute     push the aggregate down (Spark / warehouse SQL), never collect raw
Idempotency delete-insert the partition for the run date, in one transaction
Late data   3-day lookback window + merge on order_id
Checks      row count vs source, orphan count, revenue reconciliation
Output      one row per (day, country); ~200 rows/day

Every line of that is covered in the site’s data engineering track — file formats, partitioning, idempotency, late-arriving data and quality checks are the same five topics that make up the design round in lesson 4.

How to prepare

The highest-return preparation is not more problems. It is taking one pipeline you have actually built and being able to say, for thirty minutes: what it does, what broke, what you measured, and what you changed. That covers the deep dive and most of the behavioural round.

Practice

 orphaned_orders: 1
 duplicate order_id: 1007 (n=2)

 source_total  joined_total
        128.6        108.61

 reported  expected
    97.44     97.44

    status  n  total
 completed  6 128.60
  returned  1  40.00
   pending  1  63.20

Next: the SQL round — the twelve patterns that cover almost every question asked.