backfill-runbook

Backfill Runbook

Safely reprocess historical data for a pipeline stage without locking tables or losing existing rows.

Inputs

• Stage name (e.g., customer_ltv)
• Start date (YYYY-MM-DD)
• End date (YYYY-MM-DD, defaults to yesterday)
• Chunk size (number of days per chunk, defaults to 7)
• Dry run? (boolean, defaults to true — always start with dry-run)

Steps

1. Scope the backfill Estimate the volume:

   select count(*), min(event_date), max(event_date)
   from raw.{source_table}
   where event_date between '{start_date}' and '{end_date}';
   

   Multiply by your stage's typical row-expansion ratio. If the result is >10M rows, reduce the chunk size to 3 days.

2. Dry-run the first chunk

   python pipelines/transforms/{stage_name}/transform.py \
     --start {start_date} \
     --end {start_date + 6 days} \
     --dry-run
   

   The dry-run prints the SQL or DataFrame operations without writing output. Review for correctness before proceeding.

3. Back up the target table (if it exists)

   create table {stage_name}_backup_{today} as
   select * from {layer}.{stage_name};
   

   Keep the backup for 7 days.

4. Execute in chunks

   python scripts/backfill.py \
     --stage {stage_name} \
     --start {start_date} \
     --end {end_date} \
     --chunk-days {chunk_size} \
     --pause-seconds 5
   

   The backfill script:

   • Processes one chunk at a time
   • Pauses {pause_seconds} between chunks (reduces lock contention)
   • Logs progress: [chunk {n}/{total}] {start}–{end}: {rows_written} rows
   • Writes a checkpoint file (backfill-{stage}-{run_id}.checkpoint) so it can resume after a failure

5. Verify each chunk (or verify the full range after completion)

   -- Row count by date
   select event_date, count(*) as rows
   from {layer}.{stage_name}
   where event_date between '{start_date}' and '{end_date}'
   group by event_date
   order by event_date;
   

   Compare against the raw source counts from step 1. Flag any date with zero rows.