Reliability Under Failure
Concepts covered: Idempotency, Retry & Failure Handling, Late-Arriving Data, Dead Letter Queues, Schema Evolution, Pipeline Monitoring, DAG Orchestration
The last 10 minutes of the round go to failure, and the highest-scoring answers raise each one before the interviewer does. This pipeline has 6, each with a property that survives it, and the property is the answer.
Retries land on idempotent writes
Every Airflow task retries 3 times with exponential backoff from 2 minutes, and a retry is safe only if running the task twice leaves the same table. The Stripe pull re-reads the boundary second into bronze, and the silver merge on charge_id collapses the overlap. The Spark micro-batch records its Kafka offsets in a checkpoint and tags each Iceberg commit with its batch id, so a job killed between the 2 replays the batch and Iceberg skips a batch it already committed. Where no natural key exists, the run id becomes a column and the load deletes that run id's rows before inserting, the sentence to say when asked about a source with no primary key.
- wait_courier_file (quality gate, Airflow S3 sensor); alerting: page at 07:30 with no _SUCCESS
- load_courier (transform, Airflow + Spark); on error: rejects to side prefix; retries: 3; backoff: 2 min, exponential; idempotency: SHA-256 manifest skips re-uploads
- pull_stripe (transform, Airflow, hourly); retries: 3; backoff: 2 min, exponential; idempotency: high-water mark, merge on charge_id
- merge_orders (silver, dbt incremental); retries: 3; idempotency: MERGE on order_id, ingested_at watermark
- daily_revenue (gold, dbt, 45-day window); freshness: yesterday by 07:00; retries: 3; backfill: rerun with a date range over bronze; alerting: row count vs 7-day median
- wait_courier_file -> load_courier
- load_courier -> daily_revenue
- pull_stripe -> daily_revenue
- merge_orders -> daily_revenue
Late events and the window that reopens
At the dinner peak Debezium can fall 10 to 15 minutes behind the primary, so order changes reach silver with an updated_at older than buckets gold has already written. The gold job recomputes from the hour boundary 2 hours back on every run, so lag under 2 hours is repaired on the next run, and the 45-day daily_revenue rebuild catches anything later. App events arrive late another way: a phone that loses signal in a stairwell delivers on reconnect, and about 0.3% of events arrive more than 10 minutes late. The micro-batch lands them by arrival with their true event_ts, so they reach the right day partition in silver; a streaming aggregate with a 10-minute watermark would drop that 0.3%, which is why every aggregate here runs in batch over silver.
A malformed record takes the side path
One row in the 2 GB courier file carries a delivery timestamp of 31/02/2026. A parser that raises halts the load, and the whole day's finance report waits on 1 row. Rows that fail typing go to bronze/courier_deliveries_rejected/dt=.../ with the raw line and the error, and the load counts them and keeps going; an alert fires above 0.1% of the file, the threshold between a typo and a partner who changed their export. Kafka events get a dead letter topic, with the original headers kept so the record can be replayed after the fix.
Schema change upstream
The orders team adds a nullable tip_cents column on a Tuesday afternoon without telling anyone. Debezium registers a new Avro schema version with the field optional and a null default, which every compatibility mode accepts, and the silver model, selecting columns by name, ignores it until someone adds it. A rename is different. Under the registry's default BACKWARD mode, dropping amount passes. Adding amount_cents is rejected only if it is required with no default, so the rename usually goes through and silver's SELECT amount fails or reads null. Set the CDC subjects to FULL, which rejects removing a required field such as the NOT NULL amount: the connector stops and the slot holds the WAL until the mapping is fixed. A contract test on silver's columns catches whatever the registry still accepts. Iceberg tracks columns by id, so adding one to silver is an ALTER TABLE ... ADD COLUMN with no rewrite; the courier CSV has no registry, so the load asserts its header row first.
What pages, and what waits
Three monitors cover the pipeline. Freshness: max(ingested_at) per bronze table against the clock, paging at 15 minutes for Kafka sources and 90 minutes past expected arrival for the daily file. Volume: today's row count against the trailing 7-day median for the same hour, alerting below 50% or above 200%, which catches a partial file set or a replayed topic before a consumer does. Slot lag: retained WAL bytes on the replication slot, alerting at 10 GB and paging at 30, because the end of that curve is the production database. A silent run, where the DAG succeeds and gold gains 0 rows, trips the volume monitor, which is why it runs on the output table rather than task status.
- Do
- Give every box a reason tied to the source or consumer and a number the interviewer can check
- Say the retry policy and the idempotency key in the same breath
- Name the late-event percentage and the recompute window that absorbs it
- Monitor the output table's freshness and volume, since task status can succeed on 0 rows
- Route bad records sideways with the raw line kept, and alert on the reject rate
- Avoid
- Say "exactly-once" without naming the commit that makes it true
- Treat a late event as an error instead of an expected 0.3%
- Let 1 malformed row halt a 2 GB file and a 07:00 report
- Read columns by position from a source that can add one on a Tuesday
- Leave a replication slot without a WAL cap and a slot-lag alert
About this lesson
This section is part of the Design a Pipeline: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.