Open 0xRobin opened 5 months ago
catching up here - in this case, for a given uniswap pair it would have two rows in uniswap.tvl_base? also balances is already forward filled so no need to ffill again on downstream models?
in this case, for a given uniswap pair it would have two rows in uniswap.tvl_base?
Correct, the base model would have a row for each address and token combo. Other models can be build on top that aggregate the balances based on address, project, version,..
also balances is already forward filled so no need to ffill again on downstream models
balances is only forward filled in balances.daily
where the forward fill happens in a view which makes it not suitable for more advanced queries (It'll time out). balances.daily_agg
is materialized but does not contain fully filled data
heys guys, very interesting discussion! did you have any progress on it in the past weeks? one suggestion I have for the schema name is protocols
which is well-adopted by bridges, decentralized exchanges, lending, etc. Some examples:
A reliable, open-source, permissionless protocol. Build on Balancer to create new innovative types of pools and financial dApps.
The Aave Protocol is decentralised non-custodial liquidity protocol where users can participate as suppliers, borrowers or liquidators
LI.FI is a multi-chain liquidity aggregation protocol that supports any-2-any swaps by aggregating bridges and DEX aggregators across +20 networks.
I also agree liquidity
isn't the best term and I do regret creating balancer.liquidity
a few years ago 😅 TBH if I had the chance to change it today would do to balancer.tvl
since I particularly don't see the problem with the abbreviation, it's short to write, and it's probably the most-known term/metric in crypto.
thanks, added your suggestion to the list! From Dune's side there's currently no plan to start on the implementation, but anyone from the community is free to start working on it!
Current State of Balances:
We currently employ two models for managing balances:
tokens.balances
: This table is fully materialized and contains a balance record for each timestamp where a balance update was detected. However, due to the sparse nature of the data, it can be challenging to work with effectively.tokens.balances_daily
: This model enhances the sparse raw data intokens.balances
by forward-filling, providing a balance for each day, even when the balance hasn't changed. Additionally, both models have views that enrich the data with token metadata (which will be disregarded for the current discussion).However, these models are not optimal for complex queries, such as aggregating balances of known Uniswap pools or calculating the average balance over time of a defi lending project.
Goal:
The aim of
<something>.TVL
(name to be determined) is to create a fully materialized, dense balances table for a specific subset of address <> token pairs. This subset will allow for more flexible querying, as the compute-intensive forward-fill logic won't need to be rerun for each query.Schema:
The proposed schema aligns with the main balances schema, with the primary axis being
day
. It includes aggregation level columns(category, project, version)
and the primary balance columns(address, token, balance)
.Spellbook Setup:
The setup involves:
<address, token>
pair list for each project of interest, preferably through querying decoded contracts or deployment events. Manual curation is an option if automated methods are not feasible.tvl_base
macro to execute the forward-fill logic incrementally, taking inputs from the base daily balances table and the address <> token pair list.For a visual representation of the proposed setup, refer to the schema image below:
Usage: get current uniswap TVL:
compare historical bridge balances
Naming:
We'll need to think a bit more about naming and purpose. Suggestions welcome. Some options:
projects.tvl
dex.liquidity
projects.balances
protocols.tvl
considerations:
Each option has its implications, and careful consideration should be given to aligning the chosen name with the intended purpose and broader context of the project. I think my current preference would got to
projects.balances
, theprojects
schema can be expanded upon in the future with spells for daily active users, volume, retention, ...