Get Started: Databricks System Tables for Cost and Auditing

0:00hello my name is Yousef and today I'm GNA talk about system tables but first let me walk you through the newsletter I developed with Quinton Umar mainly known for DB demos and demo Center we've worked together to release this non-official data breakes newsletter and I really advise you to

0:23subscribe to subscribe to it in order to get the latest features You' see we've added many categories bi and data warehousing governance machine learning and AI you will find the latest features with videos links to documentation how to get started videos

0:46it's really an interesting way to keep in touch with all the latest features I will add the link in the description of this video and now let's talk about system tables system tables are a datab braak hosted analytical store of your account's operational data and system table can be used for historical observability across

1:10your accounts you have a bunch of system tables available and data keep adding more and more you have audit logs which include records of all the audit events across your data brex accounts and for example you can see like notebooks queries you're writing you have table inage which include a record for each

1:33read and write event on Unity catalog table or pad you will have the column lineage as well Bel usage which is a very very interesting table you can leverage which includes recourse for all bable usage across your accounts and of course each usage is an hourly aggregate of resource billable usage you have the pricing if want to get the price of the

1:55different screws you're using to multiply it with the uh with the excuse you have in order to get uh visibility of what you're consuming when while using data braks you can have uh list of the Clusters you are creating which is a slowly Chang changing Dimension table that contains the Full history of cluster configuration over

2:18time for any cluster when it was created when it was deleted you have the new types which captures the currently available note types with their Basic Hardware information we have the marketplace listings and predictive optimization which tracks the his operation history of the predictive optimization feature and as you can see

2:41over here all of them are available in a catalog called system and then of course we have a bunch of different databases we have access we have billing compute Marketplace storage and of course within some schemas we have multiple tables like for Access we have audit table lineage columns and more and more tables are going to be

3:04added but the first question how can I activate system tables first of all you need to be or you need to have Unity catalog activated on your account and all you need to be uh an account admin this is very very important once you are an account admin

3:26you just need to uh uh to run the following curl command so the first one to enable this you need to have a token and of course add your workspace ID and the metast store ID and of course you need to make sure to add this the schema name but the question how am I

3:49going to get all the schema names it's very easy you just need to run this first query which is again uh you need to have the token the workspace ID ID and the metas stur ID you get the list of all the schemas and then it will be activated and I know sometimes can it's painful so don't worry we have the solution for you

4:11you just need to go to demo Center and once you are in demo Center click on explore all demos and then write down here system and over here it's just easy you can either like uh install the library Deb demos and then import de demos and

4:33around this uh this query it will automatically enable uh system tables for you but also deploy a dashboard an example of dashboards you can have in order to track your cost and this is the example of the dashboard you can for example have the uh DBU span DBU span this month dollar debus next month

4:55the forecast you can see like the number of tables you do have have the number of schemas number of catalog types of percentage of tables created table table format the number of volume so this is just an example but of course you can you can do more and if you you don't want to have this kind of tables you can just access click here on view

5:19the notebooks and you have an uh code over here that's I did run here so this code will automatically get get the metast store ID and it will generate a token then it will run this query to get all the name different name of schemas that we have over here so we see storage you'll see

5:42storage operational data access billing and it will store them and then it will run the query to make sure that it activates all the different uh schemas available and when it's done you will have the list of all the uh in system all the different

6:04system tables available so you can have access you see audit column uh lineage table lineage you have the billing inside the billing of course you can have less price usage you can have the marketplace the storage the of course the lineage and there is uh uh more and more more and more system tables uh to

6:26come and then the question how uh am I going to how how am I going to leverage those system tables so it's very very easy you can for example just come here and click for example on the pricing and you will see the schema but of course this is something you can find easily uh on uh on the on the catalog Explorer so

6:50if you click for example on usage you will have the schema but of course you can have some examples so here it's where we're adding some examples for example how to find the list of price of particular skew on a particular date so can you can just copypaste this query to have uh this you can view total cost of everything that

7:13used in a particular for example tag if you are tagging clusters you can get the cost of uh this specific the cost of this specific tag you can view like uh price changes I mean you have a bunch of thing you can do with system tables for example if you're using the audit uh the audit I'm talking about the first one for example

7:36you can find uh how many people are writing queries Which languages uh are they're using uh which command how long does it take to run all their queries by by user uh how many success did they have how many failure uh you can you can literally do everything and and you can

7:58for example add what we call for example um alerts you can set up for example some budget that you can only spend $200 per day you can once this threshold is reached you can receive an alert for example you can uh if somehow someone created more than 200 tables you can re you can get

8:21another so you can monitor everything inside the platform to make sure uh everything works well I mean this is this is really good way and how you can use this you can just go and create a SQL warehouse and of course if you can use a serverless one it's even uh it's even better because you can have great

8:44performance and uh and the fast Auto termination and of course of course uh fast startup so I did create uh secr called query I'm going to go to the SQL editor this is just uh this is just an example I will go to catalog

9:10system I will select uh query and of course let's go for example to uh use usage so select start from and of course you can also leverage the assistant to make to make make sure for example to write some textes that would be translated to a to a SQL

9:34query so over here you can see the cost by skew uh by the cloud the date first uses first usage custom tags DBU unit the quantity and some other metadata if I if I go to for example again to uh to storage you can see the tables that has

9:56been optimized or the table that has been uh vacuumed and to honest it's a simple way to monitor everything you have inside uh inside the platform and over here you can see that for example we did vacuum those tables the operation was successful numbers file rated uh 76 and

10:21this is estimated DBU 0.48 so you can monitor the cost you can monitor for example because you know in unity catalog you you do uh Grant permissions for example youf can access the table customers but you can also audit like when did youf access the table customers and when did youf table updated this

10:44table so you have the big picture of everything happening inside the platform I hope it was useful I will add all the links in the description of uh the video bye