Draft a Backfill Runbook with Partition Ranges and Blast-Radius Checks

Tier: Foundation · Workflow area: Orchestration, ops & cost

The knowledge needed to plan one well (dependency order, partition math, cost estimation, what to check before and after) is exactly the kind of structured reasoning an LLM drafts well from your inputs. This playbook produces a reviewable backfill runbook: partition ranges, dependency-ordered execution, cost estimate, blast-radius checks, and an abort plan.

When to reach for this

• •
  A bug fix or logic change requires reprocessing weeks-to-months of historical partitions across dependent models.
• •
  Onboarding a new source that needs history loaded behind live increments.
• •
  An incident recovery where partial bad data must be replayed — under time pressure, which is precisely when a checklist beats memory.

For a single-partition rerun of one task, just rerun it — a runbook for one click is ceremony.

The workflow

1. 1.

   Gather the four inputs. (a) The change: what was wrong, what's fixed, which models are affected. (b) The affected date range and partition grain. (c) The dependency subgraph (dbt ls --select fixed_model+ --output json, or your DAG's task graph). (d) Operational constants: rough cost/runtime per partition per model (from recent runs), warehouse capacity windows, freshness SLAs of affected consumers.

2. 2.

   Prompt for the runbook, demanding the checks.

   Copy

   Draft a backfill runbook from the attached inputs (change description,
   dependency subgraph, partition grain and range, per-partition runtime/cost stats).
   
   Structure:
   1. Scope: exact models and partition range; explicitly list what is OUT of scope.
   2. Preconditions: fix deployed & verified on one partition; snapshot/backup of
      affected partitions where the store supports it; downstream consumers notified.
   3. Execution plan: dependency-ordered waves; within each wave, chunked partition
      ranges sized so one chunk finishes inside our capacity window. State the exact
      command per chunk (Airflow 3 backfill via UI/CLI or dbt build --select
      <model> --vars with partition variables).
   4. Cost & duration estimate per wave, from the attached per-partition stats;
      flag if total exceeds <budget>.
   5. Blast-radius checks: before/after row counts and one domain metric per model
      per chunk; a canary partition validated fully before proceeding to bulk.
   6. Abort/rollback plan: how to stop mid-wave, what state that leaves, how to
      restore from snapshot.
   Do not invent runtime numbers — derive only from the attached stats and show
   the arithmetic.
   

   Expected output: a runbook where every number traces to your stats and every wave has a check gate. The "show the arithmetic" rule makes the cost section auditable instead of decorative.

3. 3.

   Review the two things LLMs get wrong here. Dependency order (verify each wave's models depend only on earlier waves — diff against dbt ls output) and idempotency assumptions (the runbook assumes delete-insert/MERGE semantics; confirm each affected model actually has them — an append-only model in the list converts the backfill into a duplication engine).

4. 4.

   Run the canary, then the waves. Execute exactly one partition end-to-end, run the blast-radius checks, and get an explicit go/no-go with a named approver. Then proceed wave-by-wave with Airflow 3's backfill (the 3.x backfill UI/API makes chunked ranges first-class), checking gates per chunk. Late-night capacity windows come from the runbook, not from improvisation at 11 p.m.

5. 5.

   Close out with evidence. The completed runbook — with actual counts, actual costs, and check results pasted in — becomes the incident/change record and the template for the next backfill. Store it with the code change, not in someone's DMs.

Verify before you trust it

• •
  Wave ordering matches the real dependency graph (mechanically diffed, not eyeballed).
• •
  Every model in scope is confirmed idempotent per partition; any append-only model has an explicit dedup step in the plan.
• •
  Cost arithmetic reproduces from the input stats; total is inside the approved budget.
• •
  The canary's blast-radius checks pass and the go/no-go is recorded with a name and timestamp.
• •
  The abort path was actually rehearsed on the canary (stop, inspect state, resume) — an untested rollback plan is a hope.

Pitfalls

• Fabricated estimates. Without your runtime stats attached, the model will produce confident nonsense costs. Feed real numbers and demand shown arithmetic.
• Ignoring late-arriving-data semantics. Reprocessing partition D with today's source state can differ legitimately from the original run. The runbook must say which differences are expected.
• Backfilling into live consumers. Dashboards mid-backfill show mixed old/new data. Notify consumers, and where supported, use versioned/blue-green table swaps per wave.
• Chunk size set by optimism. One giant range saturates the warehouse and starves production ETL. Size chunks from measured per-partition runtime against your capacity window.
• Runbook drift during execution. If reality diverges (a wave fails, a check surprises), update the runbook and re-review — continuing on an obsolete plan is how "one more chunk" goes wrong.

Tools

• Claude (or another frontier LLM)drafts the runbook from your inputs; the value is structured completeness, not knowledge you lack.
• Airflow 3 backfill (UI/API)first-class chunked backfill execution with per-range control; the execution surface the runbook targets.
• dbt selectors (state: / graph operators)mechanical source of the dependency subgraph and per-wave build commands.

Client-safe variant

A backfill runbook contains schema names, cost figures, and incident context — all client-confidential, none of it needed by an external model if you abstract it: placeholder model names and relative cost units in the prompt, real identifiers substituted inside the client's environment. If even that is disallowed, use the runbook structure above as a manual template — the section list is the transferable asset. For consultancies, the closed-out runbook with evidence pasted in is exactly the artifact clients expect from a controlled change; delivering two of these tends to make the client's own team adopt the format.