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 TypeSource Event(s)ImpactGranularityDescription
Pool-level changeSwap, InitializeAll positions in poolHourlyCaptures latest tick and sqrtPriceX96 for the pool in a given hour
Position-level changeModifyLiquiditySpecific positionHourly (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.

Table Columns

Unique Key: position, activity_hour

Column NameData TypeDescription
idVARCHARUnique pool identifier used for grouping related positions
positionVARCHARSynthetic identifier for a position (id + tickLower + tickUpper)
activity_hourTIMESTAMP_NTZ(9)Hourly timestamp for the observation
sqrt_price_x96VARCHARSquare root price of the pool in Q64.96 format
sqrt_priceVARCHARHuman-readable square root price
is_in_rangeBOOLEANWhether the position is active (in-range) at this hour
tickNUMBER(38,0)Current pool tick
tick_upperNUMBER(38,0)Upper tick boundary of the position
tick_lowerNUMBER(38,0)Lower tick boundary of the position
liquidityNUMBER(38,0)Current liquidity provided by the position
token0_addressVARCHARToken0 contract address
token0_nameVARCHARToken0 name
token0_symbolVARCHARToken0 symbol
amount0_rawVARCHARToken0 raw amount (unadjusted for decimals)
token0_amountFLOATToken0 amount adjusted for decimals
token0_usd_amountFLOATUSD value of token0 at the activity hour
token1_addressVARCHARToken1 contract address
token1_nameVARCHARToken1 name
token1_symbolVARCHARToken1 symbol
amount1_rawVARCHARToken1 raw amount (unadjusted for decimals)
token1_amountFLOATToken1 amount adjusted for decimals
token1_usd_amountFLOATUSD value of token1 at the activity hour
tvl_usdFLOATTotal USD value of the position (token0 + token1) at current tick
static_pool_fieldsVARIANTStatic metadata about the pool (e.g., tick spacing, fee tier)
extra_pool_fieldsVARIANTHourly pool context (e.g., swaps, block metadata)
extra_position_fieldsVARIANTHourly position context (e.g., liquidity change summary, block metadata)
_updated_atTIMESTAMP_NTZ(9)Timestamp of last update to the row
_created_atTIMESTAMP_NTZ(9)Timestamp when the row was created