Three concepts you need to know

These three ideas appear together in every reliability conversation about data pipelines. Understanding each one โ€” and how they interact โ€” determines whether your pipelines can be fixed when they break.

Core definitions
  • โ–ธ Backfill: re-processing historical data โ€” either to repair a past failure or to apply new business logic to data that was already processed with an old version of the pipeline.
  • โ–ธ Late data: events that arrive after the processing window has already closed โ€” a payment confirmation that arrives 45 minutes after the hour, or a click event that was buffered on a mobile device with no connectivity.
  • โ–ธ Idempotency: running a pipeline multiple times produces exactly the same result as running it once. The word comes from mathematics: f(f(x)) = f(x). In pipelines, it means re-running a job for the same date never creates duplicate rows.

Why idempotency matters โ€” a โ‚น2.4 crore story

At SouthBank India, Meera's loan interest pipeline ran twice one Monday morning due to a scheduler bug. The pipeline calculated and posted daily interest charges to customer accounts. Because it used mode("append") with no deduplication, every customer was charged interest twice for that day.

The non-idempotent pipeline doubled all charges. The fix required 3 weeks of manual reconciliation by the finance team, a coordinated reversal across 180,000 customer accounts, and ultimately a โ‚น2.4 crore write-off from processing errors and penalties.

Meera's lesson: "We assumed our Airflow scheduler would never trigger a DAG twice in one day. It did โ€” during a network partition that caused the scheduler to lose track of in-flight runs. Idempotency is not a nice-to-have; it is the difference between a boring Monday morning and a month-long incident."

A simple partition overwrite would have made the second run produce exactly the same output as the first. No double-charge. No incident.

Idempotent write patterns โ€” PySpark

The most common source of non-idempotency in PySpark batch pipelines is using mode("append") without deduplication. Every re-run appends another full copy of the data.

python Lesson 23 ยท idempotent write pattern

The WRONG pattern produces 2x rows after two runs, 3x after three, and so on. The RIGHT pattern produces the same row count no matter how many times you run it โ€” because Spark overwrites only the partitions present in the DataFrame.

Dynamic partition overwrite

By default, Spark's overwrite mode replaces the entire table, not just the partitions in your DataFrame. To overwrite only the specific partitions you are writing, set this config before your write:


spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")

With this setting, a re-run of January 15's data overwrites only the year=2024/month=01/day=15 partition โ€” all other partitions remain untouched.

How partition-level overwrite works

Imagine your Parquet table has 365 day-partitions for 2024. You discover a bug in the January 15 calculation. With dynamic partition overwrite, you fix the code, filter the source data to January 15, and re-run. Spark writes only to the year=2024/month=01/day=15 folder. All other 364 partitions are untouched. Re-run the same job 5 times โ€” the partition always contains exactly one copy of January 15's data.

Database upsert pattern

When writing to a relational database instead of Parquet, the equivalent of partition overwrite is an UPSERT: INSERT a row if the business key does not exist, or UPDATE if it does. This is also called "INSERT ON CONFLICT" in PostgreSQL.

sql Lesson 23 ยท PostgreSQL upsert

Re-running the same ETL job never creates duplicates. The txn_id is the dedup key โ€” if a row with that transaction ID already exists, Spark updates the fields rather than inserting a second row. The table always reflects the most recent values for each transaction, regardless of how many times the pipeline runs.

At NaijaCart, Arjun's team uses this pattern for their daily order reconciliation job. Even when the job is triggered manually by engineers during incident recovery, the database always ends up in a consistent state.

Late data in batch pipelines

When a Spark batch job reads "yesterday's data" from a Kafka topic or object storage, events from the previous day might still be arriving for hours after midnight. A mobile app that goes offline during a commute might buffer 30 minutes of click events and flush them when connectivity returns โ€” at 2 AM the following day.

Two practical strategies for handling this in batch:

Strategy How it works When to use
Window extension Instead of processing 00:00โ€“23:59, process 00:00 to 23:59 + 2 hours grace period. Events that arrive up to 2 hours late are included in the current day's run. When late events are predictable and arrive within a known window. Adds some over-counting risk if events are also in the next day's range.
Fix-up job Run a second "straggler" job 24 hours after the primary job. This job re-processes yesterday's partition to catch any events that arrived too late for the first run. When the pipeline is idempotent (partition overwrite or upsert). The fix-up job simply overwrites the partition with more complete data.
Meera's rule: "The fix-up job strategy only works if your pipeline is idempotent. If you are using mode("append"), the fix-up job doubles your data rather than correcting it."

Late data in streaming โ€” watermark recap

Lesson 21 covered Spark Structured Streaming's watermark mechanism in depth. A quick recap for context:

Watermark in one sentence

.withWatermark("ts", "30 seconds") tells Spark: track the maximum event timestamp seen so far, and drop any event whose timestamp is more than 30 seconds behind that maximum. This bounds state size by discarding events that arrived too late to be included in any open window.

The watermark threshold is a trade-off: a longer threshold (e.g., "5 minutes") catches more late events but holds state in memory longer and delays final window output. A shorter threshold (e.g., "10 seconds") uses less memory but may discard valid late events from slow producers.

For NaijaCart's mobile order stream, Funmi's team uses a 90-second watermark โ€” enough to handle mobile buffering delays without growing state unboundedly during high-traffic periods.

Backfill strategies

Not all backfills are the same. Choosing the wrong strategy for the situation is one of the most common causes of data incidents during pipeline remediation.

Strategy When to use Risk
Full reprocess Small dataset, new business logic that must apply to all historical data Slow but simple. No incremental complexity. Works well when the full dataset fits in a reasonable Spark job runtime.
Partition-level reprocess Partitioned data (by day/month), isolated failure affecting only specific partitions Safe for idempotent pipelines. Re-run only the affected date partitions. Fastest and most targeted approach.
Delta / incremental Large historical dataset, small bug that only affects certain records Complex โ€” requires change tracking to identify which records need updating. Higher risk of missing records if the change-tracking logic is wrong.
Manual correction Isolated data error in one or two records, not a code bug Risky โ€” bypasses the pipeline entirely. No automated validation. Should require sign-off from a second engineer and be logged in an incident ticket.

Funmi's backfill checklist

At NaijaCart, Funmi runs every backfill through this checklist before touching production data. Skipping any step has caused incidents for other teams on the platform.

Before running any backfill โ€” 5 checks
  • Confirm the pipeline is idempotent โ€” partition overwrite (Parquet) or upsert (database). If it is not, make it idempotent first before backfilling.
  • Run on a small partition first โ€” one day of data. Inspect the output manually before backfilling weeks or months.
  • Verify row counts match expected โ€” compare backfilled partition count against source record count for the same period.
  • Check downstream dependencies โ€” are reports, dashboards, or downstream pipelines reading from this output? They will see changing numbers during the backfill window.
  • Communicate to stakeholders โ€” inform the analytics and product teams that numbers will change temporarily. A surprise metric shift looks like a product bug to people who do not know about the backfill.

Idempotency test pattern

Before promoting a pipeline to production, run an automated idempotency test: execute the pipeline twice for the same date and assert that the output row counts are identical. This test should live in your CI pipeline alongside unit tests.

python Lesson 23 ยท idempotency test

If this assertion fails, your pipeline is appending instead of overwriting. Find every mode("append") call and replace it with a partition overwrite or upsert pattern.

At SouthBank India, Arjun added this test to the CI pipeline for every new data job after the โ‚น2.4 crore incident. A non-idempotent pipeline now fails in CI before it ever touches production data.

Common mistakes

Using mode("append") without dedup
The classic double-count bug. Every scheduler retry, every manual re-run, every incident recovery run adds a full duplicate of the data. This compounds โ€” a pipeline re-run 3 times produces 3x the correct row count. Always use partition overwrite or upsert.
Backfilling without partition isolation
Running a backfill with static overwrite mode replaces the entire table, not just the affected partitions. A fix for January 15 accidentally wipes February. Always set partitionOverwriteMode = dynamic and verify the affected partitions before running.
Not testing idempotency before production
Discovering non-idempotency after a scheduler double-fires is how โ‚น2.4 crore incidents happen. Run the two-pass assertion test in CI for every new pipeline. It takes 30 seconds and prevents weeks of reconciliation work.

Quiz โ€” 3 questions

Question 1 of 3
An idempotent pipeline is one that:
Question 2 of 3
To safely overwrite only one day's partition in a Parquet table using PySpark, you should:
Question 3 of 3
A scheduler bug causes your pipeline to run twice in one hour. Which write pattern keeps your data correct?
0
/ 3
Idempotency mastered.

Build the streaming aggregation pipeline in the next lesson.

Interview Q&A

Medium What is idempotency and why does it matter in data pipelines?
+

Idempotency means that running a pipeline multiple times for the same input produces the same output every time โ€” the second run is indistinguishable from the first. This matters because real-world schedulers, Kubernetes pod restarts, network partitions, and manual incident recovery all cause pipelines to run more than once. A non-idempotent pipeline that uses mode("append") doubles data on every extra run. Over time this compounds into stale aggregations, double-counted revenue, or charged-twice billing scenarios. The fix is structural: use partition overwrite (Parquet) or INSERT ON CONFLICT (relational databases), which make the second run overwrite or update the first run's output rather than appending to it.

Medium How do you handle late data in a batch pipeline?
+

Two main strategies. First, window extension: instead of processing exactly 00:00โ€“23:59, include a grace period (e.g., read until 02:00 the next day). This catches most stragglers that arrive hours after midnight. Second, a fix-up job: run a second pipeline job 24 hours after the primary job. This job re-processes yesterday's partition with any events that arrived too late for the first run. The fix-up approach requires the pipeline to be idempotent โ€” the second run must overwrite the first run's partition, not append to it. Ideally, SLAs are defined with business stakeholders to agree on what "late" means and what level of accuracy is acceptable given the trade-off in complexity.

Hard Walk me through a safe backfill strategy for a 2-year historical dataset.
+

Before touching production: (1) Confirm the pipeline uses partition overwrite or upsert โ€” make it idempotent if it is not. (2) Run a two-pass idempotency test in a staging environment. (3) Test on one day's partition in production and manually verify row counts. (4) Identify all downstream consumers of this data โ€” reports, dashboards, ML features โ€” and communicate that numbers will change during the backfill window. Then execute: partition-level reprocess is the right strategy for 2 years of partitioned data. Write a driver script that iterates over each monthly or weekly partition, runs the pipeline for that partition, and logs the row counts before and after. Process partitions in chronological order so downstream consumers see a consistent forward progression. Monitor resource usage: 2 years of data running simultaneously can overwhelm the cluster โ€” use concurrency limits or a queue. After completion, run automated data quality checks (row counts, null rates, key business metrics) to confirm the backfill is correct before notifying stakeholders.

Key takeaways

  • Idempotency means running a pipeline twice for the same input produces the same output โ€” not double the output. Make every pipeline idempotent before it goes to production.
  • In PySpark, achieve idempotency with partitionBy(...).mode("overwrite") and spark.sql.sources.partitionOverwriteMode = dynamic. In relational databases, use INSERT ON CONFLICT DO UPDATE with a business key.
  • Late data in batch pipelines is handled with window extension (read extra hours of data) or a fix-up job (re-run 24 hours later). Both strategies require idempotent writes.
  • Backfill safely: confirm idempotency first, test on one partition, verify row counts, check downstream dependencies, and communicate the change to stakeholders before running at scale.
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

Build pipelines that survive production

Join the Best AI Program: The AI-Era Data Engineer โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer โ†’