Dune is a blockchain analytics platform that makes it easy to query, analyze and visualize data from a wide range of public blockchains. By letting anyone create, share, and dive into community-made dashboards and reports, Dune helps both DeFi builders and users get a better grasp of the broader crypto ecosystem — whether it's through understanding user behavior, raising awareness of new products, or benchmarking protocol performance.
Why do you need this?
There are many decentralized exchanges (DEX) out there, and Dune has made life easier by creating and maintaining the dex.trades table. This table puts all the trading data from most of the top DEXs in one place instead of having to sort through a bunch of different DEX smart contracts.
If you are a DeFi developer including DEX in the dex.trades table in Dune provides several benefits:
1) Visibility: Being listed on Dune increases the visibility of the DEX to a broad audience of DeFi enthusiasts, developers, researchers, and potential users. It can help drive awareness and adoption of the DEX.
2) Transparency: Users can access detailed data about trades, liquidity, volume, and other metrics, fostering trust and accountability in the platform.
3) Benchmarking: Dune allows DEXs to compare their performance against other exchanges. This can be useful for benchmarking and identifying areas of competitive advantage or improvement.
4) Community Engagement: By providing data to the public, DEXs can engage more effectively with their community. Users can explore data, generate their own insights, and contribute to the broader discussion about the DEX.
At a high level, if you want your DEX to be included in this table you need to write the code, open a Pull Request on Dune’s GitHub repo, and get the thumbs up from the friendly Dune team.
While this process seems complicated at a glance, this article walks you through all the steps by sharing how the Shipyard team successfully added Clipper to Dune.
How does Dune work?
Dune ingests data from blockchain node providers to raw tables for each chain. To make it easier to work with smart contracts, Dune decodes data from smart contracts and puts it to a separate table for each smart contract and function call or event.
The tables are named accordingly:
events: [projectname_blockchain].contractName_evt_eventName
function calls: [projectname_blockchain].contractName_call_eventName
In order to add your DEX data into dex.trades, you need to create a “spell” - this is what Dune calls the code which does the data aggregation.
Here is a diagram that illustrates this process:
Steps
Now let’s dive into particular steps to add a DEX to the dex.trades table.
1. Submit the Smart Contract for Decoding
The first step in this process is to get the Decoded Data for your DEX Smart Contract. To do this, go to https://dune.com/contracts/new
From there, choose the blockchain and fill in the contract address. After it is submitted, it might take some time to get approval by the Dune team. Once the contract is approved, a set of tables, corresponding to your contract, will appear in the dataset field at https://dune.com/queries
For example:
clipper_ethereum.ClipperApproximateCaravelExchange_call_swap
clipper_ethereum.ClipperApproximateCaravelExchange_evt_Swapped
2. Prepare the Dev Environment
Fork the Dune repo and install everything following these instructions
3. Add the spell
Add/update the following files:
dbt_schema.yml
dbt_schema.yml - the main configuration file for the models.
Here is the piece of code which has to be added:
clipper:
+schema: clipper
ethereum:
+schema: clipper_ethereum
models
Each model lives in a single file and contains logic that either transforms raw data into a dataset that is ready for analytics or, more often, is an intermediate step in such a transformation.
models/clipper/clipper_trades_schema.yml - the clipper.trades schema.
models/clipper/clipper_trades.sql - this SQL statement combines all Clipper data for specific chain and version from the tables described below.
The diagram below illustrates the hierarchy of the tables:
models/clipper/ethereum - the folder which contains the clipper ethereum model files.
models/clipper/ethereum/clipper_ethereum_sources.yml - this file maps the decoded tables related to the Clipper DEX.
models/clipper/ethereum/clipper_ethereum_schema.yml - the clipper_ethereum.trades table schema declaration where the DEX trades will be stored.
models/clipper/ethereum/clipper_ethereum_trades.sql - the SQL statements which add new records. Those statements aggregate data from the Clipper tables which are described below. This was how the data from all the different versions of Clipper DEX was aggregated.
models/clipper/ethereum/clipper_v1_ethereum_trades.sql - the SQL queries which combine data from the Clipper decoded tables and join it with other blockchain data from other tables (e.g. ethereum.transactions, prices.usd etc.) for a particular version of Clipper DEX Smart Contract.
(There were multiple versions of the Clipper DEX, which is whyit was necessary to have separate files for each version. These statements are triggered by Dune once new records are added to the decoded tables.)
models/dex/trades.sql - this file instructs Dune to include clipper_trades to the dex.trades table.
dex_trades_seed.csv
The dex_trades_seed.csv file contains the reference data which are used to check that the spell delivers the results as it should.
This is a CSV file with the following format:
blockchain,project,version,block_date,tx_hash,evt_index,token_bought_address,token_bought_amount,token_sold_address,token_sold_amount
For adding the test data, we pulled 3 DEX transactions from etherscan.io and input their data:
ethereum,clipper,4,2023-04-06,0x3772f0915d742bc2d40fec51d5be6c0f92e511290fea9212d45561ebae61f7f7,156,0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48,4134.968625,0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2,2.175ethereum,clipper,4,2023-04-06,0x844d2221065c96722aeb29ff2a412042c41515ce86bede33615e29357fb76d10,118,0xdac17f958d2ee523a2206206994597c13d831ec7,18956.433604,0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2,10ethereum,clipper,4,2023-03-30,0x4e4aa9d5238083c16fab7077782dfafd928cd9fc5fe1946230345a6c06393c29,168,0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2,6.766262249310329856,0xdac17f958d2ee523a2206206994597c13d831ec7,12000
Here are the steps for adding these data:
1) Open the transaction list for the contract and add the following tx data:
- blockchain=ethereum
- project=clipper
- version=4 (the version of of this smart contract)
2) Pick any transaction by clicking to its hash, e.g. 0x3772f0915d742bc2d40fec51d5be6c0f92e511290fea9212d45561ebae61f7f7
Add the following tx data:
- block_date=2023-04-06
- tx_hash=0x3772f0915d742bc2d40fec51d5be6c0f92e511290fea9212d45561ebae61f7f7
Open the Logs tab, find the address of the DEX contract in the list (Clipper: 0x655edce464cc797526600a462a8154650eee4b77)
- evt_index=156 (can be found at the left of each log item)
- token_bought_address=0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 (outAsset in this case)
- token_bought_amount=4134.968625 (outAmount in this case, converted to human amount by applying the 6 decimals used for USDC token)
- token_sold_address=0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 (inAsset in this case)
- token_sold_amount=2.175 (inAmount in this case, converted to human amount by applying the 18 decimals used for WETH token)
4. Create a Pull Request
Once files are added, make sure there are no errors with the compilation.
dbt compile
If there are no errors, push the changes to a new branch and create a Pull Request in the duneanalytics/spellbook repository.
Here is a couple of Pull Requests that we opened for adding clipper.exchange data to dex.trades, which can be used as a reference:
- https://github.com/duneanalytics/spellbook/pull/3134
- https://github.com/duneanalytics/spellbook/pull/2701
- https://github.com/duneanalytics/spellbook/pull/3291
- https://github.com/duneanalytics/spellbook/pull/3311
The Result
As a result, Clipper is now a part of Dune's widely used DEX dashboard! The dashboard takes the data from the dex.trades table, meaning once you add your DEX to this table it will automatically display its data there.
For more information on Dune spells check the official documentation: https://dune.com/docs/.
Written by
Published on