Send Looker-on Initials with airSlate SignNow

Save initials looker-on

so hi guys i'm jonathan miller corvettes I'm a data analyst on our customer support team the DCL as many of you have been hearing throughout join this deep dive is going to be to use or not use PDT so if you're supposed to be somewhere else and that would be a good time to be there okay awesome so we're gonna dive in so what we're gonna cover today is why we derive and persist so give some background to some brief introduction to derive tables types of drive tables when to use them of course what to look out for and don't worry they're gonna get worse from here guys so just bear with me okay when to move to an ETL okay and this really this talk at a high level is really about that why we build drive tables and then when is it time to actually migrate them over to the ETL the balancing act of all of this and I'd like to end with some development best practices and look or that some of you may be familiar with but I'd like to bring up so why do we derive and persist what is a derived table a drive table is a sequel query that defines a set of business logic it returns a reduced amount of data okay and it can't include complex calculations and data transformations persistence in the case of today we're going to be looking at caching data or really the data survival after its creation process is terminated so some examples of persistence is when we go to a web app and we're filling out a form and I go do something else and I come back to the web app and uh all my data is there the forum's still filled out I can start completing it till the end I don't have to start over from the beginning another example of this would be many of you are in embed case so you're caching large quantities of complex analyses to an end user and they need to access that right away and really what this is in looker as a whole is just ensuring that the data is ready for the analysis and I want to keep in mind something that I think we as analysts we focus on the problem we're able to break it apart but there's really an end user here and that end user could be someone in the public it could be another day analyst or could be a team of analysts it could be engineers so really keeping in mind who that end-user is is something that we want to keep that we want to do for the remainder of this presentation so we've got two types of derived tables and looker and I know your heads are filled with acronyms and of course the two I'm gonna give you today are gonna rhyme to make it fun so we've got a femoral derive tables or Edie T's okay and they generally start with a whiff clause I know my sequel is a little different and I'll explain the difference between the two in just a second we also have persistent derive tables or PD T's also as Lloyd mentioned this morning there are is now native derived tables now native derive tables are still both of these so I want to make that very clear they are just a different syntax for being able to create them so you write that and look ml with less sequel verses writing all the sequel out so what's the difference between these two things Edie T's build at run time of the query every time PD T's build by triggering and/or persisting and I know that data groups were mentioned so I'm going to come back to PD T's when we get to that section and kind of talk about the nuances there so when do we do this why do we build a derived table well we've got historical summaries entity and transaction tables roll-ups also aggregations I've kind of heard those terms used synonymously and to overcome sequel structural limitations those are things like window functions those are also things like correlated or nested you know sub queries so if you get nested aggregates right that's not a valid sequel statement we need to build a sub query to overcome that so let's start with Edie T's okay when do we build an EDT instead of a PDT when the view is quick to run okay it doesn't involve needing to have it ready it's quick to run when the view should include real-time data when it should be dynamically built based off of user filter inputs this is something that we see on chat all of the time I come on okay someone call it calls me or they chata and they go hey I have this template a filter and a persist drive table sorry it's not gonna work we got to move that to an ephemeral drive table okay because it requires that filter input to use that build if you omit the filter input it will still create a true statement and build that table whereas persistence is triggered not by user input it's triggered by this value that we sent and also when it needs to be dynamic but the number of permutations are manageable Kalea and this would be something like user attributes for instance okay that's something that we can use to dynamically build these tables as well so I was talking with my colleague Maxie Corbin who did a talk earlier in the day and I love that she's just so blunt about some of these topics and for me this was a great quote that she actually said and was just so wonderful so I love ephemeral derive tables because they feel lightweight and focused but they make most sense when you're doing something small quick and or if what you're doing or an agile and or if what you're doing is sensitive to frequent ETL if you don't mind doing the if you don't mind the computation cost and redoing the computation each time then I'd say don't persist so that's always a great starting place but sometimes we find that this drive table takes a long time to run so if we're trying to build it every time at runtime it's not going to work for our end user so when should we build a PDT data freshness requirements are something that we always should consider here if we're doing yearly analysis over the last let's say 50 years is today's data having the most you know essential data for today gonna help that analysis probably not that's probably a different analysis we want to do where is that a yearly scope we want to persist that so we can you know quickly access all of that data at one time so another big one is the available database resources and this ratio to resources consumed by the build we see this constantly as well my queries aren't working okay what's going on well when we run on we're getting a timeout or we're getting a table lock or something like this and you're like so we dive in we look at the queries panel there's this PDT that's been building for three hours right they're adding there at there our third kind of weekly meeting check-in for this stuff and the rest of the queries are just halted and so knowing your database knowing what limitations it has and really looking at optimizing this full system is kind of what will move into your in a sec so how do we then persist well it used to be that we would define persist for or a sequel trigger value for these builds now we've got data groups and we're gonna be saying that a lot I know you some of you saw that today it's allowing you to set a caching policy you get the best of both worlds of persist for and sequel trigger value and you can also set the cache age for these to be broken you guessed it what to look out for so PD tees are powerful but we want to stress something to you guys they are not the end-all solution in many cases they are not the perfect solution so being aware of what the front-end user experience is going to be is something that we want to take in mind here computational resources I know I touched on that and available database resources so that's both database time the amount of time it takes to process something as well as your time when you have to come and troubleshoot the query queue and cancel all these things because right it's all backed up and if you're a big query user that could also be actual money because the quantity of data that you're running actually charges for that so this is a funny meme that well it's funny to me whether you tonight you think it is I'll leave it up to you but for me it's like you know this is our meeting it's our weekly meeting it's our check-in we've got an hour to do this to sync up with everyone across the world and we've got how was your retention rate changed how much of none of the none of the queries are working a sequel error a table lock and then the lead analyst is like yeah has anyone had a table lock situation before in here oh look at this no one ever right or we don't want to admit it of course of course so when then should a PDT be part of the ETL and really what it comes down to is a couple main pieces here there's probably some others but I wanted to touch on the main pieces one is if you have a powerful ETL tool that you can leverage okay sometimes you don't so you can use looker as an interim until whether you're ready for that or to see how that behaves but please note that this is liquor as one piece of this big pipeline that we put together when the PDT is logic is well understood and consistently being used my favorite chats are the ones where they come on and they're like okay well we're using extends and we have sales one sales two in sales three okay I'm gonna tell you more now we've got sales 1.2 and 1.3 and 1.4 and you're like okay I'm lost like let's back up a step here what what are we doing what is the point of this like how do I even know what we're diving in and also we're talking about scaling data to other parts of your organization if you label them or if we organize them in a certain fashion the interpretability of them improves greatly and so someone can quickly and easily pick them up and actually understand what's going on and the table lock situation is when a PDT is being used outside of looker if it's being used outside of looker then move that query outside of looker and the reason for this is that looker acts as its own user right as we set up our connections we define a user on our database it's running checks that's running to see when caches should be broken it's running create tables drop tables rename write it's doing all of this and so if simultaneously you try to ping that same table as the looker user is doing that you can cause this table lock and your database is smart so it'll be like hey sorry you know don't proceed you can still like get around this you know you can still figure your data's not lost but I'm gonna shut down and really when the et the naming of the ETL table clearly communicates its content and that's something I want to continue to bring up about this organizational part about being clear so this is a little diagram that just kind of shows where looker fits in this process and you can see that it's very in a very iterative process and I was talking to some of the people on our team our product team and just talking about where they see this going we're at a time where we start iterating and developing more often we're constantly asking new questions and we want the ability to be agile but also have stability when we need it so looker fits into this loop where you can extract write the 'part you can transform and date the data looker and you can do that kind of iteration without sending that drive table to the end-user as you prototype so what do we do how do we move it well good luck just kidding so you already have the sequel it's really straightforward you have the sequel and look ml provides models across dialects so you move it to a database and now after we've seen with looker five if it's in two different databases boom you can stitch it together on the front end so what it comes down to is this balancing act okay it's a pragmatic balance between flexibility and reliability where few PD T's are flexible but many PD T's can be unreliable so we think of I have this problem that I'm trying to overcome and it merits building a derived table so I build it okay Wow it takes a while to build then I persisted well I still need parts of it to be you know update manually based off a filter input okay or dynamically based off a filter input okay so I need maybe another EDT okay so then we have all of this building and building we get more derived tables and more persistent drive tables and all of a sudden trying to figure out a schedule of when those should build if they should build overnight if they should build during the day whatever becomes very complicated and unreliable and then it backs up that database load on the other hand you have reliability with the ETL but it's not as straightforward to all of a sudden do ad hoc analysis or create you know PDT that's going to overcome that sequel structural limitation or whatever it is so this is really the balancing act that we go through here so I want to get into some development best practices within looker and the first and I'm going to continue to bring it back to this is use consistent naming conventions I get chats all the time where we're like okay we're looking through there's no it's not very clear it doesn't look like there's any development guidelines um and one thing that we see all the time is I want to join this table back in have you considered primary keys because you're gonna be dealing with symmetric aggregates here and so can you easily define one of these if you're grouping by a field you can call that and that's the only field you're grouping by that will be your primary key if you have multiple fields or grouping on a lot of times you can compound those to create a compound primary key we have development guidelines right many people have heard this for engineering well now we're looking at a code base for analysts and this is something that we strive to do a looker and actually do within our team iterative development I love the chats that start off with a sequel block of a thousand lines no hello no hi how's it going just here's a thousand lines and and I'm trying to figure out okay like where do we start now if you give me a paren if one of those little parens is sneaky riding these huntin and he's running off to the side I can help with that pretty quickly I've got some methods and tools to do that but when you're like yeah the data is not right the dates not right okay well what are we gonna do how do we problem solve this you know the first thing I do I comment all of those lines out and I send you the first thing that's gonna be calculated okay does this run let's see if it runs in sequel runner does it yes is the data correct no well here's one problem okay let's look at the next part that it does and so all of a sudden we take something that could have been done in iteration went through testing and we don't plan for this and what happens it takes a lot of our time to debug you have to come to us which I love I want to stress that I'm happy to sit and comment out code all day that's fine by all means please send it over but just note that this could have been done in a more efficient way and we could have planned for this so this touches on you know test the sequel as you go write tests that block see how it behaves cool this works I like this let's keep this okay now let's build the next part here let's test that let's see how it runs and continue this and for those of you that love to validate I don't know once a year and you click and all of a sudden you're like there's a hundred and eighty-eight look ml errors and we're like okay let's start with the first one just so you know this is gonna be a long chat okay sometimes I say that like we're gonna be here for the next eight hours so just you know make sure you've got your cup of tea and everything else that we're gonna do here so that's just another one changing and pushing to prod is a big one where this is just a little like how it works and so I want to make sure that this is clear if you can build your table and development mode do it there the reason is is because as soon as you make changes in the code whether it's a data group change whether it's a change to the sequel looker now recognizes that as a completely new table and it will force a build when that code makes its way on to production if however you build it in development mode then when you push that code it will promote the table that's built in dev them and some file and code structure so we've got horizontal versus vertical and I'll dive into that right now so right we have a thousand tables so already trying to just find one of the tables is kind of a nightmare and how do we make this clear well those tables have specific uses and so what we can do actually is we can prefix each view name with a certain you know keyword that basically groups these files together so if they're all for your marketing team maybe put MKT dot whatever the file name is and the reason for this is that number one the sort of those view files will all be based on that and so it'll group them together and the second thing is that when you include those files in a model or you're doing extends right we're extending models or something like that trying to track down where the include is going into all these files can be hard and take time whereas if we use a wild card that you can kind of is it coming through here yeah okay just my angle where if you use a wild card you can actually call all of those files at one time in vertical I'm gonna kind of go through a couple of these this is really just a ephemeral Drive table that I built that all it's doing is it's looking at the total amount of usage of all customers and how that compares to each customer so how much usage to the total usage has each customer done and what this does is it modular Rises my code and makes it very clear as what section does what thing okay what block of the sequel actually does and so what we can do is define Believe It or Not multiple views and a single view did everyone know you can do that yeah okay totally perfect so what we do next is we look at building out that some of usage in minutes for each customer and their email we then do that enter join and ultimately what we're looking for in this particular example is we do a cross join where we then can compare each one of these views that are in a single view file and so for instance the view file would be view and what we have is a development best practice is that if this is a standalone view it is not being joined back into the model this should never make its way to the end user or only developers or analysts who have developer permission should use it then we build us an explorer at the bottom of that file that's hidden and so our model file stays very clean that's where the model is that's where the the the code lives for the rest of our end users but for us we know that this means that this is a standalone view we use internally so at a high level one to use and kind of a take away at the end we've got EDT s real time data quick quick query or quick to run dynamically built we've got PDT data freshness available database resources prototyping ETL powerful ETL tool well understood PDT consistently used and used outside of looker alright you