Skip to main content

Block

Overview​

The block table stores blockchain block metadata for tracking synchronization state and providing temporal context for all on-chain data.

Schema​

FieldTypeModeDescription
idBYTESREQUIREDClient-generated deterministic ID using MD5 hash of (network_id, number). 128-bit hash stored as BYTES
network_idINT64REQUIREDForeign key to network table
numberINTEGERREQUIREDThe block number on the blockchain
datetimeTIMESTAMPREQUIREDUTC timestamp when the block was mined/produced
ingested_atTIMESTAMPREQUIREDTimestamp when this record was inserted into BigQuery

Primary Key​

  • id: A deterministic ID generated using MD5 hash of network_id and block number

Foreign Keys​

  • network_id: References the network table

Usage Examples​

-- Get the latest block for each network
SELECT n.name AS network_name, MAX(b.number) AS latest_block
FROM `bitpulse.blockchain.block` b
JOIN `bitpulse.blockchain.network` n ON b.network_id = n.id
GROUP BY n.name
ORDER BY n.name;

-- Get block timestamps for a specific date range
SELECT number, datetime
FROM `bitpulse.blockchain.block`
WHERE network_id = 1 -- Ethereum Mainnet
AND datetime BETWEEN '2023-01-01' AND '2023-01-02'
ORDER BY number;

Notes​

  • Block data is fundamental to all blockchain analysis as it provides the temporal context for all on-chain events.
  • The datetime field can be used for time-based analysis and joining with other tables.
  • Block data is typically ingested in real-time as new blocks are produced on the blockchain.