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. googblockchainethereummainnetus is the native data model (richer: receipts, decoded events, per-block account states with proofs, lossless value columns). cryptoethereum 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 accountsstatebyaddress; cryptoethereum.balances is a snapshot of current balances only, with no history. Column names differ between the two (blocknumber 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.

1. Draft the query selecting only the columns you need, with a block-range or

time-range constraint.

1. Dry-run it and check the bytes scanned (see

[references/query-execution.md](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.

1. Run under a hard byte cap (--maximumbytesbilled, 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](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](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](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; maximumbytesbilled 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,

amendedtokens). Wide STRING columns (input, logsbloom, 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: googblockchain* tables prune on blocknumber ranges; cryptoethereum prunes on the day of timestamp/blocktimestamp; cryptobitcoin prunes on blocktimestampmonth / timestampmonth). 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 blocknumber 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 /

*byaddress 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 ({stringvalue, bignumericvalue}) hold both, and bignumericvalue 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 (blocknumber-or-hash, transactionhash).

• 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 (ethgetBalance, ethgetProof, trace_block, …), not another indexer — third-party indexers apply their own conventions to derived data.

Query execution

Read [references/query-execution.md](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: