A Beginner's Guide to Analyzing Ethereum Data | James Mission - Flipside

[Music] hi everyone uh thanks for joining our presentation on the beginner's guide to analyzing ethereum data my name is James Mission and this is Trevor wer and we're on a data team at Flipside crypto we have only about 20 minutes so please hold off questions until the end a little uh introduction about ourselves my name is James and I'm the head of data analytics and Engineering at Flipside I've been working with data for a little bit over 16 years and been in the healthcare space educational space computer vision space and now I'm building the data team at flips side for the last three years and I let Trevor introduce himself hey everyone uh yeah my name is Trevor a senior data engineer at flips side um I am specializing in analytics engineering across all of our evm chains that we offer and uh yeah just really focused on making data U more accessible and uh transparent awesome thanks driver all right let's talk a little bit about flip side um we're a company that's based out in Boston and we've been around since 2017 we are leading onchain data analytics platform and we make data building data products easy to use uh with zero data engineering needed uh we have we're trusted by over 880,000 analysts and some of the top web 3 and web 2 firms in the world we cover over 26 blockchains and protocols and we adding more and more each and every day um you can integrate any data uh with our data or any our data into any project with using our SDK and API products we have over 500,000 written queries that analysts can use to begin their analyses um as always you can query data build dashboards and Export csvs for free using your flips side studio tool and at the bottom is a collection of all the different blockchains and protocols that we cover with our product and this is a agenda for our talk we'll go over flipsides highle analytics architecture we'll cover some data structures in our evm landscape we'll do a schema and V review we cover our uh entity relation ship diagram and then we'll go start building some queries and charts and dashboards with one of our ethereum curated models uh then leave some questions at the end all right uh here is the high level analytics data architecture this is just to show you at a high level how the data is built on our platform the far left side is where we Source the data from as you can see those are archival nodes for every blockchain that we cover we then work with node providers and we also have data nodes that is housed inhouse to ingest that data internally the second column is our ingestion platform uh we have things like live query which allows you to hit the node directly in real time uh with only using SQL so imagine like a postman on SQL and then you can integrate that data with any of our data that we have uh historically as well uh we also have something called streamline which is built on AWS lambdas that allows us to orchestrate ingesting data in large parallel batches as well now the next three columns represents our snowflake infrastructure a lot of our data is built within Snowflake and we use DBT as our orchestration layer for our testing and alerting as well um the bronze layer is the raw Json data that's pulled from the RPC node so anything that you would call with an RPC call um it would be stored in raw columns with text the next layer is a silver layer which is cleanse and optimized for that data we do things like removing duplicate data We'll add spefic specific data types we'll also do things like remove Uncle blocks for specific transactions um as well and it also converts it into an optimized snfl table that allows it for faster quering as well U the last layer is our gold layer which allows us to add specific metadata like prices and labels and tags to our data uh we apply business rules to the data and then we aggregate similar data together like you know a lot of defy data and exchange data or nft sales and things like that um and then Trevor later on we'll show you an example of some of those tables that we buil to make analysis easier to do and then on the very far right is how you can access data with flip side so we have an SDK we have our Builder and Pro Tools which gives you access access to a more dedicated uh and private environment to do your analysis we have our studio tool where anyone can write queries and build dashboards as well as export csvs which is free and we have our snowflake data shares which allows you to have unlimited access to our data using um your own snowflake environment so if you want to integrate your own data with our data in your own environment you can do that with snowflake data shares uh and then with that we'll let Trevor go and talk about evm landscape and data structures awesome thanks James um yeah so I'm going to take you guys all the way from uh the evm landscape what we offer at flips side those data structures all the way up to building a dashboard um with some queries in our data studio um so yeah to get started I'll give you a quick overview of what we offer from an ethereum and evm standpoint um we have ethereum optimism arbitrum polygon nosis Avalanche BSC base um and more coming soon um and our models um have this three- layer approach that James mentioned um called The Medallion approach um and this is a bronze silver and gold layering process for our data models so bronze is really um The Source data so everything coming from the node um or various apis um silver is really where the magic happens um that's our primary transformation stage and then um in Gold we have our final views so that's what you're going to see um in our data studio um for Public Access um and then our models also follow this star schema approach um if you haven't heard of that before um essentially it's broken down into a fact and dimensional um table structure fact is really record event based um and dimensional is more descriptive and contextual um and then we also uh have uh easy tables um which we sort of coined ourselves and that those are more conveniently decoded tables that um are a mix between fact and dimensional so really just trying to make it as easy as possible to access and understand this data um so what schemas do we offer um using ethereum as an example I'll kind of walk through the foundation um of how we build out all of our blockchains and then um what that means for our Downstream views um so on ethereum we have four foundational tables uh fact blocks transactions event logs and traces those are really your core building blocks um for everything you see on the screen and more um some other examples of coure tables could be decoded event logs um token transfers token balances eth balances contracts um you name it if we could build it off of those tables we we'll like have it or will in the future um for ethereum we also have Beacon chain data indexed um and Beacon chain data uh for that we have blocks validators at a stations um and then we also offer this uh pricing schema which helps us kind of add context to a lot of our views um and that is sourced from coin Gecko and coin market cap at the moment um so by taking all of those building blocks we can build our Downstream views um and the downstream views are really going to be pretty much every primitive you can think of um across Defi and nfts um as well as additional ones um so our goal is to really have tables for every single um primitive in crypto um so that could be dii um swaps uh deck swaps nft sales bridging lending liquid staking um we also have protocol Centric schemas um so this could be really diving deep into Unis swap data or a data um for the beacon chain we offer you know different views on deposits and which draws um validator balances and then we have a stat schema coming soon as well which will be um aggregated core stats um make it even easier for you to query this data awesome so uh what you're seeing up here on the screen right now is going to be um an entity relationship diagram basically showcasing how we build our easy deck swaps view um so all the way uh on the left you're going to see that we have our bronze layer which James mentioned earlier um so that's really all the data coming from the node um we feed that through a number of different tables but eventually they end up in silver logs and silver traces um logs are um events that are emitted by contracts um and traces are really when contracts uh interact with each other within a transaction so by taking that core data um we can decode it and build out this silver deck swaps table which has everything from the transaction hash all the way to what tokens are being swapped what what raw amounts are being swapped then we're also able to build this Dex liquidity pools table which um is everything for the creation events um or the creation traces uh on the pools that the tokens are being swapped on um so by combining those two tables we eventually can get to this defi uh. de swaps View and this is going to be um an easyto use table that allows you to query um who the the sender and the receiver are of the transaction um what pool the swap would was occurring on what the tokens are the symbols the amounts um pretty much everything descriptive alongside those records we're going to have here all right so now sort of to the fun part uh we take all that data structure and we're actually able to query it U very quickly and easily uh using SQL um and so I'll break down this query real quick on the left but as you can see on the right uh the query will will bring up these results um which is entally answering the question of uh what how much volume uh per day um is happening on each of these Dex platforms um and so in the query we start off by um breaking down the block time stamp segmenting it by day um and platform and then we're summing the amount in and the amount out to get volume and Counting um all the swaps that are occurring in this table as well as the unique contracts um to get the number of pools and so we're we're looking just over the last 7 Days um and yeah by doing that we get this awesome table right here and while having that table um is really good to have that contextual detail um it's uh not super easy to digest and so flip side also offers full charting capabilities um on our platform um so right here we actually have an example of taking that Dex swap views expanding it to 30 days um and basically showing the top eight platforms um over that period in a an area chart and so as you can see on the right um there's a number of different parameters you can edit um including uh you know what categories you want to include what columns um how you want to segment the data the color um pretty much everything you could think of and so lastly we take those charts um and we aggregate them all into dashboards and so Flipside has a massive community of analysts um I think you said 880,000 or yeah and that number is always growing and and so these are some uh example dashboards that we have up here you can see this's everything from Dex volume all the way to flash Bots data um and yeah these dashboards are public um they're sharable they are um refreshable and yeah anyone can go and access those um at any time so all this is free to use uh at in our data Studio Flipside crypto XYZ um you can Cory the data build as many dashboards you want even export it um for your own use um in your own environments I believe that's all we got so um if anyone has questions uh in the audience we'll be down there to answer them uh thank you so much thank you guys and we'll also be at Booth 231 in the dotopia area if you guys have any questions as well if you want to try the product out as well so awesome thanks everybody