---
name: backfill-safety
description: "Plans a safe historical reprocess of a data pipeline — bounded per-partition batches, idempotent (re-runnable) writes, no full-table lock or clash with the scheduled incremental run, cost/rate awareness, and a watermark that is NOT advanced. Use when someone needs to recompute, repopulate, or reprocess history in a derived table, distinct from the normal incremental load. Do NOT use for reviewing a schema migration/DDL, tuning a slow query, or designing the incremental job itself."
metadata:
  decimalai-skill-id: "9fc18d1a-05cf-4228-a8ad-51bbde01b2aa"
  decimalai-version-id: "ec2e463f-af6e-40ef-9e76-b250d3aef454"
  decimalai-version: "1"
  decimalai-body-sha256: "90324131962616c6dcebb4d8384af0645e7246ad245e37b77eeef8d4c8d9f9a1"
  decimalai-name: "backfill-safety"
  decimalai-format: "1"
  decimalai-invocation: "model"
  decimalai-category: "data"
  decimalai-source: "https://app.decimal.ai/s/backfill-safety@1/SKILL.md"
---
# Backfill Safety

Plans and reviews a **historical reprocess** — re-running a data pipeline over past data — before it touches production. A reprocess happens when a logic bug corrupted old output, a new column needs historical values, or a definition changed and the derived table must be rebuilt for a past range. The target is a run that finishes without an outage, without corrupting good data, without breaking the live scheduled job, and without a surprise bill.

This is a different job from the **incremental run** the pipeline does every day. The incremental run is small, moves forward only, appends or upserts the latest slice, and advances a cursor so the next run knows where to resume. The reprocess is large, aimed at a fixed range in the past, will be re-run partition-by-partition (retries, overlaps), and must leave the live cursor exactly where it found it. The base model knows these ideas but does not apply them by default: asked to "backfill this table" it tends to reach for one whole-table `DELETE`-then-`INSERT`, an appending `INSERT ... SELECT`, or re-pointing the incremental job at an old start date — each of which is a footgun here.

## When to Activate

Activate when the user wants to:
- Recompute / rebuild / fix a derived table for a past date range
- Repopulate a newly added column across historical rows
- Re-run an aggregation or transform over months or years of history
- Reprocess after a logic, schema, or definition change
- Review a proposed backfill script, or debug one that produced duplicates or dropped data

Do **not** activate for reviewing a schema migration (that is `migration-safety-review`), tuning a slow query's plan, or designing the ongoing incremental load.

## The Six Checks

Run every reprocess through all six. A "no" on any one is a finding.

### 1. Bounded per-partition batches — not one whole-table statement
Split the work along the table's natural grain (day, month, hour, key range, tenant) and process one unit at a time, committing after each. One statement over all of history holds locks for its whole duration, can exhaust memory or temp space, bloats the table, and cannot resume — a failure at 90% redoes everything. Batching also makes the run **resumable**: a failed unit re-runs on its own, and you can checkpoint progress by partition. Wrapping the whole loop in a single transaction defeats the point — there is no commit between units, so it is back to one long lock with no resume.

### 2. Idempotent writes — re-running a unit changes nothing
You *will* re-run units (a batch dies halfway, ranges overlap, someone reruns the job). So each unit must land the same result whether it runs once or five times. Safe shapes:
- **Partition-scoped delete-then-insert**: `DELETE` only the unit's rows, then insert them — inside one transaction per unit.
- **Upsert / `MERGE`** on a stable business key.
- **Partition replacement / `INSERT OVERWRITE`** where the engine swaps a whole partition atomically.

A bare `INSERT ... SELECT` that only appends is the classic trap: a re-run doubles that partition's rows. If someone reports "I reran the batch that died and now the numbers are doubled," this is the cause — the write appended instead of replacing its own partition.

### 3. No collision with the scheduled incremental run
The live job and the reprocess must never write the same partition at the same time, and the reprocess must not take a lock that stalls the live job. Pick one:
- **Pause the schedule** for the reprocess window, run, then resume.
- **Write to a staging/side target**, validate, then swap it in (view flip, partition swap, `RENAME`).
- **Partition-scope so they never overlap** — reprocess old history while the incremental job only ever touches today.

Never a full-table lock on a table the live pipeline or dashboards are using.

### 4. Do NOT advance the production watermark
The incremental job tracks a high-water mark (a max `updated_at`, a last-loaded partition, a Kafka offset) so it knows where to pick up. A reprocess over *old* data must leave that cursor untouched. If the reprocess pushes the cursor to the end of its historical range, the next incremental run resumes from there and **silently skips every record between the pipeline's real position and the reprocess's endpoint** — fresh data quietly lost. The reprocess reads its own fixed `[start, end]` window; the live watermark is read-only to it. Re-pointing the incremental job at an old start date is the same mistake in disguise: it reprocesses forward *and* moves the cursor — use a separate bounded job instead.

### 5. Cost and rate are known before the run
Reprocessing all of history can scan terabytes (warehouse bytes-scanned billing) or fire thousands of calls at a source API or database. Estimate the bytes / rows / calls first, prune to the partitions that actually need it, cap concurrency, and throttle. Prefer a bounded window over "all time." A reprocess that runs the bill up or rate-limits the source into failures is a failed reprocess even if the output is correct.

### 6. Bounded window, verifiable, reversible
State the exact `[start, end]` range up front. Have a check that the output is right — per-partition row counts or a checksum against an expected value — and, before overwriting production, a way back: a snapshot, or the staging-then-swap pattern from check 3, so a bad reprocess can be discarded instead of having overwritten the only copy.

## Output

Present findings as a table, then a verdict.

| # | Severity | Check | Finding | Safe alternative |
|---|----------|-------|---------|------------------|
| 1 | 🔴 Block | Idempotency | Appending select re-runs will duplicate a partition's rows | Delete the partition's rows, then insert, per unit |
| 2 | 🔴 Block | Watermark | Run pushes the live cursor to the range end, so the next incremental run skips new data | Leave the cursor untouched; use a separate bounded window |

**Verdict:** SAFE TO RUN / RESTRUCTURE FIRST / BLOCK — one line naming the single biggest reason.

## Examples

### Input
> Our nightly job recomputes a daily-summary table from raw events. A rounding bug affected the last 60 days, so we want to fix those days in production. The proposed one-statement plan truncates the whole summary table and rebuilds every day it has ever held from the raw source. The job also has a `last_processed_date` it moves forward at the end. Review before we run it.

### Expected Output

| # | Severity | Check | Finding | Safe alternative |
|---|----------|-------|---------|------------------|
| 1 | 🔴 Block | Bounded batches | Deleting and rebuilding the entire table processes far more than the 60 affected days in one locked statement, and cannot resume | Loop over the 60 days, one day per transaction, committing between them |
| 2 | 🔴 Block | Idempotency / scope | Wiping all rows destroys good history to fix 60 days | Delete then reinsert only each day in the affected range |
| 3 | 🔴 Block | Watermark | Moving `last_processed_date` forward makes the next nightly run skip days after the range | Leave `last_processed_date` where it is; drive the reprocess from a fixed range |
| 4 | 🟡 Warn | Collision | Running against the live table while the nightly job may fire risks two writers on one partition | Pause the schedule for the window, or stage and swap |

**Verdict:** RESTRUCTURE FIRST — scope to the 60 affected days, keep it idempotent per day, and do not touch the live cursor.

## Edge Cases

- **Small, recent, single-partition reprocess** (a few thousand rows on a small table, no live schedule contending): batching and staging are over-engineering — an idempotent single replace is fine. Say so instead of flagging.
- **Append-only fact with no natural key**: idempotency needs a partition-replace or a dedup key; a plain re-insert cannot be made safe by itself — call that out.
- **`--full-refresh` on an incremental model** (dbt-style): this rebuilds the whole model in one shot — same whole-table lock and cost hazard as check 1. Prefer a partition-bounded reprocess, or run the full rebuild into a staging target and swap.
- **Late-arriving data**: a fixed `[start, end]` window can miss records that landed after the window closed — note when the reprocess and the incremental job must overlap by design, and keep that overlap idempotent.
- **Downstream consumers**: dashboards or models reading the table mid-reprocess can see half-rewritten data — favor atomic partition swap or staging-then-swap over in-place row churn.

## Evaluation Criteria

A good execution of this skill should:
- [ ] Split the work into bounded per-partition units that commit independently, not one whole-table statement or one giant transaction
- [ ] Make each unit idempotent (partition-scoped replace / upsert), so a re-run does not duplicate rows
- [ ] Keep the reprocess from clashing with the scheduled incremental run (pause, stage-and-swap, or non-overlapping partitions)
- [ ] Leave the production watermark / cursor untouched, and reject re-pointing the incremental job at an old start date
- [ ] Raise cost or source-rate exposure before running a large historical range
- [ ] Not over-engineer a small, recent, single-partition reprocess