Why this matters
Data pipelines fail, late events arrive, and bugs slip into production. Reprocessing and recovery patterns let you safely replay data without duplications, gaps, or corruption. As a Data Engineer, you will use these patterns to:
- Backfill historical periods after schema changes or logic fixes.
- Recover from job crashes with checkpointing and idempotent writes.
- Replay dead-lettered events into the main flow once fixed.
- Handle late-arriving data in streaming with watermarks and deduplication.
Who this is for
- Data Engineers building batch or streaming pipelines.
- Analytics Engineers maintaining warehouse models.
- Platform Engineers owning data reliability and orchestration.
Prerequisites
- Comfort with SQL (joins, window functions) and basic Python or similar.
- Understanding of ETL/ELT workflows and partitioned datasets.
- Familiarity with job orchestration concepts (scheduling, retries).
Concept explained simply
Reprocessing is the act of running data through a pipeline again. Recovery is making the system correct after failure. To do both safely, you need patterns that prevent double-counting, ensure completeness, and maintain state.
Mental model
Think in three dials you can turn:
- Scope dial: what time range or keys you are reprocessing.
- State dial: which checkpoints, offsets, and watermarks you honor or reset.
- Idempotency dial: how to write results so replays do not create duplicates (e.g., MERGE, upsert, overwrite-by-partition).
Pick the right position for each dial based on batch vs streaming, and business correctness needs.
Key patterns you will use
- Idempotent writes: design outputs so running the same input twice produces the same final state (MERGE/UPSERT, overwrite partition, unique constraints).
- Checkpointing and offsets: store progress (e.g., last processed timestamp or stream offset) to resume after failures.
- Watermarks: define how late data can arrive; keep windows open until watermark passes, then finalize.
- Retry with backoff: transient failures often resolve; combine with idempotent writes.
- Dead-letter queue (DLQ) and replay: quarantine bad records; fix and re-ingest in controlled batches.
- Backfill vs reprocess: backfill = historical period with possibly new logic; reprocess = re-run the same logic on the same data to fix state.
- Deduplication: use natural keys, hashes, or window-based row_number to keep the latest or unique record.
- Audit and data quality gates: row counts, null checks, referential checks before publishing.
- Atomic publishes: write to a temp location/table, then swap in one step to avoid partial reads.
Worked examples
Example 1 — Batch re-run for a single day with MERGE
Scenario: A daily job loaded sales for 2025-05-06 with a bug. You fixed code and need to reprocess that date safely.
- Extract only 2025-05-06 from the source.
- Prepare a deduped staging set using row_number over (order_id, item_id) keeping the latest updated_at.
- Use a MERGE into fact_sales partition dt='2025-05-06' matching on (order_id, item_id).
- When matched and changed, update; when not matched, insert; when matched but identical, do nothing.
- Validate row counts vs source and key uniqueness after write.
Result: Re-running later produces the same final partition content (idempotent), avoiding duplicates.
Example 2 — Streaming recovery with offsets and dedupe
Scenario: A consumer crashed at 10:20. You want to reprocess from 10:00 to ensure completeness without duplicates.
- Reset consumer group offset to 10:00.
- Enable exactly-once-like behavior via idempotent sink: write to a staging table with a unique key (event_id) and upsert.
- Use a dedupe mechanism in the sink (primary key or MERGE) to ignore previously seen event_ids.
- Advance watermark only after catching up to current time, then resume normal processing.
- Monitor lag and DLQ; replay DLQ after validation.
Example 3 — Late-arriving data with watermark and finalization
Scenario: Events can arrive up to 2 hours late. Your daily aggregates must be correct by 03:00 next day.
- Set watermark to event_time minus 2 hours. Do not finalize windows still before the watermark.
- Produce provisional aggregates in a temp table; publish a stable snapshot once the watermark passes 23:59 of the day.
- If late data shows up before watermark passes, aggregates will adjust automatically; otherwise, route to a small backfill job.
Example 4 — Backfill a new dimension attribute safely
Scenario: You add customer_tier to dim_customer and need historical values for the past year.
- Create a backfill job scoped by month partitions to limit impact.
- Compute tier per customer per month; use MERGE into dim_customer with effective_from/effective_to for SCD2.
- Publish by swapping temp to prod after each month validates.
- Make the job resumable using a checkpoint on the last completed month.
How to choose a strategy
- If writes are append-only and duplicates are harmful → use MERGE/UPSERT or overwrite-by-partition.
- If streaming → prefer offset-based replay + idempotent sink + dedupe by unique event key.
- If aggregates are time-windowed → use watermarks and finalize after watermark passes.
- If data errors are frequent → DLQ with validated replay batches.
- If changes are massive → backfill by chunks (by date/partition) with checkpoints.
Exercises
Do these to internalize the patterns. They mirror the graded exercises below.
- Exercise 1: Write an idempotent MERGE for a day-level reprocess into a partitioned fact table.
- Exercise 2: Outline steps to replay 6 hours in a stream with offset reset and dedupe.
Practice checklist
- I can describe idempotency and implement it with MERGE/UPSERT or overwrite.
- I know how to use checkpoints/offsets to resume or replay.
- I can pick a sensible watermark for late data and explain the trade-off.
- I can design a DLQ replay flow that is safe and auditable.
- I can backfill in chunks with validation and atomic publish.
Common mistakes and how to self-check
- Re-running without idempotency: duplicates or double-counting. Self-check: verify unique keys and compare aggregates before/after.
- Overwriting too broadly: wiping good data. Self-check: scope reprocess by partition and preview row counts.
- Ignoring late data: window undercounts. Self-check: compute lag distributions, set watermarks accordingly.
- No atomic publish: readers see partial results. Self-check: stage outputs and swap.
- Incorrect checkpoint reset: lost or repeated records. Self-check: document the reset scope and dedupe strategy before running.
Practical projects
- Build a daily fact load with a reprocess flag that re-runs a chosen date using MERGE and validation reports.
- Create a streaming consumer that supports offset rewind and dedupes by event_id at the sink.
- Implement a DLQ table with a replay job that validates records, re-ingests them, and marks status transitions.
Learning path
- Before this: Incremental loads, partitioning, and basic orchestration.
- Now: Reprocessing and recovery patterns (this lesson).
- Next: Data quality checks, SLAs/SLOs, and cost-aware backfills.
Next steps
- Complete the exercises below and run the quick test.
- Implement a small backfill or replay in a sandbox to practice safely.
- Document your org’s standard operating procedure for reprocessing.
Quick Test
You can take this test for free. Sign in to save your score and progress.
Mini challenge
Your pipeline produced duplicate rows in a fact table after a retry storm last night. Draft a brief recovery plan that: 1) stops further writes, 2) identifies duplicates, 3) cleans via idempotent re-run, 4) validates business totals, 5) prevents a recurrence.