Demystifying Dune Analytics
Smart contract sleuthing with @ OurNetwork_
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:
While I’m still making my way through the materials, I’ve picked up some tips for other aspiring web3 data analysts.
First, most tables can be created from these three*:
*Also worth getting acquainted with these tables:
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
*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.
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.