Skip to main content
Schema: optimism.gov Table: fact_delegations Type: View This table contains the details of the OP Delegations made by users on the Optimism network.

ā€‹
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 | | STATUS | TEXT | The status of the tx, can be ā€œSuccessā€ or ā€œFailedā€ | | EVENT_NAME | TEXT | | | DELEGATOR | TEXT | The address of the delegator | | DELEGATE | TEXT | The address of the delegate | | DELEGATION_TYPE | TEXT | The type of delegation, can be ā€œFirst Time Delegatorā€, ā€œSelf Delegationā€, ā€œRe-Delegationā€ or ā€œFirst Time Delegator - Self Delegationā€ | | NEW_BALANCE | FLOAT | The new amount the user has delegated after the transaction | | PREVIOUS_BALANCE | FLOAT | The amount of OP the user had delegated before the transaction | | RAW_NEW_BALANCE | NUMBER | The new amount the user has delegated after the transaction in raw format | | RAW_PREVIOUS_BALANCE | NUMBER | The amount of OP the user had delegated before the transaction in raw format | | TO_DELEGATE | TEXT | The address of the delegate to which the delegator has delegated their votes | | FROM_DELEGATE | TEXT | The address of the delegate from which the delegator has delegated their votes | | FACT_DELEGATIONS_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;
``` |