Demystifying Dune Analytics

Although I had been using Dune to create dashboards for several months, much of the tool remained confusing. I would frequently get thrown off to open a new query and see this:

I struggled to know which table to use, until the OurNetwork Learn courses, led by Andrew Hong was released.

While I’m still making my way through the materials, I’ve picked up some tips for other aspiring web3 data analysts.

Source Tables

First, most tables can be created from these three*:

1. ethereum.“transactions”
2. ethereum.“logs”
3. ethereum.“traces”

*Also worth getting acquainted with these tables:

4. ethereum.“contracts”
5. ethereum.“blocks”
6. ethereum.“sigantures”

This fact alone elevated my understanding of Dune. This Table Mapping Guide from OurNetwork Learn Sessions provides a really nice graphical overview:

It breaks down what happens in a smart contract transaction, at a high level. Take a transaction included in one block. For example, your wallet signs a transaction to send some tokens. There is a transfer function that’s called. Then an event is emitted/logged on-chain before it happens all over again in the next block. A caveat is the transfer of Ether as the native currency of Ethereum is technically not a token.

One thing to note is that Dune Analytics maps on-chain transaction (excluding pre-chain transactions, which would require engaging with a JSON-RPC endpoint for mempool analysis, out of the scope of this post).

From these three* tables, you can build up other tables in Dune.

This line in the guide made things click for me.

You might wonder why bother with “other tables” when you can derive all the queries you need from these main 3. Speed and efficiency. Querying the ethereum tables is akin to querying a large chain.

Often times your queries simply time out, which leads us to the first step of any Dune Analytics analysis.

Decoding the Contract

After multiple queries getting time out, I realized my approach was too slow and inefficient. To avoid this, check Is my Contract decoded yet? * courtesy of 0xBoxer at Dune.

*Separate check for contracts on Polygon, xDai, BSC etc.

The team has done the heavy data-engineering lift of going through smart contracts, decode individual functions, building a pipeline into SQL tables for easier querying. If you don’t see your contract of interest, you can submit it for decoding.

Other useful Dune Utility Queries can be found here.

Converting Bytecode to Numbers

We can decode topic1 containing either a deposit or withdraw amount with:

bytea2numeric( decode ( SUBSTRING ( encode(el."data", 'hex') , 1, 64 ), 'hex'))/10^18

and filter by either a deposit or withdraw event hash:

WHERE el.topic1 = '\x90890809c654f11d6e72a28fa60149770a0d11ec6c92319d6ceb2bb0a4ea1a15' -- Deposit into Vault 

WHERE el.topic1 = '\xf279e6a1f5e320cca91135676d9cb6e44ca8a08c0b88342bcdb1144f6511b568' -- Withdraw from Vault

(h/t to Andrew Hong’s Intermediate SQL guide for this technique.)

If you’d like help with on-chain analysis, please get in touch.