Skip to main content
Schema: somnia.defi Table: dim_dex_liquidity_pools Type: View

​
What

This dimensional table contains comprehensive metadata for all DEX liquidity pools across supported protocols. It provides essential information about pool composition, token pairs, and configuration needed for analyzing liquidity provision and pool performance.

​
Key Use Cases

  • Finding all pools containing specific tokens
  • Tracking new pool deployments
  • Analyzing pool configurations and fee structures
  • Identifying trading pairs across different protocols
  • Monitoring factory contract deployments

​
Important Relationships

  • Join with ez_dex_swaps: Use pool_address to get swap activity
  • Join with dim_contracts: Use token addresses for additional metadata
  • Self-join: Find all pools with common tokens

​
Commonly-used Fields

  • pool_address: Unique identifier for the liquidity pool
  • platform: DEX protocol (uniswap_v3, curve, etc.)
  • pool_name: Human-readable pool identifier
  • tokens: JSON with token0 and token1 addresses
  • symbols: JSON with token0 and token1 symbols
  • creation_time: When pool was deployed

​
Sample queries

-- Find all pools containing USDC
SELECT 
    pool_address,
    pool_name,
    platform,
    creation_time,
    CASE 
        WHEN tokens:token0::string = LOWER('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48') THEN 
            symbols:token1::string
        ELSE 
            symbols:token0::string
    END AS paired_token
FROM <blockchain_name>.defi.dim_dex_liquidity_pools
WHERE LOWER('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48') IN (
    tokens:token0::string,
    tokens:token1::string
)
ORDER BY creation_time DESC;

-- Recently created liquidity pools
SELECT 
    platform,
    pool_address,
    pool_name,
    creation_time,
    creation_tx,
    symbols:token0::string || '/' || symbols:token1::string AS pair,
    factory_address
FROM <blockchain_name>.defi.dim_dex_liquidity_pools
WHERE creation_time >= CURRENT_DATE - 7
ORDER BY creation_time DESC
LIMIT 100;

-- Extract token information from JSON fields
SELECT 
    pool_address,
    tokens:token0::string AS token0_address,
    tokens:token1::string AS token1_address,
    symbols:token0::string AS token0_symbol,
    symbols:token1::string AS token1_symbol,
    decimals:token0::integer AS token0_decimals,
    decimals:token1::integer AS token1_decimals
FROM <blockchain_name>.defi.dim_dex_liquidity_pools
WHERE platform = 'uniswap_v3';

​
Columns

Column NameData TypeDescription
CREATION_BLOCKNUMBERThe block number when the liquidity pool was first created. Example: 12369739
CREATION_TIMETIMESTAMP_NTZThe timestamp when the liquidity pool was deployed. Example: ‘2021-05-05 12:34:56.000’
CREATION_TXTEXTThe transaction hash that deployed this liquidity pool. Example: ‘0x1234567890abcdef1234567890abcdef1234567890abcdef1234567890abcdef’
PLATFORMTEXTThe DEX protocol where the swap occurred. Example: ‘uniswap_v3’
PROTOCOLTEXTPROTOCOL column
PROTOCOL_VERSIONTEXTPROTOCOL_VERSION column
FACTORY_ADDRESSTEXTThe factory contract that deployed this liquidity pool. Example: ‘0x1f98431c8ad98523631ae4a59f267346ea31f984’
POOL_ADDRESSTEXTThe liquidity pool contract address where the swap executed. Example: ‘0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8’
POOL_NAMETEXTHuman-readable name for the liquidity pool. Example: ‘WETH/USDC 0.05%‘
TOKENSOBJECTJSON object containing token contract addresses in the pool. Example: {"token0": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2", "token1": "0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48"}
SYMBOLSOBJECTJSON object containing token symbols for the pool pair. Example: {"token0": "WETH", "token1": "USDC"}
DECIMALSOBJECTJSON object containing decimal places for each token in the pool. Example: {"token0": 18, "token1": 6}
DIM_DEX_LIQUIDITY_POOLS_IDTEXTPrimary 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(blocknumber, txhash, 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_TIMESTAMPTIMESTAMP_NTZUTC 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:
MODIFIED_TIMESTAMPTIMESTAMP_NTZUTC 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: