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.
- โธ 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.
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.
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.
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.
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. |
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:
.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.
- 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.
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
partitionOverwriteMode = dynamic and verify the affected partitions before running.Quiz โ 3 questions
Build the streaming aggregation pipeline in the next lesson.
Interview Q&A
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.
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.
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")andspark.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.
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 โ