Bigquery Blockchain Datasets

BigQuery public blockchain datasets

Full-history, continuously updated blockchain data in the bigquery-public-data project, queryable with standard SQL. Tables are typically within minutes of the chain head. Derived tables (traces, decoded events, token transfers) follow documented conventions described in the per-dataset references; when a value surprises you, the chain (a block-pinned RPC read) is the arbiter, not another indexer.

Dataset directory

Always fully qualify tables with backticks: `bigquery-public-data.<dataset>.<table>`.

Two Ethereum datasets exist on purpose. goog_blockchain_ethereum_mainnet_us is the native data model (richer: receipts, decoded events, per-block account states with proofs, lossless value columns). crypto_ethereum is the older ethereum-etl layout (receipt fields embedded in transactions, plus tokens/balances/contracts derived tables) — reach for it when a query or downstream tool was written for that layout, or when you need token metadata or contract creation data. For ETH balances at a block use the native dataset's accounts_state_by_address; crypto_ethereum.balances is a snapshot of current balances only, with no history. Column names differ between the two (block_number vs number, block_timestamp vs timestamp); they are not interchangeable.

Workflow

1. Pick the dataset from the directory above and read its reference file(s) before writing SQL — they carry the schemas and the per-chain gotchas that make naive queries silently wrong.
2. Draft the query selecting only the columns you need, with a block-range or time-range constraint.
3. Dry-run it and check the bytes scanned (see references/query-execution.md for how, with any execution setup; scripts/bq-safe.sh does dry-run → cap → run in one call). If the estimate is far above what the row selectivity suggests, your filter is not pruning — fix the filter, don't just run it.
4. Run under a hard byte cap (--maximum_bytes_billed, or the client's equivalent) sized to the estimate, with --location=US, then sanity-check magnitudes (wei vs ETH, sun vs TRX, satoshi vs BTC).

To locate a column, table or convention without opening files, grep the references: grep -rn 'token_transfers' references/.

Start from a worked query when one fits: references/recipes.md holds executed recipes with measured bytes (yearly block anchors for every chain, date→block lookup, daily counts and active addresses, fees, token transfers, NFT mints, address history, balance at a block, TRON USDT with base58 addresses, Bitcoin coinbase) — copy the closest one and change the window.

If no query execution path is available in the current environment, write the final SQL, state the dry-run expectation, and hand it to the user to paste into https://console.cloud.google.com/bigquery — never fabricate results.

What a finished answer contains

An on-chain number is only usable with the definition attached, so report:

• The answer, in the unit the user asked for (ETH, not wei; a date, not a block).
• The window, pinned: the block range or timestamp bounds actually queried, so the number can be reproduced tomorrow.
• What it excludes, when a convention changes the meaning: system or deposit transactions, internal-call transfers, empty blocks, an ERC-20-only filter. One clause is enough, and it is what separates a right number from a misleading one.
• What it cost: bytes billed (not the estimate) and, on-demand, the dollar figure at the rate you used.
• The SQL, so the user can re-run or adapt it.

If a result contradicts a well-known public figure, say so and name the likely reason (a different definition, a different window) rather than quietly shipping it.

Cost control (bytes scanned is everything)

BigQuery compute has two pricing regimes, and which one applies changes what a "cost" is:

• On-demand (the default for most projects): you pay per TiB for the columns you reference over the partitions you touch, after a monthly free allowance per billing account. A dry run's bytes ARE the price. The list rate, the allowance and the per-query minimum change — the figures this skill uses are dated in references/query-execution.md § Pricing facts; quote them as "at the time of writing" and verify against the project's real totalBytesBilled when it matters.
• Capacity (BigQuery Editions / reservations): the project is attached to a slot reservation and pays for slot-time, not bytes. Dry-run bytes then estimate the work, not a dollar price — per-query dollar figures like the table below don't apply, but every bytes-reduction rule below still cuts slot usage and wall-clock the same way. To check which regime a project is on, see references/query-execution.md.

The dollar figures in this skill assume on-demand at the US list rate in force when the sizes were measured (stamped in each reference); bytes are the durable number. The big tables here are multi-terabyte either way:

Every reference file carries the full per-table size list. Rules:

• Cap every real run. A dry run is advice; maximum_bytes_billed is the one control that makes an over-budget job fail instead of bill. Size the cap to the estimate (a 1 GB cap on a 50 MB estimate costs nothing), and pass --location=US — the datasets are US-only and a project defaulting to another region fails with "dataset not found in location".
• Never SELECT * on anything but the metadata-sized tables (tokens, amended_tokens). Wide STRING columns (input, logs_bloom, data, code, bytecode, account_proof) dominate table width — not selecting them is the single biggest saving.
• LIMIT does not reduce cost. It caps returned rows, not scanned bytes.
• Constrain the block range on every query — and constrain both the block number and the timestamp column where the table has both. Physical partitioning differs by dataset and can change as the datasets evolve (measured: goog_blockchain_* tables prune on block_number ranges; crypto_ethereum prunes on the day of timestamp/block_timestamp; crypto_bitcoin prunes on block_timestamp_month / timestamp_month). Supplying both predicates costs at most the second column's bytes and buys reproducibility plus protection against a layout change; the dry run tells you what actually pruned. On tables without a block_number column (transactions/receipts on the shared EVM shape and TRON), bound block_timestamp — an unbounded join side can scan the whole table.
• Tables clustered by address-like columns (the Ethereum accounts / *_by_address projections) make address point lookups cheap: billed bytes are a small fraction of the table. Whether the dry-run estimate reflects the clustering depends on the object's layout — when a cluster-key point lookup estimates like a full scan, the estimate is pessimistic; billed bytes are the number to trust either way. The same filter on the base transactions table reads the whole column.
• A dry run is free. When in doubt, dry-run twice (with and without a filter) and compare.

Correctness rules that apply everywhere

• Numeric precision. Wei/sun-denominated values can exceed every fixed-width SQL type. The datasets handle this three ways — check the table's schema: BIGNUMERIC columns hold up to ~128-bit values; paired *_lossless STRING columns hold the exact 256-bit decimal; dual-representation RECORD columns ({string_value, bignumeric_value}) hold both, and bignumeric_value is NULL when the value overflows while string_value never is. Never round-trip money values through FLOAT64; sum in BIGNUMERIC, or parse the string/lossless column when exact 256-bit math matters.
• Hex conventions. Hashes, addresses and byte payloads are lowercase, 0x-prefixed STRING. Compare with lowercase literals. Check the address before you lowercase it: the mixed-case (EIP-55) form is a checksum that catches a mistyped digit, and lowercasing throws that check away. A point lookup on a clustered address column returning zero rows means the account is absent from the dataset — far more often a wrong address than missing data.
• Timestamps are UTC TIMESTAMP columns.
• Units. EVM chains: wei (1 ETH = 1e18 wei), gas in gas units. TRON: sun (1 TRX = 1e6 sun), "gas" columns denominated in Energy. Bitcoin: satoshi (1 BTC = 1e8 satoshi).
• Do not deduplicate by transaction hash alone. On Cronos, Arbitrum Classic and pre-Bedrock Optimism the same hash can legitimately appear in more than one block (details in the chain references). The safe row identity is (block_number-or-hash, transaction_hash).
• Head-adjacent queries move. The tables track the chain head; two queries near the head are not repeatable. Pin an upper block bound for anything that must be reproducible.
• The chain is the ground truth: when a value here surprises you, the right arbitration is a block-pinned RPC read (eth_getBalance, eth_getProof, trace_block, …), not another indexer — third-party indexers apply their own conventions to derived data.

Query execution

Read references/query-execution.md the first time you need to actually run (or price) a query in a given environment — it covers the bq CLI, BigQuery MCP tools, pay-per-query proxies, and the no-execution fallback. Its last section maps the errors these datasets actually produce to their fixes; check it before debugging a failed run from first principles.

Bundled scripts (each needs the bq CLI and python3, both on PATH; none bills anything unless you pass --run). Every script takes --help: