[BUG] dex.trades: amount_usd has no tie-break when both legs are trusted, so a 0.698 USDC swap books as $336,873,539

Description

Anything reading dex.trades for ethereum DEX volume is currently picking up one dormant venue, balancer v1, that went from about $0.1M a day to billions a day at the end of August and is booking swaps like 0.698 USDC for 134,656 WETH at $336,873,539 a leg. We hit it reconciling our own numbers against DefiLlama, but the figure is in your table, not ours.

Daily leg volume for the venue, full series from a 35-day pull on 9 Sep (the days before 08-31 run $0.02M to $0.29M, with one 08-23 outlier at $2.13M):

2026-08-31      $4,279.82M
2026-09-01      $9,259.49M
2026-09-02      $6,195.32M
2026-09-03      $3,582.84M
2026-09-04     $24,566.58M
2026-09-05      $2,643.91M
2026-09-06    $155,302.73M
2026-09-07          $0.08M
2026-09-08          $3.82M
2026-09-09     $30,596.94M   (last day in the pull)

Re-checked 11 Sep: 93,056 legs and $194.7bn over the trailing 7 days, so it is ongoing rather than a one-day artifact.

Almost none of this is broken, which is what took us a while. The events are real and so are the transfers, and the decoding, prices and decimals all check out. The only thing we think is wrong is which side of the trade amount_usd ends up valuing.

This has come up before. Discussion #9902 reported the same shape in July, a Uniswap V3 WETH/AVAIL trade worth $6.23 stored as $1,900,384.83, and asked which leg is intended to populate amount_usd. The answer there, that the field is trade-size estimation and follows whichever leg has the more reliable price feed, came from another community member rather than a maintainer and the discussion is not marked answered, so we have treated the semantics as still open. #9914 proposes a dbt divergence test built on that answer, and #9709, #9808 and #9809 propose a related leg-preference change for Balancer V3. If the #9902 reading is in fact your position, saying so would settle this and #9914 together.

Current behavior

dbt_subprojects/dex/macros/add_amount_usd_dex_trades.sql, verbatim:

    , COALESCE(
        CASE WHEN tt_bought.contract_address IS NOT NULL THEN bt.token_bought_amount * pb.price END,
        CASE WHEN tt_sold.contract_address IS NOT NULL THEN bt.token_sold_amount * ps.price END,
        bt.token_bought_amount * pb.price,
        bt.token_sold_amount * ps.price
    ) AS amount_usd

When both sides are priced and trusted the bought side wins and the sold side never gets looked at, which holds up until the pool's internal price has moved inside the transaction.

Take tx 0x9d753d8d…35e6b0, block 25917399, pool 0x69d460e01070a7ba1bc363885bc8f4f0daa19bf5:

log 0x416  LOG_SWAP   tokenIn  USDC  698,130
                      tokenOut WETH  134,656,233,393,116,942,372,009
log 0x418  Transfer   pool -> 0xfccc10ad...  134,656.233393 WETH

The pool took 0.698 USDC and paid out 134,656 WETH, and the transfer really did happen, two logs later. dex.trades values that leg off the WETH side at $2,501.73 and gets $336,873,539. Off the USDC side the same leg is $0.70, a factor of about 480 million.

It happens because this is a flash-loan arb. Across that transaction the pool pushes about 403,946 WETH out and pulls the same back in, ending roughly half an ETH down, so the borrowed WETH moves the pool's price far enough that 0.698 USDC really does buy 134,656 WETH in there. The trade is real, it just did not happen anywhere near the price we are valuing it at.

That transaction is not a one-off. The ten largest in the window are near-identical, all through entrypoint 0xfccc10ad…, each producing 113 to 119 rows in dex.trades and each totalling $1,880M to $1,890M. Together: 1,172 rows, $18,870.5M. The tx above is the sixth of the ten.

The pools we checked all trace to the canonical BFactory 0x9424b1412450d0f8fc2255faf6046b98213b76bd, so filtering by which factory created a pool would not have helped.

Expected behavior

The #9902 answer explains its own case well: for WETH/AVAIL, AVAIL is illiquid and low-cap, so preferring the output leg picks the better price. That reasoning does not reach this case. Here both legs are USDC and WETH, both in trusted_tokens, both with a price at that minute, and neither is the more reliable feed. The macro has no tie-break for that, so it silently takes the bought side, and "trade size estimation" then reports a 0.698 USDC swap as $336,873,539.

Whichever way you define the column, we would not expect that number. Near $0.70, or the row dropped or flagged, would all be defensible.

Transaction for reference: https://etherscan.io/tx/0x9d753d8d61775b5df480774d89aeabf3581b1d538e2c27339b9aaa73bb35e6b0

Impacted model(s)

The macro is shared, so this can reach dex.trades anywhere. Where we have measured it:

dbt_subprojects/dex/macros/add_amount_usd_dex_trades.sql
balancer_v1_ethereum.base_trades
dex.trades

For balancer v1 on ethereum in the 7 days to 9 Sep: 91,761 legs, mean leg $2.36M, max leg $336.9M, $216.7bn of leg volume. dex.trades counts each hop and DefiLlama does not, so that total is not directly comparable to DefiLlama's $8.9bn for all of ethereum. Collapsing to one notional per transaction in the same query gives $16.5bn, still 1.85x the chain. We are not confident in the transaction count behind that (the same query reports 4,168 while a live count over a similar window gives 8,521), so treat $16.5bn as indicative and the leg total as the solid number.

Possible solution

Compare the two sides when both are priced and trusted, instead of taking the first one. Options, least to most invasive:

1. Take the lesser of the two.
2. Keep the current preference but drop or flag the row when the two sides diverge past some bound.
3. Expose both sides and let consumers decide.

We have not measured what any of these does to the rest of dex.trades, and that is the number you would want before merging anything, so treat them as directions and not a proposal. Two things we would worry about writing it ourselves: taking the lesser biases every row down by fees and slippage across the whole dataset to fix one venue, and a trusted token whose price feed is stale or depegged would book genuine volume at the wrong price under either 1 or 2. #9914 suggests 10% and severity: warn, which is more cautious than dropping rows and is probably the better instinct.

Happy to put up a PR for whichever direction you prefer, including none.

On scope: we spot-checked ethereum, base, arbitrum and optimism for venues where a token's implied price sits far off that token's chain-wide median, and only ethereum balancer v1 came up. We would not lean on that. It only finds cases where the two sides disagree, so a version that kept them proportionate would pass it, and the median baseline is itself drawn from dex.trades, so on a thinly traded token it can inherit the same distortion. The other three chains are untested rather than clean.

Start with dbt_subprojects/dex/macros/add_amount_usd_dex_trades.sql and reproduce the cited transaction's two priced legs, then read discussions #9902 and #9914 for the unresolved semantics. Compare the impact on balancer_v1_ethereum.base_trades and dex.trades. Done requires an agreed tie-break, flag, or drop behavior and validation against the affected models.