smithery/terrylica
Execute chunked historical blockchain data backfills using canonical 1-year pattern.
npx skills add smithery/terrylica --skill historical-backfill-execution
Related neighbors and high-traction skills in the same topics — useful to compare before installing.
Quantify realized risk from historical data using volatility estimators, drawdown analysis, and…
566 installsRetrieve historical financial ratings and key metric scores over time using Octagon MCP. Use wh…
254 installsRetrieve historical market capitalization data for any stock using Octagon MCP. Use when tracki…
250 installsRetrieve full historical end-of-day price data for market indices using Octagon MCP. Use when a…
248 installsResearches archives, contemporary accounts, and timeline reconstruction. Use when the album sub…
196 installsOpen the CoinGecko historical-data page for a coin/date in Chromium via Chrome DevTools MCP.
103 installsOther skills from smithery/terrylica · top by installs.
npx skills add smithery/terrylica
Declared targets from SKILL.md / docs. Unmarked agents are not listed — the skill may still install via the CLI.
Parsed from SKILL.md frontmatter.
Files included with this skill beyond the listing page.
SKILL.md
8,044 B
SUMMARY.md
321 B
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](/.claude/skills/historical-backfill-execution/references/backfill-patterns.md) 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 2025What This Does:
Alternative (use provided validation wrapper):
.claude/skills/historical-backfill-execution/scripts/chunked_executor.sh 2015 2025Watch 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: 18m45sAfter 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=50Load specific years only:
# Load only 2023-2024
cd deployment/backfill
./chunked_backfill.sh 2023 2024Use 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 2020Output:
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](/.claude/skills/historical-backfill-execution/references/troubleshooting.md) 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](/.claude/skills/historical-backfill-execution/references/backfill-patterns.md) 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.
validatechunksize.py](/.claude/skills/historical-backfill-execution/scripts/validatechunksize.py) - Estimate memory requirements before executionchunkedexecutor.sh](/.claude/skills/historical-backfill-execution/scripts/chunkedexecutor.sh) - Wrapper for deployment/backfill/chunked_backfill.sh with validationbackfill-patterns.md](/.claude/skills/historical-backfill-execution/references/backfill-patterns.md) - 1-year chunking rationale, comparison with alternativestroubleshooting.md](/.claude/skills/historical-backfill-execution/references/troubleshooting.md) - OOM errors, retry strategies, Cloud Run logs analysis