Chapter 39: The Data Engineering Interview

"They asked me to design a pipeline. I spent thirty-five minutes on the diagram and four on what happens when the source is late, and I found out afterwards that the ratio was the answer."

Overview

Data engineering interviews have a distinctive shape and the shape is learnable.

They are heavier on system design and SQL than a general software interview, lighter on algorithms, and they have one round that barely exists elsewhere: a conversation about data correctness — idempotency, late data, duplicates, and what you do when the numbers disagree.

This chapter is about that shape, and it makes three arguments that run against common preparation advice:

The system-design diagram is worth about 10% of what is being scored (§39.3), and requirements clarification is worth 25%. Most candidates invert that, and it is the cheapest correction available.

The SQL round is not testing SQL. It is testing whether you notice the awkward rows — the customer with no orders, the same SKU on two lines, the refund that settles in a different month (§39.6). code/interview_drills.py has twelve problems built entirely out of those cases.

And the project you just finished is worth more than any certification (§39.8) — but only if you present it as an engineering artifact rather than as a tutorial you completed, and the difference is almost entirely in whether you can say what went wrong.

One framing to carry throughout. An interview is two evaluations running simultaneously, and §39.10 is about the one people neglect: theirs of you, and yours of them. A role titled "data engineer" that is dashboard maintenance is a common outcome, it is knowable in advance, and it is knowable by asking questions whose answers you have thought about.

39.1 The Shape of the Loop

A typical data engineering loop, and what each round is actually for:

Three observations that change how you prepare:

System design and the correctness round together are usually more than half the decision, and they are the two most similar to the actual job.

The algorithm round is often absent, and where it exists it is usually easier than a general software interview's. Do not spend your preparation there unless the company has told you otherwise.

The correctness round is the one nobody prepares for, because it does not exist in general software interviews and has no LeetCode. Parts IV and V of this book are the preparation, and §39.7 is what it looks like.

39.2 What They Are Actually Screening For

Interviewers are looking for a small number of signals, and knowing which ones lets you spend your words where they count.

Have you operated something? The single strongest signal, and it comes out in the details: you mention what broke. A candidate who says "we used Airflow" and a candidate who says "we used Airflow, and the thing that bit us was that a sensor holding a worker slot deadlocked the pool" are scored very differently, and the second sentence takes four seconds.

Do you know that data is different from code? Idempotency, backfills, late-arriving data, the fact that a bug ships wrong numbers rather than an exception. §39.7.

Can you say what you do not know? Enormously underrated. "I have not run Flink in production; I have run Spark Structured Streaming and I know the watermark model" is a strong answer. Bluffing is detected roughly 100% of the time and ends the round.

Do you reason about cost and scale? Chapter 33. Any arithmetic at all puts you in a small minority.

And will you be pleasant to be on call with? The behavioral round is mostly this.

39.3 System Design: What Is Actually Scored

--rubric prints the weighting Kestrel's team uses, and it is representative:

 25%  ############   requirements clarified before designing
      volume, latency, freshness, consistency, who consumes it
 20%  ##########     a defensible choice with a stated trade-off
      not 'we use Kafka' but 'Kafka because X, at the cost of Y'
 20%  ##########     failure modes named and handled
      what happens when the source is late, duplicates, or lies
 15%  #######        correctness: idempotency, ordering, exactly-once scope
      the single strongest signal of experience
 10%  #####          cost and scale reasoned about with numbers
      any arithmetic at all puts you in a small minority
 10%  #####          the architecture diagram itself
      what candidates spend most of their time on

The diagram is 10%. Requirements are 25%.

🎓 Interview Angle — the first five minutes are worth a quarter of the score

"Design a pipeline to compute daily revenue" is not a specification, and treating it as one is the most common way strong candidates score badly.

Six questions, and asking them is the answer to the first 25%:

text 1. How much data? Rows per day, bytes per day, and the peak. 2. How fresh does it need to be? A number, in minutes or hours. 3. Who consumes it, and what decision do they make with it? 4. What happens if it's late? What happens if it's wrong? 5. Does history change? Can yesterday's number move? 6. What exists already?

Question 5 is the one that marks you out, because it is the question that distinguishes a data pipeline from a service, and most candidates never ask it. If yesterday's number can change — refunds, corrections, late events — the whole design is different (Chapter 38 §38.5), and an interviewer who put that in the problem is waiting to see whether you find it.

Question 4 is the second most valuable, because "what happens if it's wrong" is where you get to talk about reconciliation, and almost nobody does.

Two practical notes. Ask them in the first five minutes, not scattered through — it reads as a method rather than as hesitation. And write the answers down where the interviewer can see them; you will refer back, and it demonstrates that the requirements are constraining the design rather than decorating it.

What not to do: ask six questions and then design as if you had not. The requirements have to visibly change something. "Because you said hourly rather than real-time, I'm going to use a batch micro-load rather than a stream, which removes the watermark problem entirely" is the sentence the 25% is for.

39.4 A Worked System Design

"Design a system to compute daily revenue for an e-commerce company."

A strong answer, compressed. Notice how much of it is not the diagram.

Requirements (5 min). 2.4M orders/year, ~6,600/day, peak 6.28× on Black Friday. Freshness: by 06:00 for a 06:15 executive review. Consumers: one dashboard, one board pack, one finance close. History changes — refunds settle up to 90 days later. Existing: Postgres source, an object store, a warehouse.

The shape (5 min).

Postgres --CDC--> object store (bronze, append-only)
                       |
                       v
                  silver: typed, deduplicated
                       |
                       v
                  gold: fct_order_line -> daily_revenue
                       |
                       v
                  dashboard + a monthly reconciliation

The choices, with trade-offs (10 min). CDC over a nightly full extract, because a 340 GB source takes too long to extract fully and CDC gives update history — at the cost of operational complexity and a replication slot that can fill the source's disk. Batch over streaming because the freshness requirement is hours, not seconds (Chapter 29 §29.11).

Failure modes (10 min). Source late → the DAG waits, and the SLA has 72 minutes of slack. Duplicate CDC rows → dedup on (order_id, updated_at, lsn) with a deterministic tie-break. Late refunds → fct_order_line is incremental with a 90-day lookback, so a closed month can be restated. Partial failure mid-run → every task idempotent, keyed on the run's logical date.

Correctness (5 min). "The thing I would build first is the reconciliation: gold's monthly total against a direct query on Postgres, to the cent, with every difference expressed as a documented rule. In my experience there are four or five and three of them belong to finance rather than to engineering."

Cost (5 min). ~$175/night, ~$0.018/order, 0.024% of the revenue it reports.

And what a weak answer to the same question looks like, which is worth seeing side by side because it is not obviously weak:

WEAK                                  WHY IT SCORES BADLY
"I'd use Kafka, Spark, and            three products, no requirement forced
 Snowflake"                           any of them. 0 of the 20% for trade-offs.

"...and I'd add monitoring"           named, not specified. Monitoring what,
                                      alerting whom, at what threshold?

"scale isn't a problem at this        possibly true; asserted rather than
 volume"                              computed. 0 of the 10% for arithmetic.

"I'd add tests"                       software answer. Tests of the code, or
                                      assertions on the data? (section 39.7)

35 minutes on the diagram             10% of the score, most of the time.

None of those sentences is wrong. They are all defensible, they would pass an informal conversation, and collectively they score under half, because every one of them names something without deciding anything.

🔎 Read the Plan — three sentences that change how an answer is scored

The content above is ordinary. Three specific sentences in it are not, and each takes about eight seconds:

"...at the cost of a replication slot that can fill the source's disk." Naming the downside of your own choice is the clearest available signal that you have run it. Anybody can say "we used CDC."

"...with a deterministic tie-break." Four words, and they say you have been bitten by non-determinism. Chapter 38 Case Study 2's first defect, and it is the kind of detail that cannot be faked because you would not think to mention it.

"...three of them belong to finance rather than to engineering." This is a statement about organizational understanding, not technical, and senior interviewers weight it heavily — because the failure it describes (an engineer silently deciding an accounting rule) is one they have watched happen.

The pattern: the sentences that score are the ones that could only come from experience, and they are all short. You do not need to demonstrate breadth. You need three or four moments where the interviewer thinks "they've done this."

39.5 The Take-Home, and Whether to Do It

The take-home is the round with the widest quality range, from a genuinely representative four-hour exercise to an unpaid week of work that never gets read.

Three things to establish before starting, and asking is not rude:

"How long should this take?" If the answer is more than four hours, ask what they would accept in four. A well-designed take-home has a core and extensions; a badly-designed one does not know the difference.

"Who reads it, and what are they looking for?" A good answer names a rubric. A vague one predicts a vague evaluation, which is the outcome you cannot prepare for.

"Will we discuss it?" A take-home with a follow-up conversation is worth doing. One without is a work sample scored by someone you will never meet, and it is where the worst outcomes happen.

💸 Cost Check — a take-home is the most expensive round for you and the cheapest for them

Price it, because nobody does.

text a 4-hour take-home 4 h of your time, ~15 min of theirs across 5 companies 20 h ~75 min at a notional $75/h $1,500 of your time

Twenty hours is half a working week, and it is spent while you are employed, in evenings, in parallel with the rest of the process.

The asymmetry is not an argument against take-homes — they are often the fairest round, they favour people who are better at building than at whiteboards, and a good one is genuinely representative. It is an argument for treating your time as a real cost and spending it selectively:

• Do at most two at a time. Beyond that the quality of each drops and you are worse off than doing fewer well.
• Reuse deliberately. A take-home that overlaps your capstone (Chapter 38) is much cheaper for you, and saying so is fine: "this is close to a project I've built; I'll adapt it and tell you what I reused." No reasonable company objects, and it demonstrates judgment.
• Decline the unbounded ones, politely and specifically: "I'd be glad to do a four-hour version — would that work?" The answer to that question is itself information about the company.

And the highest-value hour of a four-hour take-home is the last one, spent on a README that says what you built, what you left out, and why — which is §38.12's handover artifact, at small scale, and which is what a reviewer with fifteen minutes actually reads.

What a strong take-home submission contains, in the order a reviewer looks:

README            what it does, how to run it, what you left out and WHY
tests             even three. Their absence is the most common rejection.
one design note   a decision with a stated trade-off
the code          which is what candidates spend all their time on

"What I left out and why" is the highest-signal section, for the same reason §38.12's "not done" list is: it distinguishes a deliberate scope from an incomplete attempt, and a reviewer cannot tell those apart without being told.

The live-coding variant is the same round with the time compressed and the observation added, and it rewards different behaviour:

Narrate. In a take-home the reviewer sees only the result; in live coding they are scoring your process, and silence is scored as nothing rather than as thinking. "I'm going to get something wrong working first, then handle the edge cases" is a plan they can follow.

Write the wrong thing on purpose, briefly. A working for loop beats an elegant thing that does not run at minute forty. Say you know it is not the final form.

Ask about the data, not the algorithm. "Can customer_id be null here?" is the question that separates data engineers from general programmers in this round, and it is almost always yes.

And when you are stuck, say what you are stuck on. "I know I need a window function here and I'm blanking on the frame syntax" usually gets you the syntax and costs almost nothing; twenty seconds of silence costs more. Interviewers are scoring whether you would be workable to pair with, and a person who says what they are stuck on is exactly that.

39.6 The SQL Round

The SQL round is not testing whether you know window functions. It is testing whether you notice the rows that make the naive answer wrong.

code/interview_drills.py has twelve problems on a seven-order fixture, and every awkward row in it is there because a wrong answer treats it differently:

customer 5 has NO orders            -> LEFT JOIN vs INNER
order 104 is cancelled              -> the filter everybody forgets
order 105 has KS-2 on TWO lines     -> SUM, not DISTINCT
customer 3 has two orders one day   -> GROUP BY two columns
refund on 106 settles in APRIL      -> month sold vs month settled

Run it and check yourself:

python interview_drills.py --sql
python interview_drills.py --sql --problem 6

Each problem prints the expected answer and the common wrong ones with their causes — because "you got a different number" is not useful and "you probably grouped refunds by settled_at" is.

🧪 Try It — the eight questions that separate candidates

Work these against the fixture before an interview. They are the ones that come up.

text 1. every X, including those with no Y LEFT JOIN + COALESCE 2. more than one X per Y per day GROUP BY two cols, HAVING 3. the second-highest / top-N per group a window rank, not MAX 4. a running total a window FRAME -- ROWS vs RANGE 5. the gap between consecutive events LAG/LEAD, and the nulls 6. X that never had a Y NOT EXISTS, not NOT IN 7. attribute a value to the right period the join, not the timestamp 8. a rate, in integer arithmetic multiply before you divide

Numbers 4 and 6 are the ones that separate mid from senior.

On 4: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and RANGE ... differ when the ordering column has duplicates — RANGE includes every peer row, ROWS does not. The fixture has two orders on the same date precisely to expose this, and most candidates do not know their engine's default (it is RANGE).

On 6: NOT IN (SELECT ...) returns no rows at all if the subquery contains a single NULL, because x NOT IN (1, NULL) is UNKNOWN rather than true. NOT EXISTS does not have this behaviour. It is a five-second habit that prevents a silent empty result, and interviewers ask it because the failure is invisible.

And say your assumptions out loud. "I'm assuming a cancelled order shouldn't count — should it?" is a better answer than silently choosing, because the interviewer usually left it ambiguous on purpose.

39.7 The Correctness Round

The round that has no LeetCode, and the one Parts IV and V of this book are preparation for.

Six questions that recur, with what a strong answer contains:

"Your pipeline runs twice. What happens?" → idempotency. Delete-and-insert by partition, or a merge keyed on a natural key. Not "we'd notice" (Chapter 20).

"An event arrives three days late. What happens?" → it depends on the watermark and the allowed lateness, and a strong answer asks what the business wants before answering (Chapter 29).

"You get two rows for the same order. Which do you keep?" → the later one, by a deterministic tie-break, and "if I only order by updated_at and it ties, I get a different answer on every run" (Chapter 20, Chapter 38 Case Study 2).

"A number on a dashboard is wrong. Walk me through it." → the layer binary search (Chapter 34 §34.1): wrong in gold but right in silver, wrong in silver but right in bronze, or wrong in bronze — in which case it is not your bug and you can prove it.

"How do you know your pipeline is right?" → a reconciliation against an independent source, and "independent" is the word to emphasize (Chapter 36 Case Study 1).

"You need to backfill six months. How?" → partitioned, idempotent, rate-limited, and with the question of whether the transformation logic has changed since, which is the part that catches people.

⚠️ Failure Mode — answering the correctness round like a software engineer

The generic-software instincts are wrong here in a specific, detectable way, and interviewers are listening for it.

text question software instinct data answer runs twice "it's idempotent" "delete-and-insert by (asserted) partition, keyed on the run's logical date" bad data arrives "validate at the edge" "quarantine it, keep the row, page if the queue grows" it's wrong "add a test" "add an ASSERTION on the data, and reconcile against something independent" deploy safely "canary it" "shadow-run both and diff the outputs row by row"

The pattern is that software answers protect the system and data answers protect the numbers. A canary deployment tells you the new code does not crash; it tells you nothing about whether it computes the same values, which is the only question that matters (Chapter 37 §37.6).

The single sentence that signals you know the difference: "the failure mode I care about is the one where everything is green and the number is wrong." Say it once and mean it.

39.8 Presenting the Capstone

The platform from Chapter 38 is the strongest artifact you can bring, and it is worth more than a certification because it is checkable.

Five things to have ready, and the fourth is what separates a project from a tutorial:

A one-paragraph description. What it does, at what scale, and the acceptance criterion.

The reconciliation. "Gold reconciles to the source to the cent, for any month, with four documented rules." This is the headline and almost nobody has it.

One number you can defend. Cost per order, or the SLA slack, or the rebuild cost. Interviewers probe numbers, and a defended number is worth more than five undefended ones.

What went wrong. §38.13's four failed reconciliations, or the rebuild that did not reproduce.

And what you would do differently. §38.14.

🎓 Interview Angle — "what went wrong" is the question you are being set up for

A project write-up that reports only success reads as a tutorial completed, and interviewers ask about failures specifically to find out which it is.

The weak answer is a small, safe failure — "the config was tricky", "the docs were out of date" — which reads as either a genuinely trivial project or an unwillingness to say.

The strong answer has a shape, and Chapter 38 supplies four instances of it:

text 1. what I believed "the reconciliation would close first time" 2. what happened "it failed four times" 3. what the cause was "each was a business rule nobody had written down -- and three belonged to finance" 4. what I changed "the rules are documented with owners, and the PR template asks whether a model encodes a rule finance owns" 5. what it cost me "three of the four had been shipping wrong numbers for the length of the build"

Step 5 is the one candidates omit and interviewers listen for. A failure with no cost attached is a failure you have not fully understood — and stating the cost is what makes the whole answer credible.

And the follow-up you should expect: "how would you have caught it sooner?" Chapter 38 §38.14 has the answer — build the reconciliation first, because written at the end it is an audit and written in week one it is a specification. That answer is memorable because it is a method rather than a resolution to be more careful.

39.9 The Behavioral Round

Mostly one question in several forms: how do you behave when things are hard?

Four that recur, and what each is really asking:

"Tell me about a production incident." → were you there, and what did you do? Timeline, actions, and what you changed afterwards. Chapter 26's structure works directly.

"Tell me about a time you were wrong." → can you be wrong out loud? The strongest version includes who told you and how you responded, because the failure mode being probed is defensiveness rather than error.

"Tell me about disagreeing with a decision." → do you commit after losing? Chapter 33 §33.12's four rules for delivering a finding are a good source.

"Why are you leaving?" → will you say something disqualifying? Say what you are moving toward.

The structure that survives follow-ups is situation, action, outcome, and a number. The number is what makes it a story about engineering rather than about effort — "we cut the alert volume from 412 a quarter to 31" is a different sentence from "we cleaned up the alerting."

39.10 The Questions You Ask Them

An interview is two evaluations, and this is the neglected one.

--ask prints seven questions with what each answer tells you, which is the part nobody prepares:

"How do you know a number on a dashboard is right?"
   + a reconciliation, or an assertion suite    -> good
   - "the analysts would tell us"               -> nobody is checking

"What happened in your last data incident?"
   + a specific story with a timeline           -> healthy
   - "we don't really have incidents"           -> nothing is monitored

"Who is on call for data, and how often does it page?"
   + a rota and a number                        -> mature
   - "the whole team, informally"               -> it pages one person

"What share of your engineering time is unplanned?"
   + a number under 40%                         -> sustainable
   - "most of it"                               -> you will be firefighting

"What is the last thing you deleted?"
   + a specific pipeline or table               -> they prune
   - "nothing"                                  -> everything accumulates

🔎 Read the Plan — "what is the last thing you deleted?" is the highest-yield question

It sounds like small talk and it is the most diagnostic question on the list, for three reasons.

It cannot be prepared for. Every other question has an obvious good answer that a hiring manager can produce whether or not it is true. This one requires recalling a specific event, and the pause before the answer is informative.

Deletion requires knowing what is unused, which requires measuring usage (Chapter 25 §25.12), which almost nobody does. A team that can name something it deleted has a capability most teams lack, and did not realize they were being asked about it.

And deletion requires organizational permission. A team that knows a pipeline is unused and cannot get agreement to remove it is telling you about its politics, not its tooling — which is usually what you actually want to know and is otherwise very hard to ask about.

The follow-up that doubles its value: "how did you know it was safe to delete?" Chapter 37 §37.2's four methods are the answer, and a team that says "we renamed it for four hours and waited" is a team you can learn from.

And two questions that identify the role you do not want:

"What did the last person in this role spend their time on?" — the honest description of the job.

"How many dashboards does the team maintain, and how many pipelines?" — if the first number is much larger, the title is data engineer and the job is dashboard maintenance.

39.11 Reading a Rejection

Rejections carry information and almost none of it is in the rejection.

What the message says is legally cautious and usually true in the least useful way — "we've decided to move forward with other candidates." What is worth extracting is elsewhere.

Three things you can actually learn:

Which round ended it. Recruiters will usually say if asked directly and neutrally: "was there a particular round where I was weaker? It would help me prepare." Roughly half answer, and the answer is specific often enough to be worth the email.

Whether it was you or the requisition. "The role has been put on hold" and "we found someone with more streaming experience" are completely different events, and only one of them is about you. A surprising share of rejections are the first.

And your own honest read. Write it down within an hour, before the story smooths over: which question you fumbled, which one you bluffed, where you lost the thread.

🏭 From the Pipeline — the pattern only shows across four or five

A single rejection is nearly uninformative. Interviews have high variance: a distracted interviewer, a badly-worded question, a company that had already decided internally.

A pattern across four or five is diagnostic, and it usually has one of three shapes:

```text failing the SCREEN a specific technical gap. Fixable in weeks. -> the drills, and one book chapter

failing the DESIGN round scope or structure, not knowledge. §39.3. -> usually "didn't clarify requirements"

failing the FINAL round levelling, or fit, or they had an internal candidate. -> often not about competence, and the hardest to accept as such ```

The third is the one people misread most, because it hurts most and arrives last. A final-round rejection after four strong rounds is frequently a levelling decision — they liked you and not at that level, or at that salary — and the correct response is to ask whether a different level is open, which people almost never do and which works often enough to be worth the discomfort.

The one thing not to do is optimize against a sample of one. Rewriting your whole approach after a single bad round is how people end up worse: the round you failed may have been the one you handled best, evaluated by the person who was least interested.

📏 Scale Note — the process is a funnel and the numbers are worse than they feel

A rough shape for an experienced candidate in a normal market, and it is worth knowing so that ordinary outcomes do not read as failure:

text applications 40 recruiter screens 12 30% technical screens 8 67% of screens full loops 4 50% offers 2 50%

Two offers from forty applications is a normal, successful search, and it means 38 rejections.

The stage that dominates is the first. Going 40 → 12 is the widest cut and the one least related to engineering ability — it is résumé shape, referral, and timing. An hour spent on a referral is worth more than an hour of LeetCode, and that is uncomfortable and true.

And the numbers are much better with a referral, typically by a large factor at the first stage. Which makes §39.10's questions do double duty: the people you ask them of are people you have now spoken to, and a good conversation in a company you did not join is a referral eighteen months later.

📐 Design Decision — what to build in a take-home, when you cannot build everything

A take-home exercise is a scope decision under a time limit, and the way you scope it is what is being assessed — more than the code.

The failure mode is completeness. A candidate who builds every feature in the brief, badly, scores below one who builds a third of it well and says why.

text what to spend the time on what to write a paragraph about ───────────────────────────────────────────────────────────────────── ONE path, end to end, working the paths you did not build an assertion that can fail, and evidence it does the assertions you would add next idempotency on the write, tested the failure modes you know about a README with the trade-offs what would change at 100x

The README is the highest-scoring artifact and it is the one people write last, at midnight. Write it first. It costs twenty minutes and it converts every omission from a gap into a decision.

Four things to put in it:

What you built and what you deliberately did not, with one line of reasoning each.

The assumption you were least sure about. "I assumed updated_at is set at commit; if it is set at statement start, this loses rows and the fix is CDC." This single sentence does more than any amount of code, because it demonstrates the reasoning the job requires.

How to run it, from a clean clone, in one command that works.

And how you would know it was wrong. The reconciliation you would build, or did.

On time-boxing: state the box and hold it. "I spent four hours; here is what I would do with the next four." An unbounded take-home advantages people with unbounded time, and a candidate who says where they stopped is easier to evaluate than one who does not.

And the version of this that matters after you are hired is identical, which is why it is worth practising: every real project is scoped under a constraint, and the artifact that gets you promoted is the paragraph explaining what you chose not to do (Chapter 40's scope-of-ambiguity axis).

🧱 Kestrel Platform — your capstone is your best interview answer

The hardest interview question for anyone without industry experience is "tell me about an incident." If you have built the platform in this book, you have four.

```text the question the answer you already have ───────────────────────────────────────────────────────────────────────── "tell me about a bug you found" the tiebreaker in the dedup: the row count was right, the uniqueness test passed, and a rebuild chose differently (ch 18, ch 38 CS2)

"how do you know data is right" the reconciliation, and the two independent ratios agreeing to three decimals (ch 38)

"a time you disagreed" the sessionization boundary: 1,847 short sessions that looked like real user behaviour (ch 18)

"something you'd do differently" the "not done" list (§38.10), which you wrote for exactly this

"a trade-off you made" DuckDB over the warehouse, with the cost arithmetic and the operational argument (ch 22) ```

Two things make these answers work where a project description does not.

They have numbers. "1,847 phantom sessions against 12" and "two independently derived ratios agreeing to three decimal places" are specific in a way that is very difficult to fabricate, and specificity is the signal an interviewer is reading for.

And they include what you got wrong. The boundary artifact was in your own pipeline; the tiebreaker was missing from your own model. A candidate describing their own defect, and how they found it, is demonstrating the exact behaviour the job requires — which a description of a working system cannot do.

The one-pager from Exercise 39.8 is where these live. One page: the problem, the shape, the hardest part, and the number it reconciles to. Written once, used in every loop, and it is the artifact that turns "a project from a book" into something worth twenty minutes of an interviewer's attention.

🔁 Idempotency Check — the dedup question, and the follow-up that decides the round

The most common SQL question in a data engineering loop is a deduplication, and the code is not what is being assessed.

sql -- what almost everyone writes, and it is CORRECT as far as it goes SELECT * FROM orders_raw QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

The follow-up is always some version of: "what if two rows share an updated_at?"

The weak answer is "it picks one." True, and it stops one step short.

The strong answer:

"Then it's non-deterministic — it picks whichever the engine read first, and that can differ between runs, between engines, and after a rewrite of the underlying files. So the count is right, a uniqueness test passes, and a rebuild months later produces a different table with no error. I'd append something strictly unique as the last ORDER BY term — a Kafka offset, a sequence, a file position. And I'd test it by checking whether ties exist in the data at all, because a static check on the SQL is more reliable than running the query twice and hoping the order differs."

Four things that answer does. It names the property — non-determinism, not incorrectness. It says what the failure looks like (right count, passing test, different rebuild). It gives the fix in one clause. And it says how to test it in a way that does not depend on luck, which is the detail almost nobody offers.

The same shape recurs across the whole loop, and it is worth recognising: the question has a correct answer, the follow-up asks what breaks, and the round is decided by whether you can name the failure mode rather than by whether you can write the query.

Two more with the same structure, worth rehearsing: "your incremental model is idempotent — how do you know?" (run it twice, EXCEPT both directions) and "the job retried and the data is now wrong — what happened?" (an append, a wall-clock read, or a non-deterministic transformation re-executed).

🧭 Version Note — what the loop looks for changes, and one thing does not

The shape of a data engineering interview has moved twice in a decade, and knowing what era a company's process is from tells you what to prepare.

text era the loop what it selected for ───────────────────────────────────────────────────────────────────────── ~2014 Hadoop trivia, Java, and a MapReduce whiteboard familiarity with one stack ~2018 LeetCode plus "design a data warehouse" general software interviewing, applied badly ~2021 SQL, a pipeline design, and a take-home closer to the job now SQL, design, an incident narrative, and a CORRECTNESS round the job

The correctness round is the genuinely new one — "how do you know the data is right?" — and it is the round most candidates have not prepared for, because no amount of algorithm practice touches it.

Two practical consequences.

Ask what the loop contains, before it starts. Every recruiter will tell you. A loop with no correctness round and a heavy algorithm round is a company whose data engineering is a software engineering function, which is worth knowing about the job as well as about the interview.

And prepare the incident narrative regardless. It appears in every era's loop under a different name — "tell me about a hard problem", "a time you disagreed", "something you'd do differently" — and it is the only question where a specific number about your own mistake is the strongest possible answer (§39's 🧱).

What has not changed across all four eras: the follow-up decides the round. The first answer establishes that you know the thing; the second and third establish whether you have done it, and that has been true of every technical interview anybody has ever run.

🔐 Privacy & Governance — what you may and may not take with you

Your best interview material is the work you have done, and some of it is not yours.

text safe to discuss NOT safe ───────────────────────────────────────────────────────────────────── the shape of a problem your employer's actual numbers a technique, and why it worked a customer's name, or a partner's "a mid-sized retailer with a few an architecture diagram from an million orders a year" internal wiki your own reasoning and mistakes code, in any quantity the capstone from this book, in a take-home from another company's full detail interview, reused

The left column is enough. "Revenue was reported about 11% high for a month, and the row-count check was the thing that was green" carries the whole story with no proprietary content — and it is a stronger answer than a precise figure would be, because the interviewer is assessing your reasoning rather than auditing your employer.

Three practical rules:

Round and anonymise by default. "A few million orders a year", "a mid-sized retailer", "eight figures of GMV". Nobody has ever been impressed by a number that made them wonder whether you should have said it.

Never bring code. Not a snippet, not a screenshot. A candidate who shows an internal repository has told the interviewer exactly what they will do at the next company, and every interviewer notices.

And the capstone in this book is genuinely yours to show — the platform, the reconciliation, the audit — which is one of the more useful things about having built it. A public artifact you can walk through end to end removes the whole problem.

The uncomfortable case worth deciding in advance: an interviewer asks a question you can only answer well with something confidential. The answer is to say so — "I can describe the shape but not the numbers" — and then describe the shape. It costs nothing and it is itself a signal, which is the point.

📏 Scale Note — the loop differs by company size, and so should your preparation

text a startup (<50) mid-size (50-500) large (500+) ──────────────────────────────────────────────────────────────────────── rounds 2-3 4-5 5-7, plus a hiring committee what decides it can you build the can you build can you reason at whole thing WELL within a scale, and work team across teams SQL round practical, on their data standardised standardised, and frequently the weakest signal design round "here is our a generic pipeline a system with actual problem" design constraints and trade-offs the incident question always always always what to prepare breadth, and a the correctness scale reasoning, working end-to- round and a specific end example trade-off story

The row that is the same in every column is the incident question, which is why Chapter 39 spends more on it than on SQL.

Two asymmetries worth planning around.

A startup's loop is shorter and higher-variance. One conversation can decide it, and the artifact that helps most is something they can look at — the one-pager, or a public repository (§39's 🧱).

A large company's loop is longer and more standardised, which means preparation genuinely pays: the questions are drawn from a set, the rubric is written down, and the correctness round and the design round are where the differentiation is, not the SQL.

And the levelling conversation is where the size difference is largest. At a startup the title is negotiable and means little; at a large company the level is decided by evidence of scope (Chapter 40 §40.4) — which is a different thing from evidence of skill, and it is worth preparing two examples of ambiguity you resolved rather than two examples of things you built.

39.12 Levelling, Scope, and the Offer

Three practical points, briefly.

Level is set by scope, not by years. The distinction interviewers use: a mid engineer builds what they are asked to build; a senior decides what should be built; a staff engineer changes what the organization builds. Chapter 40 is the long version.

Interview at your level and one above. The failure mode is under-levelling, which is far harder to correct after joining than before.

And take the reverse interview seriously enough to walk away. The cost of a wrong job is a year, which dominates any offer difference you could negotiate, and §39.10's questions are how you find out in forty minutes.

39.13 Summary

Data engineering interviews are heavier on system design and SQL, lighter on algorithms, and have one round that barely exists elsewhere: data correctness. That round has no LeetCode, and Parts IV and V of this book are the preparation.

🎓 Requirements are 25% of the system-design score and the diagram is 10%, and most candidates invert it. Six questions in the first five minutes, and question 5 — does history change? — is the one that marks you out, because it distinguishes a data pipeline from a service. Then let the answers visibly change the design.

🔎 The sentences that score are short and could only come from experience: naming the downside of your own choice · "with a deterministic tie-break" · "three of these rules belong to finance." You need three or four moments where the interviewer thinks "they've done this."

🧪 The SQL round tests whether you notice the awkward rows, not whether you know window functions: the customer with no orders, the cancelled order, the same SKU on two lines, the refund settling in another month. ROWS versus RANGE when the ordering column has duplicates, and NOT EXISTS rather than NOT IN, are the two that separate mid from senior. And say your assumptions out loud — the ambiguity is usually deliberate.

⚠️ Do not answer the correctness round like a software engineer. "It's idempotent" asserted versus "delete-and-insert by partition"; "add a test" versus "add an assertion on the data and reconcile against something independent"; "canary it" versus "shadow-run both and diff row by row." Software answers protect the system; data answers protect the numbers. The sentence that signals you know the difference: "the failure mode I care about is the one where everything is green and the number is wrong."

🎓 The capstone beats a certification because it is checkable. Lead with the reconciliation, defend one number, and have "what went wrong" ready in five parts — what you believed, what happened, the cause, what you changed, and what it cost you. The fifth is the one candidates omit and interviewers listen for.

Behavioral answers need a number. "412 alerts a quarter to 31" is engineering; "we cleaned up the alerting" is effort.

🔎 Ask questions whose answers you have thought about. "What is the last thing you deleted?" is the highest-yield one: it cannot be prepared for, it requires knowing what is unused, and it requires organizational permission to remove it — which is usually what you actually want to know. And "how many dashboards versus how many pipelines" identifies the role titled data engineer that is not one.

💸 Price the take-home. Four hours of yours is fifteen minutes of theirs; five companies is half a working week. Do at most two at a time, reuse your capstone deliberately and say so, and decline the unbounded ones by offering a four-hour version — the answer to that offer is itself information. And spend the last hour on the README, which is what a reviewer with fifteen minutes actually reads. "What I left out and why" is the highest-signal section, because a reviewer cannot otherwise distinguish deliberate scope from an incomplete attempt.

🏭 A single rejection is nearly uninformative; a pattern across four or five is diagnostic. Failing the screen is a fixable technical gap; failing the design round is usually structure rather than knowledge; failing the final round is frequently a levelling decision — and the right response is to ask whether a different level is open, which people almost never do. Do not optimize against a sample of one.

📏 The funnel is worse than it feels: roughly 40 applications → 12 screens → 8 technicals → 4 loops → 2 offers. Two offers is a successful search and 38 rejections. The widest cut is the first and it is the one least related to engineering ability — an hour spent on a referral is worth more than an hour of LeetCode, which is uncomfortable and true.

Level is set by scope, not years. Interview at your level and one above. The cost of a wrong job is a year, which dominates any negotiable difference.

Chapter 40 is the career — the levels, the specializations, how to tell a healthy data team from outside, and how to stay current in a field whose tools turn over every five years without chasing every one of them.

Key terms: system design round · requirements clarification · correctness round · take-home · portfolio project · reverse interview · levelling · scope · signal