Problem Statement
A data engineer needs to generate a safe and methodical backfill plan when historical gaps are discovered in a data pipeline. The workflow should take key inputs, determine the scope and approach, ensure idempotency, run a throttled batch backfill, validate results, and produce monitoring and communication artifacts. The goal is to minimize operational risk, protect downstream dependencies, and ensure data correctness.
Steps
Ingest Inputs
INGESTCollect the missing time range, dataset metadata, pipeline schedule, source-of-truth references, and downstream dependency list. Ensure that all required fields are captured and formatted for subsequent steps.
Assess Scope and Gap
EXECUTEAnalyze the missing historical window, estimate backfill volume, and identify whether the gap is contiguous or fragmented. Determine whether any data needs to be re-extracted or re-validated from authoritative sources.
Select Backfill Approach
EXECUTEChoose a safe approach such as full-range backfill, incremental replay, or dependency-aware sequencing. Incorporate constraints like throttling, stop conditions, and avoidance of pipeline contention.
Ensure Idempotency
EXECUTEGenerate guidance to make the backfill safe to rerun, including upsert patterns, deduplication logic, checkpoints, and run isolation. Highlight required safeguards for avoiding double-ingestion.
Execute Batched Backfill Plan
EXECUTEProduce a detailed step-by-step plan including batch sizing, execution windows, run ordering, and safety throttling. Include explicit stop conditions triggered by data quality failures or operational anomalies.
Validate and Monitor
EXECUTEGenerate validation SQL queries, downstream impact checks, and a post-backfill monitoring checklist. Include a communications plan for notifying stakeholders before, during, and after the backfill.
Prompt Configurations
Backfill Planning Prompt
You are a senior data reliability engineer. Draft a comprehensive, safety-first backfill plan. Inputs you will receive: dataset description, missing time range, pipeline schedule, source-of-truth systems, and downstream dependencies. Your output must include: 1. Backfill plan with steps and timeline 2. Idempotency checklist 3. Validation queries (no PII) 4. Stakeholder communications plan 5. Post-backfill monitoring checklist Tone: methodical, risk-averse, and precise. Constraints: include stop conditions, safe throttling guidance, and avoid inventing any data not provided. Do not include any PII.
Example Inputs/Outputs
Dataset: daily aggregated metrics table. Missing time range: 2024-03-01 to 2024-03-10. Pipeline schedule: runs hourly, heavy load between 09:00–12:00 UTC. Source of truth: raw event logs in object storage. Downstream dependencies: dashboards refreshed hourly.
The workflow generates: (1) a backfill plan recommending 10 daily batches executed outside peak hours with throttling; (2) an idempotency checklist including partition overwrite rules and dedupe keys; (3) validation SQL comparing aggregate counts against raw logs; (4) stakeholder comms for BI and analytics teams; (5) monitoring checklist covering volume anomalies, schema drift checks, and dashboard latency impact.