Resumable ETL Orchestrator

Project roadmap

1. 01

   Design the pipeline model

   ~2h

   Write DESIGN.md: a pipeline is an ordered list of stages, each a Python function taking the previous stage's output (a pandas DataFrame) and returning its own. A run has an id, a status, and a record per stage (status, attempts, start and end, checkpoint path, error). Decide which errors are retryable (network, timeouts) and which aren't (bad data). Store run state in SQLite. Done when: DESIGN.md describes the model, states and error handling.

2. 02

   Run stages in order

   ~2.5h

   Implement a runner that executes stages one after another, recording each stage's status in SQLite. Build the example pipeline: extract sales CSV files (from a folder or URL), clean them (types, missing values, duplicates), transform them (daily totals per product and region) and load the result into a SQLite table. Done when: a full run succeeds and every stage's record shows succeeded.

3. 03

   Save checkpoints

   ~2h

   After each stage succeeds, save its output as a Parquet file in runs/<run id>/<stage>.parquet, writing to a temporary name and renaming it, so a crash never leaves half a file. Also save a hash of the stage's settings, so a changed stage is not skipped by mistake. Done when: after a run, every stage has a checkpoint file.

4. 04

   Resume failed runs

   ~2.5h

   Implement resume(run_id): skip stages that succeeded and whose checkpoint and settings hash are still valid, load the last checkpoint, and continue from the failed stage. Make the load stage idempotent (replace that run's rows inside a transaction), so repeating it never duplicates data. Done when: a pipeline made to fail at the transform stage resumes from transform after a fix, without re-running extract and clean.

5. 05

   Retry temporary errors

   ~1.5h

   Wrap each stage in retry logic: retryable errors are retried up to a configurable number of times with exponential backoff and jitter, and every attempt is recorded. Non-retryable errors fail the stage immediately. Add a timeout per stage. Done when: a stage that fails twice with a timeout then succeeds finishes on its own, and a data error fails at once.

6. 06

   Add the FastAPI control service

   ~2.5h

   Build a FastAPI app with POST /runs (start a pipeline in a background worker), GET /runs and GET /runs/{id} (status and stages), GET /runs/{id}/logs and POST /runs/{id}/resume. Prevent two resumes of the same run at once with a lock in the database. Use Pydantic models for every response. Done when: runs can be started, watched and resumed through the API docs page.

7. 07

   Test failure and recovery

   ~2.5h

   Write pytest tests: a normal run, failure and resume skipping finished stages, a changed stage setting forcing a re-run, retries with a fake flaky stage, the idempotent load (resuming twice gives the same row count), a crash while writing a checkpoint, and the API endpoints with FastAPI's TestClient. Done when: pytest passes.

Resources

• DocsFastAPI tutorial
• DocsFastAPI background tasks
• Docspandas user guide
• Docspandas: reading and writing Parquet
• ArticleExponential backoff and jitter (AWS)