Smithery is now a part of Arcade.dev! Read more in our announcement here
Execute chunked historical blockchain data backfills using canonical 1-year pattern. Use when loading multi-year historical data, filling gaps in ClickHouse, or preventing OOM failures on Cloud Run...
Version: 2.0.0 Last Updated: 2025-11-29 Purpose: Execute chunked historical blockchain data backfills using canonical 1-year pattern
Use this skill when:
Empirically Validated Approach:
Why 1-Year Chunks?
See Backfill Patterns Reference for complete rationale (memory constraints, retry granularity, progress tracking).
eonlabs-ethereum-bqbigquery-public-data.crypto_ethereum.blocksethereum_mainnet.blocksgoogle-cloud-bigquery, pyarrow, clickhouse-connectRun the canonical 1-year chunking script:
cd /deployment/backfill
./chunked_backfill.sh 2015 2025
What This Does:
Alternative (use provided validation wrapper):
.claude/skills/historical-backfill-execution/scripts/chunked_executor.sh 2015 2025
Watch the output for year-by-year progress:
Loading blocks for year 2015...
Year 2015: Loading blocks 1 - 2,600,000
Completed 2015 in 1m42s
Loading blocks for year 2016...
Year 2016: Loading blocks 2,600,001 - 5,200,000
Completed 2016 in 1m38s
...
All years completed successfully!
Total blocks loaded: 23,800,000
Total time: 18m45s
After backfill completes, verify all blocks loaded:
cd
doppler run --project aws-credentials --config prd -- python3 -c "
import clickhouse_connect
import os
client = clickhouse_connect.get_client(
host=os.environ['CLICKHOUSE_HOST'],
port=8443,
username='default',
password=os.environ['CLICKHOUSE_PASSWORD'],
secure=True
)
result = client.query('SELECT COUNT(*) as total, MIN(number) as min_block, MAX(number) as max_block FROM ethereum_mainnet.blocks FINAL')
print(f'Total blocks: {result.result_rows[0][0]:,}')
print(f'Block range: {result.result_rows[0][1]:,} to {result.result_rows[0][2]:,}')
"
Expected Output (healthy):
Total blocks: 23,800,000+
Block range: 1 to 23,800,000+
Run gap detection to ensure zero missing blocks. The gap monitor Cloud Function runs every 3 hours automatically. For manual checks:
# Trigger manual gap check via Cloud Scheduler
gcloud scheduler jobs run motherduck-monitor-trigger --location=us-east1
# View logs
gcloud functions logs read motherduck-gap-detector --region=us-east1 --gen2 --limit=50
Load specific years only:
# Load only 2023-2024
cd deployment/backfill
./chunked_backfill.sh 2023 2024
Use Cases:
Constraint: 4GB memory limit per Cloud Run Job execution
Solution: 1-year chunks keep memory usage <4GB per execution
What Happens on OOM:
For local testing with memory constraints:
# Validate memory requirements before execution
.claude/skills/historical-backfill-execution/scripts/validate_chunk_size.py --year 2020
Output:
Estimating memory for 2020 backfill...
Block count: ~2,600,000
Column count: 11 (optimized schema)
Expected memory: ~3.2 GB
Cloud Run safe: (under 4GB limit)
See Troubleshooting Reference for complete guide.
Common Issues:
| Issue | Cause | Solution |
|---|---|---|
| OOM error (code 137) | Chunk too large | Reduce year range (6 months instead of 1 year) |
| BigQuery quota exceeded | >1TB in 30 days | Wait for quota reset or reduce query frequency |
| "Permission denied" | Missing IAM roles | Grant roles/bigquery.user to service account |
| "Table not found" | Wrong dataset | Verify bigquery-public-data.crypto_ethereum.blocks |
| Slow execution (>5min/year) | Network issues | Check Cloud Run region (use same as BigQuery: us) |
See Backfill Patterns Reference for alternatives and rationale.
Pattern Comparison:
| Pattern | Chunk Size | Memory | Time (10yr) | Retry Granularity | Recommended |
|---|---|---|---|---|---|
| 1-Year Chunks | ~2.6M blocks | <4GB | 20 min | Year-level | Yes (canonical) |
| Month Chunks | ~220K blocks | <1GB | 35 min | Month-level | Over-chunked (slower) |
| Full Load | 26M blocks | >8GB | N/A | All-or-nothing | No (OOM errors) |
Complete Historical Backfill (2025-11-10):
chunked_backfill.shSLO Achievement: Complete historical data collection (10 years, 23.8M blocks) in <30 minutes with zero manual intervention.
validate_chunk_size.py - Estimate memory requirements before executionchunked_executor.sh - Wrapper for deployment/backfill/chunked_backfill.sh with validationbackfill-patterns.md - 1-year chunking rationale, comparison with alternativestroubleshooting.md - OOM errors, retry strategies, Cloud Run logs analysis