backfill-captain

Idempotent, resumable partition backfills: SIGKILL the run at any point, resume, and the warehouse is checksum-identical to a never-interrupted run (proven 38 kills for 38 in benchmark/results/kill_resume.json).

• Re-running a failed backfill double-counts or clobbers history: here every partition publishes as an atomic staging swap, so reruns converge to identical bytes (checksum-verified in tests).
• Nobody knows which partitions a crashed backfill actually finished: a transactional SQLite ledger records per-partition state and provenance, so resume does only the remaining work.
• Late-arriving data and logic changes force all-or-nothing reprocessing: input fingerprints and versioned transforms recompute exactly the affected partitions (7 of 90 in the committed benchmark, in 25% of the cold-run time).

A backfill is the most dangerous routine operation in data engineering: rewriting weeks or months of warehouse history while the business keeps reading it. When one goes wrong, the failure mode is not an error page, it is quietly wrong numbers: duplicated rows after a mid-run retry, a half-written day where a worker died, or a "fixed" transform applied to some partitions and not others. Teams that process high-volume telemetry (the semiconductor validation pipelines this project models are a representative case) routinely reprocess months of sensor history after a cleansing rule changes, and each manual restart is an opportunity to corrupt the exact data the fix was meant to repair.

backfill-captain makes the partition the unit of correctness. For each date partition, the engine computes the full replacement content in a staging table, then publishes it with DELETE + INSERT inside a single transaction: readers and crashes see the old partition or the new one, never a mix. A SQLite ledger records, transactionally, each partition's state plus the input fingerprint and transform version that produced it. The planner compares fingerprints against the live source, so a finished partition is skipped, a partition whose source grew (late arrivals) is recomputed, and bumping a transform's version recomputes history deliberately, not accidentally. An Airflow DAG adapter fans partitions out as dynamically mapped tasks over the same engine, which makes Airflow task retries safe by construction.

The guarantees are executed, not asserted. The crash-window test suite SIGKILLs a real backfill process at four distinct points (before staging, after staging, inside the publish transaction, after publish but before the ledger hears about it) and requires the resumed warehouse to be checksum-identical to an uninterrupted control run. The chaos benchmark repeats this at scale: 5 trials on a 120-partition, 3M-row history absorbed 38 random-timing SIGKILLs and every trial converged to the control checksum with reconciliation passing. On 2 vCPUs, a cold 90-partition backfill of 2.25M rows completes in 1.9s (1.2M rows/s), and a no-op rerun of the same range takes 0.31s.

flowchart LR
    subgraph sources
        RAW[(raw_sensor_events)]
    end
    subgraph controller["backfillctl (one controller per ledger)"]
        PLAN[Planner\nfingerprint vs ledger] --> EXEC[Executor]
        EXEC -->|1. build| STG[(staging table)]
        STG -->|2. atomic DELETE+INSERT| FACT[(fact_sensor_daily)]
        EXEC -->|3. DONE + provenance| LEDGER[(SQLite ledger\nWAL, per-partition state)]
        LEDGER --> PLAN
    end
    RAW --> PLAN
    RAW --> STG
    FACT --> RECON[Reconciler\nconservation + checksums]
    LEDGER --> RECON
    subgraph airflow["Airflow adapter (thin)"]
        A1[plan_partitions] --> A2[run_partition xN mapped] --> A3[verify]
    end
    A2 -.calls.-> EXEC

Failure handling lives at two boundaries: the publish transaction (a crash can never leave a partial partition in the fact table) and the ledger transaction (a crash can never record a state the warehouse does not eventually justify, because the rerun converges).

Prerequisites: Python 3.10+, git. Docker only if you want the Airflow UI.

git clone https://github.com/Panchalvedant13/backfill-captain.git
cd backfill-captain
python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"

# generate a 30-day synthetic history, run the backfill, verify it
backfillctl generate --start 2026-01-01 --end 2026-01-30 --rows-per-day 20000
backfillctl run      --start 2026-01-01 --end 2026-01-30
backfillctl verify

# prove idempotency to yourself: the rerun skips all 30 partitions
backfillctl run --start 2026-01-01 --end 2026-01-30

# simulate late-arriving data, watch the planner recompute only that day
backfillctl late --days 2026-01-15 --rows-per-day 500
backfillctl plan --start 2026-01-01 --end 2026-01-30

pytest                       # full suite, including SIGKILL crash windows
python benchmark/run_benchmarks.py       # reproduce the numbers below
python benchmark/kill_resume_proof.py    # reproduce the convergence proof

Airflow UI (optional): docker compose up -d, then open http://localhost:8080 and trigger backfill_captain with {"start": "2026-01-01", "end": "2026-01-30"}. Airflow tests: pip install "apache-airflow==2.10.5" --constraint "https://raw.githubusercontent.com/apache/airflow/constraints-2.10.5/constraints-3.11.txt".

Methodology: benchmark/run_benchmarks.py on 2 vCPUs (Intel Xeon class, 8 GB RAM, this repo's CI-sized container), seeded synthetic data, results committed at benchmark/results/benchmarks.json. Per-partition latencies are measured through single-partition engine.run() calls, which include planning and fingerprint overhead, the same cost an Airflow mapped task pays.

xychart-beta
    title "Per-partition latency vs partition size (30 partitions each)"
    x-axis "rows per partition" [5k, 25k, 50k]
    y-axis "latency (ms)" 0 --> 100
    line "p50" [51.3, 55.5, 67.9]
    line "p95" [83.5, 60.1, 78.3]
    line "p99" [92.4, 70.9, 80.4]

Where it degrades and why: per-partition cost has a roughly 50ms floor (connection setup, planning, fingerprint scan), so at 5k rows/partition the engine is overhead-bound (93k rows/s) and only approaches its 1.2M rows/s ceiling when partitions carry 25k+ rows or many partitions share one run() call. Very small partitions should be batched per engine invocation rather than mapped one-per-task. The 5k series' p95/p99 sitting above the 25k series is small-sample noise (30 partitions per series, and the 5k scenario runs first, paying first-touch costs), not a real inversion; the p50 trend is the signal.

Two records in docs/adr/:

• ADR-001: atomic delete-and-insert partition swap over MERGE/upsert (MERGE strands deleted rows; replacement converges by construction).
• ADR-002: SQLite over Postgres for the ledger, the boring choice, with the explicit trigger for revisiting: the first moment two controllers must write one ledger concurrently.

• Multi-node distributed execution. One controller process per ledger, partitions executed sequentially. Trigger for revisiting: when partition count times per-partition time exceeds the backfill SLA window (at the measured 55ms per 25k-row partition, a 2-year daily history completes in under a minute, so the trigger is far away for this workload).
• Cross-table dependency ordering (backfill A before B). Trigger: the first pipeline where a fact table reads another backfilled table instead of raw sources.
• Streaming/CDC ingestion. This tool repairs history; a different design owns the live edge.

Secrets: none required locally (embedded databases); connection strings for a production warehouse would arrive via environment variables, with a note that the deployment path is a cloud secret manager (AWS Secrets Manager / Vault), never files in the repo. Logging: structured JSON with partition keys and counts only, never row contents, so telemetry values (which can be trade-secret process data in a fab) stay out of log pipelines. The input fingerprint is a change detector, not a cryptographic integrity guarantee against an adversary who can write both the warehouse and the ledger; reconciliation exists to catch drift, not malice. CI runs on pinned actions with a pinned Airflow constraint file for reproducible dependency resolution.

The 90-partition benchmark crashed with Out of Range Error: Overflow in multiplication of INT32 (22 * 100000000) while every one of the 77 tests was green. The generator builds synthetic event ids as day_index * 100_000_000 + row_number(). DuckDB types a bound Python parameter as INT32 when it fits, so the multiplication ran in 32-bit arithmetic and overflowed at day index 22, before the result was widened to the BIGINT column it was being inserted into.

The diagnosis mattered more than the fix: the test fixtures used 6-day ranges, so the suite structurally could not cross the 2^31 boundary that any real, deep backfill range crosses immediately. That is precisely the failure class this whole tool exists to fight, logic that behaves on recent data and breaks on history, showing up inside the tool's own test data. The fix (commit dde3e70) casts the parameter to BIGINT before the multiply, and the regression test now generates a 31-day range and asserts ids stay unique past 2^31, so the boundary is inside the tested envelope forever.

• Postgres ledger implementation behind the existing Ledger interface, activated by the ADR-002 trigger (concurrent controllers), with SELECT ... FOR UPDATE SKIP LOCKED partition claiming.
• Partition-parallel execution against client-server warehouses (Snowflake/BigQuery), where the single-writer constraint is theirs to manage, not DuckDB's.
• A backfillctl diff command that shows row-level changes a recompute would make before publishing (dry-run staging without the swap).
• First metric to watch in production: ratio of input_changed recomputes to total partitions per week, which is the direct measure of upstream late-data behavior and the input to choosing partition granularity.