Data Engineering Interview Questions and Answers

Data Engineering & Modern Data Platforms · Lesson 65 · Production Data Engineering and Capstone

Lesson Overview

The panel has a whiteboard, a clock, and a printed Monday board from TechLearningPro. They do not ask you to recite Spark configuration keys. They ask why last Monday's completion rate jumped two points after a schema deploy that nobody thought touched completion. They ask how you would replay Kafka without double-counting enrollments. They ask what you measure before you resize a warehouse. The candidate who wins is the one who can turn a vague business complaint into a grain, a cursor, a check, and a refusal list.

This lesson is not a dump of trivia from earlier modules. Lessons 1 through 64 already gave you the engines, the contracts, the cost sheet, and the SLO. Data Engineering System Design practiced end-to-end architecture. Here you practice the interview form of the same craft: clarifying questions, scored answers, trade-offs said out loud, and scenarios that punish memorized scripts. The bank below covers SQL, Spark, Kafka, modeling, reliability, and cloud. The angles are new. The scoring rubrics are explicit so you can grade yourself the way a staff engineer grades a hire.

TechLearningPro remains the spine. About 40 GB of compacted Parquet progress a day. A completion mart pinned to definition_version = 'completion-v4'. A Monday board that must be correct by 06:00. Northline appears when refunds and order lines make a cleaner finance story. Treat every answer as something you would defend in a design review, not as a phrase you hope the interviewer rewards.

Learning Objectives

By the end of this lesson you will be able to:

1. Open a data interview with clarifying questions that lock grain, freshness, and ownership before you draw boxes.
2. Answer SQL scenario prompts with window logic, anti-join reasoning, and prune-aware filters, not with template queries alone.
3. Explain Spark join and skew failures with stage evidence, not with "increase executors."
4. Design Kafka consumer answers that name offsets, idempotency, and poison messages as first-class concerns.
5. Defend dimensional and lakehouse models with slowly changing facts and a single certified publisher.
6. Score reliability answers against SLIs, error budgets, and replay, using the language of Data Reliability and SLA/SLO Design.
7. Price cloud answers with meters from Data Platform Cost and Performance Optimization: scans, idle capacity, movement.
8. Use a five-level rubric (signal → structure → trade-off → evidence → refusal) to grade any spoken answer.
9. Separate "I would Google that API" from "I do not know what correctness means here," and say both honestly when true.
10. Walk a system-design whiteboard for TechLearningPro completion without inventing a second mart.
11. Handle behavioral follow-ups that ask about incidents, using structured postmortem language without blame theater.
12. Spot interviewer traps: premature tool choice, silent late data, and cost cuts that delete checks.
13. Produce a personal drill sheet of twenty-five questions with timed answers and self-scores.
14. Hand Data Engineering Practical Exercises a list of weak topics to turn into labs.
15. Arrive at the quiz lesson ready to convert interview answers into MCQ distractors and architecture prompts.

Prerequisites

• Lessons 59 through 64 for DataOps, observability, governance, reliability, deployment, and system design. Interview answers inherit those constraints.
• Module 3 Spark lessons, especially joins, shuffle, and performance, so skew answers cite stages rather than myths.
• Module 4 Kafka lessons for producers, consumers, schema registry, and streaming–batch cursors.
• Module 5 lakehouse and cloud lessons for Iceberg or Delta merges, single-engine serving, and cost meters.
• Advanced SQL and Window Functions and Data Modeling Fundamentals for grain and SCD language.
• Willingness to write answers under a timer. Reading is not the practice. Speaking and scoring are.

Detailed Core Concepts

What an interview actually scores

Hiring panels score five layers, whether or not they say so.

1. Signal. Did you hear the real problem? Grain wrong, freshness wrong, or ownership missing are signal failures. Tool names cannot recover them.
2. Structure. Did you separate ingest, transform, publish, and serve? Did you name failure modes before optimization?
3. Trade-off. Did you compare two workable designs and say what each spends?
4. Evidence. Did you name a metric, a dry run, a query plan, an offset lag chart, or a row count check?
5. Refusal. Did you refuse a design that breaks the SLO, the contract, or the single publisher?

A polished vocabulary with no refusal list usually loses to a plainer answer that says "I will not drop the grain check to make the window." Practice all five layers on every question below.

Clarifying questions you should ask first

Before you draw, ask:

• What is the grain of the number leadership cares about?
• What is the freshness promise, and what happens if you miss it?
• Who owns the definition when product and analytics disagree?
• What volume, peak, and growth are we designing for in the next twelve months?
• Which systems are source of truth versus derived?
• What must be replayable, and what must be exactly-once from the business's point of view?

Write those questions on the whiteboard. Interviewers often grade the questions harder than the first box you draw.

Scoring rubric used throughout this lesson

Aim for level 4 in timed drills. Level 5 is the staff signal. Self-score honestly after each answer. Weak topics become labs in lesson 66 and quiz targets in lesson 67.

Curated bank A — SQL (questions 1–6)

Q1. Scenario. Monday's board shows unique learners who completed a lesson this week. Product says the number is high because users can complete the same lesson twice after a reset. Write the clarifying questions, then the SQL shape.

Model answer. Clarify whether completion after reset is a new attempt or a replacement. Clarify whether the board wants attempt count or distinct learner_id per lesson_id under completion-v4. Then:

sqlCopy
SELECT lesson_id, COUNT(DISTINCT learner_id) AS learners_completed
FROM published.completion_attempt
WHERE event_date BETWEEN DATE '2026-11-03' AND DATE '2026-11-09'
  AND definition_version = 'completion-v4'
  AND is_complete = TRUE
GROUP BY lesson_id;

If resets replace, use a window to keep the latest attempt per (learner_id, lesson_id) before counting. Say why COUNT(*) would lie. Score yourself on whether you pinned definition_version without being prompted.

Q2. Explain the difference between WHERE and HAVING using progress events that must exclude bots, then keep lessons with at least 100 distinct learners.

Model answer. Filter bots in WHERE (row grain). Aggregate. Apply HAVING COUNT(DISTINCT learner_id) >= 100. Putting the distinct threshold in WHERE is a syntax error or a wrong rewrite. Putting bot filters in HAVING wastes work and can hide null-handling mistakes.

Q3. You need the latest enrollment status per learner without a unique constraint on the source. Defend a window approach versus GROUP BY MAX(updated_at) joined back.

Model answer.

sqlCopy
SELECT *
FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (
           PARTITION BY learner_id
           ORDER BY updated_at DESC, source_lsn DESC
         ) AS rn
  FROM raw.enrollment e
) x
WHERE rn = 1;

MAX(updated_at) alone ties. You need a deterministic tie-break (source_lsn, event_id). The join-back pattern duplicates logic and can multiply rows on ties. Prefer the window, then assert uniqueness in a quality check.

Q4. Anti-join: find learners with progress in the last seven days who have no completion attempt under completion-v4.

Model answer. Prefer NOT EXISTS or a left anti-join with IS NULL on the right key. NOT IN with nullable completion keys silently drops rows. Say that out loud. Interviewers listen for null safety.

Q5. A query with DATE(event_time) = CURRENT_DATE on a table partitioned by event_date scans the year. Why, and how do you rewrite?

Model answer. Wrapping the partition column (or filtering a different column) blocks pruning. Filter event_date = CURRENT_DATE (or the engine's partition column) directly. Show that you would dry-run bytes before and after. Link the habit to lesson 58's scan meter.

Q6. Window frame trap: running seven-day unique learners using COUNT(DISTINCT ...) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) fails in some engines. What do you do?

Model answer. Many engines disallow distinct aggregates in windows. Expand to daily distincts into a temp relation, then use a self-join or a bounded explode of day ranges, or compute with approximate sketches only if the board allows approximation and labels it. Do not pretend a forbidden construct works. Propose a verified rewrite and a gold comparison for one week.

Curated bank B — Spark (questions 7–11)

Q7. A join of progress to a small lesson dimension OOMs on the driver after you broadcast the dimension. What happened?

Model answer. The "small" table was not small after a bad filter, or you broadcast a large side by accident (broadcast hint on the wrong relation). Check sizes with metrics, not with hope. If lesson dim is truly small, broadcast it; if not, sort-merge and fix skew separately. Driver OOM on collect or on an oversized broadcast is a design smell, not a cluster-size problem first.

Q8. Stage skew: one task runs forty minutes, others finish in two. Progress keyed by course_id has one mega-course. Options?

Model answer. Salt the hot key, two-phase aggregate, AQE skew join if available and verified, or pre-aggregate partials. Show you would look at task time histograms and shuffle read sizes. "Add executors" without addressing the hot key wastes money and often still leaves one fat task.

Q9. Why can repartition(200) after a wide transformation make a job slower?

Model answer. Extra shuffle. Prefer partitioning that matches the next join or write key. Coalesce when reducing files without a full shuffle. Measure stage duration, not only partition count aesthetics.

Q10. You write Parquet with 50,000 tiny files for one day. What do you tell the interviewer you will change, and what will you measure after?

Model answer. Target roughly 128–512 MB files via max partition bytes, adaptive coalesce, or a compaction job before publish. Measure file count, average size, job time, and downstream scan bytes. Connect to lakehouse compaction so the commit points at compacted files before you delete small ones.

Q11. Exactly-once Spark Structured Streaming to a Kafka sink and to a lake table. What do you actually guarantee?

Model answer. End-to-end exactly-once needs idempotent sinks or transactional sinks plus checkpointing. Kafka sink semantics depend on idempotent producer and transactional configuration; lake writes need atomic commits (Delta/Iceberg). Say "at-least-once plus idempotent merge" when that is the honest design. Do not claim magic exactly-once across an external HTTP API.

Curated bank C — Kafka and streaming (questions 12–16)

Q12. Consumer lag grows only on two partitions. What do you inspect first?

Model answer. Key distribution, consumer instance assignment, stuck processing on poison messages, and GC on those pods. Scaling the consumer group does not help if keys hash to the same hot partitions. Consider key redesign or fan-out topics for the hot entity.

Q13. You must rebuild a mart from Kafka. How do you avoid double-counting?

Model answer. Store offsets or input watermarks with the batch, use idempotent keys for merges (event_id), and separate provisional streaming cursors from certified batch cursors as in streaming–batch integration. Replay is a product feature, not a panic script.

Q14. Schema registry: a producer adds a required field without a default. What breaks, and what is the interview-grade fix?

Model answer. Downstream consumers using backward compatibility fail. Prefer optional fields with defaults, contract tests in CI, and a canary consumer. Roll forward with a dual-read period. Do not "just bump the topic name" without a migration plan for lagging consumers.

Q15. Why is enable.auto.commit=true dangerous for a progress consumer that writes to a database?

Model answer. Commit can advance before the DB write succeeds, losing messages on crash; or you reprocess without idempotency and double-write. Commit after successful sink write, or use transactional patterns, and make sinks idempotent either way.

Q16. Late events arrive after a one-hour watermark. Board stakeholders want them in Monday's number. What do you propose?

Model answer. Provisional tile with watermarked stream; certified mart with a longer reconciliation window and a documented late policy. Do not silently reopen closed windows in the certified path without a version bump. Explain user-visible labels: provisional versus certified.

Curated bank D — Modeling (questions 17–20)

Q17. Is completion a fact or a dimension attribute?

Model answer. Attempt-level completion is a fact at grain (learner_id, lesson_id, attempt_id) with degenerate attributes. Current completion status on a learner-lesson pair can be a periodic snapshot fact or a type-1 attribute on a bridge, but the Monday board should read a published fact table with definition_version, not a mutable user dimension overwritten in place without history.

Q18. SCD2 for course metadata vs type-1. When is type-1 acceptable?

Model answer. Type-1 when history of the attribute does not change metrics (cosmetic title fix). SCD2 when pricing, difficulty, or certification eligibility changes must be reconstructed as-of a date. Completion metrics that depend on eligibility need as-of joins, not latest-only.

Q19. Star versus wide lakehouse table for the board. Defend one.

Model answer. A narrow certified fact plus small dimensions is easier to test and version. A wide denormalized mart can be the serving projection if generated from the certified fact by one publisher. Two hand-built wides are Northline's dual-mart failure mode. One publisher, optional projection.

Q20. How do you model resets that invalidate a prior completion without deleting history?

Model answer. Append a new attempt or a reset event; compute current state with a window; keep history for audit. Deletes as the only tool destroy replay and compliance stories. Prefer event-sourced facts and a clear current-state view.

Curated bank E — Reliability and operations (questions 21–23)

Q21. Define SLI, SLO, and error budget for the Monday board.

Model answer. SLI: fraction of weekdays where completion-v4 mart is queryable with matching grain checks by 06:00. SLO: 95% over a rolling 28 days. Error budget: the 5% misses. A change that spends budget needs explicit approval. Link to lesson 62 language without reciting a textbook.

Q22. Pipeline green, board wrong. What checks were missing?

Model answer. Row-count reconciliation to source, uniqueness on business keys, definition_version pin in the certified query, and a volume anomaly check. Green compute is not green data. Observability lesson metrics belong in the answer.

Q23. How do you rehearse disaster recovery for a lakehouse publish?

Model answer. Time-travel or version restore drill with a named owner, RPO/RTO written down, and a successful restore recorded. Cross-region copies without a drill are not backups. Say what you refuse to claim.

Curated bank F — Cloud and cost (questions 24–26)

Q24. Forty analysts scan raw JSON on Monday. What is your first redesign sentence?

Model answer. Point the board at the pruned columnar mart with an explicit event_date filter and column projection; put a cutoff on raw; measure dry-run bytes before and after. Do not open with "buy a bigger warehouse."

Q25. Capacity sits idle all weekend. When is pausing wrong?

Model answer. When measured resume time plus merge time misses 06:00. Idle hours may be the price of the SLO. Write that on the cost sheet. Otherwise pause or lower baseline.

Q26. Two warehouses hold the same mart after a bake-off. What do you say in the interview?

Model answer. Pick one serving engine, document reopen conditions for the other, stop the duplicate nightly job, and treat drift as a correctness incident. Cost and correctness fail together when two publishers exist.

How to practice the bank

Drill in rounds of five questions. Ten minutes speak, two minutes score against the rubric, one minute note the weak layer (signal, structure, trade-off, evidence, refusal). Rotate domains so you do not only practice SQL comfort. Record yourself once a week. Awkward silence while you clarify grain is better than fluent wrong grain.

Architecture and Design Diagrams

Interview flow is itself an architecture. Treat the conversation as a pipeline with quality gates.

mermaidCopy
flowchart TD
  PROMPT["Interviewer prompt"] --> CLARIFY["Clarify grain freshness ownership"]
  CLARIFY --> SKETCH["Sketch stages ingest transform publish serve"]
  SKETCH --> FAIL["Name failure modes late poison skew"]
  FAIL --> OPT["Optional optimize cost latency"]
  OPT --> REFUSE["State refusals"]
  REFUSE --> EVIDENCE["Cite metric plan check"]
  EVIDENCE --> SCORE["Self-score 0 to 5"]

TechLearningPro system-design spine you should be ready to draw in under three minutes:

mermaidCopy
flowchart LR
  APP["Learning app events"] --> K["Kafka progress topic"]
  K --> RAW["Raw landing + compact"]
  RAW --> BATCH["Batch merge completion-v4"]
  K --> PROV["Provisional streaming tile"]
  BATCH --> MART["Published mart"]
  MART --> BOARD["Monday board 06:00"]
  PROV --> TILE["Ops tile labeled provisional"]
  BATCH --> DQ["Grain uniqueness version checks"]
  DQ --> MART

If the interviewer pushes a second warehouse "for finance," draw finance as a consumer of the same published grain, not as a second publisher.

Technical Explanation

How strong answers are built under time pressure

You have roughly two to four minutes per deep question in many loops, longer on system design. Allocate time deliberately.

1. Thirty seconds: restate the problem in your grain language.
2. Thirty seconds: clarifying questions; write them.
3. Sixty to ninety seconds: happy-path architecture.
4. Sixty seconds: failure modes and checks.
5. Thirty seconds: trade-off and refusals.

If you burn three minutes on tool selection, you will never reach failure modes. Panels remember whether you mentioned poison messages and definition versions more than whether you chose brand A or brand B.

SQL depth the panel expects

They expect you to reason about predicates, cardinality, and determinism. Be ready to discuss:

• Why SELECT * breaks both cost and contract evolution.
• Why distinct counts need a defined grain.
• How to dedupe with windows and tie-breakers.
• How partition columns must appear naked in filters for pruning.
• When approximate distinct is acceptable if labeled.

Worked micro-example for dedupe before aggregate:

sqlCopy
WITH latest AS (
  SELECT learner_id, lesson_id, is_complete, event_date, definition_version,
         ROW_NUMBER() OVER (
           PARTITION BY learner_id, lesson_id, definition_version
           ORDER BY event_time DESC, event_id DESC
         ) AS rn
  FROM staged.completion_attempt
)
SELECT lesson_id, COUNT(*) AS completed_learners
FROM latest
WHERE rn = 1
  AND is_complete
  AND definition_version = 'completion-v4'
  AND event_date = DATE '2026-11-10'
GROUP BY lesson_id;

Explain that the quality gate afterward asserts rn = 1 uniqueness, not that the SQL "looks right."

Spark depth without cargo cult

Explain the physical plan path: parse → analyze → optimize → physical stages → tasks. When something is slow, you name the stage. Skew is uneven partition sizes. Shuffle is a network and disk tax you pay to re-key. Broadcast avoids shuffle when one side fits. Caching helps only when reuse beats eviction and when the plan actually reuses the cached plan. File sizing links Spark write behavior to lakehouse read behavior; interviewers love candidates who connect those meters.

Skew salting sketch you should be able to narrate:

pythonCopy
# Pseudocode: salt hot course_id values during partial aggregate
hot = {"course_mega"}
progress = progress.withColumn(
    "salt",
    F.when(F.col("course_id").isin(list(hot)), (F.rand() * 16).cast("int")).otherwise(F.lit(0)),
)
partial = progress.groupBy("course_id", "salt", "lesson_id").agg(F.count("*").alias("n"))
final = partial.groupBy("course_id", "lesson_id").agg(F.sum("n").alias("n"))

Then say you would confirm with stage metrics that the fat task disappeared, and that downstream joins still use the unsalted key.

Kafka depth that survives follow-ups

Be fluent in partitions as the unit of parallelism and ordering, consumer groups as competing consumers, offsets as the cursor, and compaction topics versus retention topics. For TechLearningPro progress, ordering per learner_id may matter for session sequences; global ordering does not. Idempotent sinks matter more than arguing about exactly-once slogans. Schema compatibility modes (backward, forward, full) should be tied to who deploys first: producer or consumer.

Poison-message playbook sentence: isolate, park on a dead-letter topic with headers for offset and exception, alert, continue the partition, fix and replay deliberately.

Reliability and cloud depth

Reliability answers need numeric SLIs. Cloud answers need meters: storage, requests, bytes scanned, capacity hours, movement. Governance answers need identities and audit scope, not "we encrypt everything." Cost answers that never mention dry runs are incomplete. Security answers that never mention least privilege on the board identity are incomplete.

Behavioral and incident narrative

Use a short form: context → signal → incorrect early hypothesis → evidence → fix → prevention. Example: Monday board high → suspected traffic spike → dry run showed raw JSON scan and missing version filter → pointed board to mart and pinned completion-v4 → added query lint in CI. Avoid villain stories. Interviewers hire people who improve systems.

Orchestration and deployment answers that still sound like data engineering

Airflow or Composer questions are common even when the job is "data engineer, not platform." The hire-bar answer names data intervals, catchup policy, idempotent tasks, and sensors that wait on data presence rather than wall-clock hope. A DAG that marks success when a file land task finishes, without a freshness check on the mart, is a green pipeline with a red board. Say how you would gate publish on DQ operators, how backfills pin definition_version, and how secrets never live in DAG code. Infrastructure-as-code follow-ups should mention reproducible environments, migration review for table changes, and blue/green or shadow publishes for completion logic. You do not need to recite Terraform resource names. You need to show that deploy and replay are designed together.

How panels probe soft spots

After a strong happy-path answer, expect one of these probes:

• "The stream is delayed by forty minutes. What does the board show?"
• "Finance disagrees with product on the same day. Who wins?"
• "We cannot afford the warehouse this quarter. What do you cut first?"
• "A contractor committed offsets before the write. Now what?"
• "Your uniqueness check fails only on Mondays. Where do you look?"

Prepare one sentence for each. Delayed stream: provisional tile may lag; certified path still follows batch reconciliation policy; do not silently substitute stale certified numbers. Finance versus product: documented owner of definition_version, change via version bump, not hallway consensus. Cost cut: remove double serving and raw scans before you remove checks. Bad commits: stop consumers, estimate gap from sink watermarks versus offsets, replay with idempotent merge, fix commit order. Monday-only uniqueness failures: look for weekend batch overlaps, late reset floods, or a job that runs twice on Monday morning schedules.

Mapping answers to course modules without sounding rehearsed

When you cite a lesson idea, translate it into a decision. Module 2 quality gates become "I fail the publish if duplicates exceed zero for the business key." Module 3 shuffle lessons become "I read the stage with the long straggler before I change executor count." Module 4 cursors become "provisional and certified do not share a commit log." Module 5 cost sheets become "I bring dry-run bytes to the meeting." Module 6 SLOs become "I will not pause capacity until resume is measured." The interviewer hears judgment. They do not need the lesson number.

Expanded bank follow-ups (still unique)

Q27. How would you test a change to completion logic without waiting for Monday?

Answer. Shadow publish completion-v5 to a non-board schema, compare to v4 on a sampled week, contract tests for grain, and a feature flag on the board. Cut over with a version pin, not a silent overwrite.

Q28. Northline finance wants yesterday's refund-adjusted revenue by noon. Portal SLOs are five seconds p95. Design the isolation.

Answer. Separate workload groups or reservations; publish a daily finance table from the same order-line grain; do not clone a second warehouse copy with divergent SQL. Row-level security stays on the portal path.

Q29. What is your personal definition of production-ready for a data product?

Answer. Documented grain and owner, automated checks, observable freshness, replay drill, access control, cost sheet line, and a named SLO. Code alone is not production-ready.

These three are for verbal stretch. Keep the core twenty-six for timed written drills.

Practical Implementation or Design Walkthrough

Problem

You have a forty-five-minute TechLearningPro interview loop: fifteen minutes SQL and modeling, fifteen minutes Spark and Kafka, fifteen minutes system design for the Monday board. Build a drill that mirrors it, with scoring sheets and expected artifacts.

Step 1 — Assemble the prompt pack

SQL prompt: distinct learners completed under completion-v4 with resets possible. Spark prompt: skew on course_id during daily aggregate. Kafka prompt: rebuild mart from two days of topic data without double count. Design prompt: draw certified versus provisional paths to 06:00.

Step 2 — Write starter schemas for the SQL segment

sqlCopy
-- staged attempts (not yet certified)
CREATE TABLE staged.completion_attempt (
  event_id           STRING,
  learner_id         STRING,
  lesson_id          STRING,
  course_id          STRING,
  is_complete        BOOLEAN,
  event_time         TIMESTAMP,
  event_date         DATE,
  definition_version STRING,
  is_reset           BOOLEAN
);

-- published mart grain
CREATE TABLE published.completion_attempt (
  learner_id         STRING,
  lesson_id          STRING,
  course_id          STRING,
  is_complete        BOOLEAN,
  event_date         DATE,
  definition_version STRING,
  attempt_id         STRING
);

Sample rows should include a reset pair and a duplicate event_id to force dedupe discussion.

Step 3 — Timeboxed answer template

Keep a paper template:

textCopy
Restate grain:
Clarifying Qs:
Happy path:
Failure modes:
Checks:
Trade-off:
Refusals:
Evidence I would show:
Self-score (0-5):

Fill it during drills until muscle memory forms.

Step 4 — System-design expected shape

Expected boxes: app → Kafka → raw compact → batch merge → published mart → board; parallel provisional stream → labeled tile; DQ gates before publish; one serving engine; cost and SLO notes in the corner. Deduct points for a second mart, for reading raw JSON on the board, and for missing definition_version.

Step 5 — Score with the rubric table

Two graders if possible. Average the five-layer scores. Disagreement usually means the answer never said a refusal or never cited evidence. Fix the answer, not the grader.

Step 6 — Convert misses into lesson 66 labs

If skew answers stay at level 2, schedule the Spark skew lab. If Kafka replay is fuzzy, schedule the consumer lab. Interview prep that does not create practice work is entertainment.

Step 7 — Build a personal scoring spreadsheet

Columns: date, question id, domain, layer scores (signal, structure, trade-off, evidence, refusal), overall 0–5, note, next lab. After twenty rows you will see a pattern: maybe evidence is always weak, or Kafka refusals are missing. Patterns beat vibes. Bring the spreadsheet to a peer review and ask them to mark only the evidence column while listening. If they cannot hear a metric, plan, or check, the cell is zero regardless of how confident you sounded.

Step 8 — Sample timed transcript excerpt (annotated)

Candidate: "Before I design, is completion after reset a new attempt or a replacement? And is Monday's board distinct learners or attempts under completion-v4 by 06:00?"
Interviewer: "New attempt; distinct learners; 06:00."
Candidate: "Happy path: events to Kafka, compact Parquet by event_date, daily merge into published.completion_attempt with definition_version, board SQL filters version and date. Provisional stream feeds an ops tile labeled provisional. Failure modes: poison messages, skew on course_id, duplicate event_ids, late resets after publish. Checks: uniqueness on business key, version pin lint, freshness SLI. I refuse a second warehouse copy and I refuse reading raw JSON for the board. Evidence: dry-run bytes, stage skew histogram, consumer lag by partition."

Annotation: clarifying first, then stages, failures, checks, refusals, evidence. That transcript is level 5 even without drawing boxes yet.

Enterprise Real-World Scenarios

TechLearningPro hiring loop for a platform engineer

The panel includes a analytics engineer who cares about grain, a platform engineer who cares about Kafka lag and Spark stages, and an engineering manager who cares about incidents. The candidate draws a beautiful lakehouse and never asks whether completion after reset counts. They pick Kubernetes before they define the mart grain. They claim exactly-once into a webhook. They offer to drop uniqueness checks to hit 06:00.

A hire-bar candidate asks about resets in the first minute, draws provisional versus certified cursors, names offset commit timing, shows how they would dry-run the board SQL, and refuses to serve raw JSON. They admit they would look up a specific Spark config name. They tell an incident story where the fix was a check, not a hero restart.

Northline staff interview: refund drift

Northline's panel describes two warehouses with divergent refund status codes. Candidates who propose "a data mesh" without ownership fail signal. Candidates who propose one published order_line grain, workload isolation, and a contract test on refund status codes pass. Bonus points for naming a restore drill on the replica that nobody has restored, tying reliability to cost.

A take-home that looks generous and is not

Some companies email a weekend take-home: ingest a zip of events, produce a dashboard metric, and write a README. Mediocre submissions optimize chart aesthetics. Strong submissions document grain, show duplicate handling, include a failing test that catches a bad reset, list assumptions, and state what would change at ten-times volume. If the zip includes late events, call them out. If timestamps are naïve, timezone-normalize and say so. Treat the take-home as a miniature TechLearningPro publish: raw landing, validated stage, certified aggregate, and a refusal to hide holes in prose.

Onsite whiteboard sabotage patterns

Occasionally an interviewer invents constraints mid-drawing: "now make it multi-region," "now add GDPR delete," "now cut cost by half." Grade-yourself tip: each new constraint gets a new sentence on impact to grain, freshness, and publisher count. Multi-region without a restore drill is a cost line. GDPR delete needs a keyed tombstone plan in raw and curated zones, with proof that marts stop serving deleted learners. Cutting cost by half starts with measured bytes and idle capacity, not with deleting DQ. Candidates who silently redraw everything from scratch on each twist look flustered. Candidates who amend the same diagram look like people you can page.

Best Practices

• Restate grain and freshness before tools.
• Write clarifying questions on the board where the interviewer can see them.
• Prefer evidence: plans, dry runs, lag charts, row counts.
• Separate provisional and certified paths when streaming appears.
• Name a single publisher for each certified grain.
• Practice refusals: no second mart, no dropping grain checks, no pause when resume misses SLO.
• Self-score every drill; track weak layers over two weeks.
• Say "I do not remember the flag name" when true; never invent semantics.
• Keep Northline and TechLearningPro stories concrete: volumes, versions, clocks.
• Convert every miss into a lab or a quiz distractor.
• Timebox. Endless perfect answers that miss the clock fail real loops.
• Review lesson 58 meters and lesson 62 SLOs the day before cloud or reliability rounds.

Common Mistakes and Anti-Patterns

Jumping to tools. Kubernetes, Snowflake, or Flink in sentence one. Consequence: you optimize a problem you never defined. Better: grain, freshness, ownership, then tools.

Reciting earlier lesson Q&As verbatim. Panels detect scripted answers that ignore the scenario's reset rule or refund quirk. Better: adapt the bank to the prompt's constraints.

Claiming exactly-once everywhere. Consequence: follow-ups destroy trust. Better: state sink guarantees and idempotency explicitly.

Ignoring nulls in anti-joins. NOT IN with nulls. Better: NOT EXISTS and say why.

Fixing skew with cluster size only. Money spent, fat task remains. Better: salt, two-phase, or AQE with proof.

Auto-commit consumers into databases. Lost or duplicate writes. Better: commit after sink success plus idempotent writes.

Two certified marts as "isolation." Drift. Better: workload isolation on one grain.

Dropping checks to hit an SLO. You hit a clock with the wrong number. Better: spend error budget transparently or narrow scope.

No refusal list. Looks flexible, reads as unprincipled. Better: three explicit refusals.

Practicing only SQL. Real loops rotate domains. Better: round-robin the six banks.

Troubleshooting and Design Review

You freeze after the prompt. Read it aloud, underline nouns that sound like grain, ask two clarifying questions. Motion beats silence.

Interviewer keeps saying "go deeper." You are stuck on happy path. Add failure mode, check, and meter.

You overshoot time on system design. Cap boxes at eight. Details go into one deep dive the interviewer picks.

Self-scores are always 5. Calibrate with a peer or record and grade against the rubric table literally.

You know Spark APIs but fail skew questions. Study task metrics and physical plans, then redo Q8 and Q10.

Design review checklist before a real interview day:

• Can you explain TechLearningPro completion grain in one sentence?
• Can you draw provisional vs certified without notes?
• Can you describe one Spark skew fix with evidence?
• Can you describe Kafka replay without double count?
• Can you price a bad Monday scan versus a mart scan?
• What three designs do you refuse?

Interview Questions and Answers

This section compresses a second pass of high-yield prompts. Do not skip the banks in Detailed Core Concepts; those are the deep set. Use these as a final warm-up the morning of an interview. Each answer is written at approximately hire-bar density.

Q1. Walk me through how you would investigate a sudden +2% completion rate.

I pin whether definition_version changed, whether resets were included, whether late events landed, and whether the board SQL still points at the mart. I compare row counts and distinct learners for the same event_date between yesterday's publish and today's. I check deploy diffs for completion logic. I do not start with cluster CPU.

Q2. How do you design idempotent merges into a lakehouse table?

Business key plus version or event id; MERGE with deterministic predicates; job that can rerun the same day partition safely; quality check that uniqueness holds after merge. I avoid delete-all-then-insert for partitions that other readers need during the job unless I use atomic table replace semantics the engine guarantees.

Q3. Partitioning strategy for progress at TechLearningPro?

Daily event_date for board and backfill patterns. Cluster or sort by lesson_id or learner_id inside the day if selective. Not hourly unless a real hourly consumer exists. Not by learner_id as partition.

Q4. When is Flink warranted instead of Spark for this product?

Sustained event-time processing with complex late data and low latency SLOs beyond what micro-batch meets, with ops ready to run Flink well. For many Monday-board certified paths, Spark batch plus a small provisional stream is enough. I choose based on SLOs and team operability, not fashion.

Q5. How do you secure the board's identity?

Least-privilege on the published mart only; no raw read in the board service account; audited access; secrets outside notebooks; column controls for PII. Links to governance lesson practices without turning the answer into a compliance speech.

Q6. Explain watermarking to a product manager.

It is a honesty deadline for late events in a streaming computation. After the deadline, the provisional number stops waiting. Certified numbers can still reconcile later under a published policy. Labels matter so executives do not treat provisional as audited.

Q7. What metrics do you alert on for a Kafka → lake pipeline?

Consumer lag by partition, failed records, job duration versus SLO margin, file count explosions, check fail rates, and freshness of the published table. Alert on symptoms tied to user promises, not only on CPU.

Q8. How do you answer "why not query the OLTP database for the board?"

Load and locking risk, lack of historical grain, missing definition versioning, and coupling analytics freshness to OLTP capacity. CDC into raw, then curated marts. Exceptions only for tiny, explicitly approved operational reads with caps.

Q9. Describe a bad cost optimization you rejected.

Pausing the warehouse when resume could not beat 06:00, or removing uniqueness checks to save minutes. I reject optimizations that spend correctness or the SLO silently.

Q10. How do you handle PII in progress events?

Minimize collection; tokenize or hash where possible; restrict raw access; mask in non-prod; retain to policy; ensure board queries project only needed columns. Encryption at rest is necessary but not sufficient.

Q11. System design closer: draw and narrate in ninety seconds.

App events to Kafka; compact to Parquet by day; batch merge to completion-v4 mart with DQ; board reads mart; provisional stream for ops tile; one serving engine; lag and freshness monitors; cost sheet with scan versus capacity. Stop. Invite questions.

Q12. What do you study the week before the loop?

Weak layers from your drill log, one skew lab, one replay lab, lesson 58 meters, lesson 62 SLO vocabulary, and two recorded timed designs. Not twenty new tools.

Q13. How do you explain idempotency to a hiring manager who is not technical?

If we run the same day's job twice, the board still shows each learner once for that definition of completion. The machinery can retry. The business number does not stutter. That sentence is enough; offer the merge key only if they ask how.

Q14. A stakeholder wants a real-time completion number on the same tile as the certified Monday board. Response?

Separate tiles or explicit labels. Real-time is provisional with a watermark policy. Monday certified is the audited grain. Combining them without labels creates executive decisions on unstable numbers. Offer a dual-channel design, not a single muddy metric.

Q15. What is your approach when you disagree with the interviewer's preferred tool?

Acknowledge their constraint, state the trade-off, and show you can execute their choice while protecting grain and checks. Stubborn tool wars fail loops. Silent surrender on correctness also fails. Negotiate the invariant, not the logo.

Rubric cards you can print

Print six cards, one per domain bank. On the back, list must-hit phrases: for SQL, definition_version, pruning, null-safe anti-join; for Spark, stage evidence, skew fix, file size; for Kafka, offset timing, poison, replay; for modeling, grain, SCD choice, single publisher; for reliability, SLI/SLO/budget, green pipeline ≠ green data; for cloud, meters, dry run, idle versus SLO. During drills, flip the card only after you speak. If a must-hit phrase was missing, cap the score at 3 even if the story was fluent.

Hands-On Exercises

Exercise: Forty-five-minute mock loop with self-scoring

Problem statement. Run a full mock interview for TechLearningPro using the prompt pack in the walkthrough. Record audio or present to a peer. Score with the 0–5 rubric per segment.

Tasks.

1. Execute the SQL and modeling segment with the starter schemas; include a reset scenario.
2. Execute the Spark skew and Kafka replay segments with explicit evidence statements.
3. Deliver the system-design whiteboard under fifteen minutes including refusals.
4. Fill the answer template for each segment; self-score layers.
5. Produce a weak-topic list mapped to lesson 66 labs.
6. Rewrite the single lowest-scoring answer to level 4 and re-record only that answer.
7. List three designs you refuse and why.
8. Submit a one-page drill log showing scores over at least five questions from banks A–F.

Expected deliverables. Recording or peer notes, scored templates, weak-topic list, refusal list, rewritten answer, drill log.

Evaluation rubric.

Lesson Summary

Interviews reward engineers who lock grain and freshness early, structure pipelines clearly, cite evidence, and refuse designs that break correctness or SLOs. This lesson gave a curated bank of more than twenty-five detailed questions across SQL, Spark, Kafka, modeling, reliability, and cloud, with rubrics and TechLearningPro scenarios that do not merely repeat earlier lesson quizzes. Practice is timed speaking plus scoring, not silent reading.

Take your weak-topic list into Data Engineering Practical Exercises, where labs force the same skills with graded outputs. Data Engineering Quiz and Assessment then converts these angles into MCQs and timed architecture prompts. The capstone will still need a single publisher and a 06:00 board. The interview is rehearsal for defending that design when a stranger is holding the marker.

Closing drill: sixty-second pitch

Practice until it is natural:

"TechLearningPro certifies completion at attempt grain under a pinned definition_version. Events land in Kafka, compact to daily Parquet, merge through DQ into one published mart for the 06:00 board. A provisional stream feeds a labeled ops tile with a watermark policy. I isolate workloads, measure dry-run bytes, and refuse a second publisher or raw JSON board scans. Replay is idempotent by event id; poison goes to DLQ; skew on hot courses gets a measured salt or split path. Freshness is an SLO with an error budget, not a hope."

If you can say that cleanly, the rest of the bank is detail. If you stumble, return to clarifying questions and redraw the two-path diagram before memorizing more API names.

Further Reading

• Apache Spark documentation for physical plans, joins, and Structured Streaming guarantees.
• Apache Kafka documentation for consumers, offsets, and exactly-once configuration boundaries.
• Delta Lake and Apache Iceberg for merge and commit semantics you should be ready to narrate.
• Google SRE workbook on SLOs for error-budget language aligned with lesson 62.
• Cloud query docs for dry runs and bytes scanned (BigQuery, Athena, and equivalents) as cited in lesson 58.
• Internal course links: Spark Performance Optimization, Kafka Producers and Consumers, Data Modeling Fundamentals, Data Engineering System Design.
• Your own drill log. It is the most authoritative reading list for your next loop.