# Bigquery Blockchain Datasets

> Query the BigQuery public blockchain datasets — Ethereum, Polygon, Arbitrum, Optimism, Avalanche, Cronos and TRON (goog_blockchain_*_us), plus crypto_ethereum and crypto_bitcoin — with correct SQL at controlled cost. Use before writing any SQL against bigquery-public-data blockchain tables, when the user names one of these datasets, or for on-chain analytics on these chains: blocks, transactions, logs and events, traces and internal transactions, token transfers and holders, wallet history, gas fees, ETH balances, contracts, NFT activity, USDT on TRON, Bitcoin UTXOs. Not for private BigQuery datasets, Dune/Flipside SQL, or other bigquery-public-data crypto_* datasets (Solana, Litecoin, …).

- Skill: `subsquid-labs/bigquery-blockchain-datasets` (Agent Skill, multi-file: 17 files)
- Install (CLI): `npx skillmds@latest add subsquid-labs/bigquery-blockchain-datasets`
- Raw SKILL.md: https://api.skillmd.com/api/skills/subsquid-labs/bigquery-blockchain-datasets/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: MIT
- Author: subsquid-labs (https://skillmd.com/u/subsquid-labs)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/subsquid-labs/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

| Dataset | Chain | Tables | Read |
|---|---|---|---|
| `goog_blockchain_ethereum_mainnet_us` | Ethereum | blocks, transactions, logs, receipts, traces, token_transfers, decoded_events, accounts_state, + 4 address-clustered projections (`accounts`, `accounts_state_by_address`, `transactions_by_from_address`, `transactions_by_to_address`) | [references/ethereum.md](references/ethereum.md) |
| `goog_blockchain_polygon_mainnet_us` | Polygon PoS | blocks, transactions, logs, receipts, decoded_events | [references/evm-shared-schema.md](references/evm-shared-schema.md) + [references/polygon.md](references/polygon.md) |
| `goog_blockchain_arbitrum_one_us` | Arbitrum One | shared EVM shape (same 5) | [references/evm-shared-schema.md](references/evm-shared-schema.md) + [references/arbitrum.md](references/arbitrum.md) |
| `goog_blockchain_optimism_mainnet_us` | OP Mainnet | shared EVM shape (same 5) | [references/evm-shared-schema.md](references/evm-shared-schema.md) + [references/optimism.md](references/optimism.md) |
| `goog_blockchain_avalanche_contract_chain_us` | Avalanche C-Chain | shared EVM shape (same 5) | [references/evm-shared-schema.md](references/evm-shared-schema.md) + [references/avalanche.md](references/avalanche.md) |
| `goog_blockchain_cronos_mainnet_us` | Cronos | shared EVM shape (same 5) | [references/evm-shared-schema.md](references/evm-shared-schema.md) + [references/cronos.md](references/cronos.md) |
| `goog_blockchain_tron_mainnet_us` | TRON | blocks, transactions, logs, receipts, decoded_events, tron_internal_transactions | [references/tron.md](references/tron.md) |
| `crypto_ethereum` | Ethereum (legacy ethereum-etl layout) | blocks, transactions, logs, token_transfers, traces, contracts, tokens, amended_tokens, balances, tokens_latest, token_supply_latest | [references/crypto-ethereum.md](references/crypto-ethereum.md) |
| `crypto_bitcoin` | Bitcoin | blocks, transactions, inputs, outputs | [references/crypto-bitcoin.md](references/crypto-bitcoin.md) |

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](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](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:

| Largest tables | Full scan | `SELECT *` cost |
|---|---|---|
| `goog_blockchain_polygon_mainnet_us.logs` | 32.7 TB | ~$205 |
| `goog_blockchain_polygon_mainnet_us.decoded_events` | 30.3 TB | ~$189 |
| `goog_blockchain_ethereum_mainnet_us.accounts_state` | 53.5 TB | ~$334 |
| `goog_blockchain_ethereum_mainnet_us.traces` | 12.8 TB | ~$80 |
| `crypto_ethereum.traces` | 13.5 TB | ~$85 |
| `goog_blockchain_tron_mainnet_us.transactions` | 8.1 TB | ~$51 |

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](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`:

| Script | Does | Cost |
|---|---|---|
| `scripts/bq-safe.sh 'SQL'` | dry run, print bytes + $ estimate; `--run --cap 100MB` executes under a hard cap | free / capped |
| `scripts/check-partitioning.sh [dataset]` | print partitioning + clustering of every table (`bq show`) | free |
| `scripts/regenerate-sizes.sh <dataset>` | rebuild a reference's size table from dry runs | free |
| `scripts/regenerate-schema-tables.py <dataset>` | rebuild schema tables from `INFORMATION_SCHEMA` | two 10 MB-minimum metadata queries |