bigquery-ethereum-data-acquisition
Workflow for acquiring historical Ethereum blockchain data using Google BigQuery free tier. Empirically validated for cost estimation, streaming downloads, and DuckDB integration. Use when planning bulk historical data acquisition or comparing data source options for blockchain network metrics.
Full skill instructions
Workflow for acquiring 5 years of Ethereum blockchain data (2020-2025, ~12.44M blocks) using Google BigQuery's public dataset within free tier limits. Includes column selection analysis for ML/time-series forecasting, cost optimization, and empirically validated download workflows.
Status: ✅ Empirically validated (v0.2.0, 2025-11-07)
Use when:
This skill follows a 5-step workflow for acquiring Ethereum data from BigQuery:
| Step | Purpose | Output | Key Metric |
|---|---|---|---|
| 1. Free Tier Limits | Understand query/storage limits | Limits documented | 1 TB query, 10 GB storage |
| 2. Column Selection | Optimize for ML/time-series | 11 columns chosen | 0.97 GB (97% savings) |
| 3. Cost Validation | Dry-run query cost | Cost estimate | 0.1% of free tier |
| 4. Stream Download | Parquet download (no storage) | .parquet file | 62 bytes/row |
| 5. DuckDB Import | Load for analysis | DuckDB database | <100ms query time |
Detailed workflow: See references/workflow-steps.md for complete step-by-step guide with SQL queries, bash commands, and validated results for each step.
Quick start: Run uv run scripts/test_bigquery_cost.py to validate cost, then uv run scripts/download_bigquery_to_parquet.py <start> <end> <output> to download.
Optimized selection: 11 columns = 0.97 GB (0.1% of free tier, 97% cost savings vs all 23 columns)
Key finding: BigQuery is 624x faster than RPC polling (<1 hour vs 26 days for 12.44M blocks).
Full analysis: See references/cost-analysis.md for detailed cost comparison, column selection rationale, and RPC provider comparison.
One-time setup: gcloud auth, Python dependencies (google-cloud-bigquery, pandas, pyarrow, db-dtypes)
Setup guide: See references/setup-guide.md for complete authentication setup, dependency installation, and verification commands.
Validated scripts (v0.2.0):
test_bigquery_cost.py - Dry-run cost estimation (0.97 GB for 12.44M blocks)download_bigquery_to_parquet.py - Streaming Parquet download (62 bytes/row validated)Templates and usage: See scripts/README.md for complete usage examples, dependencies, and validated results.
references/workflow-steps.md - Complete 5-step workflow with SQL queries, bash commands, and validated resultsreferences/cost-analysis.md - Detailed cost comparison, column selection rationale, RPC provider comparisonreferences/setup-guide.md - Authentication setup, dependencies, verification commandsreferences/bigquery_cost_comparison.md - Empirical cost analysis (6 vs 11 vs 23 columns)references/ethereum_columns_ml_evaluation.md - Column-by-column ML value analysisreferences/bigquery_complete_ethereum_data.md - Complete dataset catalog (11 tables)references/bigquery_cost_estimate.md - Free tier limits and methodologyreferences/littleblack-hardware-report.md - Local vs cloud hardware comparisonscripts/README.md - Complete script usage guide with validated resultsVALIDATION_STATUS.md - Empirical test results, testing methodology, dependencies validatedImportant: This skill covers data acquisition from BigQuery (downloading historical Ethereum blocks), but does NOT verify the data actually landed in ClickHouse.
After completing BigQuery download:
scripts/clickhouse/verify_blocks.py to verify database stateCommon workflow:
chunked_backfill.shSee historical-backfill-execution skill for database verification and troubleshooting missing data.
historical-backfill-execution - ClickHouse database verification and backfill operationsblockchain-rpc-provider-research - RPC rate limit comparison and provider evaluationblockchain-data-collection-validation - Empirical validation workflow for data pipelines
Comprehensive Spring Framework and Spring Boot best practices including dependency injection patterns, bean lifecycle and scopes, REST API development, Spring Data JPA, service layer design, Spring Security, testing strategies, caching, AOP, async processing, error handling, and common anti-patte...
Dispatch to the right problem-solving technique based on how you're stuck
TypeScript厳格モードによる型安全性設計を専門とするスキル。 📖 参照書籍: - 『Effective TypeScript』（Dan Vanderkam）: 型設計 📚 リソース参照: - `resources/Level1_basics.md`: レベル1の基礎ガイド - `resources/Level2_intermediate.md`: レベル2の実務ガイド - `resources/Level3_advanced.md`: レベル3の応用ガイド - `resources/Level4_expert.md`: レベル4の専門ガイド - `resources/di...
Deploys applications to cloud Kubernetes (AKS/GKE/DOKS) with CI/CD pipelines. Use when deploying to production, setting up GitHub Actions, troubleshooting deployments. Covers build-time vs runtime vars, architecture matching, and battle-tested debugging.
19-agent team structure, decision trees for agent selection, Haiku vs Sonnet model selection, and agent collaboration principles. Use when deciding which sub-agent to invoke, understanding team responsibilities, or learning multi-agent orchestration.
Converts architecture descriptions, module specs, or workflow docs into Mermaid diagrams. Use when visualizing brick module relationships, workflows (DDD, investigation), or system architecture. Supports: flowcharts, sequence diagrams, class diagrams, state machines, entity relationship diagrams,...
INVOKE FIRST before any code work. Validates git workflow (branch, issue, worklog) and checks approach. Use at START of every task and END before completing. Prevents skipped steps.
This skill should be used when working with browser-rendered artifacts (i.e. Bun, React/React Native) to proactively validate that development work on localhost is being built appropriately and to support debugging browser-rendered content. Use this skill after making frontend changes to verify t...
Lead Développeur - Coordination technique opérationnelle, code review, mentoring et livraison. Pair de web-dev-process au niveau OPÉRATIONS.
Monitor Docker workers, RQ queue health, and auto-scale workers (max 4). Use when: checking job progress, monitoring queue depth, scaling workers up/down, diagnosing slow processing, or waiting for jobs to complete. Referenced by data-quality skill during reprocessing.
Proactive quality guidance system that monitors artifact creation and provides real-time feedback on documentation quality
This skill should be used when the user wants to bump the version number in the workspace. It updates versions across all pyproject.toml files (root, tde, and tda packages) and the CHANGELOG.md to keep them synchronized.
Generate, edit, and beat-sync AI video with leading models in one workspace.
The world's fastest calendar for remote work
Transform Your Design with AI Designer by ImgCreator.ai
Revolutionizing Video Production with AI-Powered Creativity
Extend an image past the frame and let AI fill the new aspect ratio.
Discover your celebrity doppelgänger with StarByFace!
ChainClarity explains 700+ crypto whitepapers in plain English, with layered summaries, comparisons, research tools, alerts, and a $4.99 Pro plan.
Opus.ai: Revolutionize Your Web Experience