An ETL pipeline extracting from multiple sources on a schedule, why batch jobs must be designed idempotent for safe re-runs, and checkpointing large jobs so a failure doesn't force reprocessing everything from scratch.
Published September 23, 2026
Design a system that extracts data from multiple source systems, transforms it, and loads it into a destination (a data warehouse, per Data Warehouse / Analytics Storage Layer next in this chapter) on a recurring schedule — reliably, and safely re-runnable if a run fails partway.
Functional: extract data from multiple heterogeneous sources; apply transformation logic (cleaning, joining, aggregating); load the result into a destination store; run on a defined schedule. Non-functional: a partial failure must be recoverable WITHOUT reprocessing everything from scratch; re-running a job (deliberately, or via retry after failure) must not produce incorrect duplicated results; handle growing data volume without linearly growing job duration forever.
Extract: pull raw data from sources (a production database, a third-party API,
event logs) — ideally READ-ONLY against sources, minimizing impact
Transform: clean, join, aggregate, reshape the extracted data into the target format
Load: write the transformed result into the destination store
Keeping these as genuinely separate stages (not one monolithic extract-transform-load-in-one-pass script) is what makes each stage independently retriable and independently scalable — a transformation bug shouldn't require re-EXTRACTING from source systems again if the raw extracted data was already captured correctly, and a slow, complex transformation stage can be scaled/optimized independently of the extraction stage's own characteristics.
// WRONG — re-running this job twice for the same day DOUBLES the totals
INSERT INTO daily_sales_summary (date, total) VALUES (today, computedTotal);
// RIGHT — idempotent: re-running produces the SAME end result, not an additive one
MERGE INTO daily_sales_summary USING (SELECT today AS date, computedTotal AS total)
ON daily_sales_summary.date = today
WHEN MATCHED THEN UPDATE SET total = computedTotal
WHEN NOT MATCHED THEN INSERT (date, total) VALUES (today, computedTotal)
This is the SAME idempotency principle from Payment — Idempotency Implementation and Message Queue System's at-least-once-plus-idempotent-consumer reasoning, applied to batch jobs specifically: a batch job WILL eventually be re-run for the same period, either deliberately (reprocessing after fixing a bug) or accidentally (a retry after a partial failure) — a job that simply APPENDS/INSERTS results is NOT safe to re-run (it double-counts); a job designed around UPSERT/MERGE semantics (replacing the prior result for that period, rather than adding to it) produces the identical correct result no matter how many times it's re-run for the same input period.
Without checkpointing: job processes 10M records, fails at record 8M ->
re-run must reprocess ALL 10M records from scratch
With checkpointing: job records its progress (e.g. "processed through record
8,000,000") periodically -> a re-run RESUMES from the last checkpoint,
reprocessing only the remaining ~2M records
For a large batch job, reprocessing EVERYTHING after any failure (even one near the very end) is a real, expensive cost — checkpointing periodically records progress durably (a bookmark, a watermark, a last-processed-ID), and a re-run after failure resumes from the last checkpoint rather than starting over. This combines directly with idempotency: checkpointed resumption means SOME records might be reprocessed (those between the last checkpoint and the failure point) — which is exactly why the job ALSO needs to be idempotent, so this overlap doesn't produce incorrect duplicated results.
Q: How often should a job checkpoint — is there a cost to checkpointing too frequently?
A: Yes — writing a checkpoint itself has a real cost (a durable write), so checkpointing after EVERY single record adds significant overhead; checkpointing periodically (every N records, or every few minutes) balances resumption granularity against that overhead, similar in spirit to Kafka's producer batching trade-off (linger.ms/batch.size) between overhead and granularity.
Q: Does 'Extract' being read-only against source systems have any performance implications for those sources? A: Yes — a poorly-designed extraction (a full, unindexed scan of a large production table during business hours) can meaningfully degrade the SOURCE system's own performance; extraction often reads from a dedicated READ REPLICA (Database Scaling Specifics) specifically to isolate this load from the production system actually serving live traffic.
Q: How would you decide between this batch approach and the stream-processing approach from Real-Time Analytics Dashboard for a given use case? A: Exactly the trade-off named explicitly in that lesson — batch is simpler and more naturally resumable/idempotent (this lesson's whole focus), appropriate when some latency (hours, not seconds) is acceptable; stream processing trades that simplicity for near-real-time freshness when the use case genuinely requires it.
Q: What happens if the TRANSFORM logic itself has a bug discovered after a job has already run and loaded results? A: Because the load stage is idempotent (via UPSERT/MERGE), fixing the bug and simply RE-RUNNING the job for the affected period correctly overwrites the previously-incorrect results with correct ones — this is precisely why idempotent design matters beyond just failure recovery: it also makes deliberate reprocessing after a bug fix safe and straightforward, not a special, riskier operation.