Contents · Chapter 1 · SQL

Joins, NULLs, and the traps

SQL screens rarely test exotic syntax. They test whether you can be trusted with the three things that quietly produce wrong numbers: join fan-out, NULL semantics, and the difference between filtering rows and filtering groups.

Know your joins by what they keep. An INNER JOIN keeps only matches. A LEFT JOIN keeps every left row and pads the right with NULLs when there's no match — which is exactly why the next trap bites.

The classic gotcha

A LEFT JOIN followed by a WHERE on the right table silently becomes an inner join — the WHERE throws away the NULL-padded non-matches. If you meant "keep everyone, and tell me about their orders", the condition belongs in the ON clause, not the WHERE. Getting this right on the whiteboard is a strong senior signal.

NULL is not a value; it's the absence of one. NULL = NULL is not true, it's NULL. So x NOT IN (subquery) returns nothing the moment the subquery contains a single NULL — a bug that has shipped in a thousand pipelines. Prefer NOT EXISTS, which handles NULLs correctly. And remember COUNT(*) counts rows while COUNT(col) skips NULLs — a difference interviewers love.

Semi- and anti-joins are the vocabulary for "does a match exist" without fan-out. EXISTS / IN is a semi-join (keep left rows that have a match, once); NOT EXISTS is an anti-join (keep left rows with no match). Reaching for these instead of a JOIN + DISTINCT shows you understand why the join would have duplicated rows in the first place.

Interview angle

"What's the difference between WHERE and HAVING?" is really "do you know the logical order of operations?" WHERE filters rows before grouping; HAVING filters groups after aggregation. You can't reference COUNT(*) in WHERE, and filtering in HAVING what you could have filtered in WHERE needlessly aggregates rows you'll discard. §1.4 makes the whole order concrete.

1.2

Window functions

If one topic separates people who "know SQL" from people who use it daily, it's window functions. A window function computes across a set of rows related to the current row — without collapsing them the way GROUP BY does. You keep every row and gain a new column computed over its window.

The anatomy is always the same: func() OVER (PARTITION BY … ORDER BY … frame). PARTITION BY splits rows into independent groups; ORDER BY ranks within each; the frame (for aggregates) says how many surrounding rows to include. Play with the pieces:

Figure 1.2. Eight employees across two departments. Switch the function and watch how ROW_NUMBER, RANK, and DENSE_RANK treat the salary tie differently, and how running totals and LAG read within each partition. Illustrative numbers.

The three ranking functions are a favorite because the tie exposes whether you truly know them: ROW_NUMBER gives every row a distinct number (ties broken arbitrarily), RANK gives ties the same number and then skips (1,2,2,4), and DENSE_RANK ties without skipping (1,2,2,3). "Second-highest salary" is a DENSE_RANK() = 2 problem, and knowing why it isn't RANK when there are ties is the whole point of the question.

Frames matter for running aggregates. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is a running total; the default frame when you supply ORDER BY is RANGE, which behaves differently around ties — a subtle bug when duplicate order values collapse into one frame. When precision matters, specify ROWS explicitly.

1.3

Patterns they actually ask

A handful of shapes cover most SQL rounds. Recognize the shape and the query writes itself.

Deduplicate, keeping the latest. The canonical ROW_NUMBER pattern — number rows within each key by recency, keep number 1:

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC) AS rn
  FROM events)
SELECT * FROM ranked WHERE rn = 1;   -- one row per user, newest wins

Top-N per group. Same engine, different filter: ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) then WHERE rn <= 3. If you want ties included at the boundary, swap to RANK.

Gaps and islands. The one that looks scary and is really a trick: to collapse consecutive runs (of logins, of active days), subtract a ROW_NUMBER from the value itself — consecutive rows share a constant difference, which becomes a group key.

-- consecutive active days collapse into one "island"
SELECT user_id, MIN(d) AS start_day, MAX(d) AS end_day
FROM (
  SELECT user_id, d,
    d - ROW_NUMBER() OVER (
          PARTITION BY user_id ORDER BY d) AS grp
  FROM activity)
GROUP BY user_id, grp;

Pivoting with conditional aggregation — SUM(CASE WHEN month = 'Jan' THEN amt END) — and running totals with a windowed SUM round out the set. If you can produce these five without hesitation, you'll clear most SQL screens.

Interview angle

When handed a SQL problem, say the shape before you type: "this is a top-N-per-group, so a windowed ROW_NUMBER with a filter." Narrating the pattern proves it's recognition, not luck — and if you've mis-read the problem, the interviewer corrects you before you've written twenty lines.

1.4

The logical execution order

SQL is written SELECT … FROM … WHERE … but it is not evaluated in that order. Almost every "why can't I reference this alias here?" and "why is my WHERE rejecting my window function?" question dissolves once you internalize the real order. Step through it:

Figure 1.4. The logical evaluation order of a query, with row counts at each stage. FROM/JOIN build the row set, WHERE filters rows, GROUP BY collapses them, HAVING filters groups, SELECT computes columns, and only then do ORDER BY and LIMIT run. Illustrative numbers.

Two consequences fall straight out of the order and are asked constantly. You can't use a SELECT alias in WHERE — WHERE runs before SELECT computes it — but you can use it in ORDER BY, which runs after. And window functions can't appear in WHERE, because they're computed in the SELECT phase, after WHERE has already filtered; that's precisely why the dedup and top-N patterns wrap the window in a subquery or CTE and filter in the outer query.

The same order explains why WHERE is cheaper than HAVING for row-level filters: filtering early means fewer rows to group. Push filters down as far as the logic allows — the query planner does this for you when it can, but writing it correctly makes your intent unambiguous.

Contents · Chapter 2 · Data modeling

2.1

Grain, facts, and dimensions

Every modeling question starts in the same place, and candidates who skip it get lost: what is one row? That's the grain — the precise meaning of a single record in a table. "One row per order line." "One row per user per day." Declaring the grain first is the single highest-leverage habit in modeling, because keys, aggregations, and joins all follow from it, and mixing grains in one table is how double-counting bugs are born.

Facts are the measurements — the rows you sum and count. They're tall, narrow, and mostly numeric: an order, a click, a payment, with foreign keys and additive measures (amount, quantity). Dimensions are the context you slice by — customer, product, date, store — wide, descriptive, and comparatively few rows. The fact says what happened; the dimensions say who, what, where, when.

Facts come in three flavors worth naming in an interview: transaction (one row per event, the default), periodic snapshot (one row per entity per period — daily balances), and accumulating snapshot (one row per process instance, updated as it moves through milestones — an order's ordered/shipped/delivered timestamps). And watch for measures that don't sum across every dimension: a bank balance is semi-additive (you can add balances across accounts but not across days), and a ratio is non-additive. Naming that distinction unprompted marks you as someone who has been burned by it.

Interview angle

"Model X for analytics" is a grain question in disguise. Answer in order: grain ("one row per …"), then the facts (the measures), then the dimensions (the slice-by context), then how the dimensions handle change (§2.3). That sequence is the senior answer — most candidates jump to tables and tangle the grain.

2.2

Star schema, and one big table

The star schema is the default analytical shape: a central fact table surrounded by dimension tables, each joined by a foreign key. It's called a star because the diagram looks like one. Its virtue is that it matches how people query — "revenue (fact) by region (dim) and month (dim)" — and BI tools understand it natively. A snowflake schema normalizes dimensions further into sub-tables; usually not worth the extra joins.

The modern counter-move is the wide table, or "one big table" (OBT): pre-join the facts and dimensions into one denormalized table so queries never join at all. Columnar engines (Snowflake, BigQuery, Spark on Parquet) only read the columns a query touches, so the width is cheap and the missing joins are fast. Toggle between the two shapes:

Figure 2.2. The same sale, modeled as a star (fact + dimension tables joined by keys) and as one wide denormalized row. Star keeps dimensions in one place and small; OBT trades storage and update-complexity for zero-join reads. Illustrative.

The trade-off is exactly the classic one. Star: dimensions live in one place, so a product rename is a one-row update, and storage is lean — but every query joins. OBT: reads are join-free and dead simple for analysts — but a product rename must be restated across millions of fact rows, and the table balloons. The honest interview answer: star (or a Kimball-style dimensional model) as the warehouse core for its maintainability, materialized wide tables on top for the hot query paths. It's not either/or; it's layers.

2.3

Slowly changing dimensions

Dimensions change — a customer moves, a product is recategorized — and how you record that change is the most-asked modeling question there is. These are slowly changing dimensions (SCD), and the "types" are just policies for what to do when an attribute changes. Watch the same customer move from Pune to Bengaluru under each:

Figure 2.3. Customer 42 relocates. Type 1 overwrites (history lost), Type 2 adds a new versioned row (full history), Type 3 keeps a "previous" column (one step of history). Illustrative.

Type 1 overwrites the old value — simple, but history is gone, so a report of "sales by region last year" silently moves last year's sales to the new region. Type 2 is the workhorse: close the old row and insert a new version, with valid_from / valid_to dates and often an is_current flag and a surrogate key per version. History is preserved, and a fact joins to whichever version was current at the time of the event. Type 3 keeps a single "previous value" column — cheap, but only remembers one hop.

Type 2 is where the real interview lives, because the fact join is subtle: you join on the natural key and the event timestamp between valid_from and valid_to — an "as-of" join. And you maintain it with a MERGE:

MERGE INTO dim_customer t
USING staged_updates s
  ON t.customer_id = s.customer_id AND t.is_current = true
WHEN MATCHED AND t.city <> s.city THEN          -- attribute changed
  UPDATE SET t.is_current = false, t.valid_to = s.effective_date
WHEN NOT MATCHED THEN                                -- brand-new or the new version
  INSERT (customer_id, city, valid_from, valid_to, is_current)
  VALUES (s.customer_id, s.city, s.effective_date, null, true);

(Type 2 via MERGE usually takes two passes — one to expire the current row, one to insert the new version — which many engines express as a multi-clause merge or two statements. The idea to convey is the close-and-insert, not the exact dialect.)

Interview angle

"A customer changes their address — how do you store it?" is a direct SCD-type question. Don't just say "Type 2." Say why: "if analytics needs point-in-time correctness — what region was this customer in when they bought — I need Type 2 so facts join to the version valid at event time. If we only ever care about the current address, Type 1 is fine and cheaper." Tying the choice to a query requirement is the senior move.

2.4

Keys, and modeling for change

A natural key comes from the business (an email, an order number). A surrogate key is a meaningless integer or hash the warehouse assigns. Dimensional models lean on surrogate keys for a specific reason: with SCD Type 2, one natural customer maps to many rows over time, so facts must reference the surrogate key of the specific version current at event time. Surrogates also insulate you from messy or changing source keys and keep fact-table joins as fast integer comparisons.

A conformed dimension is one shared consistently across fact tables — the same dim_date and dim_customer used by orders, returns, and support tickets — so metrics line up across the business and you can compare them on the same axes. Building conformed dimensions is what makes a warehouse feel coherent rather than a pile of unrelated tables.

Two edge cases interviewers use to probe depth. Late-arriving dimensions: a fact shows up referencing a dimension member you haven't loaded yet — you insert a placeholder ("inferred member") and enrich it when the real record lands, so the fact isn't dropped. Late-arriving facts: an event arrives days late and, under SCD Type 2, must join to the dimension version that was current back then, not now — which only works because Type 2 kept the history. Both are really the same lesson: model so that time is a first-class citizen, because data never arrives in a tidy order.

Contents · Chapter 3 · ETL & pipeline design

3.1

ETL, ELT, and the layers

Classic ETL extracts from the source, transforms in a separate compute tier, and loads the finished result into the warehouse. ELT flips the last two: land the raw data first, then transform it inside the warehouse. The industry moved to ELT for a mundane reason — columnar warehouse compute got cheap and elastic enough that transforming in place beat transforming in flight, and keeping the raw landing zone means you can re-derive everything when the business logic turns out to be wrong. Which it always does.

That's the answer interviewers want when they ask "ETL or ELT?" — not a definition, but the trade-off. ELT costs you storage and warehouse cycles and lets messy data in the door; it buys you replayability, because the transform is just code you can re-run against raw data you still have. ETL keeps the warehouse clean and is still right when the transform needs a library the warehouse can't run, when you must drop PII before it ever lands, or when the source is so large that filtering in flight saves real money.

Either way, mature pipelines land in layers, and the vocabulary is worth having: bronze (raw, immutable, append-only, partitioned by load date — an exact record of what the source said), silver (cleaned, deduplicated, typed, conformed — one row per real-world thing), gold (business aggregates and marts the analysts actually query). Databricks calls it the medallion architecture; Kimball-era shops call it staging/core/marts. Same idea: never transform in one hop, and never overwrite the evidence.

The layering is what makes incidents survivable. When a transform bug corrupts gold, you fix the code and rebuild from silver. When silver is wrong, you rebuild from bronze. When bronze is wrong, you genuinely have lost data — which is exactly why bronze is immutable and nobody writes business logic into it.

The through-line

Every layer boundary is a place you can restart from. Design so the expensive, irreversible thing (capturing the source) happens once, and everything downstream is a pure function you can re-run.

Interview angle

"Walk me through a pipeline you've built." Structure the answer as source → landing → cleaning → serving, and name what makes each hop re-runnable. Candidates who list tools ("Airflow, Spark, Snowflake") sound junior; candidates who describe layers and restart points sound like they've been paged.

3.2

Extraction: full, incremental, CDC

How you get data out of the source is the decision that constrains everything downstream, and it comes down to three strategies with very different costs.

Full snapshot. Read the whole table every run. Dead simple, trivially idempotent (you replace everything), and it catches deletes for free. It stops working when the table gets big — you burn source load and pipeline time re-reading rows that didn't change.

Incremental by watermark. Keep a high-water mark — the max updated_at or an auto-increment id from the last successful run — and pull only rows past it. Cheap and simple, but it has two well-known holes: it cannot see hard deletes (a deleted row simply stops appearing, and your target keeps it forever), and it silently misses rows if the source doesn't reliably bump updated_at on every write. Overlap the window slightly (re-pull the last few hours) so rows committed out of order aren't skipped — which is safe precisely because your load is idempotent.

Change data capture. Read the database's own replication log — Postgres WAL, MySQL binlog, typically via Debezium — and stream every insert, update, and delete as an event. CDC captures deletes correctly, imposes almost no query load on the source, and gives you near-real-time freshness with ordering you can trust (log sequence number, not wall-clock time). The cost is operational: you're running connectors, handling schema changes in the stream, managing offsets, and dealing with the initial snapshot-plus-catch-up dance.

The tell

Ask about deletes. A candidate who picks watermark incremental without asking whether the source hard-deletes has just designed a pipeline whose row counts drift from the source forever — and reconciliation questions are a favorite follow-up.

Interview angle

"How would you ingest this table?" is never answered with a tool. Profile the source out loud first: how big, how often does it change, does it have a reliable updated_at, does it hard-delete, can I get log access, how fresh does the consumer need it? The strategy falls out of those answers — and saying so is the signal.

3.3

Loading: append, overwrite, merge

Pipelines get retried. Tasks fail halfway, someone reruns yesterday, a backfill sweeps a month. So the only load pattern that survives production is one where running it twice equals running it once — idempotency. There are three write patterns, and only two of them are safe.

Append is the unsafe one. A rerun writes the same rows again and your counts double. It's fine only for genuinely immutable event logs where you dedupe downstream on an event id.

Overwrite the partition. Write date=2026-08-30 by replacing that partition, not adding to it. A rerun replaces the same partition with the same content, so it converges. This is the default for date-partitioned batch pipelines, and the sentence to have ready is: "I partition by event date and overwrite the partition, so retries and backfills are safe by construction."

Merge (upsert). Match on a business key and update or insert. This is what you use when rows change in place, and it's how CDC lands in the warehouse. The trap interviewers probe: if your source can deliver the same key more than once in a batch, the MERGE is ambiguous and most engines will error or pick arbitrarily. Deduplicate to one row per key first — usually the latest version by log sequence or update time.

MERGE INTO silver.customers t
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
             PARTITION BY customer_id ORDER BY lsn DESC) AS rn
    FROM bronze.customer_changes
    WHERE load_date = '2026-08-30'
  ) WHERE rn = 1          -- one row per key, latest wins
) s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'DELETE' THEN INSERT *

Interview angle

"Your job failed halfway through and you reran it — what happens?" The strong answer names the write pattern, not the orchestrator's retry setting. Say which unit you replace (the partition, or the key), and why a second run converges to the same state.

3.4

Schema drift and data contracts

Sources change without telling you. A product team adds a field, renames a column, widens an integer to a string because someone started writing "N/A" into it. That's schema drift, and it's the most common cause of a pipeline that ran green for months and then quietly produced garbage.

Changes are not equally dangerous, and saying so is the senior move. Additive changes (a new nullable column) are safe — old readers ignore them. Widening a type (int → bigint, adding an enum value) is usually safe. Narrowing or renaming is breaking: consumers reference a name or expect a range that no longer holds. This is exactly the compatibility model behind Avro/Protobuf schema registries, and naming forward- versus backward-compatibility is a strong signal.

The two policies you choose between: schema-on-read with auto-merge (accept whatever arrives, add new columns automatically) keeps the pipeline running but lets bad data in silently; a strict data contract (a declared schema the producer agrees to, validated at the boundary) fails fast and loudly, which is better than fast and wrong. Most real systems do both: auto-merge additive changes at bronze so nothing is lost, and enforce the contract at the silver boundary so breaking changes stop there instead of reaching the CFO's dashboard.

A data contract is more than a schema, and if you can say this part you're ahead of most candidates: it also carries semantics (what the field means), quality guarantees (this is never null, this is unique), freshness SLAs, and an owner — a human who gets paged. The point is to move the failure from "analyst notices numbers look weird three weeks later" to "producer's CI fails on the pull request."

Interview angle

"The upstream team added a column and your job broke — what do you do?" Don't answer with the hotfix. Classify the change (additive/widening/breaking), say where it should have been caught, and propose the contract at the boundary. Then mention the human part: schema changes are a communication failure before they're a technical one.

3.5

Late data, reprocessing, and reconciliation

Data does not arrive in order. A mobile client buffers events offline for two days; a source system posts backdated corrections; an upstream job reruns and restates a week. If your pipeline treats "the day's partition" as final the moment the clock rolls over, all of that lands nowhere.

The standard batch answer is a rolling reprocessing window: every run recomputes not just yesterday but the last N days (7 to 14 is typical), using merge or partition-overwrite so recomputation is free of duplicates. You pick N from measurement — look at the actual distribution of event_time versus load_time and cover the tail you care about. Anything later than N is caught by a periodic full restatement, or accepted as known drift.

Note what makes this possible: partitioning by event time, not ingestion time. If you partition by when the row arrived, a late event lands in today's partition and your "sales on August 30" answer changes depending on when you ask. Partitioning by event time keeps history stable and makes the correction land in the right day — the same reason streaming systems key windows off event time (§8.2).

Reconciliation is the part candidates forget. A pipeline that runs green isn't a pipeline that's correct. Compare row counts and summed measures against the source on a schedule, and alert on drift — this is how you catch the watermark that's been silently skipping rows for a month. When an interviewer asks how you'd know your pipeline is right, "we reconcile counts and control totals back to the source daily" is a far better answer than "we have retries."

Interview angle

Late data is the most reliable follow-up in the ETL round because it separates people who've operated pipelines from people who've built greenfield ones. Have a number ready — "we reprocessed a rolling 7 days because 99.5% of our late events landed inside that window" — since a measured window beats an arbitrary one.

Contents · Chapter 4 · Big data fundamentals

4.1

Why distribute at all

Distributed data processing exists for one blunt reason: the data no longer fits — in memory, on a disk, or in the time budget of a single machine. Once you accept many machines, three facts govern everything that follows, and every performance question in this guide is a consequence of them.

Partitioning. The data is split into chunks, and each machine works on its own chunk in parallel. Throughput scales with the number of partitions — until one of the next two facts stops you.
The network is the enemy. Reading local data is fast; sending data between machines is slow. Every expensive operation in big data is expensive because it moves data across the network. Hold onto this — it explains the shuffle, joins, and skew all at once.
Partial failure is normal. With hundreds of machines, something is always failing. Frameworks survive this by tracking lineage — the recipe to recompute any lost chunk — rather than by never failing.

These three give the two hard problems of the field. Data movement: minimize what crosses the network (partition well, pre-aggregate, broadcast small tables). Data skew: parallelism only helps if the chunks are even — one machine with ten times the data makes the other ninety-nine wait. Nearly every "why is my Spark job slow" answer is one of these two wearing a costume.

4.2

From MapReduce to the DAG

The original model was MapReduce: a map step transforms records independently in parallel, a shuffle groups them by key across the network, and a reduce step aggregates each group. It's still the right mental model for what happens under the hood — map is per-record and cheap, reduce needs the shuffle and is where the cost lives.

MapReduce's fatal flaw was that every step wrote its full output to disk before the next could start, so a multi-step job hammered disk between every stage. Spark's key insight was to keep intermediate data in memory and to model the whole job as a DAG — a directed acyclic graph of transformations — that it can optimize as a unit before running anything. Same map/shuffle/reduce physics; far less disk, and a planner that sees the whole computation.

Two ideas from that DAG matter for interviews and recur through the Spark chapters. First, lazy evaluation: Spark records transformations without executing them, then plans and runs only when an action forces a result (§5.2) — which is what lets it optimize across steps. Second, lineage: because the DAG is the recipe for every partition, a lost partition is recomputed from its inputs rather than checkpointed, which is how Spark tolerates machine failure for free.

The distinction that matters

Transformations split into narrow (each output partition depends on one input partition — map, filter; no data moves) and wide (each output partition depends on many input partitions — groupBy, join, distinct; a shuffle happens). Chains of narrow transformations fuse into a single stage; every wide transformation forces a new stage and a shuffle between them. "Narrow vs wide" is the vocabulary for the entire Spark performance conversation.

4.3

The shuffle

The shuffle is the beating heart of distributed processing and the number-one interview topic in this whole chapter. Whenever an operation needs all records with the same key together — GROUP BY, JOIN, reduceByKey, distinct, ORDER BY — but those records are scattered across machines, the framework must physically move them so each key lands on one machine. That movement is the shuffle.

Mechanically: each map task partitions its output by hash(key) % numPartitions and writes one file per target partition to local disk; each reduce task then fetches its partition's files from every map task across the network, and merges them. Watch keys route to their owning partition, then turn on a map-side combine:

Figure 3.3. Records partitioned by key across four map tasks are shuffled so each key lands on its owning reducer. A map-side combine (Spark's reduceByKey) pre-aggregates locally, so far fewer records cross the network. Illustrative numbers.

The shuffle is expensive on every axis at once — it writes to disk (spill), serializes records, and sends them across the network, then reads and merges on the other side. So the entire performance game is: shuffle less, and shuffle evenly. Shuffle less by pre-aggregating map-side (reduceByKey beats groupByKey precisely because it combines before the network), by broadcasting small tables to avoid the shuffle entirely (§6.2), and by not shuffling data you'll throw away (filter first). Shuffle evenly by fixing skew (§6.3) so one reducer doesn't get all the rows.

Interview angle

"Why is reduceByKey better than groupByKey?" — because groupByKey shuffles every record to the reducer and aggregates there, while reduceByKey combines values map-side first, so only one partial-aggregate per key per partition crosses the wire. Same result, a fraction of the network traffic. Drop the combine toggle in the simulator to see the count jump. This one answer signals that you understand the shuffle, not just its name.

4.4

Batch, streaming, and exactly-once

Batch processes a bounded dataset — all of yesterday's orders — and finishes. Streaming processes an unbounded flow — orders as they happen — and never finishes. The deep difference isn't speed; it's that a stream has no "end," so anything that needs completeness (a daily total, a join across sources) has to reckon with time and lateness explicitly.

That forces three concepts interviewers probe. Event time vs processing time: when the event happened vs when your system saw it — they differ because of network delays and retries, and correct aggregations key off event time. Windowing: since the stream never ends, you aggregate over finite windows (tumbling, sliding, session). Watermarks: a moving threshold that says "I'll wait this long for late data, then finalize the window and drop stragglers" — the knob that trades latency against completeness. (§7.3 makes these concrete in Structured Streaming.)

Then the delivery-guarantee question, asked in nearly every streaming interview. At-most-once: may lose data, never duplicates. At-least-once: never loses data, may duplicate (retries). Exactly-once: the effect is as if each record were processed once — the gold standard, achieved not by magic but by idempotent writes or transactional sinks plus checkpointed offsets, so a replay after failure doesn't double-count. The honest framing: true end-to-end exactly-once requires cooperation from the sink; absent that, you engineer effectively-once with idempotency and dedup keys.

Interview angle

"How do you get exactly-once?" The strong answer names the mechanism, not the buzzword: checkpoint the source offsets with the output atomically, and make the sink idempotent (upsert on a business key) or transactional (two-phase commit). Then the failure story closes itself — "on replay, re-processing the same offsets produces the same keys, and the idempotent write collapses the duplicate." That's §4.1's "what happens when it breaks," applied to streams.

Contents · Chapter 5 · The Spark execution engine

5.1

Cluster anatomy

Before tuning Spark you have to see it. A Spark application is one driver coordinating many executors, and almost every performance question maps to some piece of this picture. Click through it:

Figure 4.1. One driver, several executors, each with cores and a fixed memory pool. Tasks are the unit of work; a core runs one task at a time. Illustrative configuration.

The driver runs your main, builds the DAG, and schedules tasks — it does not process the data, but it does collect results, so collect() on a huge dataset OOMs the driver, a favorite gotcha. Each executor is a JVM on a worker node with a set of cores and a fixed memory pool; it runs tasks and caches data. A core is a slot that runs one task at a time, so an executor with 4 cores runs 4 tasks in parallel. A partition is processed by exactly one task — which is the hinge of the whole model: number of tasks that can run at once = total executor cores, and total work = number of partitions. If you have 200 partitions and 50 cores, the work runs in 4 waves.

This is why "how many partitions?" and "how much executor memory?" are the two levers everything else pulls on. Too few partitions and cores sit idle; too many and scheduling overhead dominates; too little memory per task and the job spills or dies. Chapter 5 tunes all of it — but only because this anatomy makes the trade-offs concrete.

5.2

Lazy evaluation

Spark does nothing when you call filter, select, or join. Those are transformations — they just extend the DAG, the recipe. Only an action — count, collect, write, show — forces Spark to actually plan and run. Step through what happens when the action finally fires:

Figure 4.2. Transformations queue lazily; the action triggers Catalyst to parse, analyze, optimize, and pick a physical plan, which is then split into stages and run. Illustrative.

Laziness is not a quirk; it's the whole optimization strategy. Because Spark sees the entire chain before running anything, its Catalyst optimizer can push filters down to the scan (read less), prune unused columns, collapse adjacent projections, and reorder joins — optimizations impossible if each step ran the instant you wrote it. The cost is a debugging surprise: an error in your filter doesn't surface until the count three lines later, because that's when it ran.

The practical consequences show up constantly. Every action re-runs the whole lineage — calling count() then collect() on the same DataFrame reads the source twice unless you cache() (§6.4). And df.explain() prints the physical plan without running the job, which is the single best habit for a Spark interview: "I'd check explain() to confirm the filter pushed down and the join is a broadcast" is a sentence that lands.

Interview angle

"What's the difference between a transformation and an action?" — transformations are lazy and build the DAG (they return another DataFrame); actions trigger execution (they return a value to the driver or write output). The follow-up is always "name some": narrow transformations map/filter/select, wide ones groupBy/join/distinct; actions count/collect/write/take. Then volunteer the consequence — "so multiple actions re-scan unless I cache" — to show you know why it matters.

5.3

Jobs, stages, and tasks

When an action runs, Spark builds a three-level hierarchy that you'll read constantly in the Spark UI. Understanding it is what turns "the job is slow" into "stage 3 is skewed." Build a pipeline and watch it split:

Figure 4.3. A pipeline splits into stages at every shuffle boundary. Broadcasting the small side of the join removes its shuffle — and therefore a whole stage. Illustrative.

The hierarchy is exact. One job per action. Each job splits into stages at every shuffle boundary — a stage is a run of narrow transformations that can execute without moving data, so a new wide transformation ends one stage and starts the next. Each stage runs as a set of tasks, one task per partition, executed in parallel across the cluster's cores. So: actions → jobs, shuffles → stage boundaries, partitions → tasks. Read the Spark UI through that lens and it stops being noise.

This is why reducing shuffles reduces stages, and why broadcasting the small side of a join (§6.2) is such a win — it converts a shuffle join into a shuffle-free one, deleting a stage boundary and the network traffic with it. Flip the broadcast toggle in the simulator and watch a stage disappear. When you can say "this job has three stages because there are two shuffles," you're reading Spark the way its authors intended.

5.4

Memory, and why jobs spill

An executor's memory is not one bucket. Spark's unified memory splits the usable heap between execution memory (for shuffles, joins, sorts, aggregations — the scratch space of computation) and storage memory (for cached/persisted data). They share a pool and borrow from each other: cached blocks can be evicted to make room for a big shuffle, but active execution memory can't be evicted mid-task. There's also a slice of user memory for your objects and a fixed reserved sliver.

When a task needs more execution memory than it can get, it doesn't (usually) crash — it spills to disk: sorts and hash tables overflow to local disk and are merged back, trading speed for survival. A little spill is fine; heavy spill is a top cause of slow jobs, and it almost always traces back to partitions that are too large (so each task's working set doesn't fit) or skew (so one task's does not while others' do). The fix is more, smaller, even partitions — which is exactly chapter 5.

An OutOfMemoryError is the harder failure, and interviewers ask where it strikes. On the executor: a single partition too big to process, a giant groupByKey, or a skewed key — fix with more partitions or skew handling. On the driver: collect() pulling a large result back, or a broadcast bigger than the driver can build — fix by not collecting, or lowering the broadcast threshold. Naming which side OOM'd and why is the difference between guessing and diagnosing.

Common cause

The default spark.sql.shuffle.partitions = 200 is a fixed number regardless of your data size. On 2TB of shuffle it means 10GB per task — instant spill or OOM. On 200MB it means a thousand tiny tasks and pure scheduling overhead. "It's a static default that rarely fits your data; I'd set it so each task handles ~128–200MB, or lean on AQE to coalesce" is the answer that shows you've actually tuned Spark.

5.5

Adaptive Query Execution

For years Spark planned a query once, up front, using estimates — and estimates about data it hadn't read yet were often wrong, which is how you got 200 shuffle partitions on 50MB of data. Adaptive Query Execution (AQE), on by default since Spark 3.2, fixes this by re-planning at runtime: at each shuffle boundary it looks at the actual statistics of the data just produced and adjusts the rest of the plan. It's the highest-leverage "free" performance feature in modern Spark, and a very common interview topic.

Three things AQE does, all worth naming. Coalesce shuffle partitions: it merges the default 200 partitions down to a sensible number based on real output size, curing the small-partition problem automatically. Switch join strategies: if a table turns out to be small enough after filtering, AQE flips a planned sort-merge join into a broadcast join at runtime. Handle skew: it detects oversized partitions and splits them into smaller sub-partitions so one straggler task doesn't hold up the stage (§6.3).

The senior framing is that AQE doesn't make tuning obsolete — it makes the common cases self-correcting, so your attention goes to the structural choices it can't make for you: the data layout, the partitioning key, whether a join should be a broadcast by design. "AQE will coalesce my partitions and catch runtime skew, so I focus on the things it can't see" is exactly the balance an interviewer wants to hear.

Contents · Chapter 6 · Spark performance

6.1

Partitions and parallelism

Partition count is the master dial of Spark performance, because a partition is a task and a task needs a core (§5.1). Get it wrong in either direction and you pay. Move the sliders and read the verdict:

Figure 5.1. Shuffle data divided by partition count gives per-task size; that size against cluster cores gives the number of waves. The target is even tasks of roughly 128–200MB. Illustrative.

Too few partitions: each task's slice is huge, so tasks spill or OOM, and you can't use all your cores (10 partitions can't fill 50 cores — 40 sit idle). Too many: each task is trivial, and the fixed per-task overhead — scheduling, serialization, launch — dominates the actual work; a million-task job spends its life in bookkeeping. The sweet spot is tasks of roughly 128–200MB, and enough of them to be a small multiple of your total cores so the waves are full and even.

The tools to reach it: repartition(n) does a full shuffle to exactly n even partitions (use when increasing partitions or redistributing skew); coalesce(n) merges partitions without a full shuffle (cheap, but only for reducing count, and it can leave partitions uneven). "Repartition to increase or rebalance, coalesce to shrink cheaply after a heavy filter" is the crisp distinction interviewers want — plus the note that AQE (§5.5) now coalesces shuffle partitions for you automatically.

Interview angle

"repartition vs coalesce?" — repartition triggers a full shuffle and can both increase and decrease partitions, producing even sizes; coalesce avoids a full shuffle by merging existing partitions, only decreases, and may leave them uneven. The tell of a senior answer is the when: "after a big filter that leaves 500 sparse partitions, I coalesce to avoid a needless shuffle; to fix skew or go wider, I repartition."

6.2

Choosing a join

Joins are where shuffles are born and where the biggest wins hide. Spark picks a physical strategy based on the sizes of the two sides; understanding the choice lets you force the right one. Set the table sizes and see what Spark does:

Figure 5.2. When one side is under the broadcast threshold, Spark ships it to every executor and skips the shuffle entirely. When both sides are large, it shuffles both and sort-merges. Illustrative.

Broadcast hash join (BHJ): if one table is small enough (under spark.sql.autoBroadcastJoinThreshold, 10MB by default), Spark sends a full copy to every executor and joins locally — no shuffle of the big table at all. This is the single most impactful join optimization: a large fact joined to a small dimension should almost always be a broadcast. Sort-merge join (SMJ): when both sides are large, Spark shuffles both by the join key, sorts each partition, and merges — correct at any scale, but two shuffles' worth of network and disk. (A shuffle-hash join is a third, rarer option.)

You can force a broadcast with a hint — broadcast(dim_df) or /*+ BROADCAST(d) */ — when you know a table is small but Spark's estimate is stale. But the failure mode is sharp and interviewers test it: broadcast a table that's too big and the driver OOMs building it, or every executor blows its memory holding the copy. Broadcast is for genuinely small tables (dimensions, lookups) — up to low hundreds of MB with tuning, not multi-GB.

Interview angle

"You're joining a 1TB fact table to a 5MB country lookup and it's slow — what's wrong?" The answer they want: the small table probably isn't being broadcast (maybe it's read as a large number of files, or the estimate is off), so Spark is shuffling a terabyte needlessly. Force it with broadcast() and the shuffle vanishes. Bonus points for "I'd confirm in explain() that it says BroadcastHashJoin, not SortMergeJoin."

6.3

Data skew

Parallelism only helps if the work is even. Skew is when one key (or a few) has vastly more rows than the rest, so after the shuffle one task gets a mountain and the others get molehills — and the whole stage waits for that one straggler. It's the most common cause of "99% of tasks finished in seconds, the last one ran for an hour." Crank the skew and apply the fix:

Figure 5.3. A single hot key overwhelms one task. Salting spreads that key across many tasks by appending a random suffix, so the load evens out and the stage finishes in the time of the slowest balanced task. Illustrative.

You diagnose skew in the Spark UI: one task in a stage with a duration and shuffle-read size far above the median. The classic causes are a NULL or default value that dominates a join key, or a genuine power-law (one enormous customer). The fixes, in order of preference: let AQE skew join handling split the hot partition automatically (often enough on its own now); salt the key — append a random suffix (key + "_" + rand(0..n)) to the hot side and explode the other side to match, so one key becomes n keys spread across n tasks; broadcast the other side if it's small enough to sidestep the shuffle entirely; or filter and handle the hot key separately.

Interview angle

"One task runs forever while the rest finish — what's happening and how do you fix it?" Name it as skew, explain the mechanism (one key's rows all hash to one partition/task after the shuffle), then give the ladder: check if AQE skew handling is on, salt the hot key to spread it, or broadcast the other side. Mentioning that a NULL join key is a frequent hidden culprit — because all NULLs hash together — is the detail that signals real experience.

6.4

Caching and persistence

Because every action re-runs the full lineage (§5.2), a DataFrame used more than once is recomputed each time — reading the source and redoing every transformation. Caching breaks that: cache() or persist() materializes the DataFrame after its first computation so later actions reuse it. The rule is simple and interviewers want it stated as a rule: cache only what you reuse — an iterative algorithm, a cleaned DataFrame feeding several outputs, a lookup hit repeatedly. Caching something used once wastes memory you could have given to execution.

cache() is just persist(MEMORY_AND_DISK) — the sensible default that keeps blocks in memory and spills the overflow to disk rather than recomputing. Other storage levels trade memory for CPU or resilience: MEMORY_ONLY (fastest, but recomputes what doesn't fit), DISK_ONLY, and _SER variants that store serialized bytes (smaller, more CPU to read). And caching is itself lazy — the data isn't stored until the next action materializes it. Call unpersist() when you're done to free the memory for the rest of the job.

Cache vs checkpoint

A subtle senior distinction: caching keeps the data and the lineage (if a cached block is lost, Spark recomputes it from the DAG), so it's for speed. Checkpointing writes to reliable storage and truncates the lineage — useful in very long iterative jobs where the DAG itself grows unwieldy or where you want to survive executor loss without recomputation. Most tuning is caching; checkpointing is the rarer, heavier tool.

6.5

UDFs, Arrow, and a tuning checklist

One PySpark-specific trap is asked in nearly every interview: Python UDFs are slow, and you should know exactly why. A plain Python UDF forces Spark to serialize each row out of the JVM, ship it to a Python worker process, run your function, and serialize the result back — row by row — and it's a black box to Catalyst, so no pushdown or optimization crosses it. The order of preference is therefore: built-in Spark SQL functions first (they run in the JVM and optimize), then pandas UDFs (vectorized, Arrow-based) which move data in columnar Arrow batches and run on whole vectors — often an order of magnitude faster than a row-at-a-time UDF — and only a plain Python UDF as the last resort for logic that truly can't be expressed otherwise.

That single rule — avoid Python UDFs; prefer native functions, then vectorized pandas UDFs — earns real credit, because it shows you understand the JVM/Python boundary that defines PySpark's performance. Everything else assembles into a checklist you can recite when asked "how do you tune a Spark job?":

Read less. Column pruning and predicate pushdown (partitioned/Parquet sources), filter early, don't SELECT *.
Shuffle less. Broadcast small tables, reduceByKey over groupByKey, avoid needless distinct/re-partitions.
Shuffle evenly. Right-size partitions (~128–200MB), fix skew (salt / AQE), keep tasks a small multiple of cores.
Reuse work. Cache what you read more than once; unpersist when done.
Right-code it. Native functions > pandas UDFs > Python UDFs; DataFrames over RDDs so Catalyst can optimize.
Verify. Read explain() and the Spark UI — confirm pushdown, broadcast, and even task times rather than guessing.

Interview angle

"Walk me through tuning a slow Spark job" is an invitation to show a method, not a grab-bag. Go in order — how much data is read, where the shuffles are, whether joins broadcast, whether tasks are even, what's cached, whether any Python UDFs are in the path — and end with "and I'd confirm each in explain() and the UI." Structure beats trivia; this checklist is the structure.

Contents · Chapter 7 · The PySpark coding round

7.1

Warm-up drills

The PySpark round rewards fluency with the DataFrame API and the reflex to explain the physical plan you just caused. Two warm-ups appear constantly; they're the SQL patterns of §1.3 in DataFrame form.

Deduplicate, keeping the latest. Window by key, order by recency, keep row 1:

from pyspark.sql import Window, functions as F

w = Window.partitionBy("user_id").orderBy(F.col("updated_at").desc())
latest = (events
    .withColumn("rn", F.row_number().over(w))
    .filter(F.col("rn") == 1)
    .drop("rn"))                    # one row per user, newest wins

Say out loud: "the window forces a shuffle by user_id, so I'd watch for skew if a few users dominate." That one sentence turns a coding answer into a systems answer.

Top-N per group. Identical machinery, boundary filter instead of equality:

w = Window.partitionBy("category").orderBy(F.col("sales").desc())
top3 = (products
    .withColumn("rk", F.row_number().over(w))
    .filter(F.col("rk") <= 3))          # swap to F.rank() to include ties

If the group count is huge and N is small, mention the optimization: a partial top-N per input partition before the shuffle cuts what crosses the wire — the same "combine map-side" instinct from §4.3.

7.2

Harder drills: sessionization

Sessionization is the classic "harder" PySpark problem: group a user's events into sessions, where a new session starts after a gap of inactivity (say 30 minutes). It tests windows, lag, a cumulative sum, and whether you can compose them — exactly the skills a real pipeline needs.

The trick is a two-window pattern. First, mark where each session begins by comparing each event to the previous one with lag; then take a running sum of those markers to assign a session id:

from pyspark.sql import Window, functions as F

GAP = 30 * 60                                  # 30 minutes, in seconds
w = Window.partitionBy("user_id").orderBy("event_ts")

sessions = (events
    # seconds since this user's previous event
    .withColumn("prev_ts", F.lag("event_ts").over(w))
    .withColumn("gap", F.col("event_ts") - F.col("prev_ts"))
    # 1 when a new session starts (first event, or gap too big)
    .withColumn("is_new",
        ((F.col("prev_ts").isNull()) | (F.col("gap") > GAP)).cast("int"))
    # running sum of starts = a session id, per user
    .withColumn("session_id",
        F.sum("is_new").over(w.rowsBetween(Window.unboundedPreceding, 0))))

The pattern generalizes — the same "flag a boundary, cumulative-sum into a group id" idea solves gaps-and-islands, trip segmentation, and streak counting. Naming that it's the DataFrame twin of the §1.3 SQL trick shows you see the shape beneath the syntax.

Interview angle

After you write it, volunteer the performance note: "every step here rides one window partitioned by user_id, so it's a single shuffle by user — good, unless one user has millions of events, in which case I'd watch that partition for skew." Interviewers give sessionization precisely to see whether you'll connect the elegant code to the physical shuffle underneath.

7.3

Structured Streaming

Spark's streaming API treats a stream as an unbounded table that grows, and you write almost the same DataFrame code as batch — the engine incrementalizes it. The interview-relevant pieces are the ones unique to unbounded data: watermarks, windows, and output modes (the §4.4 concepts, made concrete).

counts = (stream
    .withWatermark("event_ts", "10 minutes")        # wait up to 10 min for late data
    .groupBy(
        F.window("event_ts", "5 minutes"),          # tumbling 5-min windows
        "country")
    .count())

query = (counts.writeStream
    .outputMode("append")                          # emit a window once its watermark passes
    .option("checkpointLocation", "s3://…/ckpt")     # offsets + state → exactly-once
    .toTable("country_counts"))

The watermark is the key knob: it bounds how long the engine keeps window state waiting for stragglers, then finalizes and drops later arrivals — trading latency for completeness. Output modes decide what's emitted each trigger: append (only new, finalized rows — needs a watermark for aggregations), update (rows that changed this trigger), complete (the entire result table every time — only for aggregations, and only when the result is small). And the checkpoint is non-negotiable: it persists source offsets and aggregation state so a restart resumes exactly where it stopped — the mechanism behind the exactly-once story from §4.4.

Interview angle

"How does Structured Streaming avoid double-counting on failure?" — the checkpoint stores the input offsets together with the state, and the sink is idempotent or transactional, so on restart it re-reads from the last committed offset and re-produces the same output without duplicates. Watermarks are the other half of the answer: they let aggregations finalize and bound state so it doesn't grow forever.

7.4

"Optimize this job"

A staple: they show you a working-but-slow script and ask you to make it faster. The skill is diagnosing along §6.5's axes rather than randomly changing flags. Here's the "before" and the reasoning:

# BEFORE — correct, but slow
big = spark.read.parquet("s3://events/")                 # 2 TB
dim = spark.read.parquet("s3://country_dim/")          # 4 MB
j = big.join(dim, "country_code")                        # shuffles 2 TB (!)
res = j.groupBy("country_name").agg(F.sum("amount"))
res.filter(F.col("country_name") == "India").show()      # filter AFTER the work

Three problems, each a chapter: the 4MB dimension isn't broadcast, so a terabyte shuffles needlessly (§6.2); the filter runs after the join and aggregation, so we process everything to keep one country (§1.4 / read-less); and there's no column pruning. The "after":

# AFTER — same result, a fraction of the work
big = spark.read.parquet("s3://events/").select("country_code", "amount")
dim = spark.read.parquet("s3://country_dim/")
res = (big
    .join(F.broadcast(dim), "country_code")             # no shuffle of the 2 TB side
    .filter(F.col("country_name") == "India")           # pushdown-friendly, early
    .groupBy("country_name").agg(F.sum("amount")))
res.show()

Broadcasting deletes the big shuffle; filtering early (and selecting only needed columns) shrinks what's read and moved. The closing line that wins the round: "I'd run explain() to confirm it now shows BroadcastHashJoin and the filter pushed into the scan, and check the UI for even task times." Method, then verification — never a lone flag.

Contents · Chapter 8 · Streaming & real-time

8.1

The log: Kafka's model

Batch pipelines read a dataset that sits still. Streaming systems read a log that never ends, and almost everything strange about them follows from that one difference. Kafka is the log most interviews assume, so its model is worth being precise about.

A topic is split into partitions, and a partition is an append-only ordered sequence of records. Producers pick a partition by hashing the record key. Consumers read at their own pace, tracking an offset — a position in the partition — which they commit so they can resume after a restart. Nothing is deleted on read; retention is by time or size, which is why you can replay history simply by rewinding the offset.

Two consequences carry most of the interview weight. First, ordering is guaranteed only within a partition, never across a topic. If you need all events for one user processed in order, they must share a key so they hash to the same partition. Ask any candidate "how do you guarantee ordering?" and the answer "key by entity id" separates the ones who've used Kafka from the ones who've read about it.

Second, in a consumer group each partition is consumed by exactly one consumer. That's how you scale out — but it also caps you: with 12 partitions, a 13th consumer sits idle. Partition count is a capacity decision made up front and awkward to change later, because increasing it rehashes keys to new partitions and breaks per-key ordering across the boundary.

The metric that matters

Consumer lag — latest produced offset minus committed offset — is the single number you watch. Flat lag means you're keeping up; steadily growing lag means your consumers are slower than the producers and you have minutes before the SLA breaks.

Interview angle

"Your consumer lag is growing — debug it." Walk the chain: is it more input (traffic spike, upstream backfill), slower processing (a bad deploy, an external call in the hot path, GC), or fewer working consumers (a crash-loop, or a rebalance storm)? Then the fixes, in order: scale consumers up to the partition count, make the processing cheaper, and only then add partitions.

8.2

Event time, windows, and watermarks

Event time is when the thing happened; processing time is when your system saw it. They diverge because of buffering, retries, network delays, and phones that were in a tunnel. Correct aggregations key off event time — otherwise "sales in the 9 a.m. hour" changes depending on when you run the query, which is not an acceptable property for a metric.

Because the stream never ends, you aggregate over windows: tumbling (fixed, non-overlapping — hourly counts), sliding (fixed size, overlapping — a 5-minute count updated every minute), and session (dynamic, closed by a gap of inactivity — the streaming version of the sessionization problem in §7.2).

That raises the only genuinely hard question in streaming: when is a window done? You can't wait forever, and you can't close instantly or you'll drop the stragglers. The answer is a watermark — the system's assertion that it has probably seen everything up to event time T. Typically watermark = (max event time seen) − (allowed lateness). When the watermark passes the end of a window, the window fires and its state is released.

Which makes allowed lateness a straight trade-off, and naming it as one is the senior signal: a long watermark delay means you catch more late events but results are stale and state is held longer; a short delay gives fresh results and lean state but drops more stragglers. Well-run systems don't discard late arrivals silently — they route them to a side output or a repair path, so the count of dropped-late events is a metric someone can actually look at.

Interview angle

"What happens to an event that arrives an hour late?" The complete answer has three parts: whether the watermark has already passed its window (fires or drops), what you do with it if it's dropped (side output, dead-letter, or a batch repair job), and how you'd pick the lateness bound (measure the arrival-delay distribution, don't guess).

8.3

Delivery semantics and exactly-once

Failures are certain, so the real question is what a replay does to your output. The three semantics are just three orderings of "process" and "commit the offset."

At-most-once: commit the offset first, then process. A crash in between loses the record. Almost never acceptable for data that matters.

At-least-once: process first, commit after. A crash between the two means the record is reprocessed on restart — you never lose data, but you can duplicate it. This is the honest default for most systems.

Exactly-once is the one candidates over-claim. It is not a checkbox; it requires the offset and the output to move atomically. There are exactly two ways to get there, and knowing both is the point of the question. Either the sink is transactional — a two-phase commit tied to checkpoints, which is what Flink and Kafka transactions do — or the sink is idempotent: you write with a deterministic key so replaying produces the same row rather than a second one. The second is far more common in data engineering, because a MERGE on a stable event id collapses the duplicate (§3.3).

The honest framing to say out loud: exactly-once is a property of the whole path, not of the stream processor. Flink can give you exactly-once state, but if it writes to a sink that can't participate in a transaction and can't dedupe, you have at-least-once with extra steps. When the sink can't cooperate, you engineer effectively-once — at-least-once delivery plus a dedupe key — and say so plainly.

Interview angle

Claiming "we had exactly-once" without naming the mechanism is a red flag interviewers actively listen for. Name it: "checkpointed offsets plus a transactional sink," or "at-least-once with idempotent upserts on event_id." The second is not a weaker answer — it's the one most production systems actually run.

8.4

Operating a stream, and picking an engine

Backpressure is what happens when a downstream operator can't keep up: buffers fill, the pressure propagates upstream, and ingestion slows. It's a feature, not a bug — the alternative is unbounded memory growth and a crash. Flink propagates it naturally through its network stack; Spark Structured Streaming bounds intake per micro-batch (maxOffsetsPerTrigger). Either way the visible symptom is rising consumer lag, and the cure is the same triage as any slow stage: find the expensive operator, remove the per-record external call, or scale the parallelism.

State is the thing that quietly kills long-running streaming jobs. Windows, joins, and deduplication all hold keyed state, and state that never expires grows without bound until checkpoints take minutes and recovery takes hours. Every stateful operator needs a retention story — watermark-driven cleanup for windows, explicit TTL for dedupe caches. "How do you keep state bounded?" is a question that filters people who have run a streaming job for more than a week.

Streaming joins deserve one caution. Stream-to-stream joins need both sides buffered within a time bound, so they're expensive and require watermarks on both inputs. Stream-to-table joins (enrich an event from a slowly changing dimension) are cheaper and far more common — and the correctness question interviewers like is which version of the dimension you join to: the one current now, or the one current at event time (§2.3).

On engines, have an opinion and a reason. Flink is true per-event streaming with rich state and timers, the lowest latency, and the default at real-time-heavy shops. Spark Structured Streaming is micro-batch: latency in the hundreds of milliseconds to seconds, but it reuses the DataFrame API and the cluster you already run, which makes it the pragmatic choice when the team is already on Spark. Kafka Streams is a library embedded in your service, not a cluster — right for simple per-event transforms inside an application. The wrong answer is naming a favorite; the right one is tying the choice to latency requirement, state complexity, and what the team already operates.

Interview angle

"Should this be batch or streaming?" is usually a trap worth failing loudly. Streaming costs you always-on infrastructure, state management, and much harder debugging. If the consumer is a dashboard someone reads each morning, batch is the correct engineering answer, and saying so demonstrates judgment rather than enthusiasm.

Contents · Chapter 9 · Snowflake & the cloud stack

9.1

Snowflake architecture

Snowflake's whole design follows from one decision: separate storage from compute. Data lives once in cloud object storage; independent compute clusters (virtual warehouses) read it without owning it. That's why many teams can query the same data at once without fighting for resources, and why you scale compute up for a heavy job and back down without touching the data. Three layers: storage (the data), compute (warehouses), and a cloud services layer (the brain — metadata, query planning, security, transactions).

Underneath, Snowflake stores tables as immutable micro-partitions — small (~50–500MB uncompressed) columnar chunks, created automatically, each carrying metadata about the range of values it holds. Queries get fast through pruning: the services layer reads the min/max metadata and skips every micro-partition that can't contain matching rows — the same idea as Parquet/Iceberg stats pruning, done automatically. How well it prunes depends on clustering — how well-sorted the data is on your filter columns. Toggle clustering and watch pruning change:

Figure 7.1. A range filter against micro-partitions. When data is well-clustered on the filter column, matching rows sit in a few partitions and the rest are pruned; when it's scattered, nearly every partition must be scanned. Illustrative.

This is the core Snowflake performance lever and a common question: a well-clustered table prunes aggressively and scans little; a poorly-clustered one on the same filter scans almost everything. For large tables whose natural load order doesn't match query filters, a clustering key tells Snowflake to keep the data organized on those columns. The parallel to Spark is exact — layout is destiny; pruning is only as good as the correlation between filters and physical order.

9.2

Warehouses and cost

Snowflake bills credits per second that a warehouse runs, and the size sets the burn rate: each T-shirt size doubles the compute and the credit rate (XS = 1 credit/hr, S = 2, M = 4, L = 8, and up). This makes cost reasoning delightfully concrete — and interviewers ask whether you'll scale up or out. Explore it:

Figure 7.2. Doubling warehouse size doubles the credit rate but only speeds up the parallelizable part of the work — so the cost of a single query is roughly flat until the serial fraction dominates, where bigger stops helping. Illustrative.

Because size doubles both speed and price, a perfectly parallel query costs about the same in total credits on any size — it just finishes faster on a bigger one (you pay for fewer seconds at a higher rate). So the honest guidance: scale up (bigger warehouse) for a single heavy, complex query that spills or sorts a lot; scale out (a multi-cluster warehouse that adds more same-size clusters) for many concurrent users, where the problem is queuing, not any one query's size. Add auto-suspend (stop when idle so you stop paying) and the near-instant result cache (an identical query within 24h returns free), and you have Snowflake's cost-control story.

Interview angle

"Scale up or scale out?" — up for one big query (more memory/CPU so it doesn't spill), out for concurrency (more clusters so users don't queue). The nuance that impresses: "because doubling size doubles both speed and rate, sizing up rarely changes the credit cost of a parallel query — it buys latency, not savings — so I size to avoid spilling, enable auto-suspend, and lean on the result and metadata caches to actually cut spend."

9.3

Time travel, clones, streams & tasks

Four Snowflake features come up because they solve real pipeline problems. Time Travel queries a table as it was up to N days ago (AT (OFFSET => -3600) or a timestamp) — undo a bad load, audit a change, recover a dropped table. It's distinct from Fail-safe, a non-configurable 7-day Snowflake-managed recovery window after Time Travel expires; a favorite gotcha is knowing you can self-serve Time Travel but must call support for Fail-safe.

Zero-copy cloning (CREATE TABLE dev CLONE prod) makes an instant, storage-free copy — it only duplicates metadata pointers to the existing micro-partitions, and new storage is used only as the clone diverges. This is how teams spin up full-size dev/test environments from production in seconds without paying for a second copy; explaining why it's free (shared immutable micro-partitions, copy-on-write) is the senior beat.

Streams and Tasks are Snowflake's native CDC-and-schedule pair. A Stream tracks row-level changes to a table (a change-data-capture cursor: what's been inserted/updated/deleted since you last read it); a Task runs SQL on a schedule or dependency. Together — a Task that consumes a Stream and MERGEs changes downstream — they build incremental pipelines entirely inside Snowflake, no external orchestrator required. Pair that with Snowpipe (continuous micro-batch file ingestion) and you can describe an end-to-end near-real-time load without leaving the platform.

9.4

Managed data integration

Most enterprises — and nearly every GCC — run at least one managed integration platform alongside their code: a visual environment where pipelines are DAGs of pre-built connectors (pull from a SaaS API, map fields, land in the warehouse), scheduled or triggered, with retries and error views out of the box. The vendors rotate; the pattern doesn't, and in an interview you should talk about the pattern, not the product.

Where low-code integration genuinely wins: many SaaS and database sources with standard extract-and-load needs, mixed-skill teams, and connector maintenance you'd rather outsource — the vendor absorbs every upstream API change so your team doesn't. Time-to-first-pipeline is hours, not sprints, and for the long tail of "get Salesforce into the warehouse daily" that's the right trade.

Where it strains: complex transformation logic trapped in a GUI instead of version-controlled code, weak diff/review/test workflows compared to a git repo, per-connector or per-row pricing that scales badly, and debugging that ends at the vendor's black box. The mature pattern is a split: managed connectors for extraction and loading, code (SQL/dbt/Spark) for transformation — which is exactly the ELT boundary from §3.1.

Whatever the tool, the operational questions are identical to hand-rolled pipelines, and interviewers use them to check you ran the thing rather than clicked around it: how does it track incremental state (and what happens when you reset it), what happens to failed records (retry, dead-letter, or silent drop), is a rerun idempotent (§3.3), and how do schema changes surface (§3.4)?

Interview angle

"You've used a low-code integration tool — why should we hire you to write pipelines in code?" Answer with the split: connectors bought you speed on extraction, while the logic that mattered lived in versioned, tested SQL — and name a moment you migrated a pipeline from GUI to code when it outgrew the tool. That story lands as judgement, not tool loyalty.

9.5

The cloud stack: AWS, Azure, GCP

Indian loops are cloud-specific in a predictable way: AWS dominates product companies and Amazon itself asks its own stack by name; Azure dominates GCCs and banks; GCP shows up at startups and analytics-heavy shops. You need one stack in production depth and the other two at mapping level — because the services are the same six ideas wearing different names.

The lake is object storage. S3 (ADLS Gen2, GCS) plus Parquet plus a table format is the lake; everything else reads from it. The interview questions here are partitioning layout, the small-files problem, and lifecycle tiering for cost.

Two ways to run Spark. Glue is serverless Spark with a built-in catalog — zero cluster management, less control, priced per DPU-hour. EMR is the managed cluster — full control over versions, instance types, and spot usage, more to operate. The Amazon-flavored question is exactly this trade-off: Glue for scheduled ETL a small team owns; EMR when you need custom configs, long-running clusters, or cost engineering at scale. (Azure: Databricks or Synapse Spark. GCP: Dataproc, or skip Spark and let BigQuery do the work.)

Two ways to run SQL. Athena queries S3 in place — serverless, per-TB-scanned, perfect for ad-hoc reads on the lake; costs explode if you scan unpartitioned raw data, which is the gotcha they probe. Redshift is the provisioned warehouse for concurrent BI: know distribution keys (collocate join keys to avoid data moving between nodes) and sort keys (skip blocks on range filters), because "how do you make this Redshift join fast" is a standard Amazon India question. (Azure: Synapse. GCP: BigQuery, where the same ideas surface as partitioning and clustering.)

Streams and orchestration. Kinesis is the managed Kafka-shaped log (shards ≈ partitions, same ordering-per-shard and consumer-scaling logic from §8.1); MSK is literal managed Kafka when you need the ecosystem. Airflow appears as managed MWAA (Azure: ADF for movement + orchestration; GCP: Composer). Lambda glues events together but has a hard 15-minute limit — proposing it for heavy transforms is a known red flag; it's for triggers, not for Spark's job.

The mapping trick

Answer in problems first, services second: "we need a log, a processor, a lake, a warehouse, an orchestrator" — then name the vendor's word for each. That works in an AWS loop, an Azure GCC loop, and a GCP loop without re-learning anything. The full three-cloud mapping table is in the cheat sheets.

Interview angle

Amazon interviewers ask their stack by name, and honesty beats bluffing everywhere: "I've run Glue and Redshift in production; Kinesis I know at the architecture level" survives follow-ups, while claiming all of them invites the follow-up that ends the loop. If your production depth is Azure (common in GCC roles), say so and map fluently — that's a pass.

Contents · Chapter 10 · DevOps & MLOps

10.1

CI/CD for data

Data pipelines are software, and the platform round checks whether you treat them that way. CI/CD for data is the same idea as for any code — automate test and deploy — with data-specific tests bolted on. A healthy pipeline lives in version control, gets tested on every change, and deploys through environments (dev → staging → prod) without hand-editing anything in production.

What makes it data CI/CD is the extra test layer. Beyond unit tests on transformation logic (run Spark locally on tiny fixtures and assert the output), you add data-quality tests — schema/contract checks (did an upstream column change type?), row-count and null-rate assertions, referential and uniqueness checks — as gates in the pipeline itself, so bad data fails the run instead of silently corrupting downstream tables. Frameworks like Great Expectations or dbt tests formalize this, but the concept is what's tested.

The other data-specific piece is environment isolation without copying petabytes: dev and staging need production-like data, which is exactly what Snowflake zero-copy clones (§9.3) or Iceberg branch/tag features provide instantly and cheaply. And secrets — database creds, API keys, cloud tokens — never live in code or config; they come from a secrets manager (Vault, cloud KMS, the orchestrator's secret store) injected at runtime. "Creds in a vault, referenced by name, rotated centrally" is the one-line answer to the secrets question.

Write-Audit-Publish

A pattern worth naming: WAP — write new data to a staging branch/table, audit it with quality checks, and only publish (atomically swap it into the production table) if the checks pass. On open table formats (Iceberg branches) or with staging tables, this makes "never expose unvalidated data" a structural guarantee rather than a hope — the data-engineering version of a deploy gate.

10.2

Orchestration: idempotency & backfills

An orchestrator (Airflow, Dagster, native schedulers) runs your pipeline as a DAG of tasks with dependencies, schedules, and retries. Two concepts dominate the interview because they're what separates a pipeline that recovers from one that corrupts: idempotency and backfills. Fail a day and watch what happens:

Figure 8.2. A daily pipeline where one day fails. Downstream days block until it's fixed; a backfill re-runs the failed day (and dependents), and because the tasks are idempotent, the rerun overwrites cleanly instead of double-writing. Illustrative.

Idempotency means running a task twice produces the same result as running it once — the single most important property in orchestration, because tasks will be retried after failures and backfilled after fixes. You achieve it by making writes overwrite a specific partition rather than append (write date=2026-08-30 by replacing that partition, so a rerun replaces it again rather than duplicating), or by upserting on a key. The anti-pattern interviewers listen for is a blind INSERT/append: rerun it and you've double-counted. "I partition by date and overwrite the partition, so retries and backfills are safe" is the answer that signals production scars.

Backfilling is re-running the pipeline for past dates — to fix a bug, load history, or recover from an outage. It only works cleanly because the tasks are idempotent (rerun overwrites) and parameterized by the run date rather than hardcoded to "today" — a task that reads {{ ds }} / an execution-date parameter can be pointed at any historical date. The two ideas are joined at the hip: idempotency is what makes backfills safe, and date-parameterization is what makes them possible.

Interview angle

"How do you make a pipeline safe to retry and backfill?" Give the pair: idempotent writes (overwrite the target partition or upsert on a key, never blind-append) and date-parameterized tasks (the run reads its logical date, so it can be re-pointed at any day). Then close with the failure story: "a retried or backfilled run replaces its partition, so there's no double-counting — the run is a pure function of its date." That framing is catnip to a platform interviewer.

10.3

MLOps: the pipeline past the model

For a data engineer, MLOps is mostly data engineering with a model on the end — the questions are about the pipeline and its failure modes, not the math. The lifecycle to know: features → training → a registry → serving → monitoring, with feedback loops. Your job is the plumbing that makes it reproducible and reliable.

The concept interviewers reach for first is training-serving skew: the model was trained on features computed one way (a batch Spark job over historical data) but served features computed another way (a real-time path), and the mismatch quietly wrecks accuracy in production. The canonical fix is a feature store — compute each feature once and serve the same values to both training (offline) and serving (online), guaranteeing consistency. Being able to name skew and give the feature-store fix is a strong signal in any ML-adjacent DE interview.

The rest assembles quickly. A model registry versions trained models with their metadata and stage (staging/production), so you know exactly what's deployed and can roll back. Drift monitoring watches whether the live input distribution or the model's accuracy has moved away from training — because a model silently degrades as the world changes, and nothing errors; you have to look. And deployment safety mirrors software: champion/challenger or canary rollouts (send a slice of traffic to the new model, compare, promote if better) so a bad model doesn't take down predictions for everyone.

Interview angle

"What's training-serving skew and how do you prevent it?" — it's when features differ between training and inference (different code paths or timing), degrading the model in production without any error surfacing. Prevent it with a feature store that computes features once and serves identical values to both sides. Bonus: "and I'd monitor input drift, because even with no skew, the world shifts and accuracy decays silently."

10.4

Observability and the 2 a.m. page

The final round is often behavioral-technical: "tell me about a pipeline incident." What they're testing is whether you think about data in production — not just does the job run, but is the data right, fresh, and trustworthy. The vocabulary is data observability, usually framed as a few pillars: freshness (is the data up to date?), volume (did row counts suddenly drop or spike?), schema (did a column change or disappear?), quality/distribution (are values in range, null rates stable?), and lineage (if this table is wrong, what upstream caused it and what downstream is affected?).

The senior instinct is that a job succeeding is not the same as the data being correct — the pipeline can run green while an upstream source silently sends half the rows. So you alert on the data, not just the task status: a freshness SLA breach, an anomalous row count, a null-rate spike, a schema change. And you frame incidents by blast radius — lineage tells you every downstream table and dashboard affected, so you can communicate impact and prioritize the fix.

When you tell the incident story, use the arc that shows maturity: detect (an alert fired — ideally a data-quality alert, not an angry Slack from an analyst), diagnose (lineage traced it to an upstream schema change), mitigate (roll back with time travel / re-run the last good partition), fix and prevent (add the schema-contract test that would have caught it in CI). Detect → diagnose → mitigate → prevent, with a prevention that closes the loop, is the answer that ends the interview well — and it's the same "what happens when it breaks" thread from §0.1, all the way through.

Contents · Chapter 11 · The system design round

11.1

The round that decides senior offers

Candidates report the same thing across companies: the pipeline design round is the filter. SQL and coding rounds are the ones people over-prepare and they carry the least weight above mid-level, because correctness there is table stakes. The design round is open-ended, and open-endedness is exactly what reveals whether you've operated systems or only built them.

It is not a backend system design interview with bigger numbers. The failure modes are different: you're optimizing for throughput, correctness, and analytical access patterns rather than request latency and transactional APIs, and your system has to serve analysts, ML engineers, and compliance simultaneously. Nobody asks you to design Twitter. They ask you to design something their company already runs.

The most consistently reported rejection reason is worth stating bluntly: naming tools instead of reasoning about the problem. A candidate who opens with "I'd use Kafka, Spark, and Snowflake" has answered nothing. A candidate who opens with "before I pick anything — what's the volume, how fresh does this need to be, and who's consuming it?" has already passed the first hidden checkpoint.

Use the whole hour deliberately. The rough budget below is what strong candidates converge on, and following it visibly ("let me spend a few minutes on requirements first") is itself a signal of having done this before.

Interview angle

The interviewer is a collaborator, not an examiner. Say your assumptions out loud and invite correction — "I'm assuming we don't need sub-second freshness; tell me if that's wrong." Rigidity when they change a requirement is a documented fail; adapting the design cleanly is a documented pass.

11.2

Requirements and the envelope

Start by pinning down what the system must do and what it must be. Functional: what questions does this data answer, and who asks them? Non-functional: volume, freshness SLA, query latency, retention, correctness guarantee, and budget. The freshness number does more architectural work than anything else in the room — "the dashboard is read each morning" and "the pricing engine reads this in under a second" are two entirely different systems.

Then do the arithmetic out loud. Back-of-envelope numbers anchor every later decision and take ninety seconds: events per day × bytes per event = daily volume; divide by 86,400 for average events per second; multiply by 5–10× for peak. A design that has been sized is a design you can defend, and it prevents the classic mistake of proposing a streaming architecture for 40 MB a day or a single Postgres box for 4 TB.

Two conversions worth memorizing so you can do this without a calculator: 1 KB × 1 million = 1 GB, and anything per-day divided by ~100,000 gives you roughly the per-second rate (86,400 seconds, rounded for arithmetic you can do in your head). Round aggressively — nobody is checking your multiplication, they're checking whether you reason in orders of magnitude.

Interview angle

State the peak-to-average ratio explicitly. Systems are sized for peak, not average, and candidates who design for the daily total quietly build something that falls over every evening at 8 p.m. Saying "5× peak, so I'll size the ingestion for 60k events/sec" is a small sentence that reads as production experience.

11.3

The reference architecture

Almost every data design answer decomposes into four layers, and having this spine means you're never blank in front of a whiteboard: ingest → process → store → serve, with orchestration and observability running across all of them.

Ingest. How does data arrive? Event streams land in a log (Kafka/Kinesis/Pub-Sub) that buffers, decouples producers from consumers, and lets you replay. Databases arrive by CDC or batch extract (§3.2). Third-party APIs arrive by scheduled pull, and you should mention rate limits and pagination if that's the source.

Process. Batch (Spark on a schedule) or stream (Flink / Structured Streaming), decided by the freshness requirement, not by taste. Say where transformation happens and what the layering is — raw landing, cleaned, aggregated (§3.1).

Store. Object storage in an open columnar format (Parquet) under a table format (Iceberg/Delta) is the default lake answer; a warehouse (Snowflake/BigQuery/Redshift) is the default analytics answer; the two increasingly converge. Name your partitioning scheme and the small-files problem — thousands of tiny files from a streaming writer will destroy read performance until compaction runs, and interviewers love that detail because only people who've hit it mention it.

Serve. BI tools query the warehouse; low-latency application reads need a serving store (a key-value store, or an OLAP engine like Druid/Pinot/ClickHouse for sub-second aggregations); ML needs a feature store to keep training and serving consistent (§10.3).

Then the cross-cutting layer, which is where seniority shows: orchestration with retries and backfills (§10.2), data quality checks at the boundaries, lineage so you can answer "what breaks if this changes," and access control and PII handling — regulated environments will ask, and volunteering it scores.

Lambda, Kappa, and not over-reaching

If you propose both a streaming path and a batch path over the same data (a lambda architecture), name the cost: two implementations of the same business logic that drift apart. The modern preference is one path — stream with replay from the log, or batch with a short interval — and reaching for two only when the requirements genuinely demand it.

Interview angle

Draw the four boxes before you name a single technology. Then fill them in, justifying each with a requirement you elicited. This ordering — problem first, tools second — is the single most-cited difference between the candidates who pass this round and the ones who don't.

11.4

Worked example: clickstream analytics

"Design a pipeline that ingests click events and powers both a real-time dashboard and next-day reporting" is the most frequently reported prompt in the round. The Indian variants are the same problem in local clothing — live cricket-match concurrency at a streaming platform, UPI payment events at a fintech, a Big Billion Days order spike at a marketplace — same shape, with an even harsher peak-to-average ratio. Here's the shape of a strong answer.

Requirements. 2 billion events/day, ~1 KB each — so ~2 TB/day, ~23k events/sec average, call it 100k+ at peak. The dashboard needs freshness in seconds; the reporting tables need to be correct and stable, not fast. Retention: 90 days hot, years cold. That split — one consumer wants fast, one wants right — is the actual design problem, and naming it early is most of the answer.

Ingest. Producers write to a partitioned Kafka topic keyed by user id so per-user ordering holds (§8.1). The log is the buffer and the replay mechanism; everything downstream is a consumer of it.

Two consumers, one source of truth. A stream job maintains windowed aggregates for the dashboard — approximate is acceptable, latency is not — writing to an OLAP store. Separately, a sink writes raw events to the lake as Parquet partitioned by event date, and a batch job builds the corrected, deduplicated tables with a rolling reprocessing window for late arrivals (§3.5). The dashboard's numbers and the report's numbers will differ slightly during the day; say that out loud and explain that the batch table is the system of record.

The deep dives they'll push on. Duplicates: producers retry, so dedupe on a client-generated event id — MERGE in batch, keyed state with TTL in the stream (§8.3). Late events: watermark with bounded lateness for the stream, a rolling window for batch. Small files: the streaming writer produces many tiny files, so run compaction. Backfill: replay from the log if within retention, otherwise re-derive from the raw lake partitions — which is why you wrote them. Schema evolution: a registry with compatibility rules at the producer boundary (§3.4). Cost: partition pruning, tiering cold data to cheaper storage, and not sizing the stream cluster for peak when the log can absorb bursts.

Interview angle

Close by naming what you'd monitor and what you'd do differently at 10× scale. "I'd alert on consumer lag, freshness, and daily row-count deviation; at 10× I'd revisit partition count and consider pre-aggregating at ingest." Ending on operations rather than architecture is what a senior answer sounds like.

Contents · Chapter 12 · Behavioral & ownership

12.1

The round that loses offers

The behavioral round rarely wins you the offer. It very often loses it — and data engineers under-prepare for it more than any other discipline, because it feels unrelated to the craft. It isn't. Data engineering is the job where you find out something is wrong before the business does, argue with the team that broke it, and decide at 2 a.m. whether to hold the release or ship known-bad numbers. Those are behavioral questions with technical content.

How it's weighted varies, and knowing the variation is worth real points. Amazon evaluates Leadership Principles in every round, not just one, and adds a Bar Raiser from outside the team with veto power — expect them to drill into a single story's details until you either have them or don't. Netflix runs a dedicated culture conversation about judgment and independence rather than STAR trivia. Google screens "Googleyness," where the fatal patterns are bluster about knowledge you lack and rigidity under pushback. Meta blends it in: there's often no standalone behavioral round because ownership and product sense are assessed inside the technical ones.

The universal failure is the vague story. "We had data quality issues, so we improved our monitoring" is not an answer — it has no stakes, no decision, and no number. The fix isn't charisma; it's preparation. Write the stories down before the loop, out loud, timed.

Interview angle

Ask your recruiter what the behavioral round evaluates against. At most large companies the rubric is public (LPs, Googleyness, the Netflix memo), and mapping your stories to their named values before the call is free signal that costs an hour.

12.2

Story architecture

Use STAR — Situation, Task, Action, Result — but the version that works for engineers adds two things most candidates omit.

Scale and numbers. "A pipeline was slow" is a shrug. "A nightly job processing 4 TB had crept from 40 minutes to 6 hours and was breaking the 7 a.m. SLA" is a situation an interviewer can picture. Every story should carry at least one number in the situation and one in the result — rows, terabytes, hours, dollars, percentage, incident count.

The decision and its alternative. The interesting part is never what you did; it's what you chose not to do and why. "I could have thrown a bigger cluster at it, which would have cost about 3× and hidden the real problem — the skewed join — so I salted the key instead and cut runtime to 25 minutes." That sentence demonstrates ownership, cost awareness, and technical depth in one breath.

Keep each story to about two minutes spoken, with detail held in reserve for follow-ups rather than front-loaded. Close with a reflection when it fits — what you'd do differently, or what you changed permanently as a result ("we added a row-count reconciliation check that has caught two silent failures since"). A story that ends in a durable improvement reads as an engineer who compounds.

On the mistake question

"Tell me about a time you were wrong" is asked in almost every senior loop, and the failure mode is the disguised humblebrag ("I care too much about quality"). Pick a real mistake with real consequences, own it without hedging, and spend most of the answer on the correction and the systemic fix. Interviewers are calibrating whether you can be told you're wrong — which matters more in a data role than in most.

Interview angle

Interviewers dig. Expect three layers of "why did you do it that way?" on the same story, so pick examples you actually lived. Borrowed or embellished stories collapse at the second follow-up, and that collapse is the thing a Bar Raiser is specifically listening for.

12.3

The six stories every DE loop asks for

Across reported loops the same six themes recur, in different wording. Prepare one strong story for each and you can answer roughly ninety percent of what's asked, since most questions are these in disguise.

1 · The incident. Something broke in production and you owned it. Detection, blast radius, the call you made under pressure, the fix, the prevention. This is the single most-asked DE behavioral story.

2 · The data quality catch. You found numbers that were wrong — ideally before the business did — traced the root cause, and closed the hole. Reconciliation, a failed check, an alert you'd built.

3 · The disagreement. You pushed back on a stakeholder or another engineer. The point isn't winning; it's showing you can disagree with data, escalate appropriately, and commit to the decision even when it goes against you.

4 · Ambiguity. The requirements were unclear or contradictory and you made progress anyway — narrowed scope, shipped something small, validated, iterated.

5 · Influence without authority. You got another team to change something — a schema, a contract, a producer behavior — with no power to compel them. Data engineers live downstream of everyone, so this comes up constantly.

6 · The trade-off you'd defend. You chose speed over elegance, or correctness over deadline, and can articulate the cost you accepted and why it was right for that moment.

Below, map your own stories against the themes and the follow-ups each one attracts. Gaps in the grid are exactly where a loop will find you.

Interview angle

One story can serve several themes if you tell it from different angles — the same incident can be ownership, disagreement, or the mistake, depending on which part you foreground. But don't reuse one story for a whole loop; interviewers compare notes afterward, and a candidate with a single anecdote reads as thin.

12.4

Your questions, and the offer

The questions you ask are scored, whether or not anyone says so. Generic ones ("what's the culture like?") are neutral at best. Questions that reveal you think like an owner do real work: What breaks most often, and who gets paged? How do you know when a dataset is wrong — is there a check, or does someone notice? What's the split between building new pipelines and maintaining old ones? What does the data platform look like a year from now?

They also serve you. A team that can't answer "how do you know when data is wrong" is a team where you'll spend your first year firefighting, and the interview is the last moment you can find that out cheaply.

On compensation, the Indian market has one dominant lever: company type. Product companies pay two to three times what services pay at the same experience, which means moving tiers moves comp more than any promotion or negotiation inside a tier. The 2026 shape, roughly: senior DEs in the broad market land ₹28–42L; product companies and GCCs run ₹18–45L; fintech pays a streaming premium; and FAANG-India plus the top product companies reach ₹45–80L+ on RSU-heavy packages. Decompose every offer into fixed, variable, and ESOP — CTC is a marketing number, and unlisted-company ESOPs deserve a hard discount. Full bands are on the company playbooks page.

Three practical notes. Give a range only after you understand the level, since the same title spans very different bands. Treat the notice period as part of the deal — 60–90 days is standard in India, buyouts exist, and asking for a joining bonus that covers the buyout is normal, not cheeky. And when you get the offer, the highest-leverage question isn't about money — it's which team you're joining, because the difference between a mature platform and a firefighting one determines what your next two years look like.

Interview angle

Have two questions ready that only apply to that company — something from their engineering blog, their open-source stack, or a scale problem their product implies. It's the cheapest possible demonstration that you prepared, and interviewers remember it when they write feedback.

Contents · Company playbooks

Company playbooks

The Indian DE market splits into four tiers — FAANG-India, product companies and unicorns, GCCs, and services — and the loops differ more than the syllabus does. Same five domains everywhere: SQL, coding, modeling, pipeline design, behavioral. What changes is the weighting, the stack vocabulary (AWS at product companies, Azure at GCCs), and which round quietly does the filtering. Pick a company to see its shape, its distinctive round, and the trap candidates report falling into.

What every loop shares

Reported structure is remarkably consistent: 5–7 rounds over 3–6 weeks, built from the same five domains.

Stage	Typically	What's really tested
Recruiter screen	15–30 min	Level, logistics, comp range — plus, at good companies, a preview of the loop
Technical screen	45–60 min	SQL and Python data manipulation under time pressure
Take-home (~1 in 4)	2–8 hrs	Code you'd defend in review: structure, tests, README
Onsite: SQL	45–60 min	Windows, dedup, gaps-and-islands — and explaining why your version is cheaper
Onsite: modeling	45–60 min	Grain, star schema, SCDs. Frequently called the make-or-break round
Onsite: pipeline design	60 min	The senior filter: requirements, trade-offs, failure modes
Onsite: behavioral	45 min	Ownership, incidents, disagreement — where offers are lost

What gets people rejected

The reported rejection reasons repeat across companies, and almost none of them are "didn't know the syntax."

Pattern	Sounds like	Say instead
Naming tools, not solving problems	"I'd use Kafka, Spark and Snowflake"	"What's the volume and freshness requirement? That decides batch vs stream."
No failure modes	A design with only the happy path	Backfill, late data, schema change, partial failure, cost
Ignoring idempotency	"The orchestrator retries it"	"I overwrite the partition, so a rerun converges"
Rigidity under pushback	Defending v1 after the requirement changes	"That changes things — I'd move the aggregation upstream"
Vague behavioral stories	"We improved our monitoring"	A number, a decision, an alternative rejected
Bluffing a stack	"I know all the major services"	"I've run BigQuery and Dataflow in production; Bigtable I've only read about"
Tickets, not ownership	"I worked on the ingestion module"	"The pipeline I owned end-to-end — and the incident I took at 2 a.m."

Compensation in India, roughly

Total compensation (CTC), INR lakhs per annum, 2026 public aggregators. Orientation, not precision — decompose any offer into fixed / variable / ESOP before comparing.

Segment	Band (₹ LPA)	Note
Entry (0–2 yrs), broad market	7–14	Top of band is product-company offers; services anchor the bottom
Mid (3–5 yrs), broad market	16–26	The fastest-doubling stretch — this is when to move
Senior (6+ yrs), broad market	28–42	Product employer + a premium skill takes the top quartile
Product companies & GCCs	18–45	Walmart GT, Target, bank GCCs — Spark + cloud + design loops
Fintech (PhonePe, Razorpay, CRED)	16–40	Streaming-heavy stacks pay a 20–35% specialist premium
FAANG-India & top product, senior	45–80+	RSU/ESOP-heavy; Flipkart DE median ≈ ₹28L with senior packages far above
IT services data practices	5–14	Volume entry point; product pays 2–3× at the same experience

Four things the band tables never say. Company type is the dominant lever — moving from services to product moves comp more than two promotions. CTC is not in-hand: ask for the fixed/variable/ESOP split, and discount unlisted-company ESOPs hard. Bengaluru anchors the bands (Hyderabad and Pune close behind); remote roles benchmark to the employer, not your city. And the notice period is part of the negotiation — 60–90 days is standard, buyouts exist, and a joining bonus that covers the buyout is a normal ask. Verify against a live aggregator before you quote a number at anyone.

Contents · Question bank

Question bank

The questions themselves, grouped by chapter, each with a model answer you can rehearse out loud. The strong version of every answer leads with the trade-off and names the failure mode — not just the term.

SQL

What's the difference between WHERE and HAVING?SQL

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. So you can't reference COUNT(*) in WHERE (it doesn't exist yet), and you should push any row-level condition into WHERE so there are fewer rows to aggregate. It falls straight out of the logical execution order (§1.4).

Find the second-highest salary.SQL

Use DENSE_RANK() OVER (ORDER BY salary DESC) and filter for rank = 2. It must be DENSE_RANK, not RANK or ROW_NUMBER: if the top salary is tied, RANK skips 2 entirely and ROW_NUMBER would call one of the tied top earners "second." Naming why the other two fail is the point of the question.

A LEFT JOIN returned fewer rows than the left table. Why?SQL

Almost certainly a WHERE condition on the right table, which turns the outer join into an inner one by discarding the NULL-padded non-matches. If you want to keep all left rows, the right-table condition belongs in the ON clause, not WHERE.

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)?SQL

COUNT(*) counts rows. COUNT(col) counts rows where col is not NULL. COUNT(DISTINCT col) counts distinct non-NULL values. The NULL-skipping of COUNT(col) is the trap — it silently under-counts if the column has NULLs.

Why can NOT IN return no rows unexpectedly?SQL

Because NULL breaks it: if the subquery inside NOT IN returns even one NULL, the whole predicate evaluates to NULL (never true) and you get an empty result. NOT EXISTS handles NULLs correctly and is the safer default. This is one of the most common silent-bug questions.

ROW_NUMBER vs RANK vs DENSE_RANK?SQL · windows

On a tie: ROW_NUMBER gives distinct numbers arbitrarily (1,2,3,4); RANK ties and skips (1,2,2,4); DENSE_RANK ties without skipping (1,2,2,3). Pick by intent — dedup uses ROW_NUMBER, "Nth distinct value" uses DENSE_RANK, competition-style ranking uses RANK.

SQL patterns

Longest streak of consecutive login days per user.gaps & islands

The pattern with no keyword: gaps and islands. Dedupe to the real grain first (one row per user-day), number the rows, subtract the row number from the date — consecutive days yield a constant, and that constant is the streak id.

WITH days AS (
  SELECT DISTINCT user_id, CAST(login_at AS DATE) AS d
  FROM logins
), numbered AS (
  SELECT user_id, d,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) AS rn
  FROM days
)
SELECT user_id, MIN(d) AS start, MAX(d) AS finish, COUNT(*) AS len
FROM numbered
GROUP BY user_id, d - rn * INTERVAL '1 day'
ORDER BY len DESC;

Why it works: within a run, the date and the row number advance in lockstep, so their difference is constant; every gap breaks the constant. Senior touches: dedupe first (multiple logins per day wreck the trick), and ask whether ties at the max length should all be returned.

Top 3 products by revenue per category.top-N per group

Rank inside the partition, filter outside — a window result can only be filtered in an outer query (or with QUALIFY where supported).

WITH ranked AS (
  SELECT category, product_id, SUM(revenue) AS rev,
         DENSE_RANK() OVER (PARTITION BY category
                            ORDER BY SUM(revenue) DESC) AS rnk
  FROM sales GROUP BY category, product_id
)
SELECT * FROM ranked WHERE rnk <= 3;

The follow-up is always the ranking-function choice: ROW_NUMBER gives exactly N rows and breaks ties arbitrarily; RANK skips positions after ties; DENSE_RANK does not. Say which the business wants — "top 3 including ties" is DENSE_RANK — and you have answered the real question.

7-day rolling revenue per marketplace.window frames

SELECT marketplace_id, dt, revenue,
       SUM(revenue) OVER (PARTITION BY marketplace_id ORDER BY dt
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rev_7d
FROM daily_revenue;

The trap is the frame: ROWS counts rows, not days. If dates can be missing, six preceding rows might span three weeks. The senior move is to say so unprompted and densify first — generate a date spine (calendar table or recursive CTE), left-join revenue onto it with COALESCE(revenue, 0), then apply the frame. Mentioning that RANGE frames on dates are engine-inconsistent is a bonus.

D1/D7 retention by signup cohort.retention

SELECT u.signup_date,
       COUNT(DISTINCT u.user_id) AS cohort,
       COUNT(DISTINCT CASE WHEN e.d = u.signup_date + 1
             THEN u.user_id END) * 1.0 / COUNT(DISTINCT u.user_id) AS d1,
       COUNT(DISTINCT CASE WHEN e.d = u.signup_date + 7
             THEN u.user_id END) * 1.0 / COUNT(DISTINCT u.user_id) AS d7
FROM users u
LEFT JOIN (SELECT DISTINCT user_id, CAST(event_ts AS DATE) AS d
           FROM events) e ON e.user_id = u.user_id
GROUP BY u.signup_date;

Three things they listen for: LEFT JOIN so zero-retention cohorts still appear, DISTINCT on both sides so multi-event users count once, and a clarifying question about the definition — "returned on exactly day 1, or within 1 day?" — because that changes the predicate from = to BETWEEN.

Delete duplicate rows, keep one per email.dedup

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);
-- or, warehouse-style, rebuild instead of delete:
CREATE OR REPLACE TABLE users AS
SELECT * FROM users
QUALIFY ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) = 1;

The interview is in the follow-ups: which row to keep is a business rule (lowest id? latest updated_at?), a warehouse rebuild is safer and faster than a row-by-row DELETE at scale, and preventing recurrence — a unique constraint or a MERGE-based load (§3.3) — is the answer that upgrades you from query-writer to pipeline owner.

Data modeling

What is the "grain" of a table and why declare it first?modeling

The grain is what one row means — "one row per order line," "one per user per day." Declaring it first is the highest-leverage modeling habit because keys, aggregations, and joins all follow from it, and mixing grains in one table is how double-counting bugs are born.

A customer changes their address. How do you store it?modeling · SCD

An SCD question. If analytics needs point-in-time correctness (what region was this customer in when they bought), use Type 2: close the old row, insert a new version with valid_from/valid_to, and join facts to the version valid at event time. If only the current value matters, Type 1 (overwrite) is fine and cheaper. Tie the choice to a query requirement.

Star schema vs one big (denormalized) table?modeling

Star keeps dimensions in one place — a rename is a one-row update, storage is lean — but every query joins. OBT pre-joins everything for zero-join, analyst-friendly reads, at the cost of storage and expensive restatements when a dimension changes. Real answer: star as the maintainable core, materialized wide tables on the hot query paths. Layers, not either/or.

Surrogate key vs natural key — why do dimensional models use surrogates?modeling · keys

A natural key comes from the business (email, order no.); a surrogate is a meaningless integer/hash the warehouse assigns. Dimensional models need surrogates because SCD Type 2 maps one natural customer to many versioned rows, so facts must reference the surrogate of the specific version current at event time — plus surrogates insulate you from messy/changing source keys and keep joins fast integer comparisons.

What is a conformed dimension?modeling

A dimension shared consistently across multiple fact tables — the same dim_date/dim_customer used by orders, returns, and tickets — so metrics line up across the business and can be compared on the same axes. Conformed dimensions are what make a warehouse coherent instead of a pile of unrelated tables.

How do you handle late-arriving data?modeling

Late-arriving dimension: insert a placeholder ("inferred member") so the fact isn't dropped, then enrich it when the real record lands. Late-arriving fact: under SCD Type 2, join it to the dimension version that was current back then, not now — which works only because Type 2 kept history. Both say the same thing: model time as a first-class citizen.

Big data fundamentals

What is a shuffle and why is it expensive?big data

A shuffle physically moves records across the network so all rows with the same key land on one machine — needed by GROUP BY, JOIN, distinct, ORDER BY. It's expensive on every axis: it writes to local disk (spill), serializes records, sends them over the network, then reads and merges. The whole performance game is: shuffle less, and shuffle evenly.

Why is reduceByKey better than groupByKey?big data

groupByKey shuffles every record to the reducer and aggregates there; reduceByKey combines values map-side first, so only one partial aggregate per key per partition crosses the wire. Same result, a fraction of the network traffic — the map-side combine is the whole difference.

Narrow vs wide transformations?big data · Spark

Narrow: each output partition depends on one input partition (map, filter) — no data moves, and chains fuse into one stage. Wide: each output partition depends on many inputs (groupBy, join, distinct) — a shuffle happens and a new stage begins. It's the vocabulary for the entire Spark performance conversation.

What does "exactly-once" really require?big data · streaming

Not magic — checkpoint the source offsets together with the output atomically, and make the sink idempotent (upsert on a business key) or transactional. Then a replay after failure re-processes the same offsets, produces the same keys, and the idempotent write collapses the duplicate. True end-to-end exactly-once needs the sink's cooperation; otherwise you engineer effectively-once with dedup keys.

Event time vs processing time?big data · streaming

Event time is when the event happened; processing time is when your system saw it. They differ because of network delays and retries, and correct aggregations key off event time. Handling the gap needs windows (finite aggregations over an unbounded stream) and watermarks (how long to wait for late data before finalizing).

Spark engine

Driver vs executor?Spark

The driver runs your main, builds the DAG, and schedules tasks — it coordinates but doesn't process data, though it collects results (so collect() on a huge dataset OOMs it). Executors are JVMs on worker nodes with cores and memory that actually run tasks and cache data. Cores = parallel task slots; a partition is processed by one task.

Transformation vs action?Spark

Transformations are lazy — they extend the DAG and return another DataFrame (map, filter, join). Actions trigger execution and return a value or write output (count, collect, write, show). The consequence worth volunteering: every action re-runs the full lineage, so multiple actions re-scan the source unless you cache.

Explain jobs, stages, and tasks.Spark

One job per action. Each job splits into stages at every shuffle boundary (a stage is a run of narrow transformations). Each stage runs as tasks, one per partition, in parallel across cores. So: actions→jobs, shuffles→stage boundaries, partitions→tasks. Reducing shuffles reduces stages.

What operations cause a shuffle?Spark

The wide transformations: groupBy/groupByKey, join (unless broadcast), distinct, repartition, reduceByKey, and sorting/orderBy. Anything that needs records grouped or ordered by key across the whole dataset. Narrow ops (map, filter, select, union) don't.

A Spark job OOMs. How do you reason about it?Spark · memory

First ask which side. Executor OOM: a partition too big, a giant groupByKey, or skew — fix with more/smaller/even partitions or skew handling. Driver OOM: collect() pulling a large result back, or a broadcast too big to build — fix by not collecting or lowering the broadcast threshold. Naming the side is the difference between diagnosing and guessing.

What is Adaptive Query Execution (AQE)?Spark

Runtime re-planning (default since 3.2): at each shuffle boundary Spark reads the actual statistics and adjusts the rest of the plan. It coalesces the default 200 shuffle partitions to a sensible number, switches a sort-merge join to a broadcast when a side turns out small, and splits skewed partitions. It makes the common cases self-correcting so you focus on structural choices it can't make.

Spark performance

repartition vs coalesce?Spark · perf

repartition(n) does a full shuffle to exactly n even partitions and can increase or decrease count. coalesce(n) merges partitions without a full shuffle, only decreases, and may leave them uneven. The tell: "after a heavy filter that leaves 500 sparse partitions I coalesce; to fix skew or go wider I repartition."

When would you use a broadcast join, and when does it backfire?Spark · perf

Use it when one side is small (a dimension/lookup, under ~10MB by default): Spark ships a copy to every executor and skips shuffling the big table entirely — the biggest single join win. It backfires when the "small" table isn't: broadcasting something too large OOMs the driver building it or the executors holding it. Broadcast is for genuinely small tables only.

One task runs for an hour while the rest finish in seconds. Fix it.Spark · perf

Data skew — one key's rows all hash to one partition/task after the shuffle. Ladder of fixes: enable AQE skew-join handling (splits the hot partition automatically), salt the hot key (append a random suffix so one key spreads across many tasks), or broadcast the other side to skip the shuffle. A NULL join key is a frequent hidden culprit because all NULLs hash together.

Cache vs checkpoint?Spark · perf

Caching keeps the data and the lineage (a lost block is recomputed from the DAG) — it's for speed, on DataFrames you reuse. Checkpointing writes to reliable storage and truncates the lineage — for very long iterative jobs where the DAG grows unwieldy or you want to survive executor loss without recomputation. Most tuning is caching.

What's wrong with spark.sql.shuffle.partitions = 200?Spark · perf

It's a fixed default regardless of data size. On 2TB of shuffle that's 10GB per task (spill/OOM); on 200MB it's a thousand trivial tasks (pure overhead). Set it so each task handles ~128–200MB, or rely on AQE to coalesce shuffle partitions automatically.

Why are Python UDFs slow in PySpark?Spark · perf

A plain Python UDF serializes each row out of the JVM to a Python worker, runs your function, and serializes back — row by row — and it's a black box to Catalyst, so no pushdown crosses it. Prefer built-in Spark SQL functions (run in the JVM, optimize), then vectorized pandas/Arrow UDFs (columnar batches, often 10× faster), and plain Python UDFs only as a last resort.

PySpark coding round

Keep only the latest record per key.dedupe

from pyspark.sql import Window, functions as F

w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())
latest = (df.withColumn("rn", F.row_number().over(w))
            .filter("rn = 1").drop("rn"))

The probe: why not dropDuplicates(["order_id"])? Because it keeps an arbitrary row, not the latest — it has no ordering. Saying that one sentence is usually the pass/fail moment of the question. This window is also the merge-dedupe from §3.3 in DataFrame clothing.

Flatten nested JSON into columns.semi-structured

df = spark.read.option("mode", "PERMISSIVE") \
          .option("columnNameOfCorruptRecord", "_bad") \
          .json("s3://raw/orders/")

flat = (df
  .select("order_id",
          F.col("customer.id").alias("customer_id"),
          F.col("customer.city").alias("city"),
          F.explode_outer("items").alias("item"))
  .select("*", F.col("item.sku").alias("sku"),
               F.col("item.qty").alias("qty"))
  .drop("item"))

Three senior touches: dot-paths for structs but explode for arrays (and explode_outer so parents with empty arrays survive); a corrupt-record column instead of letting bad rows vanish; and stating that explode multiplies row count — the fan-out matters downstream. This is Flipkart's machine-coding round almost verbatim.

Sessionize a clickstream: new session after 30 idle minutes.sessions

w = Window.partitionBy("user_id").orderBy("ts")
sess = (df
  .withColumn("prev", F.lag("ts").over(w))
  .withColumn("new_s", (F.col("ts").cast("long")
      - F.col("prev").cast("long") > 1800).cast("int"))
  .fillna({"new_s": 1})
  .withColumn("session_id",
      F.sum("new_s").over(w.rowsBetween(Window.unboundedPreceding, 0))))

Same island trick as SQL: flag the boundaries with lag, running-sum the flags into ids. Name the assumption (gap threshold is a product decision) and the streaming version (§8.2 session windows do this with watermarks) to show you know where this pattern lives in production.

Word count — why do they still ask it?fundamentals

counts = (spark.read.text("s3://logs/")
  .select(F.explode(F.split("value", "\\s+")).alias("w"))
  .filter("w != ''")
  .groupBy("w").count()
  .orderBy(F.desc("count")))

Because it is the smallest program that contains a shuffle. The real question is the follow-up: which step is the wide transformation (groupBy), what crosses the network (partial counts, thanks to map-side combine), and how many output partitions there are (spark.sql.shuffle.partitions). Answer the plumbing, not just the code.

Spark, under stress

Executor OOM at 2 a.m. — walk me through the diagnosis.debugging

Ranked by base rate: skew — one task's partition is 100× the median (Spark UI, sorted task table, max vs median); fan-out — a join or explode multiplied rows mid-stage; too few partitions — data grew but the partition count did not, so each task now exceeds memory; driver-side — a collect() or an oversized broadcast. The order of inspection: Spark UI stage view first, then the query plan, then configs — touching spark.executor.memory before looking at the UI is the junior tell, because doubling memory on a skewed key just moves the OOM an hour later.

Execution vs storage memory — why does it matter?memory model

Unified memory: one pool shared between execution (shuffle, sort, join hash tables) and storage (cache). They borrow from each other, but execution can evict cached blocks while cache cannot evict running execution. Practical consequences: an over-cached application starves its own joins and spills; "memory used" in the UI conflates the two; and the fix for a spilling job is often to uncache something, not to add memory. That one sentence separates people who read the docs from people who fought the thing.

What does AQE actually do at runtime?AQE

Three re-plans using real statistics at stage boundaries (§5.5): coalesces tiny post-shuffle partitions to a sensible count, switches join strategy to broadcast when a side turns out small, and splits skewed partitions in sort-merge joins. What it does not fix: a bad initial partition count on read, an exploding UDF, under-provisioned executors, or skew in aggregations. So the senior answer to "do I still need to tune?" is yes — AQE fixes the plan mid-flight, not the physics.

How do you size executors for a job?tuning

Start from the classic heuristic: ~4–5 cores per executor (more and HDFS/S3 client throughput degrades, fewer and JVM overhead dominates), memory to match the data per task with ~10% overhead headroom, executor count from cluster capacity minus one core/node for the OS. Then say the senior part: the heuristic is a starting point — the real signal is the Spark UI after one run (spill? idle cores? GC time?), and on modern platforms dynamic allocation plus AQE means you tune the partition count and let the platform breathe. Quoting exact magic numbers with confidence is the junior tell; describing the feedback loop is the senior one.

Snowflake

What are micro-partitions and how does pruning work?Snowflake

Snowflake stores tables as immutable ~50–500MB columnar micro-partitions, each carrying min/max metadata per column. Queries prune by reading that metadata and skipping any partition that can't contain matching rows — automatic, no indexes. How well it prunes depends on clustering (how sorted the data is on your filter columns); a clustering key helps large tables whose load order doesn't match query filters.

Scale up or scale out in Snowflake?Snowflake · cost

Up (bigger warehouse) for a single heavy query that spills/sorts a lot — more memory and CPU. Out (multi-cluster warehouse) for many concurrent users where the problem is queuing, not any one query. Nuance: doubling size doubles both speed and credit rate, so sizing up buys latency, not savings, on a parallel query — size to avoid spilling, use auto-suspend, and lean on the result cache to actually cut spend.

Time Travel vs Fail-safe?Snowflake

Time Travel lets you query or restore a table as of up to N days ago (configurable, up to 90 on higher editions) — undo a bad load, recover a dropped table. Fail-safe is a non-configurable 7-day Snowflake-managed recovery window after Time Travel expires, accessible only via support. The gotcha: Time Travel is self-serve; Fail-safe is not.

How does zero-copy cloning work and why is it instant?Snowflake

CREATE TABLE dev CLONE prod duplicates only metadata pointers to the existing immutable micro-partitions — no data is copied, so it's instant and free at creation. New storage is consumed only as the clone diverges (copy-on-write). It's how you spin up full-size dev/test from production in seconds without paying for a second copy.

What are Streams and Tasks?Snowflake

A Stream is a change-data-capture cursor on a table (what's been inserted/updated/deleted since you last read it); a Task runs SQL on a schedule or dependency. A Task that consumes a Stream and MERGEs the changes downstream builds incremental pipelines entirely inside Snowflake — pair with Snowpipe for continuous file ingestion and you have near-real-time loads with no external orchestrator.

Data integration & cloud

When is a low-code integration tool the right call?integration

When the problem is many standard sources with extract-and-load needs: the vendor maintains the connectors, absorbs upstream API changes, and gives retries and error views for free. It stops being right when transformation logic grows — GUIs version and test badly — so the mature pattern is managed connectors for EL, versioned code (SQL/dbt/Spark) for T. Answering with that split, rather than a tool name, is what reads as senior.

Glue or EMR for a Spark workload?AWS

Glue for serverless, scheduled ETL a small team owns — no clusters to manage, catalog built in, pay per DPU-hour. EMR when you need control: specific Spark versions, custom configs, long-running or massive jobs, spot-instance cost engineering. The trade is operational burden versus control and unit cost at scale. Bonus point: either way the data lives in S3, so you can change the compute decision later.

Athena or Redshift?AWS

Athena is serverless SQL over S3, priced per TB scanned — ideal for ad-hoc queries on the lake, and cheap only if the data is partitioned and columnar; scanning raw unpartitioned JSON is the classic bill shock. Redshift is the provisioned warehouse for concurrent, repeated BI workloads. In Redshift, name distribution keys (collocate the join key so rows don't move between nodes) and sort keys (skip blocks on range filters) — that pair is the standard follow-up.

Kinesis or Kafka (MSK)?AWS · streaming

Same log abstraction: shards are partitions, ordering holds per shard, consumers scale to shard count (§8.1). Kinesis is fully managed and integrates natively with Lambda/Firehose — least ops. MSK is real Kafka when you need the ecosystem: connectors, exact client semantics, cross-cloud portability. If the team already speaks Kafka, MSK; if you want AWS to own it, Kinesis.

Map the AWS stack to Azure and GCP.cloud

Six ideas, three vocabularies. Lake: S3 / ADLS Gen2 / GCS. Warehouse: Redshift / Synapse / BigQuery. Spark: Glue·EMR / Databricks·Synapse Spark / Dataproc. Log: Kinesis·MSK / Event Hubs / Pub-Sub. Orchestrator: MWAA·Step Functions / ADF / Composer. Serverless SQL: Athena / Synapse serverless / BigQuery itself. Answer in problems first, then the vendor's noun — it works in any loop, and it matters in India where product companies run AWS and GCCs run Azure.

Why not do the heavy transform in Lambda?AWS

Hard 15-minute timeout, memory capped, no shuffle — Lambda is event glue (trigger on S3 arrival, route, notify), not a data processor. Proposing it for joins over TBs is a known red flag. The senior answer names the boundary: Lambda triggers the Glue/EMR job; Spark does the work.

DevOps & MLOps

What is idempotency and how do you achieve it in a pipeline?DevOps

Running a task twice produces the same result as once — essential because tasks get retried and backfilled. Achieve it by overwriting a specific partition (write date=X by replacing that partition) or upserting on a key, never a blind append. "I partition by date and overwrite the partition, so retries and backfills are safe" is the answer.

How do you make a pipeline safe to backfill?DevOps

Two joined ideas: idempotent writes (overwrite the target partition or upsert, so a rerun replaces rather than duplicates) and date-parameterized tasks (the run reads its logical date via {{ ds }}, so it can be pointed at any historical day). Idempotency makes backfills safe; date-parameterization makes them possible.

What is training-serving skew and how do you prevent it?MLOps

When features are computed differently for training (a batch job) than for serving (a real-time path), the mismatch silently degrades the model in production with no error. Prevent it with a feature store that computes each feature once and serves identical values to both offline training and online serving. Also monitor input drift, since the world shifts even without skew.

What are the pillars of data observability?DevOps · data quality

Freshness (is it up to date?), volume (did row counts drop/spike?), schema (did a column change?), quality/distribution (values in range, null rates stable?), and lineage (upstream cause, downstream blast radius). The core instinct: a job succeeding isn't the same as the data being correct, so you alert on the data, not just task status.

What is the Write-Audit-Publish pattern?DevOps

Write new data to a staging branch/table, audit it with quality checks, and only publish (atomically swap into production) if the checks pass. On Iceberg branches or with staging tables it makes "never expose unvalidated data" structural rather than hopeful — the data-engineering version of a deploy gate.

ETL & pipeline design

ETL or ELT — which and why?ETL

ELT is the modern default: land raw data first, transform inside the warehouse. It won because columnar compute got cheap and elastic, and keeping raw data means you can re-derive everything when the business logic turns out to be wrong. ETL still wins when the transform needs libraries the warehouse can't run, when PII must be dropped before landing, or when filtering in flight saves real money at the source.

How do you choose between full load, incremental, and CDC?ETL

Profile the source first: size, change rate, whether it has a trustworthy updated_at, whether it hard-deletes, and whether you can read the replication log. Full snapshot is simplest and catches deletes but doesn't scale. Watermark incremental is cheap but blind to hard deletes and fragile if the source doesn't bump its timestamp. CDC captures deletes, barely loads the source, and gives log-order guarantees — at the cost of running connectors and handling schema changes in the stream.

Your incremental load silently drifts from the source. Why?ETL

Two classic causes. Hard deletes: a watermark only sees rows that exist, so deleted rows live in your target forever. And unbumped timestamps: if the source updates a row without touching updated_at, you never pick it up. Fix with periodic full reconciliation of counts and control totals, an overlapping extraction window, or moving to CDC. The meta-answer: green pipelines are not correct pipelines.

What is the bronze/silver/gold (medallion) layering for?ETL

Bronze is raw and immutable (exactly what the source said, partitioned by load date), silver is cleaned/typed/deduplicated, gold is business aggregates. The point isn't tidiness — it's restart points. A transform bug in gold is fixed by rebuilding from silver; bad silver is rebuilt from bronze. The expensive, irreversible step (capturing the source) happens once and everything downstream is a re-runnable function.

How do you handle schema drift from an upstream team?ETL · contracts

Classify the change first: additive (new nullable column) and widening are backward-compatible; renames, drops, and narrowing are breaking. Then place the policy: auto-merge additive changes at the raw layer so nothing is lost, and enforce a data contract at the cleaned-layer boundary so breaking changes fail loudly instead of silently nulling a dashboard. A contract carries schema plus semantics, quality guarantees, freshness SLA, and a named owner.

How do you handle late-arriving data in batch?ETL

A rolling reprocessing window: every run recomputes the last N days (7–14 typical) using merge or partition-overwrite, so recomputation can't duplicate. Pick N by measuring the actual event_time-vs-load_time distribution rather than guessing. Critically, partition by event time, not ingestion time — otherwise a late event lands in today's partition and history changes depending on when you ask.

How would you deduplicate billions of records?ETL

In batch: ROW_NUMBER() OVER (PARTITION BY dedup_key ORDER BY version DESC) and keep rn = 1, where the key is a stable event id and the ordering is a log sequence number or update timestamp. In streaming: keyed state holding seen ids with a TTL, because unbounded dedupe state is what kills long-running jobs. If approximate is acceptable at extreme scale, a Bloom filter trades a small false-positive rate for a fraction of the memory.

How do you know a pipeline is correct, not just green?ETL · quality

Reconcile against the source on a schedule — row counts and summed control totals — and alert on drift. Layer that with boundary checks (nulls, uniqueness, referential integrity, freshness) split into hard-fail versus warn, so a bad load blocks publication rather than reaching a dashboard. Task success only proves the code ran; it says nothing about whether the numbers are right.

Streaming

How does Kafka guarantee ordering?streaming

Only within a partition, never across a topic. Records are routed to partitions by hashing the key, so if you need all events for one entity in order, key by that entity's id. Within a consumer group each partition is read by exactly one consumer, which means parallelism is capped at the partition count — consumers beyond that sit idle.

Consumer lag is climbing. Walk me through it.streaming

Lag is latest produced offset minus committed offset, so it grows for three reasons: more input (traffic spike or an upstream backfill replaying), slower processing (bad deploy, an external call in the hot path, GC pauses), or fewer effective consumers (crash loop, rebalance storm). Fix in that order — scale consumers up to the partition count, make per-record work cheaper, then add partitions, remembering that repartitioning breaks per-key ordering across the change.

What is a watermark?streaming

The system's assertion that it has probably seen all events up to event time T — typically max-event-time-seen minus an allowed lateness. When the watermark passes a window's end, the window fires and its state is freed. It's a pure trade-off: longer lateness catches more stragglers but delays results and holds state; shorter is fresher and leaner but drops more. Don't drop silently — side-output late events and track the count.

How do you keep streaming state bounded?streaming

Every stateful operator needs a retention story or the job dies slowly: checkpoints stretch to minutes, recovery to hours. Windows are cleaned by the watermark; dedupe caches need an explicit TTL; stream-stream joins need a bounded join window on both sides. This is the question that separates people who've run a streaming job for a week from people who've run one for a year.

Flink or Spark Structured Streaming?streaming

Flink is true per-event streaming with rich keyed state and timers, giving the lowest latency — the default where real-time is the product. Spark Structured Streaming is micro-batch (sub-second to seconds) but reuses the DataFrame API and the cluster you already run, which makes it pragmatic for Spark shops. Kafka Streams is a library inside your service, not a cluster. Tie the choice to latency requirement, state complexity, and what the team can actually operate.

Should this be batch or streaming?streaming · design

Decided by the consumer's freshness requirement, not by preference. Streaming costs always-on infrastructure, state management, and much harder debugging. If the consumer is a dashboard read each morning, batch is the correct engineering answer — and saying so demonstrates judgement. Reach for streaming when a decision is made on the data in seconds: pricing, fraud, dispatch, alerting.

System design

How do you approach an open-ended pipeline design question?design

Five phases in roughly sixty minutes: requirements (~8 min — volume, freshness SLA, consumers, retention, budget), back-of-envelope sizing (~4), high-level architecture as ingest → process → store → serve (~15), deep dives on failure modes (~20), and trade-offs plus operations (~8). The most-cited rejection reason is opening with tool names before eliciting a single requirement.

Design a real-time clickstream analytics pipeline.design

Size it first (2B events/day × 1KB ≈ 2TB/day, ~23k/sec average, 100k+ peak). Note the real tension: the dashboard wants fast, the reporting tables want right. Producers → partitioned Kafka keyed by user id. Two consumers off one log: a stream job maintaining windowed aggregates into an OLAP store, and a sink writing raw Parquet to the lake, from which a batch job builds corrected tables with a rolling late-data window. Then the deep dives: dedupe on client event id, watermarks, compaction for small files, replay-from-log for backfill, a schema registry, and tiering for cost.

What's the small-files problem?design

Streaming writers and over-partitioned batch jobs emit thousands of tiny files. Every file costs a metadata lookup and an open, so query planning and scan time collapse even though total bytes are small. Fix with a compaction job that rewrites small files into ~128MB–1GB targets, coarser partitioning, and coalesce before writing. Mentioning it unprompted in a design round signals you've operated a lake.

Lambda or Kappa architecture?design

Lambda runs parallel batch and streaming paths over the same data; its real cost is two implementations of the same business logic that inevitably drift. Kappa runs one streaming path and replays the log for reprocessing. The modern preference is a single path — stream with replay, or batch on a short interval — reaching for two only when requirements genuinely demand both a fast approximate answer and a slow exact one. If you propose both, name which one is the system of record.

Scenario drills

A job that took 40 minutes now takes 6 hours. Nothing changed. Go.scenario

"Nothing changed" is always false — find what did. In order: the data (volume growth, a skewed hot key appearing, an upstream repartition producing millions of small files — same bytes, 100× the tasks); the plan (a broadcast join silently flipping to sort-merge because a table crossed the threshold; stale statistics); the platform (cluster contention, spot loss, a quietly-changed config). Method beats guesses: pull up the Spark UI for both runs, diff stage durations, and say what you would compare. Naming small-files and the broadcast flip covers the two most common real causes.

A stakeholder says the dashboard numbers are wrong.scenario

First, make "wrong" precise: which metric, which date range, wrong compared to what? Then reproduce at the lowest layer — run the metric query yourself against the mart, then walk lineage upstream (mart → staging → raw) comparing counts and sums at each hop until the divergence appears. The usual suspects, in order: timezone boundaries (their report is IST, the pipeline is UTC), duplicates from a replay, a join that quietly fans out, late data that arrived after their snapshot. Communicate while diagnosing — an ETA and a "here is what I have ruled out" beats silence — and finish with the prevention: a reconciliation check (§3.5) at the hop that lied.

Backfill two years of data without breaking prod.scenario

Plan before code: run on isolated compute (separate cluster/warehouse so the daily SLA is untouched), process partition by partition with idempotent MERGE writes so any failure resumes instead of restarting, throttle against the source and the warehouse, and verify as you go — counts and control totals per partition against the source, not one giant check at the end. Mention the dual-write question (does new data keep flowing while you backfill? land both, reconcile at the boundary date) and cost (two years of reprocessing is a real bill — estimate it first). This is §10.2's idempotency argument as a story.

Duplicates appeared in a production dashboard overnight.scenario

Two questions locate it: where (raw already has dupes → upstream replay/at-least-once delivery; raw clean but mart dirty → your load or a fan-out join) and since when (one day → a rerun double-appended; growing over weeks → a key assumption broke). Immediate fix: dedupe the affected partitions with the ROW_NUMBER pattern and restate the dashboard. Real fix: make the load idempotent — MERGE on the business key instead of append — and add a uniqueness check that pages before the CEO sees it. Saying "the bug is that a rerun was ever unsafe" is the senior close.

You must cut the platform bill 30% this quarter.scenario

Measure first: cost per pipeline/warehouse/table, because 20% of jobs are 80% of spend and intuition lies. Then the ranked levers: kill zombies (jobs nobody reads — ask who consumes each expensive output); full-refresh → incremental (§3.2, usually the single biggest cut); right-size and auto-suspend compute (idle warehouses, oversized clusters, spot for fault-tolerant batch); storage lifecycle (compress, tier old partitions to cold storage); fix the top 3 worst queries (partition pruning beats cluster upgrades). Close with the guardrail: publish cost per pipeline so it stays visible — one-off cleanups regress in a quarter.

Upstream added a column and renamed another. Your pipeline is failing. What now — and how do you prevent round two?scenario

Now: stop the bleed (pause the load so you do not write garbage), map old→new column explicitly for the rename, deploy, then reprocess the failed window idempotently. Additions are non-breaking if your pipeline selects columns explicitly — SELECT * is why it broke. Prevention is the real answer: explicit column lists, a schema check at ingestion that quarantines unknown shapes instead of crashing (§3.4), and a conversation upstream — a data contract, even an informal one, that renames get a deprecation window. The story ends with the org fix, not the code fix.

Behavioral

Tell me about a time a pipeline broke in production.behavioral

The most-asked DE behavioral question. Structure: how you detected it (an alert you'd built beats "an analyst told us"), the blast radius, the call you made under pressure, the fix, and the durable prevention. Carry a number in the situation and one in the result. End on the systemic change — "we added a row-count reconciliation that has caught two silent failures since" — because a story ending in a permanent improvement reads as an engineer who compounds.

Tell me about a time you were wrong.behavioral

Asked in nearly every senior loop, and the failure mode is the disguised humblebrag ("I care too much about quality"). Pick a real mistake with real consequences, own it without hedging, and spend most of the answer on the correction and the systemic fix. Interviewers are calibrating whether you can be told you're wrong — which matters more in data roles, where being confidently wrong propagates downstream.

Tell me about disagreeing with a stakeholder.behavioral

The point is not winning. Show that you disagreed with evidence rather than opinion, escalated proportionately, and committed to the outcome even when it went against you. For data engineers the strongest version involves defending correctness against a deadline — you flagged that the numbers weren't ready, quantified the risk of shipping anyway, and either held the release or shipped with explicit caveats and a fix date.

What should I ask the interviewer?behavioral

Ask what reveals the job: what breaks most often and who gets paged; how you'd know a dataset is wrong; the split between building new pipelines and maintaining old ones; what the platform looks like in a year. These score as owner-thinking — and they protect you, because a team that can't answer "how do you know when data is wrong" is a team where year one is firefighting.

Contents · Cheat sheets

Cheat sheets

The dense tables worth a last glance before you walk in. Everything here is explained in the chapters; this is the compressed version for the night before.

SQL logical execution order

Written SELECT…FROM…WHERE, but evaluated in this order — which is why aliases and window functions aren't usable in WHERE. (§1.4)

#	Clause	Does
1	FROM / JOIN	Build and combine the row set
2	WHERE	Filter rows (no aggregates, no aliases)
3	GROUP BY	Collapse rows into groups
4	HAVING	Filter groups (aggregates allowed)
5	SELECT	Compute columns, aliases, window functions
6	ORDER BY	Sort (aliases usable here)
7	LIMIT	Take the top N

Ranking & window frames

How the three ranking functions treat a tie, plus the frame you'll actually specify. (§1.2)

Function	On a tie	Use for
ROW_NUMBER	1,2,3,4 (arbitrary)	Dedup, top-N-per-group
RANK	1,2,2,4 (skips)	Competition ranking
DENSE_RANK	1,2,2,3 (no skip)	Nth distinct value
Running total	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Cumulative sum
Default frame	`RANGE` (ties share a frame — specify `ROWS` for precision)	Beware around duplicates

Slowly changing dimensions

Policies for what to do when a dimension attribute changes. (§2.3)

Type	Behavior	History	Use when
Type 1	Overwrite the value	None	Only current value matters
Type 2	New versioned row + `valid_from/to`, `is_current`	Full	Point-in-time correctness
Type 3	Add a "previous value" column	One hop	Track a single prior value

Narrow vs wide transformations

Wide = shuffle = new stage. This is the vocabulary for the whole Spark performance conversation. (§4.2)

Narrow (no shuffle)	Wide (shuffle)
`map`, `filter`, `select`	`groupBy`, `reduceByKey`
`flatMap`, `union`	`join` (unless broadcast)
`withColumn`, `cast`	`distinct`, `orderBy`, `repartition`
Fuse into one stage	Force a new stage boundary

Join strategies

Spark picks by side sizes; force with broadcast() when the estimate is stale. (§6.2)

Strategy	When	Shuffle?	Watch out
Broadcast hash (BHJ)	One side < ~10MB	No (big side)	Broadcasting too big → OOM
Sort-merge (SMJ)	Both sides large	Yes (both)	Two shuffles + sort
Shuffle hash (SHJ)	One side fits in memory, no sort	Yes	Rarer; memory-heavy

repartition vs coalesce

The most-asked partition-tuning distinction. (§6.1)

	`repartition(n)`	`coalesce(n)`
Shuffle	Full shuffle	No full shuffle
Direction	Increase or decrease	Decrease only
Result	Even partitions	May be uneven
Use for	Widen, rebalance, fix skew	Shrink cheaply after a filter

Spark tuning knobs

The configs and defaults that come up in tuning questions. (§5–5)

Config / idea	Default	Note
`spark.sql.shuffle.partitions`	200	Static; aim ~128–200MB/task or let AQE coalesce
`autoBroadcastJoinThreshold`	10MB	Below this, a side is auto-broadcast
AQE	On (3.2+)	Coalesces partitions, switches joins, splits skew
Target task size	—	~128–200MB, a small multiple of total cores
UDF preference	—	Native > pandas/Arrow UDF > Python UDF
Cache default	`MEMORY_AND_DISK`	`cache()` = this; cache only what you reuse

Where OOM strikes

Name the side first — it's the difference between diagnosing and guessing. (§5.4)

Side	Cause	Fix
Executor	Partition too big, giant `groupByKey`, skew	More/smaller/even partitions, salt, AQE
Driver	`collect()` of large result, broadcast too big	Don't collect; lower broadcast threshold

Snowflake warehouse sizes

Each size doubles compute and credit rate — so a parallel query costs ~the same, just finishes faster. (§9.2)

Size	Credits / hr	Relative compute
X-Small	1	1×
Small	2	2×
Medium	4	4×
Large	8	8×
X-Large	16	16×
2XL … 4XL	32 … 128	doubles each step

Streaming output modes

What's emitted each trigger, and when each is legal. (§7.3)

Mode	Emits	Notes
Append	Only new finalized rows	Aggregations need a watermark
Update	Rows changed this trigger	Good for upsert sinks
Complete	Entire result table	Aggregations only; small results

Extraction strategies

Profile the source, then pick. The deletes column is the one candidates forget. (§3.2)

Strategy	Source load	Catches deletes	Use when
Full snapshot	High	Yes	Small tables; no reliable change column
Watermark incremental	Low	No	Large table, trustworthy `updated_at`, no hard deletes
CDC (log-based)	Minimal	Yes	Deletes matter, freshness matters, you have log access
Incremental + periodic full	Medium	Periodically	Too big to snapshot nightly, no CDC available

Load patterns and idempotency

Running twice must equal running once. Only two of these qualify. (§3.3)

Pattern	Rerun result	Use for
Append	Duplicates	Immutable event logs deduped downstream
Overwrite partition	Converges	Date-partitioned batch — the default
Merge / upsert on key	Converges	Rows that change in place; CDC landing
Merge trap	Ambiguous / errors	Duplicate keys in one batch — dedupe to one row per key first

Schema change compatibility

Classify before you react — additive and widening are safe, the rest are breaking. (§3.4)

Change	Compatible?	What happens if unguarded
Add nullable column	Safe	Old readers ignore it
Widen type (int → bigint)	Safe	Values still fit
Rename column	Breaking	Old column goes all-NULL beside a new one
Narrow type	Breaking	Uncastable values silently become NULL
Drop column	Breaking	Every downstream reference nulls out

Streaming semantics

Three orderings of "process" and "commit the offset." (§8.3)

Semantic	Mechanism	On replay
At-most-once	Commit offset, then process	Data lost
At-least-once	Process, then commit offset	Duplicates
Exactly-once	Atomic offset + output (2-phase commit)	Correct — needs a transactional sink
Effectively-once	At-least-once + idempotent upsert on a key	Correct — what most systems actually run

Kafka quick facts

The five that come up in nearly every streaming round. (§8.1)

Concept	The fact
Ordering	Guaranteed within a partition only — key by entity id
Consumer group	One partition → exactly one consumer; extra consumers idle
Parallelism ceiling	Capped by partition count, set up front
Consumer lag	Latest offset − committed offset; the metric to alert on
Replay	Rewind the offset — retention is by time/size, not by read

The design round, in sixty minutes

Budget it out loud. Most candidates overspend on the architecture box. (§11.1)

Phase	Time	Cover
Requirements	~8 min	Volume, freshness SLA, consumers, retention, budget
Back-of-envelope	~4 min	events/day → GB/day → events/sec → peak (5–10×)
Architecture	~15 min	Ingest → process → store → serve; tools last
Deep dives	~20 min	Backfill, late data, schema change, dedupe, failure
Trade-offs	~8 min	What you'd monitor; what changes at 10×; cost

The six behavioral stories

Each needs a number in the situation and a rejected alternative in the action. (§12.3)

Theme	Asked as
The incident	"A time you owned a production failure"
Data quality catch	"When you found bad data before the business did"
The disagreement	"A time you pushed back on a stakeholder"
Ambiguity	"Shipping without clear requirements"
Influence without authority	"Getting another team to change something"
The trade-off	"When you knowingly accepted technical debt"

The three-cloud dictionary

Same six ideas, three vocabularies. Answer in problems, then translate.

Problem	AWS	Azure	GCP
Object store / lake	S3	ADLS Gen2	GCS
Warehouse	Redshift	Synapse	BigQuery
Serverless SQL on the lake	Athena	Synapse serverless	BigQuery external
Managed Spark	Glue · EMR	Databricks · Synapse Spark	Dataproc
Event log / stream	Kinesis · MSK	Event Hubs	Pub/Sub
Orchestration	MWAA · Step Functions	Data Factory	Composer
Streaming compute	Managed Flink · Spark	Stream Analytics	Dataflow
Key-value / NoSQL	DynamoDB	Cosmos DB	Bigtable

SQL patterns — recognition cues

The prompt words that tell you which pattern you are in, before you write a line.

Prompt says	Pattern	Core move
"consecutive", "streak", "in a row"	Gaps & islands	date − ROW_NUMBER() is constant per run; group by it
"top N per …", "best in each"	Top-N per group	DENSE_RANK() over partition, filter in outer query
"rolling", "moving", "trailing 7 days"	Window frame	ROWS BETWEEN 6 PRECEDING — densify dates first
"came back", "day-1 retention", "cohort"	Retention	join signups→events on date offset, conditional COUNT
"session", "30 minutes of inactivity"	Sessionization	LAG() gap flag, running SUM() of flags = session id
"remove duplicates, keep latest"	Dedup	ROW_NUMBER() ordered by recency = 1
"step conversion", "of those, how many…"	Funnel	conditional aggregation per step, divide down
"compare to previous", "growth"	Offset	LAG()/LEAD(), watch NULL first rows

The DE Interview

Passing the data-engineering interview

Try one

Chapters

The shape of the loop

What senior answers sound like

How to use this guide

Joins, NULLs, and the traps

Window functions

Patterns they actually ask

The logical execution order

Grain, facts, and dimensions

Star schema, and one big table

Slowly changing dimensions

Keys, and modeling for change

ETL, ELT, and the layers

Extraction: full, incremental, CDC

Loading: append, overwrite, merge

Schema drift and data contracts

Late data, reprocessing, and reconciliation

Why distribute at all

From MapReduce to the DAG

The shuffle

Batch, streaming, and exactly-once

Cluster anatomy

Lazy evaluation

Jobs, stages, and tasks

Memory, and why jobs spill

Adaptive Query Execution

Partitions and parallelism

Choosing a join

Data skew

Caching and persistence

UDFs, Arrow, and a tuning checklist

Warm-up drills

Harder drills: sessionization

Structured Streaming

"Optimize this job"

The log: Kafka's model

Event time, windows, and watermarks

Delivery semantics and exactly-once

Operating a stream, and picking an engine

Snowflake architecture

Warehouses and cost

Time travel, clones, streams & tasks

Managed data integration

The cloud stack: AWS, Azure, GCP

CI/CD for data

Orchestration: idempotency & backfills

MLOps: the pipeline past the model

Observability and the 2 a.m. page

The round that decides senior offers

Requirements and the envelope

The reference architecture

Worked example: clickstream analytics

The round that loses offers

Story architecture

The six stories every DE loop asks for

Your questions, and the offer

Company playbooks

Question bank

Cheat sheets