Registry

dimensions.dex_pools holds one row per DEX pool, which is one place to swap tokens on one chain. It brings together the pools of every project we cover, read from the events that created them, so it lists every pool that has ever existed rather than only the ones still in use.

Columns

Column	Type	Description
`dex_pool_id`	`STRING`	Identifier of the pool: the pool address joined with the chain.
`deployment_id`	`STRING`	The operating app’s deployment on the pool’s chain.
`protocol_id`	`STRING`	The app operating the pool, for example `uniswap-v3`.
`chain_id`	`STRING`	Chain the pool lives on.
`project_id`	`STRING`	Project the operating app belongs to.
`pool_address`	`STRING`	Onchain address of the pool. Where one contract holds every pool, this is the pool’s id inside that contract.
`pool_name`	`STRING`	The pool’s tokens as a name, for example `USDC-WETH`.
`pool_display_name`	`STRING`	Pool name with the fee added on, where the pool charges a fixed one.
`fee_tier`	`BIGNUMERIC`	The fee the pool charges on every swap, as a decimal fraction, so `0.0005` reads 0.05%. Empty where the venue sets the fee swap by swap.
`created_at`	`TIMESTAMP`	Time of the block the pool was created in.
`hooks_address`	`STRING`	Address of the pool’s hooks contract, on venues that attach one. A hooks contract is extra code the venue runs around each swap.
`token_ids`	`ARRAY<STRING>`	The tokens the pool holds, in pool order, as keys of `dimensions.tokens`.

Sample queries

Pools per chain
A pool's tokens
Pools for one pair

Count pools by protocol version and chain. protocol_id names the app operating the pool and deployment_id names that app’s deployment on the pool’s chain.

select
    protocol_id,
    chain_id,
    count(*) as pools
from `dimensions.dex_pools`
group by protocol_id, chain_id
order by pools desc
limit 20

Look up a pool’s tokens. token_ids lists the pool’s tokens, in the order the pool holds them, as keys of dimensions.tokens. Symbol, name, decimals and asset_id live on that token table rather than on the pool, so join it on one element of the list.

select
    pools.dex_pool_id,
    pools.pool_display_name,
    position,
    tokens.symbol,
    tokens.decimals,
    tokens.asset_id
from `dimensions.dex_pools` as pools,
    unnest(pools.token_ids) as token_id with offset as position
join `dimensions.tokens` as tokens
    using (token_id)
where pools.dex_pool_id = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640-ethereum'
order by position

Find every pool that holds one pair. Check that both tokens appear in the list.

select
    dex_pool_id,
    protocol_id,
    pool_display_name,
    fee_tier
from `dimensions.dex_pools`
where chain_id = 'ethereum'
  and '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48-ethereum' in unnest(token_ids)
  and '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2-ethereum' in unnest(token_ids)
order by created_at
limit 50

Notes

A pool holds two or more tokens, and a pair is any two of them, so there is no separate table of pairs. A pool holding three tokens has three possible pairs, readable straight off token_ids. Keeping the tokens in a list is also what lets a pool hold more than two, which fixed token0 and token1 columns could not. Which of those pairs users traded is a separate question, and a matter of record rather than of membership. The pairs that traded and their volumes are on the daily pair table, keyed by dex_pair_id. fee_tier is filled in for Uniswap v2, where it is fixed at 0.30%, and for v3, where the pool sets it onchain. It is empty for every v4 pool, because a v4 pool’s fee can be changed swap by swap by its hooks contract. For those pools, the fee a trade paid is fee_rate on Trades rather than anything on the pool. This table lists every pool, including ones that never traded and ones long abandoned.

Overview

Core entities

Market verticals

Chain verticals

Columns

Sample queries

Notes

​Columns

​Sample queries

​Notes

Columns

Sample queries

Notes