Skip to main content
Schema: ethereum.l2 Table: ez_data_availability Type: Base Table This table contains the data availability submissions for various L2 chains, including Optimistic Rollups (OP Stack and Arbitrum) and ZK Rollups (Scroll).

​
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_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. | | TX_HASH | TEXT | Unique 66-character identifier for the transaction. Format: 0x + 64 hexadecimal characters Usage:
  • Primary key for transaction lookups
  • Join key for traces, logs, and token transfers
  • Immutable once confirmed
Example: 0x5c504ed432cb51138bcf09aa5e8a410dd4a1e204ef84bfed1be16dfba1b22060 | | ORIGIN_FROM_ADDRESS | TEXT | The externally-owned account (EOA) or contract address that initiated the transaction. Key Points:
  • Always 42 characters (0x + 40 hex chars)
  • Lowercase normalized in all tables
  • Cannot be NULL for valid transactions
  • For contract creation: sender of creation transaction
Common Patterns:
  • EOA → EOA: Simple transfer
  • EOA → Contract: User interaction
  • Contract → Contract: Internal calls (see fact_traces)
  • Known addresses: Exchange hot wallets, protocol deployers
Query Examples: 
-- User activity analysis
SELECT from_address, COUNT(*) as tx_count
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 2 DESC;

-- New user detection
SELECT DISTINCT from_address
FROM <blockchain_name>.core.fact_transactions t1
WHERE NOT EXISTS (
    SELECT 1 FROM <blockchain_name>.core.fact_transactions t2
    WHERE t2.from_address = t1.from_address
    AND t2.block_number < t1.block_number
);
``` |
| ORIGIN_TO_ADDRESS | TEXT | The destination address for the transaction - either an EOA or contract address.

**Special Cases**:
- NULL: Contract creation transaction
- Contract address: Interacting with smart contract
- EOA address: Simple transfer or receiving funds

**Important Patterns**:
```sql
-- Contract deployments
WHERE to_address IS NULL

-- Popular contracts
SELECT to_address, COUNT(*) as interactions
FROM <blockchain_name>.core.fact_transactions
WHERE to_address IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

-- Direct transfers only
WHERE to_address NOT IN (SELECT address FROM dim_contracts)
Note: For token transfers, this is the token contract, not the recipient. See ez_token_transfers tables for recipient. | | CHAIN | TEXT | The name of the L2 chain. | | CHAIN_CATEGORY | TEXT | The category of the L2 chain. | | DATA_AVAILABILITY_ADDRESS | TEXT | The address where the data availability submission was made to. | | SUBMISSION_TYPE | TEXT | The type of the data availability submission. | | BLOB_COUNT | NUMBER | The number of blobs submitted in this transaction. | | BLOB_GAS_USED | NUMBER | The total amount of blob gas used in this transaction. | | BLOB_GAS_PRICE | NUMBER | The price paid per unit of blob gas, denominated in wei. | | BLOB_FEE | FLOAT | The total blob fee paid for this transaction, denominated in ETH. | | FACT_DATA_AVAILABILITY_SUBMISSION_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;
``` |