Databricks SQL Cost Attribution: Track Costs per Query

0:04hi uh today I have C Davis he's back for a second episode he will be talking about a magnificent dashboard that's going to be super super useful in order to track the C for every single query you will run on your Databricks environment hey C hey how's it going all good all good thanks again for uh

0:26joining this uh this session I'm pretty sure you're going to help a lot of people uh with this dashboard so can you absolutely tell us maybe more about this dashboard who came up with it and maybe once when it is going to be available is it something that everyone can set up yeah so this is going to be one of the one of the first couple dashboard templates that our product is releasing

0:49is like a sort of datab bricks bless product template um this one being uh sort of focused on cost per query for DB SQL has been a really popular ask uh customers and uh people considering using Databricks uh for data warehousing across across the industry so really excited to get something out there and and and see something in use so yeah the dashboard is really aimed at

1:12you know helping take the cost of a warehouse It's a shared resource and attribute that up to different users groups and teams um this usually is for a couple reasons one of them just being finding the most expensive queries so what queries are taking up the most uh compute time and uh resources of the warehouse you don't necessarily need dollars for that um but it's a nice

1:34proxy that everyone understands um that that is pretty digestible in terms of figuring out um where the sort of problem queries might be the second piece is more around like budgeting and cost attribution for either internal budgeting so being able to attribute costs of a shared resource uh across teams so if you have a team or multiple teams that share a warehouse um you can

1:56attribute and divide the cost of that warehouse based on some um reasonable uh accurate reasonably accurate metric of cost allocation uh the third example I see the most is um actually attributing cost for Revenue generating data apps so if you have data apps in production that uh that fuel your own customer reporting in use cases you may

2:19want to attribute specific uh portions of your DB SQL usage back to that customer as a chargeback and this offers a really good way um to do that for you out of the box yeah and uh can you show us maybe where to find the uh the resources to upload this uh or to implement this dashboard absolutely let

2:41me pull it up here so it is uh officially in our datab bricks Labs uh environment so our product is publishing uh these dashboard template private previews in datab Brooks Labs um but you can also go to uh our DBC equals me medium publication to find not just the dashboard itself but a

3:04quick intro of the dashboard how to think about it how to use it um I can paste that we can you know paste that link in the in the link on this video but I can also just sort of share my screen here to Showcase where this is so yeah this is the blog if you Google dbme engineering the blog that sort of describes this initial uh dashboard it's

3:26called Introducing granular cost monitoring for datab Brick SQL and this will tell you not just where to go what to do but also a little bit about the dashboard and how the logic Works itself but uh we don't have to go to the blog today because I I'll show the actual dashboard live here and the link will be added in the description of this video awesome so what makes sense to do next

3:48yeah we can just you can share the dashboard and that's it and you can start maybe explaining I mean the upload part everyone can it's super easy part but they will have the uh so um to actually use the dashboard um you'll go to the git repo uh attached to this link and you'll have a uh twep process one to deploy a data model which just runs a

4:12materialized view or uh table data model that powers the dashboard um and then you will go to your dashboards Tab and the GitHub repo will also contain a Json dashboard template you'll go to this create dashboard import from file and simply upload that template and then you should be be Off to the Races and then you'll officially get this dashboard here so asual just keep in mind that you

4:36need to have system tables enabled else won't be having those kind those uh information popping up here yep yep so they do need you do need the query history and warehouses and Warehouse events system table enabled and billing as well so if you don't have those already um check out the datab bricks documentation to get those turned on it's pretty quick and easy to do um but

4:58yeah then once you have that this is the dashboard that you'll get uh the dashboard is pretty straightforward so um for a given period of time you can look at one or many workspaces um and of course one or many warehouses so you can um look across many warehouses at once um and you can also start to analyze sort of your top uh in analysis here so

5:21if for your use case you're looking for the top uh 10 users uh that are running queries on a warehouse um you can do that for many warehouses in this case we're looking at a bunch of our warehouses all at once but you can also say well I want the top 10 actual queries themselves so I can see what our most expensive queries are or my top 10 objects which means you know my

5:43dashboard IDs my um my actual notebooks themselves any object ID associated with a datab bricks resource that'll be tracked and also type so what are your topend uh types of resources this can be a dashboard a SQL query and even external connections like DBT five Tran and other U connections that way so

6:05those are the sort of Dimensions you can filter on and and stack rank on in our example here um we just have uh we're just going to look at top 10 executed users so this will help us understand the top 10 users um and we can even search for specific users um types of dashboards uh as well as uh query sources themselves and then we can even

6:27put in a DB SQL discount if you have a discount on SQL the system Tables by default for now are list rates so this lets you actually put in real dollars um with your own discounts on DB SQL and then the rest of this just shows um a pretty quick breakdown of the queries users and objects um stack ranked by query attributed cost um so that really

6:49shows you a stack ranked list of top dashboards top users and even your top sort of uh individual assets uh themselves so very very simple breakdown most of the meat and intelligence of the dashboard is at the bottom of this here so here you can see uh our not just our usage but our attributed usage over time and you can Trend this out by day Hour

7:11Week quarter you know month in this case daily is default this will show you the total attributed cost of the queries split up by the work done on the query so the resources that that query takes this is the total for each day attributed to that for a given Warehouse so here this is the really the meat of the entire dashboard uh in the

7:34dashboard itself we calculate a metric just called query work um put really simply it's just the amount of task time and resources that that query consumes and then the total cost of the warehouse is proportionally divided up um dependent on how much work um proportionally that each query takes up in that given time period there's a little bit more Nuance to that but the

7:57logic is publicly available you can see it um and you can you know customize it to any specific business attribution logic that you have um but the starting point is a pretty good industry standard um but here you can see the individual statements the attributed usage as well as the actual warehouse and everything you kind of need to know about it um when the query started how much work it

8:19did what the actual query duration was um when the total query start in time was what the work start in in Time start in in time is um as well as individual breakdowns on where this attribution comes from so queries can run you know for a long time so uh for each hour uh the warehouse usage for

8:41that hour is attributed proportionally to the queries so you can see the individual hour line item details of this here so this this is an example of a really long running query um that gets attributed over many many hours so overall that's really it so the the main point of this dashboard is just to help with with attribution as well as get a quick uh summary and feel for like

9:03how uh intensive your queries are um over time and uh in general for your given data products yeah especially the fact that you can select the product whether it's aii dashboard whether maybe it's consump consumed by I don't know BBI or maybe Genie and then you can like check like maybe which queries are most consuming maybe go and optimize them and

9:27it's it gives a lot of insights and kind of dashboard can be combined with the uh the uh DB SQL Warehouse advisor that we that you highlighted during the last session that's how you can really get the most of your data breaks investment and increase your your AE and also the performance of uh your your architecture

9:51yep and yeah you can even um if if you like let's say we have a really expensive query here and we want to see if we can optimize it you can actually click on the individual state IDs and it will take you to the actual Query profile itself so that makes it really easy to not just find the queries that are expensive but you know we can quickly see the most expensive operator for this query is this aggregation and

10:13we can sort of see okay is there anything else we can do to optimize this generally this looks like a pretty pretty decent query um but that's that's it makes it really easy to sort of find the needle in the Hy stack and cod if I'm not wrong joho is looking for feedback so if anyone of you uh is deploying or deployed this dashboard make sure to share your feedback with joho we will add the email

10:36uh in the description of the recording so we can improve this this dashboard made it uh or enhance it absolutely yeah it's currently in private preview now um even though it's publicly available um but you know like we mentioned this is a template so we want to refine the template and make sure so if you do want to um include your own feedback in the private preview reach out to jho uh and

10:59and uh perfect thank you so much Cod it was very useful all right