3. Reflections on the Token Flow data

Were there any challenges working with the data? Any recommendations or requests?

My experience with the Token Flow schema on Flipside Crypto revealed the following:

• It was not obvious in the interface that the "curr_value" and "prev_value" columns (in state_diffs table) are measured in hexadecimal. This was something I discovered after running the initial analysis, by exploring Token Flow EDW documentation which is linked in Snowflake (could not find it on Flipside). After converting HEX into decimal, I observed my analysis amounts change by around two-digit ETH amounts.

• I sidestepped any of the more complex analysis questions partly because of struggling to understand how to correctly interpret the "location" column (in the storage_diffs table). Some tutorials mention using this column to retrieve specific information, but it would be valuable to access a comprehensive breakdown of decoding locations.

• In the events table, what do TOPIC1 / TOPIC2 / TOPIC3 columns stand for? The docs mention that TOPIC1 is mostly used for the event signature, but I was unsure what the remaining topics contain.

How does this data change the way you think about and analyze Ethereum data?

Because Token Flow allows to query data by "by directly using variables of smart contracts" (source), I am faced with the need to learn more about Solidity and the mechanics of smart contracts in order to make fuller use of it. It is exciting to have access to this view of blockchain data, but in a way also more demanding for an analyst. If Token Flow were to really go above and beyond, I imagine that myself and other analysts could benefit from official tutorials created by the team (perhaps even in collaboration with partners like Flipside) to familiarize with the ways of the EDW.

Image source.

1. About

What is Token Flow? Is anything unique about it?

Token Flow is a Swiss blockchain data & analytics company. It offers an innovative form of Ethereum blockchain data via its flagship data ecosystem – the Ethereum Data Warehouse (EDW). EDW access is available on Token Flow via Snowflake (instructions here) and through Flipside Crypto (the tokenflow_eth schema).

The Ethereum Data Warehouse contains a full history of Ethereum on-chain data: events, calls, transactions, state and storage data (source). But the key emphasis is on capturing changes of the blockchain's state and storage data across time. Token Flow has described its mission as building the Blockchain Time Machine (source).

This way of aggregating and organizing on-chain data is 'closer to the source', in the sense that it is more similar to how smart contracts interact with data directly on the blockchain. It promises new easier querying and analysis possibilities. So the answer is a definite YES, Token Flow data offering is unique and can transform significant parts of blockchain analysis.

2. Trying Token Flow for data query & analysis

"Turn and face the strange – changes" 👩‍🎤

What exactly are "state changes" & "storage changes"? Why are they important?

A state is essentially a snapshot of all the data held by a system at a given time, in this case a snapshot of the blockchain data. Storage slots are data stored in a smart contract. Some of the most interesting and valuable data on-chain is contained in state changes and storage changes.

How to use Token Flow on Snowflake?

1. Log in or sign up for Snowflake https://app.snowflake.com/

2. Follow the marketplace link for Token Flow to request access https://app.snowflake.com/marketplace/listings/Token%20Flow%20Insights

3. Reference documentation where needed https://dbdocs.io/tomek2fa5a9f49e/EthereumDataWarehouse

How to use Token Flow on Flipside?

Flipside Crypto contains a data schema titled tokenflow_eth.

The below chart represents the distribution of top 20 whale recipients of ETH since the beginning of March 2022 (and as of 24 March 2022). Curve Finance is the leader, taking more than a quarter (27%) of all ETH that the top 20 has received so far this month. It is followed by FTX (close to 14%) and OpenSea (9%). Also among the leaders are 1Inch, Uniswap and Sushiswap, Huobi, Coinbase, Arbitrum, and others.

For this exploratory analysis, we use the state changes table (state_diffs) in the Token Flow schema.

This table offers the current balance and previous balance per wallet among its columns. Let's utilize these to find which wallets receive the largest volume of ETH in a day (aka whales in terms of transfers / 'transfer whales'). For this we:

• select the most recent full day at the time of this analysis (the analysis is performed on 24 March 2022 so the data we will need is for 23 March 2022),

• calculate the difference between the current and previous balance,

• convert the difference from WEI to ETH (divide by 10^18),

• add identifying labels to wallets where it is known which organization owns a wallet (do this by matching the wallet addresses in the Terra Flow data to labels data from the general Flipside ethereum schema),

• where there are several wallets per org (e.g. Binance), we aggregate the amounts across all wallets,

• capture top 20 rows to represent biggest ETH-receiving entities.

Now we will examine the actual amounts of ETH received by these top 20 whales. This data is presented below in a table instead of a chart, because the difference between 1th and 20th amounts is so drastic that top whales' amounts would drown out the relatively smaller ones in a visualization.

The table confirms the inequality in the distribution of ETH transfers received so far in March 2022:

• the top ETH recipient Curve Finance got 42x the amount of the token than the 20th highest recipient Bancor (107,676 ETH vs. 2,515 ETH),

• even the second highest recipient (OpenSea) only received half of what the top recipient (Curve) did (55,063 ETH vs. 107,701 ETH).

How was doing this analysis on Token Flow data different from Flipside Crypto's ethereum data?

Image source.

Image source.