Should dex.trades backfill prices from dex.prices? (Where there's no entry in prices.usd)

[MSilb7]

Originally was going to post this as an issue, but figured it may be better as a discussion

---

Description

In dex.trades, amount_usd will only be > 0 if the price feed for one of the tokens traded exists in coinpaprika. This understates total DEX volume, since tokens may have known prices, but not be supported in the token price API.

Current behavior

Trades between 2 non-prices.usd/coinpaprika tokens returns NULL amount_usd

Expected behavior

dex.trades pulls from dex.prices when amount_usd is null.

Impacted model(s)

dex.trades

Possible solution

When amount_usd is NULL via prices.usd / coinpaprika, use the token price from dex.prices to calculate amount_usd. To avoid some data quality errors, we could add some filtering on these matches and/or select the lowest USD value of each token.

Example of impacted trades: https://dune.com/queries/2357626

I'd expect that there's some dependency issues, so raising here before making any approach.

[Hosuke]

There may be a dependency loop need to solve, I am not sure {{this}} or something similar can tell the dependency tree dex.prices is using historical version of dex.trades to calculate.

Or when dex.prices is updated, make a static snapshot/mirror of it, maybe in dagster, to use as a source for dex.trades.

[jeff-dude]

thinking off the top of the head here, without looking closely at dex_prices as a refresher first. i think this may be possible. in terms of the dependency loop, even if we miss on initial load, our incremental has a rolling seven day window, so we could potentially update over time.

the downside is: we'd have to edit all underlying base models. we're already in conversation to redesign dex.trades from an etl perspective (the end result would remain the same). i wonder if we revisit this in that redesign, since we'll be touching all models already, and finalize then? it'd just essentially be another join, another piece of logic in the coalesce for the field

[MSilb7]

Yeah, one potential redesign could be to do a "final" decimals and prices mapping in the final dex.trades model.

For this, I was thinking you do something like 'If amount_usd is null, then use dex.prices'

[jeff-dude]

noted ✍️

[jeff-dude]

closing this in favor of #4637 where i've added this to the appendix section of the issue