Skip to main content
Schema: crosschain.evm Table: fact_blocks Type: View

​
What

This table contains block-level data for EVM blockchains, providing a complete record of all blocks produced on the chain. Each row represents one block with its associated metadata, transactions, and gas metrics. See evm.dim_chains for a list of all supported EVM chains.

​
Key Use Cases

  • Analyzing gas price trends and network congestion over time
  • Tracking block production rates and miner/validator performance
  • Monitoring network upgrades and their impact (e.g., EIP-1559, EIP-4844)
  • Calculating transaction throughput and network utilization
  • Identifying uncle blocks and chain reorganizations

​
Important Relationships

  • Join with evm.fact_transactions: Use block_number and blockchain to analyze all transactions within a block
  • Join with evm.fact_traces: Use block_number and blockchain to examine internal transactions
  • Join with evm.fact_event_logs: Use block_number and blockchain to find all events emitted in a block

​
Commonly-used Fields

  • block_number: Sequential identifier for blocks
  • block_timestamp: UTC timestamp of block production
  • gas_used: Total gas consumed by all transactions
  • gas_limit: Maximum gas allowed in the block
  • tx_count: Number of transactions in the block
  • miner: Address that received block rewards
  • blockchain: The blockchain the block occurred on

​
Sample queries

-- Average gas price and utilization by hour
SELECT 
    DATE_TRUNC('hour', block_timestamp) AS hour,
    AVG(gas_used / gas_limit) AS avg_gas_utilization,
    COUNT(*) AS blocks_count,
    SUM(tx_count) AS total_transactions
FROM crosschain.evm.fact_blocks
WHERE block_timestamp >= CURRENT_DATE - 7
    AND blockchain = 'ethereum'
GROUP BY 1
ORDER BY 1 DESC;

-- Identify high-value blocks by total gas used
SELECT 
    block_number,
    block_timestamp,
    tx_count,
    gas_used,
    gas_limit,
    (gas_used::FLOAT / gas_limit) AS utilization_rate,
    miner,
    blockchain
FROM crosschain.evm.fact_blocks
WHERE block_timestamp >= CURRENT_DATE - 1
    AND blockchain = 'ethereum'
ORDER BY gas_used DESC
LIMIT 100;

​
Columns

Column NameData TypeDescription
BLOCKCHAINTEXTThe blockchain the record occurred on. See evm.dim_chains for a list of all EVM chains. Format: VARCHAR Example: ‘ethereum’ Usage: Filtering by blockchain Joining across tables Analyzing chain-specific patterns
BLOCK_NUMBERNUMBERSequential counter representing the position of a block in the blockchain since genesis (block 0). Key Facts: Immutable once finalized Primary ordering mechanism for blockchain data Increments by 1 for each new block Used as a proxy for time in many analyses Usage in Queries: Important: Block numbers are chain-specific. Block 15000000 on Ethereum ≠ block 15000000 on Polygon.
BLOCK_HASHTEXTThe unique 32-byte Keccak-256 hash of the block header, prefixed with ‘0x’. Example: ‘0x4e3a3754410177e6937ef1f84bba68ea139e8d1a2258c5f85db9f1cd715a1bdd’
BLOCK_TIMESTAMPTIMESTAMP_NTZUTC timestamp when the block was produced by validators/miners. Format: TIMESTAMP_NTZ (no timezone) Precision: Second-level accuracy Reliability: Set by block producer Can have minor variations (±15 seconds) Always increasing (newer blocks = later timestamps) Best Practices: Note: Use for time-series analysis, but be aware that block production rates vary by chain.
TX_COUNTNUMBERNumber of transactions included in the block. Example: 142
SIZENUMBERBlock size in bytes. Example: 125432
MINERTEXTAddress that received block rewards. Example: ‘0xea674fdde714fd979de3edf0f56aa9716b898ec8’
EXTRA_DATATEXTArbitrary data included by block producer (max 32 bytes). Example: ‘Geth/v1.10.23-stable/linux-amd64/go1.18.5’
PARENT_HASHTEXTHash of the previous block (block_number - 1). Example: ‘0x3d7a3754410177e6937ef1f84bba68ea139e8d1a2258c5f85db9f1cd715a1bee’
GAS_USEDNUMBERTotal gas consumed by all transactions in the block. Example: 15234567
GAS_LIMITNUMBERMaximum gas allowed for all transactions in this block. Example: 30000000
UNCLE_BLOCKSVARIANTArray of uncle block headers (PoW only). Example: []
NONCENUMBERProof-of-Work nonce value. For PoW chains, this demonstrates computational work. Post-merge Ethereum and PoS chains typically show 0x0000000000000000. Example: ‘0x0000000000000000’
FACT_BLOCKS_IDTEXTPrimary key - unique identifier for each row ensuring data integrity. Format: Usually VARCHAR containing composite key generated using MD5 hash of the relevant columns. Example: MD5(blocknumber, txhash, trace_index) Usage: Deduplication in incremental loads Join operations for data quality checks Troubleshooting specific records Important: Implementation varies by table - check table-specific documentation.
INSERTED_TIMESTAMPTIMESTAMP_NTZUTC timestamp when the record was first added to the Flipside database. Format: TIMESTAMP_NTZ Use Cases: Data freshness monitoring Incremental processing markers Debugging data pipeline issues SLA tracking Query Example:
MODIFIED_TIMESTAMPTIMESTAMP_NTZUTC timestamp of the most recent update to this record. Format: TIMESTAMP_NTZ Triggers for Updates: Data corrections Enrichment additions Reprocessing for accuracy Schema migrations Monitoring Usage: