Question

A retail analytics team uses Cloud Composer (Apache Airflow) to orchestrate a daily batch pipeline that reads the previous day's sales files from Cloud Storage, transforms them in Dataflow, and appends results into a BigQuery table partitioned by date. Occasionally a task fails midway and Airflow automatically retries it. The team also frequently runs manual backfills for past dates. They notice that retries and backfills sometimes produce duplicate rows in BigQuery. What is the best way to redesign the pipeline so that retries and backfills remain safe?

Accepted Answer

Make each run idempotent by having the load step overwrite (WRITE_TRUNCATE) the specific date partition it processes, using the Airflow execution date to target the partition.

Answer

Set the Airflow task retries to 0 so that failed tasks never re-run and cannot create duplicates.

Answer

Switch the pipeline to streaming ingestion with Pub/Sub exactly-once delivery to eliminate duplicates.

Answer

Add a downstream scheduled query that periodically deduplicates the table using ROW_NUMBER() to remove duplicate rows.