Liquidity Pool

Overview

The liquidity_pool table tracks individual liquidity pool instances across all DEXes, storing pool creation data and identity information. This table serves as a central registry for all liquidity pools tracked in the system.

Schema

Field	Type	Mode	Description
id	STRING	REQUIRED	Client-generated deterministic ID using truncated MD5 hash of (dex_instance_id, address). Same pool always gets same ID
dex_instance_id	STRING	REQUIRED	Foreign key to dex_instance table
pool_creation_datetime	TIMESTAMP	NULLABLE	UTC timestamp when the pool was created on-chain
pool_creation_block	INTEGER	NULLABLE	Block number when the pool was created
name	STRING	NULLABLE	Human-readable pool name (e.g., 'WETH/USDC 0.05%')
address	STRING	REQUIRED	Pool contract address on the blockchain
ingested_at	TIMESTAMP	REQUIRED	Timestamp when this record was inserted into BigQuery

Primary Key

• id: A deterministic ID generated using truncated MD5 hash of dex_instance_id and pool address

Foreign Keys

• dex_instance_id: References the dex_instance table

Usage Examples

-- Get all liquidity pools for a specific DEX
SELECT lp.id, lp.name, lp.address, lp.pool_creation_datetime
FROM `bitpulse.dex.liquidity_pool` lp
JOIN `bitpulse.dex.dex_instance` di ON lp.dex_instance_id = di.id
WHERE di.name = 'Uniswap V3'
AND di.network_id = 1 -- Ethereum Mainnet
ORDER BY lp.pool_creation_datetime DESC
LIMIT 100;

-- Find the oldest liquidity pools by DEX
SELECT di.name AS dex_name, 
       MIN(lp.pool_creation_datetime) AS earliest_pool,
       COUNT(*) AS pool_count
FROM `bitpulse.dex.liquidity_pool` lp
JOIN `bitpulse.dex.dex_instance` di ON lp.dex_instance_id = di.id
GROUP BY di.name
ORDER BY earliest_pool;

Notes

• The liquidity_pool table serves as a central registry for all DEX pools tracked in the system.
• Each pool has a unique ID that remains consistent across all related tables.
• The human-readable name field is useful for display purposes but may not be available for all pools.
• Pool creation data (pool_creation_datetime and pool_creation_block) may be null for pools created before data collection began.