Plan a Safe Backfill

English | 中文

Produce an execution-ready plan without performing the backfill. Treat planning and execution as separate user intents.

• If the user asks for a plan, analysis, or review, return only the plan.
• If the user explicitly asks to execute, first return the complete dry-run scope and unresolved risks. Do not start writes in the same step.
• Before any irreversible write, require explicit confirmation that identifies the datasets, partitions, write semantics, and code version being approved.

This Skill is platform-neutral. Do not assume an orchestrator, storage engine, deployment system, environment, or default execution command.

Required Inputs

Collect or clearly mark as unknown:

1. Root dataset: the first dataset whose historical output must change, its grain, business key, and partition column.
2. Partitions and time zone: inclusive start and end, partition format, business time zone, daylight-saving behavior, and any partial-day boundary.
3. Code versions: the version that produced the bad data and the immutable version proposed for recomputation. Record configuration or schema versions when they affect output.
4. Write semantics: overwrite, append, or merge, including the exact replacement or match boundary. Treat append as unsafe until duplicate prevention is demonstrated.
5. Dependency cascade: direct and transitive downstream datasets, partition mapping, required ordering, fan-out, and explicit exclusions.
6. Idempotency: whether repeating each step produces the same result, how retries are detected, and how partial completion is reconciled.
7. Retention windows: source-data availability, late-arrival window, metadata or lineage retention, log retention, snapshot retention, and the latest safe completion time.
8. Resources and concurrency: estimated input size, compute and storage cost, batch size, rate limits, shared-resource contention, overlapping scheduled work, and maximum parallelism.
9. Validation: expected grain and keys, row-count or completeness checks, null and duplicate checks, conserved measures, control partitions, and consumer acceptance criteria.
10. Rollback: restorable snapshots or versions, rollback owner, restoration sequence, time limit, and what cannot be reversed.

Do not invent missing lineage, row counts, uniqueness guarantees, retention, or rollback capability. Ask focused questions when an unknown can materially change safety. Otherwise, preserve the unknown in the plan and make it a gate.

Use references/backfill-checklist.md for the detailed decision checklist.

Planning Method

1. Define the smallest bounded root scope.
2. Build a dataset-and-partition dependency graph from the root outward.
3. Assign each dataset a write semantic, idempotency assessment, validation gate, and rollback method.
4. Order work topologically. Keep downstream recomputation behind successful upstream validation.
5. Design a read-only or non-publishing dry run that proves partition selection, dependency resolution, code/config resolution, estimated work, and intended writes.
6. Start with a canary partition and a known-good control partition. Expand only after validation passes.
7. Split the remaining scope into batches with explicit concurrency and resource limits.
8. Define batch-level monitoring, stop conditions, ownership, and rollback checkpoints.
9. End with approval gates. Separate approval to publish reversible results from confirmation of any irreversible write.

For append, require a deterministic deduplication or anti-join strategy and a retry-safe commit protocol. For merge, require stable match keys and duplicate-source handling. For overwrite, verify that the write boundary cannot replace partitions outside scope and that concurrent writers are excluded or coordinated.

Required Output

Use these headings in this exact order. Include every heading even when details are unknown.

Scope

State the root dataset, grain, business key, partition range and time zone, bad and fixed code versions, write semantics, affected dependency set, exclusions, retention constraints, assumptions, and unresolved gates.

Order

List the topological dataset and partition sequence. Place validation gates between the root and every downstream layer. Explain partition mapping when downstream partitions differ from the root.

Dry-Run

Describe the non-publishing preview: selected partitions, resolved code/config versions, dependency expansion, write intents, estimated rows/bytes or another bounded work estimate, resource estimate, conflicts, and expected validation queries or checks. A dry run must not mutate published data.

Execution Batches

Define the canary, control, subsequent batch boundaries, maximum concurrency, rate limits, retry behavior, idempotency protections, checkpoints, and approval required to advance. Provide procedural steps, not a default platform command.

Validation

Define pre-run baselines and post-batch checks for grain, key uniqueness, nulls, completeness, row counts, conserved measures, freshness, schema compatibility, downstream reconciliation, and consumer acceptance. Include tolerances and who decides pass or fail.

Monitoring

Specify progress, runtime, failure, resource, freshness, and data-quality signals; observation frequency; owner; and where evidence will be recorded. Monitor scheduled or concurrent writers for collisions.

Stop Conditions

List objective conditions that halt expansion or publishing, such as invariant failure, unexpected scope, duplicate growth, missing input, retention risk, resource saturation, concurrency conflict, error threshold, or loss of rollback capability.

Rollback

Describe the rollback trigger, restorable artifact, reverse dependency order, partition scope, owner, validation after restoration, and time limit. If rollback is impossible, say so and require explicit confirmation before the irreversible write.

See references/example-plan.md for a fictional seven-day example.

Related Skills

• Root cause still unknown: investigate-data-quality.
• Broader production change request (schema, rollout, monitoring): prepare-production-data-change.
• Implementation diff review before approval: review-data-pipeline.
• Original job failed or stalled on YARN: debug-spark-yarn.

Safety

Default behavior is planning only: do not submit jobs, publish data, alter schedules, delete data, overwrite partitions, merge records, or trigger downstream recomputation. If execution is explicitly requested, first present the complete dry-run scope and wait for the user to review it. Never infer approval from the original request, silence, or approval of a different scope. Require fresh, explicit confirmation immediately before any irreversible write, naming the affected datasets, partitions, write semantics, and immutable code version. Prefer bounded canaries, reversible writes, snapshots, and staged publishing. Do not expose credentials, connection details, proprietary lineage, or sensitive data in plans or evidence.