[BUG] dex.trades table missing token symbols partially in some swaps while it's present in other swaps

sui414 commented 1 week ago

Description

Some tokens symbols are missing in some entries but not missing for other swaps - it looks like a issue with some specific pools:

see the example here checking RENZO token - https://dune.com/queries/4032804

Current behavior

Expected behavior

If the token symbol is available by the time of insertion for any swap pair, that means the token meta info is available, should double check if some dex decoding is using a outdated token table in joining.

Impacted model(s)

namespace.model

dex.trades

Possible solution

jeff-dude commented 1 week ago

@sui414 thank you for raising. this appears to be only 1inch trades which contain this tokens symbol. 1inch team maintains their own pipeline that feeds into dex.trades, and it appears there is slightly different logic for applying symbol within those models vs. the rest of the pipeline.

1inch code:

applies a coalesce() on symbol for left joins to both prices.usd and tokens.erc20, to grab from either when available

rest of dex trades code:

only pulls from tokens.erc20 metadata

we had recently undergone and effort to automate erc20 token metadata via a third party API, but we are still missing some tokens in that source. we have left open the option to manually add tokens to compliment the automated source until resolved. there will also be changes to how prices pipelines are run in the near future, which may make the coalesce example above to be irrelevant.

my suggestion for now is to add the token manually here: https://github.com/duneanalytics/spellbook/blob/main/dbt_subprojects/tokens/models/tokens/ethereum/tokens_ethereum_erc20.sql

until we have the opportunity to figure out the gaps in the automated source for token metadata

jeff-dude commented 1 week ago

quick query proof:

select
  'erc20_metadata',
  symbol
from
  tokens.erc20
where
  blockchain = 'ethereum'
  and contract_address = 0x3b50805453023a91a8bf641e279401a0b23fa6f9
union all
select distinct
  'prices_metadata',
  symbol
from
  prices.usd
where
  blockchain = 'ethereum'
  and contract_address = 0x3b50805453023a91a8bf641e279401a0b23fa6f9

sui414 commented 1 week ago

sounds good and thanks for investigating! These LRT tokens are trading decent amount of volume, but i guess it's not too bad given it's not affecting the volume number.

jeff-dude commented 1 week ago

i will close this as resolved for now, but feel free to add tokens to those chain specific static files as needed 🤝

duneanalytics / spellbook