BigQuery Bitcoin Dataset: How to Query It and What It Costs

The BigQuery public dataset is the usual first stop for anyone who wants to ask a question about Bitcoin in SQL rather than click through a block explorer. It is real, it is maintained, and it removes the part of the job most people dread, which is running and syncing a node just to get at the data.

What surprises people is the shape of what arrives. The dataset is the chain in raw form, not a finished analytics layer, and the billing model charges for how much data your question touches rather than how much it returns. Both facts change how you write queries. Here is what is actually in there, what it costs, and where teams end up spending money they did not plan to.

What is in the BigQuery Bitcoin dataset?

Four tables, produced by the open-source Blockchain ETL pipeline and loaded into the bigquery-public-data project. They mirror the structure of Bitcoin itself rather than the structure of the questions you want to ask.

Note what is missing from that list. There is no address table, no balance column, no exchange or mining pool label, no clustered entity. Those are all derived, and deriving them is your job. That is not a criticism of the dataset, it is the honest boundary of a raw chain export, and it is the single biggest reason a BigQuery project that started as a weekend experiment turns into a maintained pipeline.

How much does it cost to query the Bitcoin dataset in BigQuery?

The dataset itself costs nothing to access, and you pay no storage for it. You pay for analysis. Under on-demand pricing, the meter counts bytes scanned by each query, published at $6.25 per TiB in US regions with the first terabyte of query data each month free, checked in August 2026. Rates differ by region and change over time, so confirm the current number on Google's pricing page before you build a budget around it.

The important part is the unit. You are not billed for rows returned, query runtime, or how clever the query is. You are billed for the columns and partitions the query had to read. A query that returns a single number can cost the same as one that returns a million rows, if both had to sweep the same column.

That is why LIMIT 10 does not save you anything. It caps the result, not the scan. A newcomer exploring the transactions table with SELECT * ... LIMIT 10 gets ten rows back and a bill calculated on every column in the table.

Why is my BigQuery Bitcoin query so expensive?

Almost always one of four reasons, in roughly this order of frequency.

You selected columns you did not need. BigQuery stores data by column, so naming three columns reads three columns. SELECT * on a chain table reads script hex, witness data and every other wide field along with the two you wanted. Naming columns explicitly is the single highest-return habit here.

You did not filter on the partition. The chain tables are partitioned by block timestamp. A query with no date bound scans the whole history back to 2009 even when you only cared about last week. Adding a date predicate on the partitioning column is what turns a full-history scan into a few days of data.

You ran it again. Exploratory work means running near-identical queries dozens of times in an afternoon, and each run is metered fresh unless the result is cached. Iterating on a query against a multi-terabyte table is where the monthly free terabyte quietly disappears.

The question was an aggregation over all of history. Some questions genuinely require reading everything: a current balance, a rich list, the full UTXO set. Those cannot be narrowed with a date filter, because the whole point is that they depend on the entire chain.

Two defenses are worth building into your habits. Use a dry run to see the byte estimate before executing, since the estimate is free and appears in both the console and the command line client. And set a maximum bytes billed on your queries, which turns an accidental full scan into a failed query rather than an invoice. If your Bitcoin work sits alongside other cloud spend, it is worth being able to see what those scans add up to across the whole bill rather than discovering it at the end of the month.

How do I get a Bitcoin address balance in BigQuery?

You reconstruct it, because Bitcoin does not store balances anywhere. The protocol records outputs, and an address balance is simply the outputs paid to it that have not yet been spent. In the public dataset that means summing the outputs table and subtracting the inputs table for the same addresses.

WITH received AS (
  SELECT addr, SUM(value) AS v
  FROM `bigquery-public-data.crypto_bitcoin.outputs`, UNNEST(addresses) AS addr
  WHERE addr IN UNNEST(@addresses)
  GROUP BY addr
),
spent AS (
  SELECT addr, SUM(value) AS v
  FROM `bigquery-public-data.crypto_bitcoin.inputs`, UNNEST(addresses) AS addr
  WHERE addr IN UNNEST(@addresses)
  GROUP BY addr
)
SELECT r.addr, r.v - IFNULL(s.v, 0) AS balance
FROM received r
LEFT JOIN spent s USING (addr)

Three things to know before you trust the output. The addresses field is repeated, which is why it needs unnesting; multisig outputs list more than one address. The value column is a NUMERIC, so check its unit in the table schema and keep the arithmetic in NUMERIC rather than converting to a float, or rounding will eventually cost you a satoshi in a reconciliation. And an address that only ever received funds will have no row in the spent side, hence the left join and the null guard.

The query is correct and it is the standard pattern. The catch is that it has to read the address column across the full history of both tables every time it runs, and there is no date filter that can help, because a balance depends on the whole chain. Passing a list through UNNEST at least amortizes that scan over many addresses at once instead of paying it per address, which is why the parameterized array pattern is what experienced users converge on. Once this becomes something you run daily rather than once, an indexed Bitcoin address balance API answers it as a lookup instead of a scan, and the same is true for checking a long list of addresses in one request.

Does the BigQuery Bitcoin dataset have address labels?

No. This is the gap people hit fastest and it is worth stating plainly: the public Bitcoin dataset contains no exchange names, no mining pool attribution, no service labels and no clustered entities. It is chain data, and the chain does not know that an address belongs to a particular exchange.

What people do instead is join against a separate labels dataset published by a third party, typically shaped as address, tag and entity, then filter it with the same parameterized array pattern the balance query uses. It works, and it is how a lot of BigQuery Bitcoin analysis actually gets done. The costs are that you now depend on someone else's refresh schedule, you inherit their labeling decisions without visibility into how they were made, and every join adds another scanned table to the bill.

It also puts a ceiling on what you can answer. Labels are only as useful as the clustering behind them, and clustering is a set of heuristics with known failure modes rather than ground truth. If entity attribution is central to your work, it is worth understanding how address clustering actually works before you build on top of a labels table, and worth having entity labels indexed alongside the chain rather than bolted on beside it.

How fresh is the BigQuery Bitcoin data?

The public dataset tracks roughly three blocks behind the chain tip. In practice that is on the order of half an hour, since blocks arrive about every ten minutes on average.

For research, backtesting and reporting, three blocks is irrelevant. For anything operational it is not. Payment confirmation, deposit crediting, mempool-aware fee decisions and live monitoring all need data closer to the tip than a warehouse export is designed to deliver, and a job-based SQL interface is the wrong shape for a request that has to return while a user waits.

When BigQuery is the right choice

It is a good fit in three situations, and it is worth being honest that they are common ones. If your team already works in BigQuery and the real value is joining chain data to your own tables, having both in one warehouse beats any external API. If your workload is a handful of exploratory analyses a month, the free terabyte may cover the entire thing. And if you want full control over how metrics are derived, raw tables are exactly what you want, because nothing has been decided for you.

It fits badly when the questions repeat. A balance, a rich list, a UTXO set or a flow between entities is the same aggregation over the same history every time, and paying a full scan for an answer that has not changed shape since yesterday is the pattern that pushes teams to an indexed layer. That is the tradeoff behind our BigQuery Bitcoin dataset comparison: raw tables you aggregate yourself against balances, labels and flows already computed and reachable over REST as well as SQL against an indexed Bitcoin chain.

Neither is wrong. A warehouse export and an indexed data layer solve different halves of the same problem, and plenty of teams run both: BigQuery for the one-off analysis that needs raw rows, an indexed Bitcoin blockchain dataset for the questions that run on a schedule. The mistake is only ever assuming the raw dataset includes the derived layer, and then discovering the aggregation bill after the fact.

Last updated August 2026. Pricing and dataset details reflect Google's published figures at that date; verify current rates before budgeting. Informational on-chain data and analytics only, not investment, financial or legal advice.