Skip to main content
Schema: avalanche.dexalot 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: 
-- Recent data
WHERE block_number >= (SELECT MAX(block_number) - 1000 FROM fact_blocks)

-- Historical analysis
WHERE block_number BETWEEN 15000000 AND 16000000

-- Join across tables
JOIN <blockchain_name>.core.fact_event_logs USING (block_number)
Important: Block numbers are chain-specific. Block 15000000 on Ethereum ≠ block 15000000 on Polygon. | | BLOCK_HASH | TEXT | The unique 32-byte Keccak-256 hash of the block header, prefixed with ‘0x’. Example: ‘0x4e3a3754410177e6937ef1f84bba68ea139e8d1a2258c5f85db9f1cd715a1bdd’ | | BLOCK_TIMESTAMP | TIMESTAMP_NTZ | UTC 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: 
-- Time-based filtering (most efficient)
WHERE block_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP)

-- Hourly aggregations
DATE_TRUNC('hour', block_timestamp) AS hour

-- UTC date extraction
DATE(block_timestamp) AS block_date
Note: Use for time-series analysis, but be aware that block production rates vary by chain. | | NETWORK | TEXT | Network identifier within the blockchain (e.g., ‘mainnet’, ‘testnet’). Example: ‘mainnet’ | | TX_COUNT | NUMBER | Number of transactions included in the block. Example: 142 | | SIZE | NUMBER | Block size in bytes. Example: 125432 | | MINER | TEXT | Address that received block rewards. Example: ‘0xea674fdde714fd979de3edf0f56aa9716b898ec8’ | | EXTRA_DATA | TEXT | Arbitrary data included by block producer (max 32 bytes). Example: ‘Geth/v1.10.23-stable/linux-amd64/go1.18.5’ | | PARENT_HASH | TEXT | Hash of the previous block (block_number - 1). Example: ‘0x3d7a3754410177e6937ef1f84bba68ea139e8d1a2258c5f85db9f1cd715a1bee’ | | GAS_USED | NUMBER | Total gas consumed by all transactions in the block. Example: 15234567 | | GAS_LIMIT | NUMBER | Maximum gas allowed for all transactions in this block. Example: 30000000 | | BASE_FEE_PER_GAS | NUMBER | Base fee per gas unit in wei (EIP-1559 chains only). Example: 25000000000 | | DIFFICULTY | NUMBER | Mining difficulty at block production time. Example: 0 | | TOTAL_DIFFICULTY | NUMBER | Cumulative difficulty from genesis to this block. Example: 58750000000000000000000 | | UNCLE_BLOCKS | VARIANT | Array of uncle block headers (PoW only). Example: [] | | NONCE | NUMBER | Proof-of-Work nonce value. For PoW chains, this demonstrates computational work. Post-merge Ethereum and PoS chains typically show 0x0000000000000000. Example: ‘0x0000000000000000’ | | NUMBER | NUMBER | | | SHA3_UNCLES | TEXT | Keccak-256 hash of uncle blocks list. Example: ‘0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347’ | | RECEIPTS_ROOT | TEXT | Merkle root of all transaction receipts in the block. Example: ‘0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421’ | | STATE_ROOT | TEXT | Merkle root of the entire blockchain state after executing this block. Example: ‘0xd7f897bbebe1f8d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d4934’ | | TRANSACTIONS_ROOT | TEXT | Merkle root of all transactions in the block. Example: ‘0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421’ | | LOGS_BLOOM | TEXT | 2048-bit bloom filter containing all log addresses and topics from the block’s transactions. Example: ‘0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000’ | | FACT_BLOCKS_ID | TEXT | Primary 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(block_number, tx_hash, 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_TIMESTAMP | TIMESTAMP_NTZ | UTC 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: 
-- Check data latency
SELECT
    DATE_TRUNC('hour', block_timestamp) as block_hour,
    DATE_TRUNC('hour', inserted_timestamp) as insert_hour,
    AVG(TIMESTAMPDIFF('minute', block_timestamp, inserted_timestamp)) as avg_latency_minutes
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2;
``` |
| MODIFIED_TIMESTAMP | TIMESTAMP_NTZ | UTC 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**:
```sql
-- Recently modified records
SELECT *
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
AND modified_timestamp >= CURRENT_DATE - 1;

-- Data quality tracking
SELECT
    DATE(modified_timestamp) as mod_date,
    COUNT(*) as records_updated,
    COUNT(DISTINCT block_number) as blocks_affected
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
GROUP BY 1
ORDER BY 1 DESC;
``` |
| HASH | TEXT |  |