Skip to main content
Schema: somnia.core Table: fact_blocks Type: Base Table

​
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.

​
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 fact_transactions: Use block_number to analyze all transactions within a block
  • Join with fact_traces: Use block_number to examine internal transactions
  • Join with fact_event_logs: Use block_number 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
  • base_fee_per_gas: Base fee for EIP-1559 chains
  • tx_count: Number of transactions in the block
  • miner: Address that received block rewards

​
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,
    AVG(base_fee_per_gas) AS avg_base_fee,
    COUNT(*) AS blocks_count,
    SUM(tx_count) AS total_transactions
FROM <blockchain_name>.core.fact_blocks
WHERE block_timestamp >= CURRENT_DATE - 7
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
FROM <blockchain_name>.core.fact_blocks
WHERE block_timestamp >= CURRENT_DATE - 1
ORDER BY gas_used DESC
LIMIT 100;

​
Columns

Column NameData TypeDescription
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.
NETWORKTEXTNetwork identifier within the blockchain (e.g., ‘mainnet’, ‘testnet’). Example: ‘mainnet’
TX_COUNTNUMBERNumber of transactions included in the block. Example: 142
SIZENUMBERBlock size in bytes. Example: 125432
MINERTEXTAddress that received block rewards. Example: ‘0xea674fdde714fd979de3edf0f56aa9716b898ec8’
MIX_HASHTEXT256-bit hash used in PoW mining process. Post-merge, contains prevRandao for randomness. Example: ‘0x0000000000000000000000000000000000000000000000000000000000000000’
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
BASE_FEE_PER_GASNUMBERBase fee per gas unit in wei (EIP-1559 chains only). Example: 25000000000
DIFFICULTYNUMBERMining difficulty at block production time. Example: 0
SHA3_UNCLESTEXTKeccak-256 hash of uncle blocks list. Example: ‘0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347’
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’
RECEIPTS_ROOTTEXTMerkle root of all transaction receipts in the block. Example: ‘0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421’
STATE_ROOTTEXTMerkle root of the entire blockchain state after executing this block. Example: ‘0xd7f897bbebe1f8d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d4934’
TRANSACTIONS_ROOTTEXTMerkle root of all transactions in the block. Example: ‘0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421’
LOGS_BLOOMTEXT2048-bit bloom filter containing all log addresses and topics from the block’s transactions. Example: ‘0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000’
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: