Simplified relational diagram for Bitcoin public data on Google BigQuery

Find a simplified relational diagram for the Bitcoin data coming from an amazing opensource project https://github.com/blockchain-etl/bitcoin-etl.

Keeping cost low

• When working with crypto_bitcoin.blocks and crypto_bitcoin.transactions try to use partitioned field block_timestamp_month. This will speed up your queries and reduce your costs.

WHERE transactions.block_timestamp_month = '2021-02-01'
WHERE blocks.block_timestamp_month = '2010-12-01'

Location of the metadata on table partitions in BigQuery

Simplified diagram

• This version of the relational diagram does not show the record type fields transactions.input and transactions.output on bigquery-public-data:crypto_bitcoin.transactions as most SQL users are still not used to dealing with arrays or UNNEST function.
• I will post a more complex version of the diagram.

Demo SQL

Open this query direct in Google BigQuery.

Interactive diagram

Find an interactive diagram at https://dbdiagram.io/d/6023c75280d742080a39fb6d with more column-level details.