Skip to main content
Schema: avalanche.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_TIME | TIMESTAMP_NTZ | The timestamp when the liquidity pool was deployed. Example: ‘2021-05-05 12:34:56.000’ | | CREATION_TX | TEXT | The transaction hash that deployed this liquidity pool. Example: ‘0x1234567890abcdef1234567890abcdef1234567890abcdef1234567890abcdef’ | | PLATFORM | TEXT | The DEX protocol where the swap occurred. Example: ‘uniswap_v3’ | | PROTOCOL | TEXT | | | PROTOCOL_VERSION | TEXT | | | FACTORY_ADDRESS | TEXT | The factory contract that deployed this liquidity pool. Example: ‘0x1f98431c8ad98523631ae4a59f267346ea31f984’ | | POOL_ADDRESS | TEXT | The liquidity pool contract address where the swap executed. Example: ‘0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8’ | | POOL_NAME | TEXT | Human-readable name for the liquidity pool. Example: ‘WETH/USDC 0.05%’ | | TOKENS | OBJECT | JSON object containing token contract addresses in the pool. Example: {"token0": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2", "token1": "0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48"} | | SYMBOLS | OBJECT | JSON object containing token symbols for the pool pair. Example: {"token0": "WETH", "token1": "USDC"} | | DECIMALS | OBJECT | JSON object containing decimal places for each token in the pool. Example: {"token0": 18, "token1": 6} | | DIM_DEX_LIQUIDITY_POOLS_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;
``` |