Token balances tables feedback

[hildobby]

I'm confused by the tokens_{chain}.balances tables, so here is some feedback:

1. how should it be used if unaggregated by hour/day/week? Using transfers remains a lot easier which probably shouldn't be the case, I don't know any efficient usage of this unaggregated balances table 🤷‍♂️
2. 99% of the time, wizards just look at fungibles or NFTs, I've never seen any good queries/charts with both. So materializing it with both fungibles and NFTs just makes it more inefficient to query. I would much rather have nft.balances and fungible.balances, especially since some columns are only needed for NFTs (token_id, collection_name). I would also much rather have crosschain (views or ideally materialized tables but that's another topic) not inefficiently plagued by having both token types.
3. type is a really bad column name since it's interpreted as a function, token_standard would make more sense as it would align it with other spells
4. wallet_address is also a weird name, not all addresses are wallets, I would just stick to address
5. There's also a lot of inconsistencies with some spells using symbol and some using token_symbol, it would be cool for you guys to define a default one of the two and enforce it across spellbook, same with decimals and token_decimals

I'm curious on your thoughts @aalan3 @jeff-dude @0xRobin @MSilb7

[0xRobin]

Thanks for the feedback @hildobby!
My quick thoughts:

1. how should it be used if unaggregated by hour/day/week?

Think of the current balances as the most raw form we can get the data.
You get the result of the read-only balanceOf(..) function for every time the balance was updated. I imagine (just as with evt_Transfer ) that the most useful tables will have to built on top. As an example. I'm reworking balances_daily here to have it perform a forward fill so it can be properly used as time-series data. Unfortunately, as we're dealing with a ton of data, performance issues can be a limiting factor for spells built on top..

2. I would much rather have nft.balances and fungible.balances

I agree that splitting them up will remove some confusions and current schema issues.

3. rename type to token_standard

Good suggestion.

4. wallet_address is also a weird name, not all addresses are wallets, I would just stick to address

Also a good suggestion, although address on it's own can be a bit ambiguous. Don't have a better suggestion though.

5. There's also a lot of inconsistencies with some spells using symbol and some using token_symbol, it would be cool for you guys to define a default one of the two and enforce it across spellbook, same with decimals and token_decimals

If we have token_address and token_standard, using token_symbol makes sense. I try to be consistent within a table (either all columns referring to the entity have a prefix (token_), or none.)

[aalan3]

This is great feedback @hildobby thanks for writing this out. I'll address them below. We are redoing the balances spells atm so your timing is perfect, will try to incorporate as much as possible.

1. We are currently working on a daily version of the balances tables which is aggregates the data and turns it into a time series as a view (but aggregation is materialized).
2. The main reason for having it all in one table is atm is because that's how the raw data is (we get all balances together). We will go with this as the initial version. but a couple of points:
   • I see your point about there maybe not being as many use cases for querying all token types (specifically nft vs the other) at the same type, one use case I can think of would be to get a full overview of a wallet (maybe not the most popular use case).
   • We could eventually create separate tables for fungible and nft but them combined with different aggregations or time series can potentially result in a lot of tables which is not necessarily a bad thing but we wanted to get something out first and go from there.
   • We will also remove the prices join from the existing balances (non aggregated) tables so you should see better performance there.
   • We can experiment with cross chain as well but materializing these are not that easy, specially when these datasets grow and as we add more chains. We could start with a cross chain view where it makes sense.
3. Agreed, we can change the name here to be consistent.
4. This is also a good point, will consider switching.
5. Yeah we need to keep this stuff consistent. Will have another look here and see what makes sense. Long term we need to work on some sort of glossary for column consistency.

[hildobby]

Also, native token contract (ETH) should be 0x0000000000000000000000000000000000000000 and not 0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee to match other tables imo. Rn we need to do weird stuff like this: https://github.com/duneanalytics/spellbook/pull/5469/files#diff-c01f4362f7d33bda62723a0a403a479138265718f51899ef6f3113b46b0c9fd6R177

[jeff-dude]

Also, native token contract (ETH) should be 0x0000000000000000000000000000000000000000 and not 0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee to match other tables imo. Rn we need to do weird stuff like this: https://github.com/duneanalytics/spellbook/pull/5469/files#diff-c01f4362f7d33bda62723a0a403a479138265718f51899ef6f3113b46b0c9fd6R177

we should be leveraging this global variable (guilty of not always validating myself):
https://github.com/duneanalytics/spellbook/blob/main/dbt_project.yml#L19

[0xRobin]

@hildobby on 0x000.. vs 0xeeeee...
Relevant EIP: https://eips.ethereum.org/EIPS/eip-7528
and the discussion: https://ethereum-magicians.org/t/eip-7528-eth-native-asset-address-convention/15989

[hildobby]

@hildobby on 0x000.. vs 0xeeeee...

Relevant EIP: https://eips.ethereum.org/EIPS/eip-7528

and the discussion: https://ethereum-magicians.org/t/eip-7528-eth-native-asset-address-convention/15989

yeah but this is only applicable to eth mainnet and its L2s, 0x000 seems to me like an easy neutral ground for all evm chains' native asset. if its 0xeee for ETH, what would the standard for bnb, avalanche, gnosis, celo, etc be?

either way, i just want consistency across tables, regardless of the specific address.