Idempotency And Backfills

Why this matters

As a Data Architect, you must ensure data pipelines can be re-run without corrupting data and that historical corrections (backfills) are safe, fast, and verifiable. Real tasks you will face:

• Designing pipelines that can reprocess a day, month, or the entire history without creating duplicates.
• Defining unique keys and MERGE strategies for facts and dimensions.
• Choosing partitioning and watermarking for incremental loads.
• Running backfills after schema changes, late-arriving data, or logic fixes, with rollback plans.
• Validating outcomes with row-level checks and aggregate reconciliations.

Concept explained simply

Idempotency

Idempotency means: running the same job multiple times produces the same final state. If your job is idempotent, retries or backfills do not multiply rows, inflate metrics, or lose updates.

• In practice: use deterministic keys, deduplicate before writing, and write with upsert/merge or partition-overwrite semantics.
• Target: exactly-once outcomes, even if the runtime is at-least-once.

Backfills

Backfills are controlled reprocessing of historical data to fix errors, apply new logic, or load missed data. Safe backfills are:

• Scoped (by partition or time range),
• Repeatable (can re-run without harm),
• Observable (validated with metrics and samples),
• Reversible (rollback plan exists).

Mental model

Core building blocks

• Business keys: stable, deterministic identifiers (e.g., order_id + line_id). Avoid using random or run-specific IDs.
• Dedupe windows: remove duplicates using ROW_NUMBER() over business key ordered by event time or source commit time.
• UPSERT/MERGE: update when matched and insert when not. Compare hashes to update only when fields changed.
• Partition overwrite: rebuild a full partition and atomically replace it to guarantee idempotency.
• Watermarks: track the last processed event time or source position (e.g., LSN). Support late data with a small lag window.
• Change data capture (CDC): order by source commit time; apply upsert/delete with last-write-wins or true source ordering.
• Soft deletes: model deletes as is_active=false for simpler idempotency and auditability.
• Validation: row counts by partition, distinct key counts, checksums/hashes, sample spot checks.

Worked examples

WITH src AS ( ... ),
src_dedup AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY order_id, line_id ORDER BY event_time DESC) AS rn
  FROM src
)
MERGE INTO fact_order_lines t
USING (SELECT * FROM src_dedup WHERE rn=1) s
ON (t.order_id=s.order_id AND t.line_id=s.line_id)
WHEN MATCHED AND (t.row_hash != s.row_hash OR t.is_active != s.is_active) THEN
  UPDATE SET qty=s.qty, price=s.price, is_active=s.is_active, row_hash=s.row_hash, updated_at=CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (order_id,line_id,qty,price,is_active,row_hash,created_at)
  VALUES (s.order_id,s.line_id,s.qty,s.price,s.is_active,s.row_hash,CURRENT_TIMESTAMP);

Exercises

These mirror the graded exercises below. Do them here first, then submit in the Exercises section for feedback.

• I identified the business keys.
• I included dedupe and a deterministic write strategy.
• I documented validation metrics and rollback.

Common mistakes and how to self-check

• Using random IDs per run: breaks idempotency. Self-check: can I re-run without increasing row count?
• No dedupe before merge: duplicate source rows cause churn. Self-check: enforce ROW_NUMBER()=1 per key.
• Updating when nothing changed: unnecessary rewrites. Self-check: compare row_hash to skip no-op updates.
• Choosing ingestion_time as watermark when business logic needs event_time: late data lost. Self-check: simulate late arrivals; do metrics remain correct?
• Backfilling without isolation: users see partial data. Self-check: use staging/shadow tables and atomic swap/partition overwrite.
• No rollback plan: risky. Self-check: snapshot target or keep last N versions to revert.

Self-check before running a backfill

• Scope is precise (partitions/time range listed).
• Write path is idempotent (merge/overwrite documented).
• Dedupe keys and order (event_time or commit_time) are defined.
• Validation queries and acceptance thresholds are written.
• Rollback plan and timebox are defined.
• Stakeholders informed about window of change.

Practical projects

• Build an idempotent ingestion pipeline: file -> staging -> warehouse with MERGE and backfill for one month.
• Add late-data handling: watermark with a 2-day lag; measure the impact on final metrics.
• Create a backfill playbook: a template with steps, queries, and checklists your team can reuse.

Who this is for

Data Architects, Senior Data Engineers, and Platform Engineers who design and operate ETL/ELT systems and need reliable reprocessing.

Prerequisites

• Comfort with SQL (including MERGE/UPSERT, window functions).
• Basic understanding of partitions, file formats, and batch/streaming concepts.
• Familiarity with source systems (e.g., CDC, APIs, event streams).

Learning path

1. Understand keys and dedupe patterns.
2. Practice MERGE and partition-overwrite strategies.
3. Implement watermarks and late-data policies.
4. Run a small, scoped backfill with full validation.
5. Document and automate your backfill process.

Next steps

• Automate a daily partition overwrite job for a derived table.
• Add job-level idempotency checks (e.g., no-op detection, idempotency tokens).
• Introduce a sandbox backfill workflow with approval and rollback gates.

Mini challenge

You discover a pricing bug affecting 7 days of sales facts. Outline a plan that includes: isolation, recomputation scope, validation queries (three checks), and rollback. Keep it to 10 steps or fewer.

Quick test and progress

The quick test is available to everyone. Sign in to save your progress and resume later.