Data Pipeline Failure & Recovery Scenarios

The reasoning

A retry is not an exceptional event you can design around later. It is the normal operating mode of every orchestrator ever built: tasks are retried on timeout, on a killed worker, on a transient network error, and often by a human clicking “clear” without reading what the task does. So the real question is not “will this run twice” but “what does the table look like when it does”.

For a plain append the answer is: every row twice. Three orders became six. Note what did *not* happen — no constraint fired, no task went red, no alert triggered. The pipeline is in a perfectly healthy state by every signal it emits, and revenue is double. This is why duplicate-from-retry is usually found by a finance analyst rather than by monitoring, often weeks later.

The fix is not “be careful with retries” — you do not control who reruns what. The fix is to make the write itself produce the same table whether it runs once or five times, which means keying it: delete-then-insert on the business key, or overwrite the whole partition the file belongs to. Both are covered in the next two questions, and both turn this failure into a non-event.

The reasoning

The pattern is delete-then-insert scoped to the keys present in the incoming batch, run inside one transaction. In a warehouse you would usually write it as a MERGE and in a lakehouse as MERGE INTO on a Delta or Iceberg table; the shape is identical and so is the reasoning. What makes it idempotent is that the effect of the load is a *function of the batch*, not a function of the batch plus whatever was already there.

The transaction matters and is the part most often left out. Delete-then-insert as two separate commits has a window in which the table is missing those keys entirely, and if the job dies in that window you have gone from duplicated data to *missing* data — a worse outcome. Every engine that supports this pattern properly gives you a way to make the pair atomic, and using it is not optional.

Now the limit. Idempotence says: running twice equals running once. It says nothing about whether running once was right. A load can be perfectly idempotent and still drop rows, keep deleted records alive, or write the wrong column — and four of the questions in this bank do exactly that. Idempotence is a property that makes failures recoverable; it is not a correctness proof, and treating it as one is a common and expensive mistake.

The reasoning

Think about what each pattern can express. A MERGE keyed on order_id says: for every key in this batch, make the target match. It has no opinion about keys that are *not* in the batch, which is exactly right when the batch is a stream of changes and exactly wrong when the batch is a complete re-extract of a day.

Partition overwrite says something stronger: for this partition, the batch *is* the truth — delete everything in it and write what I brought. That makes absence meaningful. When the source deduplicates a day and re-sends it two rows shorter, only the overwrite removes the third row. In the run below, e3 is a duplicate the source fixed; it disappears because the second batch does not contain it, and a key-level merge would have left it there indefinitely.

The trade is blast radius and cost. Overwrite rewrites an entire partition even to change one row, which is fine for a day-partitioned fact and ruinous for a table partitioned by something coarse. And it requires that your partition boundary matches the unit the source can re-send: if the source can only give you “all orders changed since X”, you cannot overwrite by day, because your batch is not a complete day. In practice: overwrite for reprocessable, partition-aligned extracts; MERGE for change streams.

The reasoning

A full rerun of an appending load at least produces something predictable: exactly double. A crash mid-write does not. It leaves however many rows the engine had committed at the moment it died — a number that depends on batch sizes, buffer flushes, and where the OOM landed, and that nobody logged. Rerunning on top of that gives you a total that cannot be reasoned about after the fact.

In the run below the first attempt got two orders in before dying. The rerun adds all three, and a second rerun adds three more: eight rows for a file containing three. Reconciling against a full refresh is the only thing that reveals it, and reconciliation is exactly what nobody runs on a Tuesday.

This is the argument for atomicity being a design requirement rather than a nice property. The write should be a single commit — one transaction, or a staged write that is published by an atomic metadata swap, which is what Delta, Iceberg and Hudi give you. Then a crash leaves *nothing*, the rerun starts from a known state, and the failure mode you have to reason about drops from “unknown partial” to “did or did not happen”.

The reasoning

The load below deduplicates with ROW_NUMBER() OVER (PARTITION BY order_id) and keeps the first row per order. But the table’s grain is one row per order *line*. An order with three lines arrives, three lines are real, and two are discarded as though they were duplicates. Order 1 loses 75 of its 175.

Every consistency property you might check still holds. The load is idempotent — rerun it as often as you like and the answer is stable. Row counts are stable day to day. There are no nulls, no constraint violations, no failed tasks. The only signal is that the number is smaller than it should be, and there is nothing in the pipeline to compare it against.

Which is the lesson: **idempotence and correctness are independent**, and only one of them is visible from inside the pipeline. The check that finds this is a reconciliation — recompute the table from the full history and compare, exactly as the “full refresh” block below does. A dedupe key should be the grain or a superset of it, and if you cannot state the grain in one sentence, you cannot know whether it is.

The reasoning

The watermark is inclusive of everything you have *seen*, but it is being used as though it were inclusive of everything that *exists* at that instant. Those come apart whenever the timestamp is not unique. The extract at 07:00 reads orders 1 and 2, and order 2 carries 09:00:00. Order 3 commits a fraction of a second later and also carries 09:00:00 — same second, different transaction. The watermark is now 09:00:00, the next run asks for strictly greater, and order 3 is never read again.

The reason it is so hard to reproduce is that the loss is *permanent and silent*. There is no error, no gap in a sequence, no row count that looks wrong — you are missing one order out of four and the only way to see it is to compare against the source. The run below does that comparison explicitly: the incrementally-built table has three orders and the full recompute has four.

Three things make it worse in practice. Second-resolution timestamps make ties common rather than rare. A source that assigns updated_at at statement time rather than commit time widens the window to the length of the transaction. And read replicas add replication lag on top, so “the max I saw” can be meaningfully behind “the max that was committed”.

The reasoning

>= is the right predicate. Overlapping reads are unavoidable if you want at-least-once coverage of the source, and every serious incremental design deliberately re-reads a margin around the boundary — some go further and subtract a safety interval, reading from watermark - 15 minutes to survive clock skew and replication lag.

But an overlapping read is only safe if the *write* can absorb it. Pairing >= with a plain INSERT means every boundary row lands again on each run, and a retried task lands the whole overlapping window a third time. In the run below the retry re-reads order 4 and the table ends up with five rows for four orders.

So the two halves have to be designed together, and this is the single most useful sentence to say out loud in the interview: **read with an overlap, write with a key**. The predicate’s job is to guarantee you never miss a row; the write’s job is to guarantee that seeing a row twice is harmless. Fixing either one alone gives you a broken pipeline that fails in a different direction.

The reasoning

Three decisions, each load-bearing. **Read inclusively** (>=, and in production usually >= watermark - margin) so no row can slip through the boundary — this is the guarantee that you see everything. **Write by key** so that seeing a row twice produces the same table as seeing it once. **Advance the watermark last**, in the same transaction as the write, so a crash between the write and the bookkeeping leaves the watermark where it was and the next run simply re-reads a window it can safely re-read.

That ordering is the part people drop. Advancing the watermark first — or in a separate commit — means a failure after the update and before the write skips that window permanently, and it is the same permanent silent loss as the > bug with a different cause. The rule is that the watermark must never move past data you have not durably written.

And be precise about what this is. It is at-least-once delivery made *effectively*-once by an idempotent write. It is not exactly-once, and the distinction is worth stating because interviewers listen for it: nothing here prevents the source being read twice, it only makes the second read a no-op. That is the property real systems ship, and it is achieved at the write, not in the transport.

The reasoning

There are two timestamps on every event and they are not interchangeable. Event time is when the thing happened, and it is what every report must aggregate by — nobody wants a purchase counted on the day the phone found signal. Ingest time is when your system first saw it, and it is assigned by you, on arrival, in order.

Only one of those is monotonic, and a watermark is fundamentally a claim about monotonic progress: “I have seen everything up to here.” Event time arrives out of order by nature — phones buffer, apps retry, batches upload on reconnect — so a watermark on event time is a claim that the data itself keeps violating. In the run below e3 happened at 23:40 and arrived at 00:07, behind a watermark already at 23:55. It is never loaded, and no future run will look for it.

The resolution is to use them for different jobs: **watermark on ingest time, aggregate on event time**. Ingest time is monotonic so it is safe to track progress with; event time is what the business means so it is what you partition and group by. The consequence — and this is what the follow-up is about — is that a late arrival lands in an old event-time partition, so yesterday’s numbers can change today, and your downstream has to be built to expect that.

The reasoning

The mechanism is simple and the run below shows it: e3 carries event time 23:40 and ingest time 00:07:12. The watermark is on ingest time, sitting at 23:55:02, so the row is comfortably ahead of it and gets loaded. The target ends up matching a full recompute over everything delivered — which is the property you actually want and the one the event-time version fails.

The cost is that **the past is now mutable**. e3 belongs to 2026-03-01 by event time, and it arrived on 2026-03-02. Whatever you published for the 1st was incomplete, and is now wrong-but-fixable. That is not a bug you can engineer away; it is the physical reality of distributed event collection, and the only choice you have is whether your architecture acknowledges it.

Acknowledging it means three concrete things. Downstream aggregates over recent event-time partitions must be recomputed on a rolling window rather than written once — typically the last 3 to 7 days, sized from the observed arrival-lag distribution rather than guessed. Consumers need to know a recent number is provisional, which is a data-contract concern and not a technical one. And you should be measuring the lag between event time and ingest time as a first-class metric, because it is the number that tells you how big the rolling window needs to be and when it needs to grow.

The reasoning

Arrival order is not commit order and there is no mechanism anywhere in the stack that makes it so. Kafka guarantees ordering within a partition and says nothing across partitions; a connector reading several partitions in parallel interleaves them arbitrarily; a retry re-delivers an old record after newer ones have already passed. Any of these produces the batch below, where LSN 101 sits above LSN 99.

This is precisely why every CDC source hands you a monotonic ordering column — LSN in Postgres and SQL Server, SCN in Oracle, the binlog file-and-position pair in MySQL, and a _ts_ms/sequence pair in Debezium’s envelope. It is not metadata to log; it is the only thing in the message that tells you which change happened last. Ordering by it is what makes the apply deterministic regardless of how the batch arrived.

The failure has a particularly nasty shape: it is intermittent, it depends on partition assignment and broker timing, and it self-heals the moment the customer changes again. So it presents as “a handful of dimension rows are occasionally stale and we cannot reproduce it” — which is the description of this bug, every time.

The reasoning

Two changes together buy the whole property. Selecting MAX(lsn) per key makes the result independent of arrival order — the batch can be shuffled arbitrarily and the answer does not move. Writing it as a keyed delete-insert makes the result independent of how many times the batch is applied.

Together those mean the target is a pure function of the *set* of change records seen, and neither the order they came in nor the number of times they came matters. That is the property you need, because a CDC connector will absolutely re-deliver: offsets are committed periodically, so a restart rewinds to the last checkpoint and replays everything since. This is normal operation, not a failure.

One subtlety worth raising unprompted, because it is what the follow-up is for: MAX(lsn) per key is correct *within a batch*, but the target can already hold a row from a later LSN if a previous batch got ahead — which happens after a rewind that replays old changes. Robust implementations therefore keep the applied LSN on the target row and refuse to overwrite a newer one, so a replayed old change cannot regress a key that has since moved on.

The reasoning

The mechanics are worth being precise about. A CDC delete carries a *before* image and typically nulls for the after-image, so a naive SELECT after.* produces a row of nulls. The obvious-looking fix is to filter deletes out of the apply. That does stop the nulls, and it also stops the deletion, which was the entire message.

The damage compounds because it is silent and one-directional. Every cancelled subscription stays in MRR forever; the number only ever grows relative to the truth; and the gap widens with churn. There is no error and no null to notice. In the run below, subscription 2 is cancelled and the target still carries its 30.

The correct apply treats op as part of the instruction rather than as a row to filter: delete the keys the batch touches, then re-insert only the non-delete final states. Which raises the design choice worth naming — hard delete or soft. A soft delete keeps the row with is_deleted and a timestamp, which preserves history and lets you answer “what did we think last month”, at the cost of every downstream query needing the filter. A hard delete is simpler and loses the audit trail. For anything regulated, or anything with a right-to-erasure obligation, the choice is made for you.

The reasoning

The first half of the sentence is true. A CDC stream *is* an append-only log of changes, and treating it as one — landing every record in a raw, immutable table — is not just acceptable, it is the recommended bronze-layer design. It gives you replay, audit, and the ability to rebuild any downstream table from scratch.

The mistake is letting that shape leak into the dimension. dim_customer promises one row per customer; a raw change log promises one row per change. Appending the log into the dimension gives you a table with three rows for customer 7 and no column that says which one is current. Every fact-to-dimension join now fans out, so revenue triples, and every WHERE tier = 'silver' matches a customer who is also matched by WHERE tier = 'free'.

The distinction to state out loud is between the log and the projection. Land the log raw. Then *project* it into the shape the consumer needs: current-state per key for a Type 1 dimension, or — if history matters — a Type 2 with valid_from / valid_to derived from the LSN ordering, which is genuinely one row per change and is safe precisely because the validity columns tell a join which version applies.

The reasoning

This is the payoff for the previous two questions. If the apply takes the highest LSN per key and writes by key, then applying a change twice is a no-op and applying an old change alongside a newer one loses to the newer one. Overlap is therefore free, and the run below demonstrates it: the stream replays from LSN 0, over a snapshot that already contains those rows, twice, and the target still equals a full recompute.

That is why every mature CDC setup deliberately arranges an overlap. You take the snapshot at a known LSN and start the stream at or *before* it, never after. Debezium’s initial-snapshot mode does exactly this — it records the log position before reading the table and streams from there, accepting that it will re-deliver changes the snapshot already captured.

The failure mode to be afraid of is the gap: snapshot taken at 10:00, stream started from 10:05, and every change in between lost permanently with nothing to indicate it. It is silent, it is unrecoverable without a full re-snapshot, and it is the direct consequence of trying to avoid overlap. **Overlap costs a little redundant work; a gap costs data you cannot get back** — so when the boundary is uncertain, always err earlier.

The reasoning

An appending load cannot express a correction. It only knows how to add, so a backfill carrying a fixed value adds it *next to* the wrong one rather than replacing it. Order 2 was 250 and is now 90; after the backfill the table contains both, and SUM(amount) is not merely wrong but wrong in a way that gets larger the more corrections you make.

The trap is that this pipeline is fine on any ordinary night. Nightly appends of non-overlapping days work perfectly for months. The design only fails when you reprocess — and reprocessing is exactly what you do under pressure, at the point where someone senior is already asking why the number is wrong.

Which is the principle worth stating: **the ability to reprocess a window is a property you have to design in, not an operation you can perform on any pipeline.** Concretely it means the load must be re-runnable over a bounded range and land the same result — partition overwrite or keyed merge — and the range must be a parameter of the job rather than yesterday hard-coded inside it.

The reasoning

Write the load as a function of a date range: it deletes the partitions in the range and writes what the source now says about them. Then the nightly schedule is that function applied to yesterday, and a backfill is that function applied to a wider range — the same code, the same tests, the same review, no separate script that only ever runs on the worst day of the quarter.

Two properties fall out. It is idempotent, so a double-click on the backfill button is harmless — the run below applies the backfill twice and the target does not move. And it converges: whatever the source now says is what the table now says, so a correction propagates by construction rather than by someone remembering to delete the old rows.

Two things worth raising before you are asked. Backfills need throttling — running a year of partitions at once will happily saturate the warehouse and break the SLA of every other pipeline, so they run in bounded chunks with concurrency limits. And downstream tables need to know the window was restated, or they keep serving aggregates built on the old rows; a lineage-aware orchestrator handles this, and in its absence it is a manual step you must not forget.

The reasoning

This is the one case in the bank where an idempotent, correct, well-built load destroys data, and the run below is deliberately claimed only as idempotent — there is no drift to detect, because the pipeline is faithfully reproducing what the source says. The 90 that finance depended on was never in the source. It existed only as a manual UPDATE, and a derived table cannot keep a secret from the thing it derives from.

So the rule is that a derived table is derived. Anything written to it out of band has a half-life of exactly one reprocessing, and worse, nobody knows it is coming — the person who ran the UPDATE has moved teams and the person running the backfill has no way to discover that this window is special. The failure surfaces two months later as a number that changed for no reason.

The fix is to give corrections somewhere legitimate to live. Best is upstream: fix the source and let the correction flow through, which is the only option that survives everything. When that is impossible — and it often is, for a system nobody owns — the correction belongs in a version-controlled overrides table that the transformation *reads*, so it is reapplied by every run including the backfill, is visible in code review, and shows up in lineage. What it must never be is an ad-hoc statement against the warehouse.

The reasoning

SELECT * into a target is a positional contract: the first column of the source goes to the first column of the target, whatever either one is called. When upstream reorders, the mapping shifts and the data lands in the wrong columns. Whether anything complains depends entirely on the engine — and the engines data platforms are built on are the permissive ones. SQLite here accepts it outright. Parquet read positionally, CSV without a header contract, and a Spark DataFrame written with .write.insertInto all do the same thing.

Even a strict engine only saves you when the types happen to disagree. Swap two string columns, or two numerics, and the strictest system on earth cannot tell there is a problem — there isn’t one, at the type level. The only thing that would have caught the run below is that amount is now the text 'EUR'.

You find out when someone notices, which is the real answer to the second half of the question. There is no error to alert on, so detection has to come from assertions you chose to write: named-column loads that fail on an unexpected shape, a schema check against a registered contract before the load runs, and value-level expectations — currency is one of a known set, amount is numeric and positive. That last one is the check that catches the type-compatible swap nothing else can.

The reasoning

Naming the columns converts a positional contract into a nominal one, and the run below confirms it: the same reordered file loads correctly and matches a full recompute. It is the single highest-value habit in this whole area and costs nothing but typing.

Then be honest about the remaining four cases, because that is the answer that separates candidates. A **rename** now fails loudly, which is the good outcome — you find out at deploy time rather than in a dashboard. A **new column** is silently ignored, which is usually fine and occasionally means you have been missing a field for months. A **dropped column** fails loudly. A **type change** — an integer becoming a decimal, a string becoming a struct — is the dangerous one, because engines coerce: your amounts start truncating or turning to null, the job succeeds, and nothing in the pipeline objects.

So the load is the last line of defence, not the first. The first is a contract with the producer that declares the fields, their types and their nullability, enforced at the boundary before anything is written — a schema registry with compatibility rules for streams, or a schema assertion on landing for files. Then additive changes flow through automatically, and breaking changes are rejected at the door with a name attached, instead of being discovered downstream by whoever happens to look.

The reasoning

**The stream is not a dead-letter queue.** It is the transport. A record that fails contract validation at the producer, or fails to publish after its retries are exhausted, never enters the stream at all — so a downstream DLQ can never see it. If there is nowhere to put it, the only remaining option is dropping it silently, which is how pipelines lose data without anyone noticing.

**So there are two.** At the producer: a failure store — an object-storage bucket, a table, a queue — for records rejected by validation or abandoned after retry. Downstream: a conventional DLQ for records that reached a consumer and could not be processed, because of a schema mismatch, a deserialisation failure or a business-rule violation.

**What a dead-lettered record must carry**, and every field earns it: the **raw record exactly as received**, not a parsed version, because if parsing is what failed then a parsed copy is worthless; the **failure reason** and the specific validation that failed; the **origin** — source, partition, offset, ingestion time — so it can be located and correlated; and the **timestamp** it was quarantined. Without the origin you cannot tell whether you are looking at one bad producer or one bad hour.

**A DLQ nobody replays is a landfill.** It needs to be queryable so someone can ask what is in it, it needs alerting on rate rather than on individual records, and it needs a replay path — fix the schema or the code, then reprocess from the DLQ with the same idempotent write the main path uses, so replay cannot double-count.

The reasoning

**Two different problems.** A duplicate is the *same* event arriving twice — the fix is an idempotent write keyed on `event_id`, or a merge that is naturally idempotent. Out-of-order is *different* events arriving in the wrong sequence, and no amount of deduplication helps: both events are legitimate, and applying them in arrival order leaves the entity in the older state.

**A monotonic version solves the second.** The producer stamps each event for an entity with an increasing `version` — 1, 2, 3, 4 for the same payment. The consumer stores the version alongside the row and applies an incoming event only when its version exceeds the stored one. Version 3 arriving after version 4 is a no-op rather than a regression, and version 4 arriving twice is applied once and then ignored — so one mechanism covers both cases.

**Why a timestamp is the weaker version of this.** Event time works when clocks are trustworthy and the resolution is fine enough to separate two changes, and both assumptions fail: producers run on different machines with drifting clocks, and two updates within the same millisecond are indistinguishable. For CDC there is usually something better available — a log sequence number or transaction id from the source database, which is monotonic by construction and does not depend on anyone's clock.

**The requirement this places on the producer** is worth naming, because it is where the design fails: the version must be assigned by whoever owns the entity, and a retry must reuse its version rather than incrementing. A version generated by the consumer, or incremented on each send, is not a version — it is a counter of transmissions.

The reasoning

**Two different kinds of failure.** A record-level error — one row with a malformed date — affects that row. A system-level error — the source is unreachable, the schema changed entirely, credentials expired — affects everything. Failing the job on the first is brittle; continuing through the second produces a confidently wrong output.

**The default for record-level errors is quarantine and continue**: valid records proceed, invalid ones go to a dead-letter store with their reason attached, and a metric records the rate. This is the behaviour you want for third-party feeds, where a single malformed line should not stop a nightly load.

**The threshold is what turns that from negligence into policy.** Three bad records in a million is normal. Thirty thousand in a million is not a data-quality blip — it is a schema change, an encoding change, or a bug upstream, and processing the remaining 970,000 publishes a number that is quietly 3% short. So declare the rate at which the pipeline stops: usually a percentage of the batch, sometimes an absolute floor for small batches, and it should be a configuration value rather than a constant buried in code.

**What makes the difference visible** is a null-and-reject rate published as a metric per run, and an alert on the delta rather than the level. A run where the reject rate jumps from 0.01% to 2% is worth waking someone for even though the job succeeded, and a run that fails outright is already visible. The silent case — steady, low, growing — is the one only a metric catches.

The reasoning

**Scope it before touching anything.** Which numbers are wrong, by how much, and do they change what the meeting decides? A metric that is 0.3% off and a metric that is missing a region are different emergencies, and one of them does not need to be an emergency at all. Check the whole chain quickly — did the load run, did it complete, did the transformation change, did an upstream schema move — because that tells you which layer you are in.

**Communicate before you have an answer.** A short message saying what is wrong, what the impact is, that a workaround is coming and when you will next update is worth more than a fix delivered silently at minute fifty-nine. The failure mode you are avoiding is the numbers being discussed in the room before anyone knows they are wrong.

**Ship a fallback in parallel.** This is the move that matters and the one people skip: the last validated snapshot, an exported version of yesterday's report, or a manually reconciled summary of the few KPIs the meeting actually needs — clearly labelled with its as-of time. The meeting gets defensible numbers even if the pipeline does not recover, and labelling it is what keeps the fallback from becoming the next incident.

**Parallelise the investigation** if there is more than one of you: one on the pipeline, one on the warehouse and query layer, one on the report itself and the communication. Serial debugging through three layers does not fit in an hour.

**Root cause afterwards, not now.** Once the numbers are restored, validate against the source, watch for stability, and only then investigate properly and add the control that would have caught it — a reconciliation check, a freshness alert, a volume-anomaly test. Doing the post-mortem during the incident is how the hour gets spent without a fallback ever going up.