Uniswap v4 Position Holdings Hourly
Get position holdings of Uniswap v4 positions at the hourly granularity and power pool-level TVL calculations.
The dex.uniswap_v4_position_holdings_hourly
table tracks the hourly changes in v4 position holdings. Changes are derived from position-level liquidity and pool-level price changes. This enables accurate tracking of position and pool level LP holdings, amounts, and TVL over time.
- Status: This model is currently in beta and may be subject to change.
Note: This table only captures hourly changes to Uniswap v4 position amounts. To obtain the full state at a given hour/day (e.g., for TVL or amount0/1 snapshots), a forward-fill of historical records is required. See example query below.
Methodology
- This table captures updates from both position-level and pool-level changes:
- Position-level changes come from the ModifyLiquidity v4 event. These affect individual positions and require cumulative sums of liquidity up to each hour. Contextual details are stored in extra_position_fields.
- Pool-level changes are derived from the most recent Swap or Initialize v4 event in each hour. These impact all positions within the pool. Contextual swap activity is stored in extra_pool_fields and static fields are stored in static_pool_fields.
- Positions are uniquely identified by a synthetic ID built from (id, tickLower, tickUpper) due to the absence of an emitted token_id in v4. The final model performs an outer join between the liquidity and price change updates at the (pool, position, hour) level. It forward-fills missing data and computes token amounts using Uniswap source math (inputs: sqrt_price_x96, tick, tick_lower, tick_upper, liquidity).
Change Types
Change Type | Source Event(s) | Impact | Granularity | Description |
---|---|---|---|---|
Pool-level change | Swap , Initialize | All positions in pool | Hourly | Captures latest tick and sqrtPriceX96 for the pool in a given hour |
Position-level change | ModifyLiquidity | Specific position | Hourly (cumulative) | Tracks liquidity deltas per position; cumulative sum required per hour |
TVL in USD is defined as the sum of amount0_usd and amount1_usd across all positions in a given pool, priced at the latest hourly pool tick.
Sample Query
How to generate daily balances from block-level balances table.
Generating Daily TVL of v4 Pool from Hourly Changes
Generating Daily TVL of v4 Pool from Hourly Changes
Table Columns
Unique Key: position
, activity_hour
Column Name | Data Type | Description |
---|---|---|
id | VARCHAR | Unique pool identifier used for grouping related positions |
position | VARCHAR | Synthetic identifier for a position (id + tickLower + tickUpper) |
activity_hour | TIMESTAMP_NTZ(9) | Hourly timestamp for the observation |
sqrt_price_x96 | VARCHAR | Square root price of the pool in Q64.96 format |
sqrt_price | VARCHAR | Human-readable square root price |
is_in_range | BOOLEAN | Whether the position is active (in-range) at this hour |
tick | NUMBER(38,0) | Current pool tick |
tick_upper | NUMBER(38,0) | Upper tick boundary of the position |
tick_lower | NUMBER(38,0) | Lower tick boundary of the position |
liquidity | NUMBER(38,0) | Current liquidity provided by the position |
token0_address | VARCHAR | Token0 contract address |
token0_name | VARCHAR | Token0 name |
token0_symbol | VARCHAR | Token0 symbol |
amount0_raw | VARCHAR | Token0 raw amount (unadjusted for decimals) |
token0_amount | FLOAT | Token0 amount adjusted for decimals |
token0_usd_amount | FLOAT | USD value of token0 at the activity hour |
token1_address | VARCHAR | Token1 contract address |
token1_name | VARCHAR | Token1 name |
token1_symbol | VARCHAR | Token1 symbol |
amount1_raw | VARCHAR | Token1 raw amount (unadjusted for decimals) |
token1_amount | FLOAT | Token1 amount adjusted for decimals |
token1_usd_amount | FLOAT | USD value of token1 at the activity hour |
tvl_usd | FLOAT | Total USD value of the position (token0 + token1) at current tick |
static_pool_fields | VARIANT | Static metadata about the pool (e.g., tick spacing, fee tier) |
extra_pool_fields | VARIANT | Hourly pool context (e.g., swaps, block metadata) |
extra_position_fields | VARIANT | Hourly position context (e.g., liquidity change summary, block metadata) |
_updated_at | TIMESTAMP_NTZ(9) | Timestamp of last update to the row |
_created_at | TIMESTAMP_NTZ(9) | Timestamp when the row was created |