backfill-runbook

Name: backfill-runbook
Author: zakelfassi

17stars0forks

How to install backfill-runbook?

zakelfassi/skills-driven-development/backfill-runbook

$npx -y skills add zakelfassi/skills-driven-development --skill backfill-runbook

Installs into the current project.

Summary

Safely backfill historical data for a pipeline stage — scope the date range, run a dry-run, execute in chunks to avoid overwhelming the database, and verify row counts after each chunk. Use when historical data is missing, when a pipeline stage is added retroactively, or when ask

Files · 1

View on GitHub

SKILL.md

1	# Backfill Runbook
2
3	Safely reprocess historical data for a pipeline stage without locking tables or losing existing rows.
4
5	## Inputs
6	- Stage name (e.g., `customer_ltv`)
7	- Start date (`YYYY-MM-DD`)
8	- End date (`YYYY-MM-DD`, defaults to yesterday)
9	- Chunk size (number of days per chunk, defaults to `7`)
10	- Dry run? (boolean, defaults to `true` — always start with dry-run)
11
12	## Steps
13
14	1. Scope the backfill
15	Estimate the volume:
16	```sql
17	select count(*), min(event_date), max(event_date)
18	from raw.{source_table}
19	where event_date between '{start_date}' and '{end_date}';
20	```
21	Multiply by your stage's typical row-expansion ratio. If the result is >10M rows, reduce the chunk size to 3 days.
22
23	2. Dry-run the first chunk
24	```bash
25	python pipelines/transforms/{stage_name}/transform.py \
26	--start {start_date} \
27	--end {start_date + 6 days} \
28	--dry-run
29	```
30	The dry-run prints the SQL or DataFrame operations without writing output. Review for correctness before proceeding.
31
32	3. Back up the target table (if it exists)
33	```sql
34	create table {stage_name}_backup_{today} as
35	select * from {layer}.{stage_name};
36	```
37	Keep the backup for 7 days.
38
39	4. Execute in chunks
40	```bash
41	python scripts/backfill.py \
42	--stage {stage_name} \
43	--start {start_date} \
44	--end {end_date} \
45	--chunk-days {chunk_size} \
46	--pause-seconds 5
47	```
48	The backfill script:
49	- Processes one chunk at a time
50	- Pauses `{pause_seconds}` between chunks (reduces lock contention)
51	- Logs progress: `[chunk {n}/{total}] {start}–{end}: {rows_written} rows`
52	- Writes a checkpoint file (`backfill-{stage}-{run_id}.checkpoint`) so it can resume after a failure
53
54	5. Verify each chunk (or verify the full range after completion)
55	```sql
56	-- Row count by date
57	select event_date, count(*) as rows
58	from {layer}.{stage_name}
59	where event_date between '{start_date}' and '{end_date}'
60	group by event_date
61	order by event_date;
62	```
63	Compare against the raw source counts from step 1. Flag any date with zero rows.
64
65	6. Run the data quality gate (invoke `data-quality-gate` skill)
66	Check null rates, ranges, and referential integrity on the backfilled range.
67
68	7. Drop the backup (after 7 days, if no issues)
69	```sql
70	drop table {stage_name}_backup_{today};
71	```
72
73	## Conventions
74	- Always start with `--dry-run`; never skip this step
75	- Chunk size ≤ 7 days for tables with >1M rows/day; ≤ 30 days for smaller tables
76	- Backfills run during off-peak hours (after 22:00 UTC on weekdays, any time on weekends)
77	- The backfill script uses `INSERT OVERWRITE` (or `REPLACE INTO`) semantics — idempotent per chunk
78	- Checkpoint files are committed to `backfills/` so the team can see what has been run
79
80	## Edge Cases
81	- Pipeline failure mid-chunk: Resume from the last checkpoint: `python scripts/backfill.py --resume backfill-{stage}-{run_id}.checkpoint`.
82	- Source data changed retroactively (late-arriving events): Add `--allow-late-data` flag; document in the backfill log that the range was reprocessed for late-arrival reasons.
83	- Backfill conflicts with a running daily load: Check the scheduler; delay the backfill or pause the daily DAG temporarily. Never run both simultaneously on the same partition.
84	- Very long date range (>1 year): Split into multiple backfill jobs of ≤90 days each; run sequentially and verify between jobs.

People who liked this also viewed

6 picks

Type

backfill-runbook

17stars0forks

View on GitHub

How to install backfill-runbook?

zakelfassi/skills-driven-development/backfill-runbook

$npx -y skills add zakelfassi/skills-driven-development --skill backfill-runbook

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Backfill Runbook
2
3	Safely reprocess historical data for a pipeline stage without locking tables or losing existing rows.
4
5	## Inputs
6	- Stage name (e.g., `customer_ltv`)
7	- Start date (`YYYY-MM-DD`)
8	- End date (`YYYY-MM-DD`, defaults to yesterday)
9	- Chunk size (number of days per chunk, defaults to `7`)
10	- Dry run? (boolean, defaults to `true` — always start with dry-run)
11
12	## Steps
13
14	1. Scope the backfill
15	Estimate the volume:
16	```sql
17	select count(*), min(event_date), max(event_date)
18	from raw.{source_table}
19	where event_date between '{start_date}' and '{end_date}';
20	```
21	Multiply by your stage's typical row-expansion ratio. If the result is >10M rows, reduce the chunk size to 3 days.
22
23	2. Dry-run the first chunk
24	```bash
25	python pipelines/transforms/{stage_name}/transform.py \
26	--start {start_date} \
27	--end {start_date + 6 days} \
28	--dry-run
29	```
30	The dry-run prints the SQL or DataFrame operations without writing output. Review for correctness before proceeding.
31
32	3. Back up the target table (if it exists)
33	```sql
34	create table {stage_name}_backup_{today} as
35	select * from {layer}.{stage_name};
36	```
37	Keep the backup for 7 days.
38
39	4. Execute in chunks
40	```bash
41	python scripts/backfill.py \
42	--stage {stage_name} \
43	--start {start_date} \
44	--end {end_date} \
45	--chunk-days {chunk_size} \
46	--pause-seconds 5
47	```
48	The backfill script:
49	- Processes one chunk at a time
50	- Pauses `{pause_seconds}` between chunks (reduces lock contention)
51	- Logs progress: `[chunk {n}/{total}] {start}–{end}: {rows_written} rows`
52	- Writes a checkpoint file (`backfill-{stage}-{run_id}.checkpoint`) so it can resume after a failure
53
54	5. Verify each chunk (or verify the full range after completion)
55	```sql
56	-- Row count by date
57	select event_date, count(*) as rows
58	from {layer}.{stage_name}
59	where event_date between '{start_date}' and '{end_date}'
60	group by event_date
61	order by event_date;
62	```
63	Compare against the raw source counts from step 1. Flag any date with zero rows.
64
65	6. Run the data quality gate (invoke `data-quality-gate` skill)
66	Check null rates, ranges, and referential integrity on the backfilled range.
67
68	7. Drop the backup (after 7 days, if no issues)
69	```sql
70	drop table {stage_name}_backup_{today};
71	```
72
73	## Conventions
74	- Always start with `--dry-run`; never skip this step
75	- Chunk size ≤ 7 days for tables with >1M rows/day; ≤ 30 days for smaller tables
76	- Backfills run during off-peak hours (after 22:00 UTC on weekdays, any time on weekends)
77	- The backfill script uses `INSERT OVERWRITE` (or `REPLACE INTO`) semantics — idempotent per chunk
78	- Checkpoint files are committed to `backfills/` so the team can see what has been run
79
80	## Edge Cases
81	- Pipeline failure mid-chunk: Resume from the last checkpoint: `python scripts/backfill.py --resume backfill-{stage}-{run_id}.checkpoint`.
82	- Source data changed retroactively (late-arriving events): Add `--allow-late-data` flag; document in the backfill log that the range was reprocessed for late-arrival reasons.
83	- Backfill conflicts with a running daily load: Check the scheduler; delay the backfill or pause the daily DAG temporarily. Never run both simultaneously on the same partition.
84	- Very long date range (>1 year): Split into multiple backfill jobs of ≤90 days each; run sequentially and verify between jobs.

People who liked this also viewed

6 picks

Type