Ez State Validation

l2.ez_state_validation

Schema: ethereum.l2 Table: ez_state_validation Type: Base Table

This table contains the state validation for various L2 chains, including Optimistic Rollups (OP Stack) and Optimium (Mantle).

Columns

Column Name	Data Type	Description
BLOCK_NUMBER	NUMBER	Sequential 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. | | EVENT_INDEX | NUMBER | Zero-based sequential position of the event within a transaction’s execution.

Key Facts:

• Starts at 0 for first event
• Increments across all contracts in transaction
• Preserves execution order
• Essential for deterministic event ordering

Usage Example:

-- Trace event execution flow
SELECT
    event_index,
    contract_address,
    topic_0,
    SUBSTRING(data, 1, 10) AS data_preview
FROM <blockchain_name>.core.fact_event_logs
WHERE tx_hash = '0xabc...'
ORDER BY event_index;
``` |
| 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**:
```sql
-- 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. | | CONTRACT_ADDRESS | TEXT | Smart contract address that emitted this event or received the transaction.

Key Points:

• Always the immediate event emitter for logs
• May differ from transaction to_address
• Lowercase normalized format
• Never NULL for valid events | | CHAIN | TEXT | The name of the L2 chain. | | CHAIN_CATEGORY | TEXT | The category of the L2 chain. | | VALIDATION_ADDRESS | TEXT | The address where the state validation was made to. | | VALIDATION_TYPE | TEXT | The type of the state validation. | | VALIDATION_DATA | TEXT | The data of the state validation. | | VALIDATION_DATA_TYPE | TEXT | The data type of the state validation. | | VALIDATION_DATA_JSON | OBJECT | The JSON data of the state validation based on the validation type. | | FACT_STATE_VALIDATION_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;
``` |