Dune 🛠 Build the frontier of web3 data analytics - Jackie Zhang

[Applause] [Music] okay Welcome to our our do workshop for EF sorry the for some reason my computer just decided that it wants to be in the night mode and I want to sleep so we're GNA just go with us today uh so my name is Jackie I work as uh wizard relations so basically developer relations uh for June I go by AA online um and then I guess a little bit about my background I used to be a developer for Banking and then decided I want to do more data stuff uh for traditional finance and then I found out about crypto and I'm just like oh my God I need to be in this field and then I just started doing like when you do web three data you cannot help but run into Dune basically so that's how I found out about Dune and then here I am working for Dune So today we're gonna go on a journey about how to build the frontier of web3 data analytics okay so if you want to follow along there's like links and stuff this is the um the link for the slides itself so I put some like reference docs links there um I'll leave it on for like a few more seconds to um for you guys to [Music] scan put this a little lower okay cool looks like we are getting there okay cool okay data right so up till now the data in the old world where like the web two world where Harold living right it's like proprietary uh you have to pay for the access and it's very slow um so think about like I don't know like JP Morgan these big Banks uh everything for them is like super close they want to make sure that they have the best competitive Advantage so like everything is like Preparatory for them like close and then you need to pay like Bloomberg it's like very expensive right like if you have a BL Bloomberg machine for your analytics um and then if you think about like oh there's like always talks about earning Seasons right like you have quarterly report so like you don't really have real- time data about like your firm like the industry per se um so as a result of that you're doing a lot of repeated work so that could be like data scientists from like two different big banks that might be working on the same project but they're not really like collaborating which with each other and like learning from each other um and because it's so expensive right like that you don't really have access to like good good data so it's like High barrier to entry and as a result of all of this it's just like less Innovation for humans for like people who are like interested in web through data in general crypto though on the other hand we have a shared public back end right and then it we are able to have a live view of the chain right so it's like real time you can see like what exactly is happening with all the transactions and you want can't analyze it real time flexible and like it's super collaborative like we're all building toward the same goal so as a result of this as a result of like a super open system a collaborative system and like everyone's building on top of each other right real time um I would argue we would argue that the a non crypto paper actually has better access to uh data about their system than say Jamie Diamond uh from JP Morgan right because of the crypto ethos and the way data is set so for me personally that was like one of the big reasons like I got into up three data it's just like for me for the first time in history I see because like before it would be like kago if you're a data scientist it would be just like okay kago we have like free data then we can like tree models but now it's like we have real data about blockchain it's free and it's real time and we can just like everyone's building toward the same goal and that just really excited me okay so how does Doom fit in with like the whole web3 data thing that we're just kind of talking about so Dune is a leading Community powered crypto data platform and then our mission is to make crypto data accessible to everyone um so kind of assuming like if you're here like you're intered about blockchain and like you hear about like how blockchain is like open and everyone has access but if you actually like look at it from like a node to like when you can actually like produce analytics the journey is like quite a lot right you have to like first harvest the data and then you have to preprocess it to be the format you want and if you like do analytics it's just like quite a lot of work in between actual data and then like the data that's being produced from blockchain so like what Dune can provide for you is that so like comply so basically in aligning with the uh web 3 ethos it's uh open to everyone it's composable so you can like I'm like building Lego style you can build on each other's work I can show a little bit more later and then it's very Community Driven so like everyone is here to build with everyone else and we're like all here to help each other so that is Dune together with like the we through ethos and like a bit more specifically the way I think about Dune is like two component so you've got the data component which has like the roll tables the decoded tables in the spell book we call them they're like abstractions that are built on top of the Roll tables and decoded tables I'll give you some some examples like later on right after this so that's like the data side and there's the community side so we call our community members Wizards because they're just like making magic to to the data um so they're they're like an essential part because if you think about it like like when when gith have become like really popular you got like these open source um coding that's happening so like everyone's like open software right and that's like how we got to the space to be where we are today and we're like so early in the open source data field right so like it's like tens of thousands of people like eventually hopefully like millions of people are just like here and like building the data analytics right so before this like people don't really think about engineering or building data analytics but we can because we have the same shared backend and like why not um okay so for the rest of the talk just going to kind of like explain how you can use Dune um to build the frontier of web three data through kind of like three different ways they're not very they're not independent they actually feed into each other but just categorically speaking there these are the three ways uh and then we're going to kind of like go into the angle from uh nft Marketplace analysis so we're going to like try to look at like nft uh Marketplace um okay so data so just kind of like level setting um data is produced by oning activities right so here we're just going to use a super super super simple example because we're talking about nft Marketplace so we're talking about like nft trading so simply put person a just like get person B some money and person B just like transfers the enfy back to person a right like in a very very simpley format that's like what happens so when this transfer event happens kind of like the life cycle of it it's like an externally owned wallet has to sign this transaction right and then the transaction the sign transaction would call like certain functions right let's say like a transfer function um and then also it should also admit and event not not all of them do but like let's just say it complied with the with the um with the standard andit events so after this right so from the functions in terms of like how it generates data so the call functions will generate call data uh along with like the S transaction and the data will get reflected in one of the r tables called like the transactions table right if you're familiar with like the ethereum architecture so that's like how the data gets mapped to the action and then also uh the surface level transactions will trigger some internal transactions right so the those data in turn goes into like the traces um table that we have another Ro table and then for the emit uh emitted events uh those are logged in the locks table um so we can kind of like walk through an example so here on um I have an example of just like on openc somebody wanted to buy some uh this kpr token or NFD um and this is like the transaction has for it um and then so in turn if I want to look at kind of like the transaction data associated with it what I can do is like I would grab the transaction hash from it right and then I can make this bigger so you can actually see okay so here uh remember I was kind of talking about like the three types of data actually let me minimize it one more time so on the left hand side you can see that we have like the roll table right the decoded projects and the Spells so the rooll tables are just like transactions traces and locks those like raw data that's being like emitted by the chain like broadcasted by the chain the decoded projects come from like let's say you have like Unis Swap and you have Smart contract and those like produced data but instead of just going straight up to like the transactions like the role tables you can actually go to the decoded project to get like better data and it's like really powerful if you know what you're looking for um and then the Spells are just like abstraction spell on top of both raw and decoded projects um and let's say like if you want to analyze all the trades in the nft sector um and then you might not know so like as a person who might not have like deep backgrounds about um how is a trade being constitut how how how can you read the trade data basically from like the the the solidity code level right you can go to we have this is like all Community gathered uh data so we have like these nft do trades table um and then from here you can already see there's like the amount that's being traded right you can also see like the buyer the seller of it so like if you start from like the top we actually have table like this like spell Book Table that you can like start but those are like kind of like a um D diation from what we're talking here so those are three types of table that that we have um so kind of coming back to the rooll table example we're talking about so we can Cory from uh ethereum transactions table and then we just like filter for the specific hash we're looking at um putting the block number just to like optimize the performance a little bit um and then we can simply see oh like from from this person to this person like this is like the transactions that's been triggered and then this is like the data associated with it but like as you can see right it's very like gibberish like you can't really read it it's just like blah blah blah blah blah blah so you you need to do more work about this but yeah so that's the transaction stable and then I guess quickly show um literally the same logic about um the traces table you just Cory the ethereum traces for the transaction hash and then you get all the internal transactions that were triggered by this like top level transactions uh but again the problem here is just like it's still like not very human readable it requires you to have like a lot of skills to be able to like parse the data here you can cuz in order to get the higher level tables we talk about like someone has to be equipped with this knowledge but that's like the beauty of like the space only one person needs to like find that and then everyone else can benefit from you so that's like to me that's like the beauty of like open source data um okay I'm going to like skip the the locks table but you kind of get the idea these are like the RO tables and then uh just decode a table like just to give you an idea so with openc right like they release their like cport um contracts and stuff so you can like directly Cory for the open C Cort contract uh this particular event order fulfilled um but again like this requires you to have some skills you have to like know how to parse um these data tables however if you just go to Spellbook um so for nf. trades table that I was just like showing you guys literally like here it's like super clean you can just select the seller the buyer amount original currency symbol and then you get like a really clean results um I don't know so when I started looking at I used to flip nfts and then when I was like looking at the data to support like what nft Arbitrage opportunities I should look at it was like so much to just look at the contract level data but then like once I like once we have this en that trades table it's just like so beautiful you're able to just do analytics so quickly and then it almost like I feel like in a way it's like the community itself is like democratizing the community itself right like everyone's like building toward the higher level table and then we're just going to get better and better as um as an ecosystem um but yeah so those are the data tables um and then just like a quickly a word the way you get from some like base level tables from transactions traces to nf. trades um we used this thing called like DBT for data transformation um you can read more I'm going to skip this part that I just want to show you guys like there's like a quite nice lineage Trace from like from like this abstraction table like how do you get from this abstraction uh from the lower level to abstraction table there's like a lineage graph that you can like go look at um but yeah it's we're I'm going to skip this for today um and if you want to know more about how to contribute to our Spellbook you can check this link out there's like three different guides depending on your style you can go like digest and um and do it so so yeah that's how you can contribute to open source data from like the data perspective um the second perspective is uh the dashboard which actually like is tightly coupled with the first one because without data you can't actually like do dashboards uh but just going to show you guys an example so here we're like talking about how do we do nft Marketplace metrics right um so this goes back to the spell book book um that I was talking about so with the nd. trades table we have data for open SE on like ethereum and salana Magic Eden looks rare I'm not going to read all of them but like literally like someone so one person can go contribute to openc on ethereum and like someone else can do openc on salana and like together we get like this more complete um system view of like all the nft marketplaces and that's just that's just beautiful amazing right so then with this table what you can do it's just like okay we want to see the total volume uh uh uh in USD in USD term or in trans count you can just do that and the way you do that is like super simple um if you know SQL um it's just you see like two lines of code select count distinct unique trick ID from nf. trades right so like this really simplifies the process like when we think about aggregating like total value Locked In Like A protocol it's just like such an insurmountable task really right because you have to like know what are out there you have like aggregated blah blah blah but like with a table like nf.

trades it's like everyone is trying re trying to reach that like complete picture for nft marketplaces right and then as a result like the the people who come after like we're all benefiting from the like people before us so we can just write a simple quy like this but obviously like the caveat is you have to understand what data you're quing so like you have to understand like the data you're recing only includes these marketplaces that are like listed um but yeah as long as you know like what data story you're telling from um I think it that's I guess that's like the tradeoff you're you're doing like you have more flexibility but you also have like more responsibility because you got to know like what data story you're telling from um but yeah so with this data set you can also just do some like visualizations about like the market share from different marketplaces you can rank them by their total volume and all these is done by just like writing some simple SQL and then once you have the SQL ready let me open one of these um uh so let let's see with with this pie chart right um with this uh so you write some SQL and then once you finish writing that SQL you can generate a visualization from the uh results table um and then it just and then you can just like add it to your to your dashboards um I'm flying through all of these just feel free to stop by our booth like if you have any questions uh because there's like quite a lot to to cover for for our platform um but yeah so another thing like I quite often do uh feeding into like learning from each other like building public um we have so I go to this like discover page uh for for for Doom and then I just look at like what's trending and like if I like I recently was like really doing a deep dive on Unis swap but like I wanted to see like what people already did for UNIS swap so I just like came here and I just did oh like Unis Swap and then I just searched for it and then I see like oh my God like a lot of people already did similar things and I can like go like click into each of them right and then I just see like what other people have done and then I just click into that quy and I learned from it so like in this way I feel like we're also building the frontier of web through data through the dashboards you're learning from each other you're sharing it's like all in the public and they're just like you're also having fun together how nice is that um okay so lastly also kind of like feeding to the the data side you can also build the frontier of upb three data through um just just apps right through apis okay so we are still in the beta uh testing stage of uh API but uh if you want it come by our booth and we'll give you an API key to test it out uh I'm just going to quickly show you like a demo of how we can do this um let me just pick something let's see what did I pick over here okay so we're going to just pull data for for this one so we're uh pulling data for the 24hour volume on all the nft market places that we we have on do um and it's just like a a number so the way you can use use API to pull Dune data is you get this Corey ID right so the first chunk of number after Cory's so you copy the Cory ID and then uh going to come back here I'm going to paste it here okay oh I already did okay so let's see so nothing really hard I'm going to just import the packages that are needed um and then I'm going to I just wrote like a quick function kind of like calling our end point points to get the data uh I can walk through that in a little bit um but pretty much like just standard call to get the data okay so first like I'm loading my environment right to make sure I can have my um D API key cool I do and like make sure okay this is like the query that I want to query for and then cool I'm just like running this function so all it does it's just like first okay authentication and then calling our like API June endp point and like putting the Cory ID and then say okay let's execute right once it execute it's going to like get the execution ID for this particular query ID um so you grab that and after you grab that execution ID it has to because it hits our buck in it's like literally running the query live like in in our like engine so you need to wait till the quy finishes so here I just have a simple while loop just waiting for the quy to finish executing and once the Cory finished executing then I basically call another endpoint to get the results for this particular execution ID um and then I just do some simple parsing to get like a pendis data frame um but yeah we only have one number here so it's pretty easy we just got Tada like 24 25.9 2 three um but yeah like a very very simple example but you get the idea so uh the way you can like pull data from Dune is just like you write a query about like whatever you want to analyze and then and after that um you grab the Corey ID and then with the do an API key um you just get the results and then you can feed into whatever application that uh you want to build um so going back to here um and then this is like the link to API docs if you want to like actually dive into a little bit more uh so today or this weekend uh we are giving five times 2,000 bounties to just kind of like for opic in integration with our Dune API um read more details online about this obviously um and then you can fail this form if you want an API key um this is the QR code for the form uh but yeah so lastly just want to leave with some like inspiration ideas right because like in a hackathon I think sometimes unless you came with an idea it's like quite hard to know what you're building so with doing API in the past what people have done um they built like sector specific Market intelligence so like the moon blocks they did like nftd ctor marketing intelligence so they were just like pulling data from from from Dune like basically the md. trades table that I was showing you guys and then they feed that into their own app or someone else did like a no code interface um so they basically used D API to integrate with like Google sheet functions that's something you can also do um or there's like data transparency tool um so right like uh tornado cache was just like such a Hot Topic so somebody like a team implemented where you can put in your address and like it will tell you like how much uh closer you are to being like kind of like docked uh by the government um but yeah so some other things you can do you can like build trading automation so because you can get like trading data live from from from Dune um you can think about how to like transform web two things into web 3 so like the same funnel that web two analytics often do you can do the same thing like in web 3 um just uh yeah in the same stack um but yeah so the data must flow and uh Happy huging this weekend thank you [Applause]