Scale By The Bay 2021 : Cedric Dussud, Activity schema data modeling using a single table

Scale By The Bay 2021 : Cedric Dussud, Activity schema data modeling using a single table

Recording: Scale By The Bay 2021 : Cedric Dussud, Activity schema data modeling using a single table

and uh so today i'll be talking about uh this this thing we call the activity schema which is a new way of data modeling and talk first about well why did we do this so you know this is a scale conference and i'm just going to make the case that data modeling does not scale and i don't mean performance you know i don't mean how fast is it to to run sql against a warehouse what i mean is complexity you know the amount of effort it requires to build out a end-to-end data system and maintain it um can get really really really nasty as you grow um you know we we had this experience that we work where we had hundreds of data sources thousands and thousands and thousands of sql queries you know i don't know how many data models materialized views aggregations you know all these things um it became a huge huge thing to manage and we kept doing these enormous initiatives to come up with like this is the one good set of blessed data models with a great data dictionary that everyone can use and it never quite worked out um and so i won't spend too much time on this i'll assume most of you are familiar with a little bit about about how data can go can go wrong um but you know the idea is you know we build out all these data models and a lot of them end up becoming kind of similar so my example here of quarterly sales by region which is okay cool that's a sales thing and it's depending on you know set of different tables then recurring revenue over time not the same thing right definitely not but it's also kind of related to sales and it has a slightly different definition so it's going to depend on a different set of tables and so every time that we want to kind of make a new metric or look at something else that requires us to create new models refactor old ones if we're lucky um you know it's not always clear what to use and you have to read tons and tons of different sql data um just to like figure out how these things are are built and how they fit together um so i have this higher up but there's no boundary between data models and data questions and what i mean by that is is like you know you don't get to make a beautiful data model and then answer 60 different questions off of it generally speaking you've got to start to build a bunch of different independent things on top of it and it's not entirely clear is the sql statement meant to just like look up this beautiful data model or is it like itself a data model so you know what's one good way to fix this um you know we always talk about oh let's just have a single source of truth right like the most amazing thing in the world and you know maybe that would solve our data modeling problems because as soon as someone wants to know what a sale is they would go to that amazing beautiful source of truth for sales that's perfectly maintained and then it would be very quick to go and do whatever aggregation or metric they need from there in reality how do we actually do that uh so this is kind of similar to my earlier example but this is kind of you know our life back when we were doing data workout we work was the reality is just not so simple so you've got uh two different metrics which are basically the same thing but they came from different places and the numbers don't add up um you know this has happened to us a bunch of times where the ceo would just run in and say hey why my sales numbers not match my head of growth sales numbers and then some poor data person has to dig in to like thousands of lines of the sql to figure out exactly what assumptions were baked into one thing versus the other right and so no one really necessarily can agree on like what are the sales numbers for february because that means like a lot of little asterisks so um let's maybe dive into this example of it so what i'm going to do is show how we build this up in a normal way really quickly and then talk about like what if we built it differently to kind of intro to how we get into the activity scheme it's a little bit of a long intro into the concept but um we'll get there so let's say we want to understand um how people are converting based on their site visits um so we kind of want to build this table below so you know somebody viewed the website um hopefully we know who they are let's assume we do and they came from some kind of lead source maybe it was email maybe it was paid maybe it was organic organic traffic and eventually they completed an order or they didn't right and so we'd like to understand you know okay how do people convert to completing an order um based upon their ad source so how do we build this table you know from this table we can then you know aggregate it up together and and do it by by add source or by month or by however we like but first we kind of got to bring a bunch of different concepts together right so in the kind of normal data world that we all know we might have let's say we've got these three raw tables you know we've got web sessions which tell us who visited the site when um we have sales so who completed an order when and you know hopefully it's the same user but let's let's leave that assumption to get simple um and then maybe we've got another table saying here are all the people who opened emails um so in this case you know i'm not going to show the sql we don't have to dive into the details but let's just say that we're joining based on utm params so web session has great utm params that say you know how they came and let's say that we've got our sales table also was able to kind of it's in the same session maybe so our sales table has those as well and so we're able to join the web sessions for the sales so we know that this web session you know eventually contributed to the sale uh emails you know we let's just say it's coming from a different system so we'll try to be clever with our sql and we're going to say okay you know if they opened an email 30 minutes before that session then then we know that we can associate that particular email um with with that with that visit and so that way you know if the utm param doesn't have anything or has organic we can say oh wait wait you got an email just before so let's assign it to email so you know i'm not like again i'm not gonna show the sequel for this but you can kind of imagine how we built all this up so this is where i'm going to take you guys down kind of a different path it may not be clear immediately why we're doing this but we're going to kind of talk about how to do this in a fundamentally different way and see where that gets us so let's step back a moment and look at what we're doing we're trying to put web sessions sales and emails which are three very different things basically to the same table um but one thing we can notice is that all three of them have a similar structure um if you kind of look at it you know web session is someone visited a site at a certain time a sale is someone did a sale at a certain time and an email is someone got an email at a certain time so what if we did all the joins that way you know what if we joined on customer in time for all of them not just the emails now again these are not the most obvious joins in the world to do and we'll sort of get into that in a second um but what it means is that we can sort of build our marketing to sales table by kind of doing the same join in all three if we kind of treat all three the same so the next step to that would be wait a second what if literally all three of those tables are actually the same um i can picture a you know a data structure a table where we say here's a customer here's a time where they did something and here's the thing they did they you know opened an email they had a web session they had a sale maybe some metadata that describes um stuff about those things like utm params and so on we did that then again the y isn't clear yet but we'll get into that in a minute so if we were to do that then all of a sudden we're taking one table and you know we'll still have a couple different joins but they're the kind of the same conceptual joins to build our marketing visitors to sales table so where am i going with this is you get this one interesting layer of dependencies this sort of magic table that i made that has all the emails all the sessions all the sales kind of in the same format right because it's the same table they can answer a lot of different questions and we don't have to remodel it to do that we just have to kind of change up our joins so if i want to do visitors to sales with emails you know just that's one way to do it visitor to sales with emails and whether they've called us uh that's another one um and then quarterly sales right do the same thing but maybe um decimate the time stamp by quarter so this is actually what the schema is and this is how it's built so the idea of the activity schema um is that it's a single table with a very specific structure and i'll talk about that structure in just a sec and the idea is that from that one table as long as you've got all the events that you care about so you know website visit or website session you know customer order emails and frankly any other number of things inside of it then with these sort of joins over time then you can make all the different tables you care about all right so uh what is this specifically um this doesn't show all the columns so activity scheme has 11 columns most of them are metadata but the high level point is that it's a single table with the same structure and conceptually it's always there's a customer who we identify uniquely either with an email or it's null if we just really don't know who they are but we can always have a um like a different identifier which like there's a detail i can get into some other time but still we have a customer there's an activity that you did um which could be generally speaking any interesting interaction that the customer has with the business you know completed order viewed page um submitted a support ticket um shipped a product or some you know returned a product like anything kind of relevant to the business and as i said just a second ago we query that table using relationships and time i'll talk about what is the relationship and what does that mean just a sec um i should note there's a couple little dots here for the other columns uh we have metadata columns as well because you know for opened email which email did i open for pageview which page did i view right you have to know some stuff about these these activities um so there is metadata in here as well um so like why are we doing this what's the point is that we're trying to deeply separate models what we call data models from doing the actual queries from you know pulling out actual metrics to answer very specific questions um i mentioned earlier that you know my experience in the data world had been that frequently whenever there is a new data question you know when a business stakeholder says oh i'd really would love to know how calls you know phone calls relate to people moving out um let's say this was that we work a lot of times you've got to go build new data models to answer that question um you know it's not obvious how to how to stick all those things together so the idea here is you know we want to live in a world where we can model uh concepts about the business and keep them the way they are whenever a new question comes up you just have to issue a query so that's the high level idea and whether or not like we you know we believe that's possible um the claim i'm making is that the activity schema is designed to do that so how does that work so um i've mentioned this a couple times but you know the activity schema table will represent all the data that you care about meaning all the data that is relevant for analytics about a business as i said the uh the activities um they're all independent in their defined once um what i mean by independent is a given activity doesn't have any knowledge of any other activity um so the example i give earlier with the email and so on they don't need to have ids to each other so an email is just here's the time at which a customer saw an email here's the time at which the customer had a web session those things don't need links to each other in the activity schema you just define those concepts fully independently and i'll briefly show how they're defined in a moment so then once you have an activity schema built then every query you ever want to make you know any metrics you want to define any kind of bi you want to do any materialized views you want to make any aggregations all of that goes straight to the activity table it's just query it directly with sql and just have no dependencies um so really this the structure is you've got raw source tables you have an activity schema and then you've got whatever derived metric table whatever you like but no no dependencies on on models and then you know as i said earlier no metrics are queries they do not require changes to activities uh so the benefits to this is that each thing is defined once so it's a single source of truth so any numbers that are meant to be the same number will actually match you know if we're talking about sales per month and we have a very a single place that defines a sale a single activity says customer made in order then any way you're going to aggregate that it's going to match um you know you could say order by region order by whatever but of course like you know that's that's pretty clear that you're that you're um partitioning in a different way the downsides to this which might have been clear as i was talking about these joins over time is that querying it with sql directly is actually challenging um the the joins to uh to kind of put these tables together from this activity schema can be tough and i'll explain like how those are done and how to how we solve for that in a minute so this is actually the activity schema i guess you know architecture diagram if you like uh you've got a series of source tables which are the raw tables in a warehouse coming from whatever authoritative system you know salesforce zendesk your own production database you know segment for your web visits whatever that is whatever raw data that is you know that lives in your warehouse you sync that in there through five train or something um build an activity schema from those directly and i'll show that in a second and then every query you care about doesn't go exactly against that selectivity schema table and that's it no other dependencies no layers that gives you like one nice place to debug everything okay so how do we build these um it turns out actually building an activity scheme is actually straightforward so all we need to do is put data into the activity schema format so this little chunk of sql i'm not going to dive into it too deeply um it does show all the actual uh most of the um the columns that we use in the activity schema there's a couple that are computed but you know you really just need to have an id a time stamp um a customer some metadata you have to have the name of the activity so if you think about this in terms of a customer doing an activity in time then it's actually not that hard to think about how to transform most source things into this format so you know this example says shopify order so great there's an orders table and maybe you want to bring in discount codes to understand um how to compute the total amount of money that you made from that order that's okay just do a couple joins by the end of the day all you really need is to figure out well who made that order um and now what time did it happen um so you know emails same thing um crm for salesforce you could look at your leads table to see when someone became a lead when someone became closed one you know the interesting thing is that a lot of data sort of represents a business and business data is about it usually a customer interacting with the business in some way and so generally speaking it all fits into this format uh and one thing i should i should point out just in case it's not clear is that the idea of a customer is is a little bit abstract it can actually be anything um if my business was a scooter business that rented out scooters to ride the scooter itself could be the customer and then my activities could be you know the scooter took a ride scooter was sent to maintenance scooter was bought for the first time right it doesn't really matter exactly so these simple transformations so generally speaking you know we've we've been running narrator for about four years now and our customers build these very quickly um they take about 25 lines on average is what we've seen again because there's no dependencies from one to another you just have to independently decide what is a completed order and then you're good to go um you know one example we've seen was a customer who actually was using and migrated their completed order their ordering system from i forget exactly what it was before it was shopify but something to shopify and so they had built a transformation for the old system they'd built another transformation for the new system and because ultimately the idea of a completed order has the same structure both systems would make the same activity just at different times and it emerges seamlessly it works really well okay so um kind of a big shift here we're going to talk about okay well how do we query this you know let's say that building this is straightforward where it makes sense how do we actually get real stuff out of this um so i probably spent about the rest of the talk talking about this um so let's say that we wanted to build a table that would tell us you know per month what was the conversion rate to completed order from visiting a website you know we talked about this a little bit um this just doesn't have the emails so you know at a very very high level conceptually what are we doing um the activity schema is kind of a time series table and so we're saying okay like let's take all the visits and for each visit do they complete an order during that visit so we just have to figure out what is the sequel to tell us okay let's get all the visits as the rows and for each one of those rows let's figure out um did they complete an order again oh sorry in that visit before the next visit and if so you know make a column that says yes they did or no they didn't and then this is table showing sort of the aggregation which is then straightforward so the total number of rows which is the total number of web visits at the total sessions the sum of this zero one column um did they order or not would tell you the total orders and then the conversion rate would just be the average of all those zeros and ones um now the trick to all this is well how do we know that for a given webvisit they did an order in that visit so for that um we actually talked about querying a little differently for the activity schema we don't talk about it in terms of joins because all of them look similar and and they're hard to sort of conceptualize directly as as sequel um we actually talk them in terms of relationships and uh i'm going to show a couple of these i'm not going to go through all of them but what we found is that we can express any kind of analysis we've ever had to do with 11 different relationships um so let me talk about one uh initially so the example i just gave was using this relationship called first in between and this is not the exact same one but it's it's pretty similar um so we're saying what's the best email campaign to get customers to order and the idea here is um whether did someone after having an email campaign did they complete an order what's the conversion rate or did they do it or not right it's a very similar question the other one the idea is someone receives an order someone opens an email and then later at some point they may or may not have completed an order and the question is is do that open email relates that completed order and so we use this idea of first in between where we say okay take a look at all the open emails um and then for each specific given customer what is the first completed order between the email we just had and the next email right because if it's the next email the completed orders after that then you want to take that one as the one that that you match um so the idea here is every single row is going to be one opened email and then the first in between completed order will add a completed order row well we'll add um yeah we'll add data in the completed order columns what if they had a completed order first in between that email meaning completed order after that email but before the next completed email um these take a little bit to sort of understand but i'll just talk through a couple more like really quickly um you know which paywall is best at getting subscribers uh you would say okay well you have to start a subscription um what's the last before viewed paid wall and in that case you're saying um at some point someone did a paid wall uh paywall and i know some point someone subscribed to something so what's the last pay wall they ever saw and the idea here might be that um you know the they're sorry the last paywall they ever saw before that subscription happened and so that was the most recent people they saw before you can use that as a way to say okay like let's you know break out all the different subscriptions by pay while this person saw um right that might be like another way to do it uh and the point through going all these is not to understand exactly how these relationships work it's more just to kind of say like different questions can be answered by using different ones so you know how long does it take from first site visit to be a lead well give me the first ever website visit i ever had and give me the first submitted lead after that website visit uh so there's two things that are hard about quite next uv schema one is kind of thinking in terms of these relationships it's not always obvious how to how to sort of in your head turn data problems or data metrics or questions into this relationship format it gets pretty easy with time but you know that's the first little bit of a hurdle the second one is just literally writing out these relationships as sql i'm not showing the actual code here because it's a little bit hairy uh you know this diagram shows us a first in between which is okay i've found my website visit great now which completed order am i sticking next to it if one so i've got to make sure i i find this one and put it on the same row but not this one right here so joining that way can be kind of nasty so you know you want to join where it's the same activity where the time stamp is between that viewed website time stamp but before the next viewed website time stamp make sure you you know you pick the first one in case there's more than one between the two of them and there's a bunch of educations as to like what if you don't have any more viewed websites events um the actual sequels right here in tiny tiny fonts again like let's not look at it it's it's crazy um but one very interesting thing about it is that because there's only 11 and they're all very similar they're extremely easy to auto generate you know once you've written one once and you're very happy with okay this is really representing what first in between means then every other sort of data question you've got that uses first in between can just generate that query right the only difference really is going to be the activity id and you know any like filters you want to add onto it this isn't a pitch narrator or app but the point is to say with an activity schema you can build a ui that will help you query it help you build out these relationships and do whatever you need without having to dive into the sequel to do it um and we found actually that was was sort of the best way to to use the activity scheme in practice okay so that was a lot so you know what if what have we really sort of talked about here is you can create a single source of truth by creating activities the concept of activities and the activities came up together um you know every business concept you have regardless of what it is you know you only need one activity per concept and you can have as many you know concepts as you like they're easy to write they're easy to debug because they're independent um there's only one of them there's no dependencies between them and then you know the other side of things separating querying from for modeling once you've got your your data models as an activity schema then you run all your queries on top of that um you know any interesting analysis you want to do with conversion rates or anything else you're going to want to relate activities together using one of these 11 relationships um and again using a web tool or sorry a ui of some kind is probably the best way to do this so for best results auto generate these queries so you know what happens if if this becomes a thing you know if everyone has an activity schema there's a lot of kind of funny interesting things that come out of this so activity schema because it's a fixed format everyone will have the exact same table for their data so everyone's data models will differ right everyone has different ideas of what is a sale and and different ways to model it in different ways to get into the activity schema in different activities but the structure is actually the same and because the structure is the same i'm not talking about this today but a narrator we've been playing a lot around with auto generating analysis you know we are able to say okay you know just tell us which is the activity name that represents an order and which is activity and that represents you know let's say your ad spend and we can sort of automatically build you customer acquisition cost because we know the data structures the sql we can issue is going to work so yeah this is a small sort of i just mentioned that a second ago we can quickly automatically build out um auto generate analysis based on on sort of issuing a bunch of sql that we've pre-built on on the activity schema because it's the same format now this is something we've done live but again like we're not talking about this thing we just mostly want to talk about activity schema uh one last thing i think i forgot to mention is you know activity scheme has a website it's an open standard that you know came from narrator that came from our experience building this company but that we are very happy to sort of have other people use and pick up and learn about so you know feel free to visit it um there's a spec on the site um you know i'm always happy to talk about it so feel free to email me with with questions comments thoughts um you know uh if you don't believe it's possible or that it sounds crazy love to hear that too um but yeah so i think we're we're just about at the end of time so i'm gonna think switch over to the question and answer tool you