Skip to main content
Schema: optimism.core Table: dim_contracts Type: Base Table

​
What

This table provides comprehensive metadata for all smart contracts deployed on EVM blockchains. It includes contract names, symbols, decimals, and deployment details read directly from the blockchain.

​
Key Use Cases

  • Identifying contracts by name, symbol, or address
  • Understanding token properties (decimals, symbols)
  • Tracking contract deployment patterns and trends
  • Finding contracts deployed by specific factories or deployers
  • Filtering protocol-specific data across other tables

​
Important Relationships

  • Join with fact_transactions: Use address = to_address for contract interactions
  • Join with fact_event_logs: Use address = contract_address for contract events
  • Join with ez_token_transfers: Use address = contract_address for token movements

​
Commonly-used Fields

  • address: The deployed contract’s blockchain address (lowercase)
  • name: Human-readable contract name from the name() function
  • symbol: Token/contract symbol from the symbol() function
  • decimals: Number of decimal places for token amounts
  • creator_address: Address that deployed this contract
  • created_block_timestamp: When the contract was created

​
Sample queries

Find All Uniswap V3 Pool Contracts 
SELECT
    address,
    name,
    created_block_number,
    created_block_timestamp,
    creator_address
FROM <blockchain_name>.core.dim_contracts
WHERE creator_address = LOWER('0x1F98431c8aD98523631AE4a59f267346ea31F984') -- Uniswap V3 Factory
ORDER BY created_block_number DESC
LIMIT 100;
Analyze Contract Deployment Trends 
SELECT
    DATE_TRUNC('week', created_block_timestamp) AS week,
    COUNT(*) AS contracts_deployed,
    COUNT(DISTINCT creator_address) AS unique_deployers
FROM <blockchain_name>.core.dim_contracts
WHERE created_block_timestamp >= CURRENT_DATE - 90
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

​
Columns

Column NameData TypeDescription
ADDRESSTEXTUnique identifier - the deployed contract’s blockchain address.
Example: ‘0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48’ | | SYMBOL | TEXT | Token/contract symbol from the symbol() function. Example: ‘USDC’ | | NAME | TEXT | Human-readable contract name from the name() function. Example: ‘USD Coin’ | | DECIMALS | NUMBER | Number of decimal places for token amounts, read directly from the contract code. Example: 6 | | CREATED_BLOCK_NUMBER | NUMBER | Block number when contract was created. Example: 17500000 | | CREATED_BLOCK_TIMESTAMP | TIMESTAMP_NTZ | Timestamp when contract was created. Example: 2023-06-15 14:30:00.000 | | CREATED_TX_HASH | TEXT | Transaction hash of the contract deployment. Example: ‘0x4f01db1f857e711af502ad6fa8b5b3ccd9e36b5f8c8a7b2c1d3e4f5a6b7c8d9e’ | | CREATOR_ADDRESS | TEXT | Address that deployed this contract (transaction from_address). Example: ‘0x95ba4cf87d6723ad9c0db21737d862be80e93911’ | | DIM_CONTRACTS_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;
``` |