Designing Idempotent and Recoverable Pipelines

Quick Answer: Idempotent pipelines produce the same result when run multiple times. Use MERGE INTO for upserts, INSERT OVERWRITE for full refreshes, and checkpoints for streaming recovery. Avoid INSERT INTO without deduplication logic.

What Is Idempotency?

An operation is idempotent if executing it multiple times produces the same result as executing it once.

Idempotent Patterns

Pattern 1: MERGE INTO for Incremental Updates

-- Safe to rerun: updates existing, inserts new, no duplicates
MERGE INTO silver.orders t
USING bronze.raw_orders s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Pattern 2: INSERT OVERWRITE for Full Refresh

-- Safe to rerun: replaces everything with the same result
INSERT OVERWRITE gold.daily_summary
SELECT order_date, SUM(amount) AS total
FROM silver.orders
GROUP BY order_date;

Pattern 3: CREATE OR REPLACE TABLE

-- Safe to rerun: atomic table replacement
CREATE OR REPLACE TABLE gold.customer_summary AS
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM silver.orders
GROUP BY customer_id;

Anti-Pattern: Non-Idempotent INSERT INTO

-- DANGEROUS: Creates duplicates if rerun
INSERT INTO silver.orders
SELECT * FROM bronze.raw_orders;
-- First run: 1000 rows
-- Second run: 2000 rows (duplicates!)

Fix: Add Deduplication

-- Fix with MERGE (dedup on insert)
MERGE INTO silver.orders t
USING bronze.raw_orders s ON t.order_id = s.order_id
WHEN NOT MATCHED THEN INSERT *;

Recoverability

Streaming Recovery

# Checkpoints enable exactly-once recovery
(stream_df.writeStream
    .option("checkpointLocation", "/checkpoints/my_stream")
    .trigger(availableNow=True)
    .toTable("silver.events")
)
# If the stream fails midway:
# - Restart the same code
# - Checkpoint tells Spark where it left off
# - Processing resumes from the last committed batch

Batch Recovery

# For batch pipelines, use Delta Lake transactions for atomicity
try:
    # This is atomic: either all rows are written or none
    df.write.mode("overwrite").saveAsTable("silver.daily_data")
except Exception as e:
    # The table remains in its previous state
    print(f"Write failed: {e}")
    # Safe to retry — overwrite is idempotent

Design Principles for Production Pipelines

On the Exam: Know which operations are idempotent (MERGE, INSERT OVERWRITE, CRAS, Auto Loader, COPY INTO) and which are not (INSERT INTO without dedup). Understand that checkpoints enable exactly-once streaming recovery.