Exercises: The Data Engineering Interview

These are practice, not study. Most of them require a timer, a whiteboard, or another person. Exercises marked 🧪 use code/interview_drills.py.

Warm-Up

Exercise 39.1 — Score yourself against the rubric

Difficulty: ★☆☆ · Time: 20 minutes · 🧪

python code/interview_drills.py --rubric
  1. Recall your last system-design interview (or a design discussion at work). Score yourself on all six lines.
  2. Where did your time go? Estimate the minutes spent on each.
  3. §39.3 says most candidates invert requirements (25%) and the diagram (10%). Did you?
  4. Which single line would gain you the most, and what would you do differently?

Exercise 39.2 — The six questions

Difficulty: ★☆☆ · Time: 20 minutes

  1. Write §39.3's six requirements questions from memory.
  2. Apply them to three problems: "design a system for real-time inventory," "design a customer 360 view," "design an ML feature pipeline."
  3. For each, say which question's answer would change the design most.
  4. §39.3 says question 5 — does history change? — is the one that marks you out. Why that one?

Exercise 39.3 — Read the drills

Difficulty: ★☆☆ · Time: 30 minutes · 🧪

python code/interview_drills.py --sql
  1. The fixture has five deliberately awkward rows. Find all five before reading the comments.
  2. For each of the twelve problems, predict the answer before looking.
  3. Which of the listed wrong answers have you produced before? Be honest; it identifies your gaps.

Core

Exercise 39.4 — Do the drills properly

Difficulty: ★★☆ · Time: 2 hours · 🧪

  1. Write the SQL for all twelve, against the fixture, in whatever engine you use.
  2. Compare against the expected answers. Record which ones you got wrong and why.
  3. For problems 4 and 8, check your engine's default window frame. Is it RANGE or ROWS?
  4. For problem 10, write it with NOT IN and add a NULL to the subquery. Watch it return nothing.
  5. §39.6 says 4 and 6 separate mid from senior. Do you agree after doing them?

Exercise 39.5 — Time yourself on a design

Difficulty: ★★☆ · Time: 45 minutes

  1. Set a 45-minute timer. Design a system to compute daily active users from a clickstream.
  2. Record yourself or write timestamps as you go.
  3. Afterwards, score against the rubric and compute the actual minutes spent per line.
  4. Case Study 1's candidate B spent 35 of 45 minutes on the diagram. What did you spend it on?

Exercise 39.6 — The trade-off sentence

Difficulty: ★★☆ · Time: 30 minutes

Case Study 1's pattern:

"<choice>, because <requirement it satisfies>,
 at the cost of <specific operational downside>,
 which means <who is affected and what they need to do>."
  1. Write it for five technologies you have actually run. The downside must be specific and operational.
  2. Which ones can you not complete? Those are technologies you have used and not operated, and it is useful to know which.
  3. Now write it for three you have not run. Notice how much weaker the downside clause is — that is what an interviewer hears.

Exercise 39.7 — The correctness round

Difficulty: ★★☆ · Time: 45 minutes

§39.7's six questions.

  1. Answer all six out loud, timed at three minutes each. Record yourself.
  2. Play it back. How many of your answers are the "software instinct" column rather than the data one?
  3. For "how do you know your pipeline is right," do you say the word independent? (Chapter 36 Case Study 1.)
  4. Add a seventh: "you need to change a transformation that six months of history was built with. What do you do?" Answer it.

Exercise 39.8 — Write the project one-pager

Difficulty: ★★☆ · Time: 60 minutes

§39.8's five things.

  1. Write the one-paragraph description of your capstone. Scale and acceptance criterion.
  2. Write the reconciliation sentence. "Gold reconciles to source to the cent, for any month, with N documented rules."
  3. Pick one number and prepare to defend it — including how you computed it and what it does not account for.
  4. Write "what went wrong" in five parts: what you believed, what happened, the cause, what you changed, what it cost you.
  5. Give the one-pager to someone. Ask them what question it prompts — that is what you will be asked.

Exercise 39.9 — The reverse interview

Difficulty: ★★☆ · Time: 40 minutes · 🧪

python code/interview_drills.py --ask
  1. Apply §39.10's test to every question you currently ask: could the answer have been bad?
  2. Rewrite the three weakest so a bad answer becomes possible.
  3. Ask "what is the last thing you deleted?" of your own team. What is the answer?
  4. Compute your own team's pipelines-to-dashboards ratio, and its trend.
  5. Case Study 2's engineer asked four questions that could not go badly. How many of yours can?

Advanced

Exercise 39.10 — Extend the drills

Difficulty: ★★★ · Time: 90 minutes · 🧪

Add three to interview_drills.py, with self-checks:

  1. Three more SQL problems whose wrong answers come from the fixture's awkward rows — one on a self-join, one on a pivot, one on a gaps-and-islands question.
  2. A --check mode that takes your answer and reports which listed wrong answer it matches.
  3. A second fixture with nulls in the join keys, so problem 10's NOT IN failure is live.
  4. A design-round timer that prompts you through the rubric's six lines with a budget for each.
  5. A behavioral-answer checker that asks whether your answer contains a number.
  6. A company-scoring sheet built from §39.10's answers.

Then: #2 is the useful one. What does it require you to think about that a bare expected answer does not?

Exercise 39.11 — Run a mock loop

Difficulty: ★★★ · Time: 3 hours

With another person, ideally one who has interviewed candidates.

  1. A 45-minute system design, scored against the rubric, with the scores shared afterwards.
  2. A 30-minute SQL round using the drills.
  3. A 30-minute correctness round using §39.7's six questions.
  4. Twenty minutes of feedback, and ask specifically: "was there a moment where you were not sure I had done this?"
  5. Swap roles. Interviewing someone teaches this faster than being interviewed.

Exercise 39.12 — Diagnose a rejection

Difficulty: ★★★ · Time: 45 minutes

§39.11.

  1. Take a rejection you have had. Write down which round ended it, and whether you know or are guessing.
  2. Send the email asking which round was weakest. Note whether you get an answer.
  3. §39.11 says a single rejection is nearly uninformative. Do you have four or five to look across? What is the pattern?
  4. Have you ever asked whether a different level was open? §39.11 says it works often enough to be worth the discomfort.

Exercise 39.13 — Interview a company you are not going to join

Difficulty: ★★★ · Time: 60 minutes

The most useful exercise in this chapter, and the least comfortable.

  1. Take a call with a company you are not seriously considering.
  2. Ask all seven of §39.10's questions, and score their answers.
  3. Record what you learn about the questions themselves — which ones produced information, which got deflected, which felt rude.
  4. Refine the list. Then use it where it matters.
  5. Case Study 2's engineer sorted four companies in four conversations. How discriminating was your list?

Project Milestone

Exercise 39.14 — Be ready

Difficulty: ★★★ · Time: 4–6 hours

The deliverable is a folder you can open the morning of an interview.

  1. The project one-pager (Exercise 39.8), one page, with the reconciliation sentence in the first three lines.
  2. Five trade-off sentences (Exercise 39.6) for technologies you have actually run.
  3. The twelve SQL drills, completed, with your wrong answers and their causes noted.
  4. Six correctness answers, written out, in the data column rather than the software one.
  5. Four behavioral stories, each ending in a number.
  6. Your reverse-interview list, with what each answer would mean — and a rule for what would make you decline.
  7. The three things you do not know, stated the way §39.2 recommends: "I have not run X; I have run Y and I know Z."

Deliverable: the folder, and a mock loop (Exercise 39.11) run against it.

Item 6's last clause is the one people skip. A reverse-interview list with no decline rule is a list you will read the answers of and rationalize. Write the rule before you need it — which is Chapter 26 §26.8's pre-decided call, and Chapter 38 §38.14's build the reconciliation first, applied to your own career.