Building Resumable ETL Pipelines That Repair, Not Corrupt

Anyone can write a script that downloads data once. The hard part — and the part that separates a pipeline you babysit from one you forget about — is what happens on the second run. Does it re-download everything? Does it duplicate rows? Does a half-finished run leave the dataset in a worse state than before it started? Over building roughly 28 ingestion pipelines for a market-data platform, I converged on a small set of patterns that make reruns boring. Boring is exactly what you want.

Fetch only what is new

The foundation of every reliable pipeline I build is incremental loading. Take the price pipeline as an example: it tracks roughly 1,665 tickers, and on each run it resumes every ticker from its last stored bar and fetches only the new data since then. The first run is a full history pull; every run after that touches a few new rows per symbol. This single decision changes the economics of the whole system. A full re-download of twenty years of prices for 1,665 symbols is an overnight job that hammers the source. An incremental refresh is a few minutes and a polite number of requests.

Incremental loading also makes the pipeline naturally resumable. If the last stored bar is the source of truth for where to continue, an interrupted run does not need a separate checkpoint file — the data itself is the checkpoint. Restart it and it asks each ticker "what is the latest bar you have?" and carries on.

Slow down when the source pushes back

External sources rate-limit you, and they rarely do it politely. You get an HTTP 429, an auth error, or — worst of all — a silent throttle where requests still return but the data quietly degrades. A pipeline that ignores this will either get banned or corrupt its own dataset with partial responses.

My answer is adaptive concurrency. The price downloader runs on a concurrent.futures thread pool, but the pool size is not fixed. On an HTTP 429 or auth error it drops workers and pauses for 30 seconds, then — once it sees 15 clean calls in a row — it ramps concurrency back up. The system speeds up when the source is healthy and slows down the moment it pushes back, without any manual tuning. This is the difference between a downloader that respects rate limits and one that fights them.

Reruns must repair, not corrupt

This is the rule I care about most. A rerun should always move the dataset toward correctness. If a previous run left a gap, the rerun fills it. If it wrote a bad row, the rerun fixes it. It should never be possible for running the pipeline again to make the data worse.

Concretely, that meant building data-quality safeguards directly into the load step: purging rows with NaN prices, merging duplicates on a (Date, Symbol) key, and consolidating duplicate files that accumulate over time. Because these run on every load, the dataset self-heals. You can rerun after a crash, after a bad source response, or just out of paranoia, and the result converges instead of drifting.

Deduplication deserves special care because real-world keys are messy. The same record can come back with numeric drift — 629221 one time, 629221.0 the next. A normalized composite-key anti-join collapses that drift so overlapping fetch windows never double-count. I wrote about that in more detail in the post on scraping NSE insider filings, where overlapping windows are unavoidable.

Handle format changes without breaking history

Sources change on you. Midway through 2024, NSE changed the URL format for its F&O bhavcopy files (the move to UDiFF). A brittle pipeline would simply start 404-ing and silently stop ingesting. Mine detects which format applies for a given date and handles both, streaming each ZIP from memory into per-day CSVs. The historical data keeps flowing and the new format is picked up transparently. Designing for the source to change — rather than assuming it never will — is what keeps a backfill from rotting.

The payoff: unattended pipelines

Put these together — incremental loads, adaptive backoff, self-healing reruns, and tolerance for format changes — and you get pipelines that run unattended. That is the whole point. I am not watching dashboards waiting to manually restart a failed job; the pipeline restarts itself from the data, paces itself against the source, and repairs any damage on the next run. The same patterns generalize well beyond market data: the resilient-ingestion approach carried directly into the real-time scraping in Product Explorer, where queues and caching play the role that incremental loads play here.

If you take one idea away, make it this: design for the second run, not the first. The first run always works in a demo. The second run is where reliability lives.