1. The Problem
When an Airflow task crashed halfway through writing a 1M row batch to target storage, retrying the task appended duplicate records, creating 400,000 duplicate rows.
2. What I Initially Thought
3. What I Learned
Pipelines must be idempotent: executing a pipeline N times with the same input must produce the EXACT same result as running it once. Use atomic partition overwrites instead of appends.
4. What I Built
Idempotent write module in PySpark using `spark.sql.sources.partitionOverwriteMode = dynamic` to overwrite target partitions atomically during task retries.
# Configure Spark for Idempotent Dynamic Partition Overwrite
spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")
# Overwrites ONLY the target date partition atomically -> No duplicates on retry!
df.write \
.mode("overwrite") \
.partitionBy("process_date") \
.parquet("abfss://silver/tasks/")5. The Experiment
Task failure during write appended 400,000 duplicate rows on Airflow retry.
Configured PySpark `partitionOverwriteMode=dynamic` for atomic partition replacement.
Task retries execute safely 100% of the time with zero duplicate records created.
6. What Went Wrong
Used standard `mode('overwrite')` without dynamic partition mode, which wiped out the ENTIRE table directory instead of the specific date partition.
7. Engineering Decision & Trade-offs
Mandated dynamic partition overwrites or Delta Lake MERGE INTO statements as mandatory standard for all write operations.
8. What I Would Do Differently in Production
Ensure Airflow tasks pass execution date parameters (`{{ ds }}`) down to transformation scripts to maintain deterministic partition targets.
Questions I Can Now Answer Confidently in an Interview:
- What does idempotency mean in the context of data engineering pipelines?
- How does dynamic partition overwrite differ from static table overwrite in Apache Spark?
- How do you design Airflow DAGs that can be safely backfilled or retried without duplicate data?