Overview
- Dataset ID:
0411 - Table:
0411_liquidity_pools_yields - What it is: Rolling yield/ROI predictions per pool over fixed horizons (1D, 3D, 7D, 14D, 30D, 90D, 365D), based on historical swap fees (
0303_liquidity_pools_swap_fees) and current reserves (latest0301snapshot). - Grain: 1 row per (pool, as-of event triple, horizon).
- Primary location fields:
pool_uid,block_number,block_time(UTC),tx_index,log_index - Join keys:
- Pool:
pool_uid→0203_liquidity_pools.uid - Provenance:
(block_number, tx_index, log_index)→in0103_logs
- Pool:
This dataset emits aligned arrays (one element per pool token). Use
tokens[] to line up current_reserves[], volumes, fees, and ROI predictions.Core concepts
- Target horizon vs observed history
target_period_days: horizon you requested (1, 3, 7, 14, 30, 90, 365)observed_period_days: how many days of history were available/used
- Extrapolation
- When
observed_period_days < target_period_days, the dataset marks the row as extrapolated and scales observed values by:- (extrapolation_factor = target_period_days / observed_period_days)
- When
- ROI
- For each token index
i:- (roi_predicted[i] = user_fees_predicted[i] / current_reserves[i])
- For each token index
Columns
| Column | Type | Description |
|---|---|---|
id | BIGINT | Surrogate identity for the yield record (auto-incremented). |
pool_uid | BYTEA | Pool identifier (internal). |
block_number | BIGINT | Block height of the as-of snapshot anchor. |
block_time | TIMESTAMPTZ | UTC timestamp of the as-of snapshot anchor. |
tx_index | INTEGER | Transaction index within the block. |
log_index | INTEGER | Log index within the transaction. |
target_period_days | SMALLINT | Target horizon in days. Allowed: 1, 3, 7, 14, 30, 90, 365. |
observed_period_days | SMALLINT | History actually used (must be > 0 and <= target_period_days). |
is_full_period | BOOLEAN | true when observed_period_days == target_period_days. |
is_extrapolated | BOOLEAN | true when the row is scaled from shorter history. |
extrapolation_factor | NUMERIC(18,6) | Scaling factor applied when extrapolating (must be > 1 when extrapolated; 1 when full period). |
window_start_time | TIMESTAMPTZ | Start time of the observed window. |
window_end_time | TIMESTAMPTZ | End time of the observed window. |
tokens | BYTEA[] | Pool token addresses (aligned arrays index by this order). |
current_reserves | NUMERIC(78,18)[] | Current reserves per token (decimals-adjusted). |
volume_observed | NUMERIC(78,18)[] | Observed traded volume per token in the window. |
volume_predicted | NUMERIC(78,18)[] | Predicted traded volume per token for the target horizon. |
user_fees_observed | NUMERIC(78,18)[] | Observed user/LP fees per token in the window. |
user_fees_predicted | NUMERIC(78,18)[] | Predicted user/LP fees per token for the target horizon. |
roi_predicted | NUMERIC(78,18)[] | Predicted ROI fraction per token for the target horizon. |
_tracing_id | BYTEA | Deterministic BlockDB lineage identifier for the yield record. |
_genesis_tracing_ids | BYTEA[] | Tracing IDs for the raw artifacts that seeded this record. |
_parent_tracing_ids | BYTEA[] | Tracing IDs for upstream derived records referenced during computation. |
_created_at | TIMESTAMPTZ | Record creation timestamp. |
_updated_at | TIMESTAMPTZ | Record update timestamp. |
Use Cases
- Pool-level yield and ROI forecasting across standardized horizons
- Ranking pools by predicted fee yield (token-wise and pool-wise)
- Inputs for portfolio allocation and liquidity mining analytics
- Validating fee models by comparing predicted vs realized forward windows