Idempotent Pipeline Design AI Prompt

Design idempotent data pipelines that can be safely re-run without producing duplicate or incorrect data.

Pipeline type: {{pipeline_type}} (ELT, streaming, batch scoring)
Storage target: {{target}} (database table, S3, data warehouse)
Re-run scenarios: {{scenarios}} (duplicate events, partial failure, backfill)

1. Idempotency definition:
   A pipeline is idempotent if running it multiple times with the same input produces the same output as running it once.
   All production pipelines should be idempotent to allow safe retries and backfills.

2. Techniques for idempotency:

   UPSERT (INSERT OR UPDATE):
   - Use MERGE or ON CONFLICT for database targets
   - Requires a unique key per record
   - Safe to run multiple times: existing rows are updated, new rows are inserted

   Delete + reinsert for partitioned tables:
   - Delete all rows for the partition being processed, then re-insert
   - DELETE FROM orders WHERE date = '2024-01-15'; followed by INSERT
   - Atomic if done in a single transaction

   Deduplication after load:
   - Load all records including duplicates into a staging table
   - Final table: SELECT DISTINCT ON (primary_key) ... ORDER BY updated_at DESC

   S3 key naming for idempotency:
   - Use deterministic paths: s3://bucket/year=2024/month=01/day=15/run_id=20240115T120000Z/
   - Overwriting the same S3 key produces a deterministic result
   - Avoid: appending to existing files (non-idempotent)

3. Partitioned backfill:
   - Process one time partition per pipeline run
   - Parameter: execution_date → determines which partition to process
   - Backfill: run the pipeline for each historical date partition
   - Airflow: dbt run --vars '{"execution_date": "2024-01-15"}'

4. Testing idempotency:
   - Run the pipeline twice for the same input date
   - Verify: row count is the same after the second run
   - Verify: no duplicate rows in the output (run unique test on primary key)

Return: idempotency technique for each storage target, backfill pattern, partition-based processing, and idempotency test design.