Cannot see WETH - ETH swap in token transfers

[70nyIT]

Ethereum ETL version: v.1.5.2

I am running into an issue. Here's the following scenario, with an example.

Transaction :
https://etherscan.io/tx/0x6c69a07b0e65e70a1b08131f8675b8d3caeecccdcc3de5dd2feb80d27fd97aa1

At the end of this transaction there is a swap of WETH for ETH. Transactions like this happens pretty often (the account in the example above has thousands of transactions like this).

If I export token transfers with ethereum-etl, for that transaction hash I get two lines

• first line tells that 0x693 sent 4 MCB to a Uniswap address
• second line tells that he Uniswap address sent back 0.016 WETH

So I expect the balance of account 0x693 to have 0.016 WETH at the end but no, the address has Ethereum and 0 WETH. This means that in token transfers the swap between a token and eth is not taken into account, even if that swap happens in the same transaction.

If I check traces, I see the internal transaction that swaps WETH with ETH

So my question is: how can I correct this to make sure that, if I sum all the in and out token transfers, the total amount of WETH for address 0x693 is correct?

What I see is that in token transfers' list is missing a line that says WETH moved from 0x693 to WETH contract.

Thanks,

[70nyIT]

As a further reference, this missing transaction will affect also the query to evaluate the 'Token balance for any address on any block height' (reference https://evgemedvedev.medium.com/exporting-and-analyzing-ethereum-blockchain-f5353414a94e , article of @medvedev1088 )

[medvedev1088]

Most likely it's because WETH issues Deposit and Withdraw events instead of Transfer when tokens are minted and burned? Most other tokens issue Transfers from/to 0x00.. for mints and burns.

[70nyIT]

thanks @medvedev1088 for your answer. Yes, I was thinking the same about the reason, happy to see we agree.
My question is now, what can be a good workaround? Maybe to be included in the tool too. Otherwise, just looking at transfers, the correct total amount will be incorrect, especially cause it affects WETH, one of the most used token.

[medvedev1088]

The solution we ended up with is parsing any custom events in ethereum-etl-airflow e.g. for WETH https://github.com/blockchain-etl/ethereum-etl-airflow/tree/master/dags/resources/stages/parse/table_definitions/weth then amending them to token transfers. Here are instructions for easily adding any Ethereum contract to BQ https://towardsdatascience.com/how-to-get-any-ethereum-smart-contract-into-bigquery-in-8-mins-bab5db1fdeee

[contxtsio-jetson]

try this out. :)

-- ------------------------------------
-- WETH
-- ------------------------------------
-- withdraw - silent burn
CREATE OR REPLACE FUNCTION functions.hexToDec(x STRING) RETURNS STRING LANGUAGE js AS """
dec = BigInt(x, 16);
    return dec.toString();
""";

SELECT
    'custom_weth_burn' as _type,
    transaction_hash,
    block_timestamp,
    log_index,
    "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2" as token_address,
    concat('0x', substr(topics[offset(1)], 27, 64)) as from_address,
    '0x0000000000000000000000000000000000000000' as to_address,
    cast(null as string) as item_id,
    functions.hexToDec(data) as amount,
from `bigquery-public-data.crypto_ethereum.logs`
where true
and address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
and topics[offset(0)] = '0x7fcf532c15f0a6db0bd6d0e038bea71d30d808c7d98cb3bf7268a95bf5081b65'
-- FIXME: Array index 2 is out of bounds (overflow)
and array_length(topics) = 2
union all

-- deposit - silent mint
SELECT
    'custom_weth_mint' as _type,
    transaction_hash,
    block_timestamp,
    log_index,
    "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2" as token_address,
    '0x0000000000000000000000000000000000000000' as from_address,
    concat('0x', substr(topics[offset(1)], 27, 64)) as to_address,
    cast(null as string) as item_id,
    functions.hexToDec(data) as amount,
from `bigquery-public-data.crypto_ethereum.logs`
where true
and address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
and topics[offset(0)] = '0xe1fffcc4923d04b559f4d29a8bfc6cda04eb5b0d3c460751c2402c5c5cc9109c'
-- FIXME: Array index 2 is out of bounds (overflow)
and array_length(topics) = 2