Data Engineer

Data Engineer Interview Roadmap

A complete, phase-by-phase plan to prepare for and pass Data Engineer interview loops — from diagnostic through offer.

Default timeline: 12 weeks at ~10–12 hrs/week. Compression and expansion options are in Adjusting the Timeline.

What You're Actually Being Tested On
The Interview Loop
Diagnostic: Where Do You Stand
Phase 1 — SQL (Weeks 1–2)
Phase 2 — Python & Coding (Weeks 2–3)
Phase 3 — Data Modeling (Weeks 3–4)
Phase 4 — Distributed Processing & Spark (Weeks 4–6)
Phase 5 — Streaming, Orchestration, Cloud (Weeks 6–7)
Phase 6 — System Design (Weeks 7–9)
Phase 7 — Projects, Resume & Behavioral (Weeks 9–10)
Phase 8 — Mocks & Polish (Weeks 10–12)
In-The-Room Playbooks
Question Bank
Why Candidates Get Rejected
Level Adjustments
Resources
Application & Offer Logistics

1. What You're Actually Being Tested On

Data Engineering interviews aren't a single skill test. They probe six distinct competencies, and most candidates over-index on the first one.

Competency	Rough weight	Where it shows up
SQL fluency	25%	Screen, dedicated SQL round
Data modeling	20%	Modeling round, design round
Pipeline/system design	20%	Design round, HM round
Programming (Python/Scala)	15%	Coding round, take-home
Distributed systems fundamentals	12%	Spark round, design round
Communication & ownership	8%	Every round, especially behavioral

The critical insight: interviewers are hiring someone who will own production data that other people make decisions on. Every answer should reflect that — correctness, idempotency, recoverability, monitoring, and cost. A technically correct answer that ignores "what happens when this fails at 3am" reads as junior regardless of your years of experience.

2. The Interview Loop

Typical structure. Not all companies run all rounds; product companies run more, service/consulting companies run fewer but weight tool experience higher.

#	Round	Length	What they're checking
0	Recruiter screen	20–30 min	Level fit, stack overlap, comp range, logistics
1	Online assessment / SQL screen	60–90 min	Can you write correct SQL unsupervised
2	SQL deep dive (live)	45–60 min	SQL under pressure + optimization reasoning
3	Coding (Python/PySpark)	45–60 min	Data manipulation, clean code, some DSA
4	Data modeling	45–60 min	Dimensional design, grain, SCDs, tradeoffs
5	Pipeline / system design	45–60 min	End-to-end architecture, scale, failure handling
6	Resume / project deep dive	45 min	Are your claims real; depth of ownership
7	Behavioral / hiring manager	45 min	Collaboration, incidents, judgment
8	Bar raiser / VP (sr. roles)	45–60 min	Influence, tradeoffs, long-term thinking

Take-homes appear at startups and mid-size companies — usually build a small pipeline, 3–6 hours of real work. Treat these as portfolio pieces, not throwaway code.

3. Diagnostic: Where Do You Stand

Do this before you plan anything. Spend one sitting (~3 hours) and score yourself honestly 1–5.

Timed self-test:

[ ] Solve 3 hard SQL problems in 45 min total (window functions, gaps-and-islands, cohort retention)
[ ] Write a Python function that reads a messy CSV, dedupes on composite key keeping latest, and outputs partitioned Parquet — no help, 30 min
[ ] On a blank page in 20 min: design a star schema for an e-commerce returns process. Name the grain.
[ ] Out loud for 15 min: explain what happens internally when Spark runs a join between a 2 TB and a 50 MB table
[ ] Tell a 3-minute STAR story about a production data incident you handled

Scorecard:

Area	Score 1–5	Weeks to allocate
SQL
Python / PySpark
Data modeling
Spark internals
Streaming (Kafka)
Orchestration (Airflow/dbt)
Cloud platform (pick one)
System design
Behavioral stories

Anything scoring 1–2 gets double the default phase time. Anything scoring 4–5 gets halved — spend the freed time on mocks instead.

Phase 1 — SQL (Weeks 1–2)

SQL is the round most people fail and the one that's most fixable. The bar is not "can write a join" — it's fluent pattern recognition under time pressure plus the ability to reason about how the engine executes it.

Patterns to master

Drill each until you can write it without thinking:

Window functions

[ ] Top-N per group — ROW_NUMBER vs RANK vs DENSE_RANK (and knowing why ties matter)
[ ] Running totals, moving averages — frame clauses (ROWS BETWEEN, RANGE BETWEEN, default frame gotcha)
[ ] LAG / LEAD — period-over-period change, session gap detection
[ ] FIRST_VALUE / LAST_VALUE / NTH_VALUE
[ ] SUM() OVER (PARTITION BY ... ORDER BY ...) vs GROUP BY — when each applies
[ ] NTILE, PERCENT_RANK, CUME_DIST, percentiles

Core patterns

[ ] Deduplication — ROW_NUMBER over partition, QUALIFY where supported
[ ] Gaps and islands — consecutive-day streaks, uninterrupted sessions
[ ] Funnel / conversion analysis — multi-step drop-off
[ ] Cohort retention — Day-N / Week-N retention grids
[ ] Self-joins and recursive CTEs — org hierarchies, bill-of-materials
[ ] Pivot via conditional aggregation (SUM(CASE WHEN ...))
[ ] Median without a percentile function
[ ] Anti-joins — NOT EXISTS vs LEFT JOIN ... IS NULL vs NOT IN (know the NULL trap)
[ ] Date bucketing, generating a date spine, filling missing dates
[ ] Sessionization with a 30-minute inactivity rule
[ ] Point-in-time / as-of joins against an SCD2 table

Correctness traps interviewers plant

[ ] NULL behavior in NOT IN, aggregates, COUNT(col) vs COUNT(*), joins
[ ] Fan-out from joining two one-to-many tables → duplicated measures
[ ] UNION vs UNION ALL (silent dedupe cost)
[ ] HAVING vs WHERE vs QUALIFY execution order
[ ] Filtering an outer-joined table in WHERE instead of the ON clause
[ ] Integer division, implicit casts, timezone handling
[ ] COUNT(DISTINCT) on multiple columns

Optimization — the part that separates levels

Being able to write the query gets you a pass. Being able to make it fast gets you the offer.

[ ] Read an EXPLAIN / query plan: scan type, join type, row estimates, spill
[ ] Predicate pushdown and why it fails (functions wrapped on the filter column)
[ ] Partition pruning vs full scan — write sargable predicates
[ ] Broadcast vs shuffle join at the SQL layer
[ ] Column pruning — cost of SELECT * on columnar storage
[ ] Pre-aggregate before joining to reduce shuffle volume
[ ] Clustering / sort keys / Z-ordering, and when they help
[ ] Incremental vs full-refresh logic
[ ] Materialized views vs CTEs vs temp tables
[ ] Skew in a join key (hot customer_id, NULL keys)

Practice plan

Week 1: 6–8 problems/day, easy → medium. Focus on patterns above, untimed.
Week 2: 4 problems/day, medium → hard, strictly timed at 12 min each. Write on a plain text editor with no autocomplete — that's the interview environment.
Always: after solving, ask "how would this behave on 5 billion rows?" and write down the answer.

Phase 2 — Python & Coding (Weeks 2–3)

The DE coding round is not a FAANG algorithms round. It's usually easy/medium DSA plus data-shaped problems. But sloppiness gets punished hard.

Python fundamentals

[ ] Data structures: dict, set, list, tuple, collections (defaultdict, Counter, deque)
[ ] Comprehensions, generators, yield — and why generators matter for large files
[ ] Iterators, itertools (groupby, chain, islice)
[ ] Context managers, exception handling, custom exceptions
[ ] Type hints, dataclasses, pydantic basics
[ ] Functions as first-class objects, decorators (retry, timing, logging)
[ ] Light OOP: classes, inheritance, composition — enough to structure a connector or extractor
[ ] logging over print; structured logging
[ ] Testing: pytest, fixtures, parametrize, mocking an API/DB call
[ ] Virtual envs, requirements.txt / pyproject.toml, packaging basics

DSA that actually appears

Stick to these; don't grind graph theory.

[ ] Hashmap counting/grouping problems
[ ] Two pointers, sliding window
[ ] String parsing and manipulation
[ ] Sorting with custom keys; merge intervals
[ ] Heaps — top-K
[ ] Basic recursion; simple tree/JSON traversal (flatten nested JSON is a classic DE question)
[ ] Big-O reasoning out loud

Data-shaped coding problems (very common)

[ ] Flatten deeply nested JSON into rows
[ ] Parse a log file and compute per-user session metrics
[ ] Deduplicate records on a composite key keeping the latest by timestamp
[ ] Reconcile two datasets and emit a diff report
[ ] Chunked read of a file too large for memory
[ ] Implement a retry with exponential backoff for a flaky API
[ ] Paginate an API and write results incrementally
[ ] Validate rows against a schema, route failures to a quarantine path
[ ] Merge/upsert logic implemented by hand

pandas & PySpark

[ ] pandas: groupby/agg/transform, merge types, pivot_table, melt, datetime handling, apply cost vs vectorization
[ ] PySpark DataFrame API: select, withColumn, filter, groupBy/agg, join, window, explode, when/otherwise
[ ] Reading/writing with schema specified explicitly (never inferSchema in production — know why)
[ ] Converting SQL logic ↔ DataFrame API on demand
[ ] Handling nested structs and arrays

Do this: be able to solve the same problem three ways — pandas, PySpark, and SQL. Interviewers switch mid-round.

Phase 3 — Data Modeling (Weeks 3–4)

The most under-prepared round, and the one where mid-level candidates most often reveal they've only ever written transformations someone else designed.

Foundations

[ ] OLTP vs OLAP; normalization (1NF–3NF) and why analytics denormalizes
[ ] Star schema vs snowflake schema — tradeoffs, when snowflaking is justified
[ ] Grain — define it in one sentence before anything else. This is the single highest-signal habit.
[ ] Surrogate keys vs natural keys vs hash keys; composite keys
[ ] Additive, semi-additive, non-additive measures

Fact tables

[ ] Transaction fact
[ ] Periodic snapshot fact
[ ] Accumulating snapshot fact (order lifecycle with multiple date columns)
[ ] Factless fact (event registration, coverage)
[ ] Handling returns/cancellations — reversing rows vs updating
[ ] Degenerate dimensions

Dimensions

[ ] SCD Type 0, 1, 2, 3, 6 — implement Type 2 with MERGE from memory
[ ] Conformed dimensions across marts
[ ] Role-playing dimensions (one date dim, many aliases)
[ ] Junk dimensions
[ ] Bridge tables for many-to-many
[ ] Hierarchies — fixed, ragged, recursive
[ ] Late-arriving dimension rows (fact arrives before dim)
[ ] Unknown/default member handling instead of NULL FKs

Modern layering

[ ] Medallion: bronze / silver / gold — what belongs in each
[ ] dbt layering: staging → intermediate → marts
[ ] Data Vault (hubs, links, satellites) — know when it's chosen and its cost
[ ] One Big Table / wide denormalized tables — when the columnar engine makes this correct
[ ] Semantic / metrics layer — single source of truth for KPI definitions
[ ] Lakehouse table formats: Iceberg, Delta, Hudi — ACID, time travel, schema evolution, hidden partitioning

Operational modeling

[ ] Idempotent loads — rerunning a partition produces identical output
[ ] Backfill strategy without corrupting downstream aggregates
[ ] Schema evolution — additive changes vs breaking changes, contracts
[ ] Soft deletes and CDC-driven upserts
[ ] Partitioning strategy (usually by date; know cardinality traps)
[ ] Small-files problem and compaction
[ ] GDPR/PII — right to be forgotten in an immutable lake

Practice

Design these on paper, out loud, in 30 min each. State grain, list dims and facts, draw the ERD, name the SCD types, then defend two decisions you'd revisit at 100× volume:

E-commerce orders, returns, and shipments
Ride-hailing trips with dynamic pricing
Subscription SaaS with plan changes and MRR reporting
Hotel/flight bookings with cancellations and amendments
Multi-tenant B2B product usage analytics
Healthcare claims (many-to-many diagnoses)
Banking transactions with fraud flags
Content streaming watch events plus catalog metadata

Phase 4 — Distributed Processing & Spark (Weeks 4–6)

If the job description mentions Spark or Databricks, expect a round that goes deep. Surface-level API knowledge is transparent to interviewers.

Execution model

[ ] Driver vs executor vs cluster manager
[ ] Job → stage → task; how stage boundaries are created
[ ] Lazy evaluation, DAG construction, actions vs transformations
[ ] Narrow vs wide transformations
[ ] Shuffle mechanics — write, fetch, spill; why shuffle dominates runtime
[ ] spark.sql.shuffle.partitions — the classic default-200 problem
[ ] Catalyst optimizer: parsing → analysis → logical opt → physical planning → codegen
[ ] Adaptive Query Execution: coalescing partitions, switching join strategy, skew join handling
[ ] Tungsten, whole-stage codegen

Joins

[ ] Broadcast hash join — threshold, when to hint, driver memory risk
[ ] Sort-merge join — the default for large-large
[ ] Shuffle hash join
[ ] Broadcast nested loop join — the one that silently kills jobs
[ ] Bucketed joins — avoiding shuffle entirely
[ ] Choosing a strategy given two table sizes (be ready to be quizzed with numbers)

Skew and performance

[ ] Detecting skew from the Spark UI (one long-running task in a stage)
[ ] Salting the key; splitting hot keys; isolated broadcast
[ ] AQE skew join
[ ] repartition vs coalesce — shuffle vs no-shuffle, when each is right
[ ] partitionBy on write vs repartition in memory
[ ] Caching/persist levels — and when caching hurts
[ ] Filter early, project early, aggregate before join
[ ] UDF cost vs built-in functions; pandas/Arrow UDFs
[ ] Debugging OOM: executor memory, storage vs execution memory, spill, GC

Storage

[ ] Row vs columnar; Parquet internals (row groups, column chunks, pages, footer stats)
[ ] ORC, Avro, JSON, CSV — pick one per use case and justify it
[ ] Compression: Snappy vs Gzip vs ZSTD — splittability matters
[ ] Predicate pushdown and min/max statistics
[ ] Partitioning vs bucketing vs clustering / Z-order
[ ] Small-files problem, target file sizes, compaction jobs
[ ] Schema evolution in Parquet vs table formats

Practice

[ ] Take a deliberately slow Spark job and optimize it; document each change and the reasoning
[ ] Read the Spark UI for a real job and explain every stage
[ ] Be able to whiteboard the shuffle path for df1.join(df2, "key").groupBy("x").agg(sum("y"))

Phase 5 — Streaming, Orchestration, Cloud (Weeks 6–7)

Kafka / streaming

[ ] Topics, partitions, offsets, replication, ISR, leader election
[ ] Consumer groups, rebalancing, partition assignment
[ ] Ordering guarantees — per-partition only; keying implications
[ ] Retention vs log compaction
[ ] Delivery semantics: at-most-once, at-least-once, exactly-once; idempotent producer, transactions
[ ] Event time vs processing time vs ingestion time
[ ] Watermarks, allowed lateness, late-arriving data handling
[ ] Windowing: tumbling, sliding, session
[ ] Stateful processing, checkpointing, state store size management
[ ] Consumer lag monitoring, backpressure
[ ] Schema registry, Avro/Protobuf, compatibility modes
[ ] Spark Structured Streaming vs Flink — micro-batch vs true streaming tradeoffs
[ ] CDC: log-based (Debezium) vs query-based; handling deletes
[ ] Lambda vs Kappa architecture; reprocessing history

Orchestration

[ ] Airflow: DAGs, operators, sensors, XComs, task dependencies, executors
[ ] Idempotency and execution_date / logical date — templating for reruns
[ ] Backfills, catchup, max_active_runs, pools, priority weights
[ ] Retries, SLAs, alerting, on-failure callbacks
[ ] Dynamic task mapping; avoiding top-level code in DAG files
[ ] dbt: models, refs, sources, tests, snapshots, incremental strategies, macros
[ ] Dagster/Prefect assets model — if the JD mentions it
[ ] Dependency management across pipelines; data-aware scheduling

Cloud — pick ONE and go deep

Match the job description. Know one properly rather than three superficially.

AWS: S3 (storage classes, consistency, lifecycle), Glue (catalog, crawlers, jobs), EMR, Athena, Redshift (dist/sort keys, RA3, Spectrum), Kinesis vs MSK, Lambda, Step Functions, DMS, Lake Formation, IAM basics

GCP: BigQuery (slots, partitioning, clustering, cost model, materialized views), Dataflow/Beam, Pub/Sub, Dataproc, Composer, Datastream, BigLake

Azure: ADLS Gen2, Data Factory, Synapse (dedicated vs serverless), Databricks, Event Hubs, Stream Analytics, Purview

Plus, regardless of cloud:

[ ] Cost model of your warehouse — what actually generates the bill
[ ] Snowflake if relevant: virtual warehouses, micro-partitions, clustering, time travel, zero-copy clone, Snowpipe
[ ] Docker basics; CI/CD for data pipelines; Terraform awareness
[ ] Secrets management, least-privilege access, encryption at rest/in transit

Data quality & governance

[ ] Test types: not-null, unique, referential integrity, accepted values, freshness, volume anomaly, distribution drift
[ ] dbt tests, Great Expectations, Soda
[ ] Data contracts between producers and consumers
[ ] Lineage and impact analysis
[ ] SLAs/SLOs on freshness and completeness
[ ] Circuit breakers — fail the pipeline vs quarantine bad rows vs alert and continue (know when each is right)
[ ] Reconciliation against source systems
[ ] Observability: metrics, logs, alerting that doesn't cause fatigue

Phase 6 — System Design (Weeks 7–9)

This round decides your level and often your comp. Use a repeatable framework so you never freeze.

The framework — 45 minutes

1. Requirements (5–8 min) — never skip this

Ask, don't assume:

What's the business question this data answers? Who consumes it?
Data sources — how many, what type, do we control them?
Volume: rows/day, bytes/day, expected growth
Velocity: batch cadence or streaming? What latency does the consumer actually need?
Freshness SLA and query latency SLA — separate things
Historical backfill scope
Correctness bar: can we tolerate approximate/eventual, or must it reconcile to the cent?
PII/compliance constraints
Team size and existing stack

2. Scale math (3 min) — write it down

Do the arithmetic out loud. Example: 50M events/day × 1 KB = 50 GB/day raw → ~5–10 GB/day in Parquet with Snappy (typically 5–10× compression on event data) → ~2–4 TB/year. That number decides batch vs streaming, single-node vs distributed, and partitioning granularity. Treat these ratios as rough planning figures, not guarantees — state your assumptions.

3. High-level architecture (5 min)

Draw the layers left to right:

Sources → Ingestion → Raw/Bronze → Transform → Curated/Silver → Serving/Gold → Consumers

Overlay: orchestration, catalog/metadata, quality checks, monitoring.

4. Component decisions (12–15 min)

For each, state the choice and the alternative you rejected and why:

Ingestion: batch pull, CDC, streaming, file drop, API
Storage: object store vs warehouse vs both; file format; table format
Compute: Spark, warehouse SQL, Flink, serverless
Data model: the star schema/grain from Phase 3
Orchestration and scheduling
Serving: warehouse tables, OLAP store (Druid/ClickHouse/Pinot) for sub-second, API, cache

5. Reliability (8 min) — where senior candidates win

Idempotency: rerunning any partition yields identical results
Exactly-once vs at-least-once + dedupe downstream
Late-arriving and out-of-order data
Backfill without breaking incremental state
Schema evolution and upstream breaking changes
Failure isolation and retries; dead-letter/quarantine paths
Data quality gates and what happens when they fail
Monitoring, alerting, on-call runbook

6. Cost & tradeoffs (4 min)

Where the money goes; storage tiering; compute right-sizing
Partition pruning to reduce scan cost
What you'd do differently at 10× and at 1/10× scale
What you deliberately deferred to v2

Design questions to practice

Do 10–12 of these, timed, ideally out loud with a recording or a partner:

Clickstream analytics platform for a web/mobile product
Ride-hailing trip data platform with surge pricing analytics
Near-real-time fraud detection pipeline
Ingestion framework for 500 tables from 20 OLTP databases via CDC
Company-wide metrics/KPI layer with a semantic layer
Data quality framework applied across 1,000 tables
Migrate an on-prem Hadoop warehouse to a cloud lakehouse
IoT sensor telemetry at 1M events/sec
ML feature store serving both training and low-latency inference
GDPR right-to-be-forgotten across an immutable data lake
Multi-tenant analytics with strict data isolation
Recommendation-system data pipeline (batch features + real-time signals)
Financial reporting pipeline that must reconcile exactly
Log aggregation and search platform
A/B experimentation platform

Phase 7 — Projects, Resume & Behavioral (Weeks 9–10)

Portfolio projects

You need one or two end-to-end projects you can discuss for 30 minutes without running out of depth. Not five shallow ones.

A strong DE project includes:

[ ] Real, messy data source (public API, CDC from a seeded DB, generated event stream)
[ ] Ingestion with incremental logic and idempotency
[ ] Storage in a lakehouse table format, sensibly partitioned
[ ] Transformation layered properly (bronze/silver/gold or dbt staging/marts)
[ ] Actual dimensional model with a documented grain and at least one SCD2
[ ] Orchestration with retries, backfill capability, and alerting
[ ] Data quality tests that can fail the run
[ ] Infrastructure as code or at least Dockerized, reproducible setup
[ ] CI running tests on PR
[ ] A README with the architecture diagram, design decisions, and the tradeoffs you made
[ ] Documented cost and a stated scaling limit

That last item — knowing where your own design breaks — is what makes a project credible.

Resume deep dive prep

For every bullet on your resume, prepare:

The architecture, drawn from memory
Actual volumes, latencies, and costs (know your numbers)
Your specific contribution vs the team's
Why that tool over the alternatives
What broke, and how you found and fixed it
What you'd redesign today

Do not write anything you can't defend for 10 minutes. Inflated resume claims are the fastest rejection in this field — interviewers probe one level deeper than you expect, always.

Behavioral stories

Build 8 STAR stories with metrics, reusable across prompts. Write them out; rehearse to 2–3 minutes each.

Prompts to prepare for:

[ ] Walk me through a pipeline you built end to end
[ ] A time bad data reached production and a stakeholder found it before you did
[ ] A time you were on call for a failure at an awful hour
[ ] A time you pushed back on a request from a stakeholder or manager
[ ] A tradeoff between shipping fast and doing it right
[ ] A significant cost or performance optimization you drove
[ ] Conflict with an analyst, DS, or another engineer
[ ] A technical decision you got wrong
[ ] A time you had to influence without authority
[ ] Something you built that nobody used — and why
[ ] Mentoring or raising the bar for others
[ ] A time you missed a deadline

For each: quantify the impact, be specific about your role, and end with what you learned rather than a tidy moral.

Questions to ask them

Signals seriousness and screens the job:

How is data engineering organized — platform vs embedded in product teams?
What does the on-call rotation look like, and what's the typical incident volume?
What proportion of time goes to new development vs maintenance and firefighting?
How are data quality issues currently detected — and by whom?
What's the most painful part of the current stack?
How do analysts and DS consume what my team builds?
What does success in the first 6 months look like concretely?
What's the deprecation story — do old pipelines actually get retired?

Phase 8 — Mocks & Polish (Weeks 10–12)

Practice under real conditions. Reading is not preparation.

[ ] 6+ mock interviews minimum: 2 SQL, 1 coding, 1 modeling, 2 system design
[ ] Record yourself. Watch it. It's unpleasant and highly effective.
[ ] Practice thinking out loud continuously — silence reads as being stuck
[ ] Practice on the actual tool (CoderPad, HackerRank, a shared doc) — no IDE, no autocomplete
[ ] Practice the whiteboard/virtual-canvas version of design rounds
[ ] Rehearse your 90-second "walk me through your background"
[ ] Sequence your applications: warm-up companies first, top choices in weeks 3–5 of interviewing
[ ] Build a tracker: company, round, questions asked, what went badly, follow-up

Weekly rhythm in this phase: 2 mocks, 10 timed SQL problems, 2 design questions, 1 resume/behavioral rehearsal, plus review of every gap the mocks exposed.

In-The-Room Playbooks

SQL round

Read the schema aloud; confirm grain and cardinality of each table
Restate the question in your own words; confirm edge cases (ties? NULLs? inclusive dates? time zone?)
Say your approach before typing
Build incrementally with CTEs; run/verify each step if you can
Walk through your logic on 2–3 sample rows
Volunteer the optimization discussion unprompted: "on a large table I'd want a partition filter here, and I'd check whether this join skews on..."

Coding round

Clarify input format, output format, scale, and constraints
State approach and complexity before coding
Write clean, named, small functions — no single 40-line block
Handle edge cases explicitly: empty input, nulls, duplicates, malformed rows
Mention how you'd test it, even if not asked to write tests
Narrate continuously

Modeling round

Requirements and consumers first — what questions must this answer?
State the grain in one sentence. Then facts, then dimensions.
Draw it. Label keys and SCD types.
Address history, late arrivals, and updates/deletes without being asked
Discuss how it evolves when a new business process is added

System design round

Requirements for 5–8 minutes. Resist the urge to name tools early.
Do the scale math visibly
High-level boxes first, then depth where the interviewer steers
Every choice paired with the rejected alternative
Reliability and failure handling before you run out of time — not as an afterthought
Close with tradeoffs, cost, and what you'd build in v2

Behavioral round

STAR, tightly — 2–3 minutes, not 8
Lead with context in one sentence, then your actions
Quantify results
Own failures plainly; no blame-shifting
Have a real question ready for every interviewer

Question Bank

Self-test against these. If any make you uneasy, that's your next study block.

SQL

Second-highest salary per department, handling ties
7-day rolling active users
Longest streak of consecutive login days per user
Month-over-month revenue growth percentage
Day-1/7/30 retention by signup cohort
Sessionize events with a 30-minute inactivity gap
Find records in A missing from B, three different ways
Deduplicate keeping the most recent version
Median order value per category
Multi-step funnel conversion rates
Detect overlapping date ranges in a table
Point-in-time lookup against an SCD2 dimension
Why is this query slow, and how would you fix it?

Python/coding

Flatten arbitrary nested JSON into tabular rows
Compute per-user session stats from a raw log file
Stream-process a 100 GB file on a 16 GB machine
Implement retry with exponential backoff and jitter
Reconcile two datasets and emit a typed diff
Validate rows against a schema, quarantine failures
Top-K frequent items in a large stream
Merge overlapping intervals

Modeling

Design a star schema for [domain]; state the grain
Implement SCD Type 2 with MERGE; handle a late-arriving row
Model a many-to-many relationship in a dimensional warehouse
When would you denormalize into one big table?
Model order returns without corrupting historical revenue
How do you handle a source system that hard-deletes rows?
Star vs Data Vault for this scenario — pick and defend

Spark / distributed

Explain what happens when you call .collect() on a 500 GB DataFrame
Your job takes 4 hours; one task takes 3.5 of them. Diagnose.
repartition(200) vs coalesce(200) — when each?
How does broadcast join work and when does it fail?
Explain the shuffle
Why is inferSchema a problem in production?
Debug a persistent executor OOM
Why is Parquet faster than CSV for analytics?
What does AQE change at runtime?

Streaming

How do you get exactly-once from Kafka to a warehouse?
Explain watermarks and late data handling
Ordering guarantees in Kafka, and their consequence for your key choice
Consumer lag is growing steadily. Walk through your response.
Log compaction vs retention — pick one for a CDC topic
Reprocess six months of history without downtime

Fundamentals

Idempotency in a data pipeline — define it and show how you'd achieve it
Batch vs streaming — how do you actually decide?
CAP theorem and its relevance to your storage choice
OLTP vs OLAP and why we don't just query the replica
Normalization vs denormalization tradeoffs
Data lake vs warehouse vs lakehouse
What is a data contract and who owns it?

Why Candidates Get Rejected

Learn these; they're the difference between "strong technical skills" and "hire."

Jumping to tools in the design round without gathering requirements. Instant level downgrade.
Resume claims that collapse two questions deep. Fatal and unrecoverable.
Correct SQL, zero optimization reasoning. Reads as someone who's never had a query cost real money.
Tool name-dropping without fundamentals — knows the Airflow syntax, can't define idempotency.
No failure thinking. Never mentions retries, backfills, late data, or monitoring unprompted.
Silence while coding. Interviewers can't credit reasoning they can't hear.
No cost awareness. At senior levels this alone can sink you.
Behavioral stories with no metrics or no clear personal contribution.
Ignoring the consumer. Never asks who uses the data or what decision it drives.
Not asking clarifying questions — a strong signal about how you'd behave with a vague ticket.
Defensive under pushback. Interviewers challenge good answers on purpose to see how you handle it.
Over-engineering a problem that needed a scheduled query and a table.

Level Adjustments

0–2 years (entry / junior)

Weight: SQL 40%, Python 25%, fundamentals 20%, modeling 10%, design 5%

SQL and Python are almost everything — get them genuinely strong
One well-built end-to-end project substitutes for work experience; make it excellent
Know fundamentals cold: OLTP vs OLAP, star schema, batch vs stream, why Parquet
Design rounds will be light — a clean linear pipeline with basic error handling is enough
Behavioral: academic projects, internships, and open-source count; be specific

3–6 years (mid / senior)

Weight: SQL 20%, design 25%, modeling 20%, Spark 20%, behavioral 15%

Design and modeling rounds decide your level and comp
Spark internals are expected in depth, not just API familiarity
Have incident stories with real root-cause analysis
Know your production numbers: volumes, latencies, costs, SLAs
Show ownership — pipelines you designed, not just implemented

7+ years (staff / lead / architect)

Weight: Architecture 35%, influence & communication 25%, modeling 15%, cost/platform 15%, coding 10%

Platform thinking: frameworks and standards others build on, not individual pipelines
Migration stories: legacy → modern, with the political and technical cost
Explicit cost management and capacity planning
Team topology, hiring, mentoring, setting technical direction
Stakeholder management and cross-org influence
Coding rounds still happen — don't let fundamentals rust

Resources

Books

Fundamentals of Data Engineering — Reis & Housley (best single overview of the field)
Designing Data-Intensive Applications — Kleppmann (the distributed systems foundation)
The Data Warehouse Toolkit — Kimball & Ross (dimensional modeling canon)
Spark: The Definitive Guide / Learning Spark, 2nd ed.
Kafka: The Definitive Guide
Streaming Systems — Akidau et al. (event time and watermarks, properly explained)
Fluent Python — Ramalho (if your Python needs depth)

SQL practice

StrataScratch and DataLemur — closest to real DE interview questions
LeetCode SQL 50 → then the hard set
HackerRank SQL for volume drilling
Advanced SQL puzzle collections for gaps-and-islands style problems

Coding practice

LeetCode easy/medium, hashmap and string tags
Your own repo of the data-shaped problems from Phase 2

Hands-on

Docker Compose local stack: Postgres + Kafka + Spark + Airflow + MinIO + dbt
Free-tier cloud accounts; set billing alerts before you start
Public datasets: NYC TLC trips, GH Archive, OpenSky, Wikipedia pageviews, government open data portals

Mocks

Peers in the field (highest value — they'll probe like a real interviewer)
Paid mock platforms with actual DEs, worth it before top-choice loops
Recording yourself solo, if nothing else is available

Staying current

Engineering blogs: Netflix, Uber, Airbnb, Spotify, DoorDash, Databricks, Shopify — these are also where design-round questions come from
Note: the specific hot tools in this field shift every 12–18 months. Fundamentals don't. Prioritize accordingly, and check current job descriptions in your target market to see which names are actually in demand right now.

Application & Offer Logistics

[ ] Tailor your resume per role family; mirror the JD's vocabulary where it's honestly true
[ ] Lead every bullet with impact and a number, not the tool name
[ ] Referrals over cold applications, by a wide margin — activate your network first
[ ] Apply in waves: warm-ups first, top choices after you've done 3–4 real loops
[ ] Keep a tracker: company, stage, dates, questions asked, gaps exposed, follow-ups
[ ] After every interview, write down every question within an hour — this becomes your best study guide
[ ] Don't give a first number in the recruiter screen if you can defer; ask for their range
[ ] Research market comp for your level, location, and company tier before any negotiation
[ ] Try to align offer timelines so you can compare
[ ] Evaluate the whole package: comp, on-call load, tech debt, team, growth, manager
[ ] Rejections are information, not verdicts — ask for feedback, adjust, keep going

Adjusting the Timeline

4 weeks (compressed): SQL patterns + optimization (wk 1), Python + modeling (wk 2), Spark + design framework with 6 practice questions (wk 3), mocks + behavioral + resume defense (wk 4). Drop deep streaming theory and Data Vault. Accept that design depth will be thinner.

8 weeks (balanced): Halve every phase, keep all of them. This is the most common realistic plan for someone currently employed.

12 weeks (default): As written above.

6 months (leisurely / while employed): Same phases, but add a second portfolio project, learn a second cloud, read DDIA and Kimball properly rather than skimming, and do 15+ mocks. This is also enough time to change level, not just change jobs.

One last thing: the candidates who pass aren't the ones who memorized the most. They're the ones who consistently ask what the data is for, state their assumptions out loud, and think about what happens when it breaks. Build those three habits into every practice session and they'll show up in the room automatically.

Data Engineer Interview Roadmap

Table of Contents

1. What You're Actually Being Tested On

2. The Interview Loop

3. Diagnostic: Where Do You Stand

Phase 1 — SQL (Weeks 1–2)

Patterns to master

Optimization — the part that separates levels

Practice plan

Phase 2 — Python & Coding (Weeks 2–3)

Python fundamentals

DSA that actually appears

Data-shaped coding problems (very common)

pandas & PySpark

Phase 3 — Data Modeling (Weeks 3–4)

Foundations

Fact tables

Dimensions

Modern layering

Operational modeling

Practice

Phase 4 — Distributed Processing & Spark (Weeks 4–6)

Execution model

Joins

Skew and performance

Storage

Practice

Phase 5 — Streaming, Orchestration, Cloud (Weeks 6–7)

Kafka / streaming

Orchestration

Cloud — pick ONE and go deep

Data quality & governance

Phase 6 — System Design (Weeks 7–9)

The framework — 45 minutes

Design questions to practice

Phase 7 — Projects, Resume & Behavioral (Weeks 9–10)

Portfolio projects

Resume deep dive prep

Behavioral stories

Questions to ask them

Phase 8 — Mocks & Polish (Weeks 10–12)

In-The-Room Playbooks

SQL round

Coding round

Modeling round

System design round

Behavioral round

Question Bank

Why Candidates Get Rejected

Level Adjustments

0–2 years (entry / junior)

3–6 years (mid / senior)

7+ years (staff / lead / architect)

Resources

Application & Offer Logistics

Adjusting the Timeline