Interviewing

Data Engineer Interview Prep for 2–4 Years of Experience

Seven weeks, one round of a mid-level loop each, weighted by what gets candidates rejected.

7 weeks178 steps~45h total

It opens on your dashboard and ticks itself off as you go. Every step below is a chapter or an exercise that already ships.

When you finish, you can

You can prepare every round of a mid-level loop: SQL, Python, modeling, design, Airflow, Spark, behavioral.

Loop day Ready for a mid-level loop

The plan, 7 weeks

Ordered by what each one buys you, not by topic. Every step says why it is here.

Week 01 · ~8h
The SQL screen: correct, then fast
You can write joins, windows and dedupes correctly against a clock.
Skip this if you sessionize and rank top-N per group unaided in 20 minutes.
1. SectionClarify, contract, build, verify, explain7 minThe loop you are graded on, before any syntax.
2. SectionFan-out: when a join doubles your metric7 minThe most-asked SQL trap, at every level.
3. InterviewDISTINCT vs GROUP BY6 minFan-out asked as a definition. DISTINCT usually hides it.
4. ExerciseTop buyers by revenue15 minin the studioAggregate at the right grain first, then rank.
5. SectionTop N per group6 minOn the screen in almost every loop.
6. ExerciseTop two tracks in every genre20 minin the studioTop N per group, where the ties matter.
7. SectionROW_NUMBER for deduplication6 minName the dedupe key before you write the window.
8. ExerciseDeduplicate order snapshots15 minin the studioLatest row per key: the most reused pattern.
9. ExerciseRunning daily revenue15 minin the studioRunning totals. The frame clause is the test.
10. SectionLAG and LEAD6 minCompare each row with the one before it.
11. ExerciseMonth-over-month change in plays20 minin the studioLAG over months: the change, not just the total.
12. SectionGaps and islands: date minus row number8 minStreaks and runs. Learn the trick once.
13. ExerciseSessionise plays with a 30-minute rule30 minin the studioSessions from a 30-minute gap: the classic mid-level ask.
14. InterviewDay 1 / 7 / 30 retention6 minThe definition is the hard part. Say which one.
15. SectionDays since first order6 minRetention counts days since each first order.
16. ExerciseSimple cohort retention slice20 minin the studioOne cohort, counted by days since its first order.
17. ExerciseNew and repeat orders by month25 minin the studioROW_NUMBER marks the first order. Count new against repeat.
18. InterviewIN vs EXISTS6 minThe opener most screens use. NULLs decide it.
19. SectionChain CTEs, step by step6 minOne step per CTE. Interviewers read your structure.
20. ExerciseReadable revenue query18 minin the studioNarrate each CTE as you write it.
21. SectionRead a plan with EXPLAIN7 minThe follow-up once the query is correct.
22. ExerciseFind the predicates no index can help20 minin the studioSpot the predicate no index can help.
23. InterviewWhy is this query slow?6 minName five causes first, then read the plan.
24. SectionTimezone boundaries6 minA day depends on whose clock you use.
25. ExercisePacific daily active users15 minin the studioDaily users by Pacific day, not UTC.
26. SectionExtracting JSON attributes6 minPull one field out of a JSON payload column.
27. ExerciseExtract device from JSON12 minin the studioPull device and campaign out of a JSON payload.
28. SectionExpanding arrays into rows6 minOne row per array item. Watch the fan-out.
29. ExerciseExpand checkout items15 minin the studioArrays to rows, then count correctly.
30. ExerciseLate arriving event audit15 minin the studioCount late events by the day they happened.
31. SectionRows into columns6 minConditional sums turn rows into columns.
32. ExercisePivot months into columns18 minin the studioTurn months into columns with conditional sums.
33. SectionEdge-case checklist6 minNULLs, ties, empty groups. Rejection notes name these.
34. ExerciseInterview top buyers with ties18 minin the studioStretch: the same pattern at interview grade.
35. Step2–4 years: the SQL screen85 minin the studioThree unseen queries against the clock, then two follow-ups.
36. QuestionsSQL · Query formulation15 minSay the output contract before you write anything.
Week 02 · ~8h
Python: the data-wrangling round
You can parse, group, dedupe and join records in plain Python.
Skip this if you write top-N per key from raw JSON unaided.
1. SectionWhat the Python round looks like6 minWhat the round looks like, before you practise it.
2. SectionGrouping records by key6 minMost data engineering coding rounds are dicts, not algorithms.
3. SectionJSON payloads, one document at a time6 minMessy JSON is the usual phone-screen input.
4. ExerciseFlatten nested event payloads30 minin the studioNested payloads to flat rows, keys intact.
5. ExerciseSessionize an unsorted event feed35 minin the studioLast week in SQL, now without SQL.
6. SectionDedupe, keep the latest6 minSay which record wins before you write code.
7. ExerciseBuild a golden record from several sources40 minin the studioDuplicates that disagree. Pick a winner and defend it.
8. ExerciseTop N per key, in bounded memory35 minin the studioTop N per key with a heap, not a full sort.
9. ExerciseLongest browsing run with no repeated page30 minin the studioThe sliding window, as an algorithms round asks it.
10. ExerciseCount ledger runs that net to a target30 minin the studioPrefix sums in a dict. Reported at Paytm for data engineers.
11. ExerciseFind the peak number of jobs running together30 minin the studioOverlapping intervals: sort the edges, then sweep.
12. ExerciseFind the k error codes paging you most30 minin the studioCount with a dict, pick the top k with a heap.
13. SectionHash join with a dict6 minIndex once, join many. They check the complexity.
14. ExerciseJoin two feeds and measure the fan-out35 minin the studioTwo feeds, one key, and the rows that do not match.
15. ExerciseRetry under a policy, as a decorator35 minin the studioRetries come up whenever an API is involved.
16. InterviewA 200 GB file on a 16 GB box6 minStreaming versus loading, asked as a design question.
17. Interviewgroupby: agg, transform or apply6 minIf your resume says pandas, expect this one.
18. InterviewA merge that multiplies rows6 minA silent row-count bug. Say how you catch it.
19. InterviewBatches that include the last one6 minTest the empty input and the short last batch.
20. InterviewMoney without floats6 minA correctness trap that sinks otherwise clean code.
21. InterviewThe list that remembers6 minA classic output-prediction trap. Explain it in one breath.
22. InterviewMutable vs immutable6 minThe conceptual opener behind half the bugs above.
23. Step2–4 years: the data-wrangling round71 minin the studioTwo wrangling problems, timed, then batching aloud.
24. QuestionsPython · Approach & data structures15 minName the data structure before you write a loop.
Week 03 · ~6h
Data modeling: the round most people fail
You can declare the grain and defend a star schema with history.
Skip this if you state the grain first and pick SCD types without hesitating.
1. SectionSame shop, two workloads6 minThe opening question. One shop, two kinds of work.
2. SectionWhy analytics denormalizes6 minNormalized for the app, flat for the analyst.
3. InterviewLogical vs physical schema6 minMeaning versus storage. A partition key is storage.
4. SectionClarify: turning a vague request into a spec7 minQuestions before boxes. Skipping them is a common fail.
5. SectionDeclaring grain before you build6 minAn unstated grain is the most-cited modeling failure.
6. InterviewWhat is the grain of a table6 minOne sentence, then an example. Nothing longer.
7. SectionThe four-step design process7 minProcess, grain, dimensions, facts: a script for the round.
8. InterviewFact or dimension6 minSounds basic. The follow-up is where it gets hard.
9. ExerciseSubscription warehouse grain35 minin the studioPick the grain, build the star, defend both.
10. SectionStar vs snowflake: the trade-offs6 minAsked by name. Know when a snowflake earns its joins.
11. SectionNatural vs surrogate keys6 minJoining on natural keys is a named failure reason.
12. InterviewPrimary key vs unique key6 minKeys are not enforced in most cloud warehouses.
13. ExerciseOrders you can trust eight months later45 minin the studioKeys and totals that survive every join.
14. SectionChoosing the right SCD type7 minType 1 versus Type 2, argued from what the business needs.
15. InterviewWalk me through the SCD types6 minName the types, then pick one for a real column.
16. ExerciseAddresses that stay true to the past40 minin the studioHistory that still answers where they lived at order time.
17. ExerciseRefunds without double counting45 minin the studioRefunds as facts, without double-counting revenue.
18. InterviewDesign a financial ledger6 minA full design prompt, taken from a real loop.
19. SectionNarrating a modeling answer6 minGrain first, then the trade-offs, out loud.
20. Step2–4 years: the modelling round66 minin the studioA timed design, then the four pressure questions.
21. QuestionsData Modeling · Model design15 minDesign prompts. Open every answer with the grain.
Week 04 · ~6h
Pipeline design: safe to run twice
You can design a batch pipeline that survives retries, backfills and late data.
Skip this if you explain idempotent writes and a backfill plan without notes.
1. SectionThe eight steps7 minRequirements before boxes. The happy path alone fails.
2. SectionStep 1: ask questions6 minNot asking is a common reason design rounds fail.
3. SectionAnswer the five-minute prompt6 minStreaming by default reads as over-engineering here.
4. SectionIdempotency: safe to repeat7 minThe word every design round is waiting to hear.
5. InterviewAppend, merge or overwrite6 minThe write mode decides what a rerun does.
6. SectionBackfills: rewriting history safely7 minBug fixed, six months wrong. Plan the rewrite.
7. SectionData Intervals: When Runs Start6 minThe logical date trips most candidates. Predict the runs.
8. ExerciseThe fact arrived first45 minin the studioLate facts, joined without losing or doubling rows.
9. ExerciseSLA-aware alerting flow40 minin the studioRetries, alerts, and an SLA someone actually owns.
10. ExerciseFive minutes behind the source45 minin the studioCDC in practice: updates and deletes, minutes behind.
11. InterviewFail the record or fail the job?6 minQuarantine versus stop: a judgement call they probe.
12. InterviewDeduplicating with a version6 minDedupe on retry, asked the way a loop asks it.
13. InterviewProject: a rerun without duplicates6 minProve a rerun adds nothing, with numbers.
14. InterviewProject: the source changed a field6 minThe first hour: stop, look, land raw, decide.
15. InterviewKafka architecture, in one breath6 minKnow Kafka in one breath: topics, partitions, brokers.
16. InterviewWhat a consumer group is6 minConsumers share partitions. Say what a group is.
17. InterviewAt-most, at-least, exactly-once6 minWhy exactly-once is hard, in three sentences.
18. InterviewChoosing a partition key6 minPartitioning decides what a rerun can safely replace.
19. ChapterSystem & Pipeline Design20 minHow design rounds are run, and what gets scored.
20. Step2–4 years: safe to run twice75 minin the studioDesign it timed; defend the rerun and the backfill.
21. QuestionsData Pipeline · Failure & recovery15 minSay what a rerun does before they ask.
Week 05 · ~5h
Airflow, CDC and the warehouse
You can explain Airflow runs, CDC, and how a warehouse stores data.
Skip this if you can explain a backfill in Airflow and CDC against polling.
1. SectionOperators, sensors and @task6 minThree ways to write a task. All become operators.
2. SectionThe Trap: Using Now6 minThe classic bug: the clock instead of the interval.
3. SectionFour ways to write safely6 minDelete-insert, upsert: a retry must not double-write.
4. SectionRetries, and how long they take6 minPick retries by cause. A bug fails every time.
5. SectionRunning past days without breaking the source6 minBackfill when the runs never existed.
6. InterviewAirflow, Glue or Step Functions6 minSay when you would not run Airflow at all.
7. InterviewTask failures and retries in Composer6 minRetries only help if each task is idempotent.
8. SectionUsing XCom6 minPass a small value from one task to the next.
9. SectionXCom is for small values, not data6 minXCom carries references, not the data itself.
10. SectionCommon Production Failures6 minThe failures you will be asked about.
11. SectionThe integrity test6 minThe test worth writing first.
12. SectionWhat a watermark load misses6 minWhy polling by updated_at is not enough.
13. SectionRead the database log6 minChanges come from the database log, not queries.
14. SectionApply changes with MERGE6 minApply the events to the warehouse with one MERGE.
15. ExerciseDiff two snapshots of a table35 minin the studioCompare two snapshots to find changes, no log needed.
16. SectionA check is a query6 minA check is a query that returns bad rows.
17. SectionWrite, audit, publish6 minStage, check, then publish. Bad data stays off the dashboard.
18. ExerciseOrder anomaly audit35 minin the studioFind the bad rows with checks, one CTE at a time.
19. SectionName only the columns you need6 minWhy SELECT * costs more in a warehouse.
20. SectionSplit a table by date6 minPartition by date so filters skip whole slices.
21. SectionCluster to skip blocks6 minSorted blocks let the engine skip more data.
22. ExercisePlan a small-file compaction40 minin the studioMany small files cost more. Plan the compaction.
23. InterviewAthena against Redshift6 minQuery files in S3, or load a warehouse?
24. InterviewWhat is a data lakehouse?6 minOpen files plus a table format. Say what it adds.
25. InterviewPartitioning vs clustering6 minThe BigQuery version of a question every warehouse asks.
26. InterviewWhat is the medallion architecture6 minBronze, silver, gold: raw, clean, business-ready.
27. Step2–4 years: Airflow, CDC and the warehouse42 minin the studioA DAG to fix, then CDC and Airflow failures aloud.
28. QuestionsAirflow · Conceptual15 minSay what a run is, and why it ran.
Week 06 · ~6h
Spark: the mid-level round
You can explain shuffles, joins and skew from a Spark plan.
Skip this if you can say why a join shuffled, and fix it.
1. InterviewThe three components, named6 minDriver, executors, cluster manager: the warm-up question.
2. SectionNothing runs until show()7 minNothing runs until an action. Say why.
3. SectionApplication, job, stage, task6 minJob, stage, task: the words every answer uses.
4. InterviewHow many DAGs does a script build?6 minOne action, one job. Cached or recomputed?
5. Sectionrepartition or coalesce6 minAsked by name, and often answered backwards.
6. SectionHow a shuffle works7 minEvery Spark performance answer starts from the shuffle.
7. SectionMake the shuffle smaller6 minThe fix they want after you name the problem.
8. SectionBroadcast join recap6 minThe first fix for a slow join with a small side.
9. Exercisebroadcast() with auto-broadcast off, and the case where Spark ignores it20 minin the studioAuto-broadcast off, lookup join shuffling. Make broadcast() work anyway.
10. Exerciserow_number vs rank vs dense_rank: why the top-3 counts disagree20 minin the studioTop-N per group in PySpark. Ties decide the answer.
11. ExercisedropDuplicates(["customer_id"]) keeps which row?20 minin the studioDedupe in PySpark. Which row survives?
12. Exerciseleft_semi and left_anti: "customers who did / never did" without a full join20 minin the studioRows in A with no match in B, without a full join.
13. ExerciseWhy are Python UDFs slow? Find the answer in the plan25 minin the studioCompare built-ins with a Python UDF in the plan.
14. Exerciseunion vs unionByName: the silent column mix-up15 minin the studioUnion by position can silently mix columns.
15. Sectioncache() and reuse6 minWhat cache saves, and what it does not.
16. SectionWhen not to cache6 minA cache costs memory that joins and shuffles need.
17. InterviewReading a file with bad rows in it6 minChoose a read mode for a file with bad rows.
18. Interviewmap vs mapPartitions6 minPer-row work against per-partition setup.
19. InterviewLogical vs physical plan6 minReading a plan at a high level is expected.
20. SectionFind the hot key6 minSpot the straggler. Designing salting is Senior depth.
21. ExerciseWhere does skew show up? Reading task metrics, not the DAG20 minin the studioFind skew in task metrics, and where it is absent.
22. InterviewWalk me through fixing skew6 minMid-level answer: symptoms, AQE, then broadcast.
23. SectionThree AQE features6 minKnow what AQE fixes before tuning by hand.
24. ExerciseIf AQE coalesces anyway, does spark.sql.shuffle.partitions still matter?20 minin the studioWhat the partition setting still controls once AQE is on.
25. SectionPick a format, skip data6 minParquet versus CSV, argued in one minute.
26. InterviewWhy small files hurt, step by step6 minA production problem every Spark team has hit.
27. InterviewHow many partitions for 1 TB?6 minBack-of-envelope sizing, asked with real numbers.
28. Step2–4 years: the Spark round62 minin the studioPySpark to write, a notebook, three plan questions.
29. QuestionsApache Spark · PySpark coding15 minSay the code, then why it is correct.
30. QuestionsApache Spark · Debug & fix15 minDiagnose aloud: symptom, cause, then the fix.
31. QuestionsApache Spark · Conceptual & architecture15 minFundamentals said cleanly. Deeper internals are Senior depth.
Week 07 · ~6h
Loop week: the stories and the rehearsal
You can tell three STAR stories and walk a full loop.
Skip this if your ownership, incident and conflict stories are rehearsed aloud.
1. ChapterThe Recruiter & Hiring-Manager Screen20 minYour two-minute story, and a worked project walkthrough.
2. ChapterBehavioral Storytelling20 minOwnership, incident and disagreement stories, in STAR form.
3. InterviewA pipeline incident you owned6 minThe incident story every loop asks for, told end to end.
4. InterviewA time you made a mistake6 minThe most-asked behavioral prompt. Own the mistake in sentence one.
5. InterviewA disagreement, and how it resolved6 minDisagree with evidence, then commit. Asked at Amazon, Meta, Google.
6. InterviewConflicting priorities and deadlines6 minManagerial rounds at product and services firms both ask it.
7. InterviewProject: walk me through it6 minFixed order, real numbers, about two minutes.
8. InterviewProject: how do you know it is right6 minThree places to check: input, output, trends.
9. InterviewProject: ten times the data6 minName what breaks first from your own design.
10. ChapterLive Coding: SQL & Python20 minHow live rounds are scored beyond a correct answer.
11. ChapterTake-Home Projects20 minScale-ups and services firms often send one instead.
12. ChapterAmazon20 minLeadership Principles are scored in every round.
13. ChapterFlipkart20 minModeling, SQL, then a Spark machine-coding round.
14. ExerciseFlipkart: a daily snapshot from sparse changes35 minin the studioStretch: a Flipkart-style snapshot question.
15. ExerciseAmazon: net revenue across two fan-outs30 minin the studioStretch: Amazon-style fan-out, twice over.
16. Step2–4 years: loop day88 minin the studioA full loop in one sitting, graded on what ran.
17. QuestionsBehavioral · Ownership & delivery15 minTwenty reported prompts. Point one of your stories at each.

Start it now

Reading is free and needs no account. The plan itself tracks on your dashboard.

Start Data Engineer · 2–4 yearsTracked, 7 weeks, ~45h See the other plans17 more, by what you are aiming at

When you finish, you can

The plan, 7 weeks

The SQL screen: correct, then fast

Python: the data-wrangling round

Data modeling: the round most people fail

Pipeline design: safe to run twice

Airflow, CDC and the warehouse

Spark: the mid-level round

Loop week: the stories and the rehearsal

Start it now