Deeper Data, Dune and Beyond: On-chain Analytics with Llama
May 26th, 2022

Written by scottincrypto (@scottincrypto).

As Llama builds and implements economic infrastructure for DAOs, it’s vital to have a robust source of data from which communities can create reports and assess the results of Llama’s treasury actions. Llama seeks to make its reporting and analytics as accessible as possible by abstracting away the difficult parts of the analysis. After all, what good is open, on-chain data if it’s not accessible?

For good reason, the weapon of choice for this reporting is Dune Analytics. Dune provides a great environment for doing on-chain analytics work. There is an easy-to-learn graphical frontend for making dashboards, with Dune taking care of hosting and all the other backend plumbing. The queries are written in SQL, which has been around forever and many people already know. Dune decodes smart contract data so that it's findable via human readable table names instead of the binary mess that are the internal blockchain data structures.

Building a Dune Dashboard

If you want to build a Dune dashboard, you need a few things first:

You need to know SQL

This is the easy part. If you know selects, joins & window functions then you are already 90% of the way there. If you don't know this, a couple of online courses will get you up to speed quickly.

You need to know how the protocol works

A crypto protocol typically has a collection of smart contracts which execute on the blockchain.

When these contracts have their functions called, or they do something and emit an event, a record is written to the chain which gets picked up by Dune. What these calls and events do, and what the parameters mean, is the puzzle. Some protocols have great documentation, others less so. Often documentation is not up to date with the latest deployed contracts. Seemingly simple things, like calculating APR/APY, or how fees are accounted for, are unique to each protocol. Be prepared to comb through Github and read the deployed contract code on Etherscan to figure out how things actually work.

You need the contracts in question decoded on Dune

Smart contracts are compiled into bytecode which is executed on the blockchain. This bytecode isn't human readable, nor is the data which they write to the chain. Thankfully Dune takes care of this for us.

Using the ABI of a smart contract as the key, this bytecode can be translated back into human readable function calls and event data. Dune handles this via a contract decoding process. This requires a protocol to have published their contract ABIs publicly, either by verifying the contract on Etherscan or publishing a copy elsewhere. It's surprising that this isn't always the case.

If you're lucky, the protocol you want to make a dashboard for already has the contracts decoded. If you're really lucky, they'll even be decoded correctly. Decoding requests are submitted by users, so the input data is not always reliable and hence there can be problems with some contracts. Thankfully, Dune has some friendly and helpful people to help out with decoding issues.

Building for TribeDAO - Putting it into Practice

As part of Llama's work with TribeDAO, I built a dashboard to track the lending rates for the FEI stablecoin across Aave, Compound and Rari Fuse.

This dashboard needed to calculate the amount of deposited FEI in each of these platform pools, as well as the APY received by depositors for both FEI and competing stablecoins. Fortunately, Rari Fuse is a fork of Compound (with each pool a separate instance of Compound) so the code and the accounting was very similar for both protocols.

In these Compound-like protocols, a user deposits FEI into a Pool and receives an amount of cTokens in return. These cTokens act as a fungible deposit receipts, representing a redeemable claim on the underlying assets. As interest is earned on the deposited tokens, the exchange rate of the token/cToken increases (calculated by the smart contract) and a user can redeem their cTokens for an increased number of underlying assets at a later date.

An Easy Problem

Calculating total deposits for FEI (or any token) on a Compound-like protocol is simple in Dune.

The deposit and withdrawal transactions emit a Mint event (deposit FEI, mint cFEI) or a Redeem event (withdraw FEI, burn cFEI) which contain the quantities of the cToken and underlying FEI in the transaction. Here is an event from Etherscan illustrating this for the Rari Fuse Pool 8 FEI deposit contract:

Once we understand how Mints and Redeems relate to the total deposits of FEI, we can query it on Dune. This is simply a matter of grabbing all these events in a query, then summing by day and calculating a rolling total.

A Harder Problem

The two key things which differentiate Rari Fuse from Compound are that each Fuse pool is a full separate instance of the Compound protocol, and it is permissionless, so anyone can create a pool and add assets to it. These features have led to an explosion in Pools and listed assets - there were 200 pools and almost 800 listed assets at the time of this article.

Dune only had 200 assets showing in the tables, and there was no table available which mapped Fuse Pools, cTokens and underlying assets. Further investigation revealed that cTokens are created in at least three different ways, with some being via multicall operations (where call data is obscured) and some via unverified contracts. It was impossible to reconstruct the full picture of Fuse assets via call & event data only.

There was, however, another solution... querying the Fuse smart contracts directly! Dune allows users to submit code which creates custom tables or views, known as abstractions. These can be used to cache data for faster access or add external data to Dune for access in queries. In order to create an abstraction, there were some key pieces of information I needed to understand first:

  • The FusePoolDirectory contract has a method called getAllPools() which returns a list of pool names and contracts.
  • Each pool contract has a method called getAllMarkets(), which returns the list of cTokens in a pool.
  • Each cToken contract has a method called underlying() which returns the contract address of the underlying asset.

To solve this problem for our dashboard (and for everyone else who wants to work with Fuse contracts in Dune), I generated an abstraction table using the contracts above. To do this, I wrote code to iterate through the Fuse smart contracts and generate a SQL table creation query for an abstraction. I used the python library eth-brownie for this, although it can be done in or equivalent libraries in other languages. The code can be found here and the resulting abstraction is available in Dune as rari_capital.view_rari_fuse_ftokens.

Smart contract developers typically don’t have Dune reporting as a high priority when they develop their protocols. Enabling rich reporting through emitted events has a gas cost, so we can expect to see only the bare minimum of event data written on-chain. Maintaining a flexible approach to available data sources is the key to navigating these problems as an analyst.

Llamas Delivering

As Llama prepared the Quarterly Financial Report for TribeDAO, it became apparent that some of the data was going to be very difficult – or even impossible – to get via our Dune dashboards. With the FEI-Rari merger, Rari Capital income needed to be included in the financial reports. This presented a number of non-trivial challenges to work through.

Income from Rari’s Fuse Pools is generated for TribeDAO via the Platform Fee, a percentage of the interest charged on borrowing. While calculating this fee may sound simple enough, there were several complicating factors:

  • This cut is usually 10%, but not always.
  • This fee isn't reported out via any events from the Fuse contracts and the fees accrue inside the Fuse contracts for retrieval by the Rari team when required.

Thus, there are no token transfer events which can reliably account for the Fuse fees earned by the protocol. This is unlike many other treasuries where there is a stream of token transfers to designated treasury accounts.

At Llama, we strive to produce the highest quality work possible, even if that means testing the boundaries of the impossible. Instead of excluding Rari income from the report, we found a way to solve our Fuse pool income problem by directly querying the contracts. Here’s how we did it:

Each Fuse cToken contract has a totalFuseFees() method which returns the fees accrued in the contract. By writing custom code to query an Ethereum archive node, we were able to build a picture of the platform fees accrued in each Fuse Pool over time.

This was only part of the solution – the accrued fees then needed to be adjusted for withdrawals already made to the Rari treasury.  Finally, converting everything back to USD required querying on-chain oracle contracts to get exchange rates for the fees accrued over time. In the end, we were left with an accurate picture of Rari’s Fuse earnings over time, which we integrated into our Financial Reports.

Reporting on a protocol’s performance and financial metrics is complex but crucial work. At Llama, we endeavor to produce the most accurate and advanced reports possible. Tools like Dune make sense in a lot of applications, but it's important to understand their limitations and how they can be extended. Llama will use a range of technologies to solve these problems, and build the tools required if they aren't already available.

For more information on Llama’s reporting, please visit our Dune profile at

Stay Up to Date on Llama 🦙

Follow us on Twitter, check out our website, and subscribe to our Substack.

Artwork credit: 0xEFRA.

Subscribe to Llama
Receive new entries directly to your inbox.
This entry has been permanently stored onchain and signed by its creator.