https://community.databricks.com/t5/technical-blog/queries-for-cost-attribution-using-system-tables/ba-p/76558 <P><SPAN>Organizations have expressed the need to see trends across their Databricks Accounts and drill down into Workspaces, SKUs, tags, and users. <A href="https://docs.databricks.com/en/admin/system-tables/index.html" target="_self">System Tables</A> provide this visibility with little to no setup needed, just an API call to opt into certain schemas. In this blog, we will leverage Databricks System Tables for cost observability by using the billing, access, query, and compute schemas. We include a common template for a Lakeview Dashboard that is versioned and shareable in a <A href="https://github.com/databricks-solutions/databricks-blogposts" target="_self">public repo</A>.<BR /></SPAN></P> <H1><SPAN>Sourcing common insights from System Tables&nbsp;</SPAN></H1> <P data-unlink="true"><SPAN>System tables provide a centralized way to gain insights into how your Databricks resources are functioning and changing over time. They must be <A href="https://docs.databricks.com/en/administration-guide/system-tables/index.html" target="_self">enabled</A> by an account admin and operate at the schema level.&nbsp;</SPAN></P> <H2><SPAN>Core System Tables needed to source cost attribution</SPAN></H2> <P><SPAN>Here are the tables that will help you gain insights into the Account's cost analysis and drill down into the Workspaces, Warehouses, Clusters, Users, and Tags.<BR /><BR /></SPAN></P> <TABLE> <TBODY> <TR> <TD> <P><STRONG>Table name</STRONG></P> </TD> <TD> <P><STRONG>Definition</STRONG></P> </TD> </TR> <TR> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>Contains entries for events broken out by Service and Action Names. For example, we filter on the notebook service and runCommand to determine the duration of a command run.&nbsp;&nbsp;&nbsp;</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>Helps us correlate runs from query history and the access audit tables to what was billed and by whom.</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>system.query.history</SPAN></P> </TD> <TD> <P><SPAN>Captures user-level query execution information.</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>system.compute.clusters</SPAN></P> </TD> <TD> <P><SPAN>Contains events relating back to the cluster configuration.</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>system.billing.list_prices</SPAN></P> </TD> <TD> <P><SPAN>Contains the list prices for a period.</SPAN></P> </TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <H2><SPAN>Building insights into cost attribution&nbsp;</SPAN></H2> <H3><SPAN>Cost attribution by tags</SPAN></H3> <P><SPAN>The billing table provides a <EM>custom_tags</EM> field that can be used as a parameter to filter workloads that are appropriately tagged.&nbsp; The Tags column in the billing usage table is a map that can be filtered with <A href="https://learn.microsoft.com/en-us/azure/databricks/dashboards/parameters" target="_self">Lakeview Parameters</A> by leveraging a simple condition to select all tags or specific ones filtering on the key and value of the map as seen below.&nbsp; These Parameters can then be part of the dashboard to filter the visuals appropriately:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python"> :tag_name :: string = 'all' or u.custom_tags [:tag_name::string] = :tag_value :: string</LI-CODE> <P>&nbsp;</P> <P><SPAN>Parameters for Tag Name and Tag Value in the Dashboard&nbsp;</SPAN></P> <TABLE> <TBODY> <TR> <TD> <P><STRONG>column</STRONG></P> </TD> <TD> <P><STRONG>data type</STRONG></P> </TD> <TD> <P><STRONG>source</STRONG></P> </TD> <TD> <P><STRONG>description</STRONG></P> </TD> </TR> <TR> <TD> <P><SPAN>workspace_id</SPAN></P> </TD> <TD> <P><SPAN>string</SPAN></P> </TD> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>The unique ID of the workspace&nbsp;</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>workspace_name</SPAN></P> </TD> <TD> <P><SPAN>string</SPAN></P> </TD> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>The name of the workspace&nbsp;</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>usage_date</SPAN></P> </TD> <TD> <P><SPAN>date</SPAN></P> </TD> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>Activity date on the workspace</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>cost</SPAN></P> </TD> <TD> <P><SPAN>decimal</SPAN></P> </TD> <TD> <P><SPAN>system.billing.list_prices</SPAN></P> </TD> <TD> <P><SPAN>Total cost on the workspace per day</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Tag</SPAN></P> </TD> <TD> <P><SPAN>String</SPAN></P> </TD> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>Tag name</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Value</SPAN></P> </TD> <TD> <P><SPAN>String</SPAN></P> </TD> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>Value of tag</SPAN></P> </TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <H3><SPAN>Cost attribution by account</SPAN></H3> <P><SPAN>The combo chart below displays month-over-month analysis for dollars and DBUs consumed for the Databricks account, representing the consumption of all Workspaces provisioned with the cloud provider used.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Account Trends dbus &amp; $dbus.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9218iEE48CDC16331912B/image-size/large?v=v2&amp;px=999" role="button" title="Account Trends dbus &amp; $dbus.png" alt="Account Trends dbus &amp; $dbus.png" /></span><BR /><BR /></P> <H3><SPAN>Cost attribution by Workspace&nbsp;</SPAN></H3> <P><SPAN>The below bar chart shows a drill down into Workspace consumption month over month.</SPAN></P> <DIV id="tinyMceEditorsamerzabaneh_2" class="mceNonEditable lia-copypaste-placeholder">&nbsp;</DIV> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Workspace ID Trends by Month copy.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9220i3C9C40432F191CCD/image-size/large?v=v2&amp;px=999" role="button" title="Workspace ID Trends by Month copy.png" alt="Workspace ID Trends by Month copy.png" /></span></P> <P>&nbsp;</P> <H3><SPAN>Cost attribution by SKU</SPAN></H3> <P><SPAN>The bar chart below shows the month-over-month $DBU consumption for various Databricks SKUs. The SKUs are taken from the <EM>sku_name</EM> field in the Usage table, and a simple case statement below gives us the categories of interest. Of course, these can be rolled up to a higher level with the new fields provided in the Usage table, <EM>product_features</EM> and <EM>billing_origin_product</EM>.</SPAN></P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Sku Trends by Month.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9221i68B84C3F4F68CD28/image-size/large?v=v2&amp;px=999" role="button" title="Sku Trends by Month.png" alt="Sku Trends by Month.png" /></span></P> <P>&nbsp;</P> <P>You can use this case-when statement to help summarize consumption by product SKU.</P> <P>&nbsp;</P> <LI-CODE lang="python"> Case when u.sku_name like '%SERVERLESS_SQL%' then 'SQL-Serverless' when u.sku_name like '%SQL_PRO%' then 'SQL-Pro' when u.sku_name like '%SQL%' then 'SQL-Classic' WHEN u.sku_name like '%DLT_ADVANCED_COMPUTE%' and u.sku_name like '%PHOTON%' THEN 'DLT Advanced (PHOTON)' WHEN u.sku_name like '%DLT_ADVANCED_COMPUTE%' THEN 'DLT Advanced' WHEN u.sku_name like '%DLT_PRO_COMPUTE%' and u.sku_name like '%PHOTON%' THEN 'DLT Pro (PHOTON)' WHEN u.sku_name like '%DLT_PRO_COMPUTE%' THEN 'DLT Pro' WHEN u.sku_name like '%DLT_CORE_COMPUTE%' and u.sku_name like '%PHOTON%' THEN 'DLT Core (PHOTON)' WHEN u.sku_name like '%DLT_CORE_COMPUTE%' THEN 'DLT Core' when u.sku_name like '%JOBS%' and u.sku_name like '%PHOTON%' then 'AUTOMATED (PHOTON)' when u.sku_name like '%JOBS%' then 'AUTOMATED' when u.sku_name like '%ALL_PURPOSE%' and u.sku_name like '%PHOTON%' then 'INTERACTIVE (PHOTON)' when u.sku_name like '%ALL_PURPOSE%' then 'INTERACTIVE'</LI-CODE> <P>&nbsp;</P> <H3><SPAN>Cost attribution by Warehouse</SPAN></H3> <P><SPAN>This widget in the cost attribution dashboard represents the weekly cost for a DBSQL Warehouse.&nbsp;</SPAN></P> <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Warehouse Cost over Period copy.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9222iF5FFE3199A404887/image-size/large?v=v2&amp;px=999" role="button" title="Warehouse Cost over Period copy.png" alt="Warehouse Cost over Period copy.png" /></span></SPAN></P> <H4>&nbsp;</H4> <H4><SPAN>Warehouse insights by user</SPAN></H4> <P><SPAN>The below table shows the hourly warehouse consumption pattern and ties back the warehouse usage cost to a user.&nbsp; How it ties back to the usage is based on grouping the query history table into hours in the day so that the duration of the queries executed within the hour ties back to the Usage table for cost.&nbsp; A simple calculation of the duration of the query compared to the hourly total cost can give a sense of the percentage of cost that can be attributed to a user.&nbsp; </SPAN></P> <P><SPAN><EM>*As a disclaimer, the estimates below depict the duration of the query relative to the Usage tables billing for that hour. Other factors may be needed for appropriate chargeback, but this gives a reasonable view of what users are contributing to costs based on query time.</EM></SPAN></P> <P><SPAN>This below table shows the hourly warehouse consumption pattern and ties back the cluster usage cost to a user.&nbsp;</SPAN></P> <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="cost_per_user_warehouse.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9230i0B963768F0B0D08C/image-size/large?v=v2&amp;px=999" role="button" title="cost_per_user_warehouse.png" alt="cost_per_user_warehouse.png" /></span></SPAN></P> <P>&nbsp;</P> <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Warehouse top users per duration (1).png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9238iB67C7C8A0035237A/image-size/large?v=v2&amp;px=999" role="button" title="Warehouse top users per duration (1).png" alt="Warehouse top users per duration (1).png" /></span></SPAN></P> <P>&nbsp;</P> <P><SPAN>Below is a snippet of how this is achieved, while the full code can be found in the <A href="https://github.com/databricks-solutions/databricks-blogposts" target="_self">repo</A>.</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">with usage_by_user as ( select workspace_id, executed_by, compute.warehouse_id, date(start_time) `event_date`, hour(start_time) `hour`, sum(total_duration_ms) / 1000 `total_duration_by_user_in_hour`, ( select sum(qhs.total_duration_ms) / 1000 from system.query.history qhs where date(qhs.start_time) = date(qh.start_time) and date(qhs.end_time) = date(qh.end_time) and hour(qhs.start_time) = hour(qh.start_time) and qhs.workspace_id = qh.workspace_id and qhs.workspace_id = :workspace_id::string and qhs.compute.warehouse_id = qh.compute.warehouse_id group by date(qhs.start_time), hour(qhs.start_time), qhs.workspace_id, qhs.compute.warehouse_id ) `total_duration_by_hour` from system.query.history qh where date(start_time) &gt;= date_add(week, - :duration_in_weeks :: integer, now()) and qh.workspace_id = :workspace_id::string group by all ) select u.workspace_id, u.executed_by, u.warehouse_id, u.event_date, u.hour, round(t.dollar_dbus, 2) `cost_by_hour`, round(t.DBUs, 2) `dbus_by_hour`, u.total_duration_by_user_in_hour, u.total_duration_by_hour, t.custom_tags, round( ( total_duration_by_user_in_hour / total_duration_by_hour ) * 100, 2 ) `percent_consumption_by_user_in_hour`, round( t.dollar_dbus * `percent_consumption_by_user_in_hour` / 100, 2 ) `cost_by_user_in_hour` from usage_by_user u inner join ( select date(u.usage_start_time) `event_date`, hour(u.usage_start_time) `hour`, u.workspace_id, u.usage_metadata.warehouse_id `warehouse_id`, to_json(u.custom_tags) `custom_tags`, round(sum(usage_quantity), 2) `DBUs`, sum(usage_quantity * lp.pricing.default) `dollar_dbus` from system.billing.usage u INNER JOIN system.billing.list_prices lp ON u.account_id = lp.account_id and u.cloud = lp.cloud and u.sku_name = lp.sku_name and u.workspace_id = :workspace_id::string and u.usage_start_time between lp.price_start_time and coalesce(lp.price_end_time, u.usage_end_time) and u.usage_date &gt;= date_add(week, - :duration_in_weeks :: integer, now()) and u.sku_name like '%SQL%' and ( :tag_name :: string = 'all' or u.custom_tags [:tag_name::string] = :tag_value :: string ) group by all ) T ON u.workspace_id = T.workspace_id and u.workspace_id = :workspace_id::string and u.warehouse_id = t.warehouse_id and u.event_date = t.event_date and u.hour = t.hour order by event_date asc, hour asc</LI-CODE> <P>&nbsp;</P> <H4><SPAN><BR />Warehouse insights metrics</SPAN></H4> <TABLE class=" lia-align-center" style="height: 943px; border-style: solid;"> <TBODY> <TR> <TD> <P><STRONG>column</STRONG></P> </TD> <TD> <P><STRONG>data type</STRONG></P> </TD> <TD> <P><STRONG>source</STRONG></P> </TD> <TD> <P><STRONG>description</STRONG></P> </TD> </TR> <TR> <TD> <P><SPAN>workspace_id<BR /></SPAN></P> </TD> <TD> <P><SPAN>String<BR /></SPAN></P> </TD> <TD> <P><SPAN>system.query.history<BR /></SPAN></P> </TD> <TD> <P><SPAN>The ID of the workspace this query statement belongs to<BR /></SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>warehouse_id</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>String</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>system.query.history.compute</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>The ID of the warehouse this query statement runs on</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>total_cost_by_hour</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>Decimal</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>Usage by SKU(e.g Compute Type)&nbsp; per Hour in dollars</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>total_dbus_by_hour</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>Integer</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>Usage by SKU(e.g Compute Type)&nbsp; per Hour in dbus</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>total_consumption_by_hour</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>Integer</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>system.query.history</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>diff(request.starttime - request_params.endttime)</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="50px"> <P><SPAN>total_consumption_by_user_in_hour</SPAN></P> </TD> <TD width="129.913px" height="50px"> <P><SPAN>Integer</SPAN></P> </TD> <TD width="158.15px" height="50px"> <P><SPAN>system.query.history</SPAN></P> </TD> <TD width="233.8px" height="50px"> <P><SPAN>User’s total usage per hour</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>percent_of_consumption_by_user_in_hour</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>Decimal</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>derived field</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>User’s total usage percentage per hour in the warehouse</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>cost_by_user_in_hour</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>Decimal</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>derived field</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>User’s total cost per hour in the warehouse</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>tags&nbsp;</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>MAP&lt;STRING, STRING&gt;</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>The user-supplied tags associated with this warehouse</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>executed_by</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>String</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>system.query.history</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>The ID of the principal who ran the query</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>event_date</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>Date</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>system.query.history</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>The day when the query was executed</SPAN></P> </TD> </TR> <TR> <TD width="314.125px" height="77px"> <P><SPAN>event_hour</SPAN></P> </TD> <TD width="129.913px" height="77px"> <P><SPAN>Integer</SPAN></P> </TD> <TD width="158.15px" height="77px"> <P><SPAN>system.query.history</SPAN></P> </TD> <TD width="233.8px" height="77px"> <P><SPAN>The time when the query was executed</SPAN></P> </TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <H3><SPAN>Cost attribution by cluster</SPAN></H3> <P><SPAN>The bar chart below looks at cluster costs over a time period. The query uses the audit, usage, and cluster tables to display trends. When working with the clusters table, it is important to ensure the max record is taken to retrieve the latest configuration, as seen in the code snippet below. A subquery is leveraged to retrieve the max change record for a given cluster and join against the usage table.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Cluster Cost over Period (2).png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9239iD10060A7CBA93FD0/image-size/large?v=v2&amp;px=999" role="button" title="Cluster Cost over Period (2).png" alt="Cluster Cost over Period (2).png" /></span></P> <P>&nbsp;</P> <LI-CODE lang="python"> inner join ( select max(change_time), cluster_name, cluster_id, workspace_id, account_id from system.compute.clusters group by all ) </LI-CODE> <P>&nbsp;</P> <H4><SPAN>Cluster insights by user</SPAN></H4> <P><SPAN>The below table shows the hourly cluster consumption and ties back the usage to a user.&nbsp; How it ties back to the usage is based on grouping the audit log tables and duration of runCommand queries into hours in the day so that the duration of the queries executed within the hour ties back to the Usage table for cost.&nbsp; A simple calculation of the duration of the query compared to the hourly total cost can give a sense of the percentage of cost that can be attributed to the user.&nbsp; It is important to note that considering this is using the Audit Log table, the insights gathered are dependent on </SPAN><A href="https://learn.microsoft.com/en-us/azure/databricks/admin/account-settings/audit-logs#:~:text=notebook-,runCommand,-Available%20when%20verbose" target="_blank" rel="noopener"><SPAN>verbose logs</SPAN></A><SPAN> being enabled to capture notebook commands being run.</SPAN></P> <P><SPAN><EM>*As a disclaimer, the estimates below depict the duration of the query relative to the Usage tables billing for that hour. Other factors may be needed for appropriate chargeback, but this gives a reasonable view of what users are contributing to costs based on query time.</EM></SPAN></P> <P>The below table shows the hourly cluster consumption pattern and ties back the cluster usage cost to a user.</P> <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="cost_per_user_cluster.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9228i77E7F209A36B31A2/image-size/large?v=v2&amp;px=999" role="button" title="cost_per_user_cluster.png" alt="cost_per_user_cluster.png" /></span></SPAN></P> <P>&nbsp;</P> <P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Cluster top users per duration (1).png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9240i603C3B06EB361A6C/image-size/large?v=v2&amp;px=999" role="button" title="Cluster top users per duration (1).png" alt="Cluster top users per duration (1).png" /></span></SPAN></P> <P><SPAN>Below is a snippet of how this is achieved while the full code can be found in the <A href="https://github.com/databricks-solutions/databricks-blogposts" target="_self">repo</A>.</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">with usage_by_user as ( select a.workspace_id, a.user_identity.email, a.request_params.clusterId `cluster_id`, a.event_date, hour(a.event_time) `hour`, sum(a.request_params.executionTime) `total_duration_by_user_in_hour` from system.access.audit a where a.service_name in ('notebook', 'jobs') and a.action_name = 'runCommand' and a.event_date &gt;= date_add(week, - :duration_in_weeks :: integer, now()) and a.request_params.clusterId is not null --and a.workspace_id = :workspace_id :: string group by all ), total_cost_by_cluster as ( select a1.event_date `event_date`, hour(a1.event_time) `hour`, a1.request_params.clusterId `clusterId`, sum(a1.request_params.executionTime) `total_duration_by_hour` from system.access.audit a1 where a1.service_name in ('notebook', 'jobs') and a1.action_name = 'runCommand' and a1.event_date &gt;= date_add(week, - :duration_in_weeks :: integer, now()) and a1.request_params.clusterId is not null --and a1.workspace_id = :workspace_id :: string group by all ) select u.workspace_id, u.email `executed_by`, c.cluster_name, u.cluster_id, u.event_date, u.hour, round(t.dollar_dbus, 2) `cost_by_hour`, t.DBUs `dbus_by_hour`, u.total_duration_by_user_in_hour, tc.total_duration_by_hour, t.custom_tags, round( ( total_duration_by_user_in_hour / total_duration_by_hour ) * 100, 2 ) `percent_consumption_by_user_in_hour`, round( t.dollar_dbus * `percent_consumption_by_user_in_hour` / 100, 2 ) `cost_by_user_in_hour` from usage_by_user u inner join total_cost_by_cluster tc on u.event_date = tc.event_date and u.hour = tc.hour and u.cluster_id = tc.clusterId inner join ( select date(u.usage_start_time) `event_date`, hour(u.usage_start_time) `hour`, u.workspace_id, u.usage_metadata.cluster_id `cluster_id`, to_json(u.custom_tags) `custom_tags`, round(sum(usage_quantity), 2) `DBUs`, sum(usage_quantity * lp.pricing.default) `dollar_dbus` from system.billing.usage u INNER JOIN system.billing.list_prices lp ON u.account_id = lp.account_id and u.cloud = lp.cloud and u.sku_name = lp.sku_name and u.usage_start_time between lp.price_start_time and coalesce(lp.price_end_time, u.usage_end_time) and u.usage_date &gt;= date_add(week, - :duration_in_weeks :: integer, now()) and u.sku_name like '%ALL_PURPOSE%' and ( :tag_name :: string = 'all' or u.custom_tags [:tag_name::string] = :tag_value :: string ) group by all ) T ON u.workspace_id = T.workspace_id and u.cluster_id = t.cluster_id and u.event_date = t.event_date and u.hour = t.hour inner join ( select max(change_time), cluster_name, cluster_id, workspace_id, account_id from system.compute.clusters group by all ) c on c.cluster_id = u.cluster_id and c.workspace_id = u.workspace_id order by event_date asc, hour asc </LI-CODE> <P>&nbsp;</P> <H4><SPAN><BR />Cluster insights metrics</SPAN></H4> <TABLE> <TBODY> <TR> <TD> <P><STRONG>column</STRONG></P> </TD> <TD> <P><STRONG>data type</STRONG></P> </TD> <TD> <P><STRONG>source</STRONG></P> </TD> <TD> <P><STRONG>description</STRONG></P> </TD> </TR> <TR> <TD> <P><SPAN>workspace_id</SPAN></P> </TD> <TD> <P><SPAN>String</SPAN></P> </TD> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>The ID of the workspace this command statement belongs to</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>cluster_id</SPAN></P> </TD> <TD> <P><SPAN>String</SPAN></P> </TD> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>The ID of the cluster this command statement belongs to</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>cluster_name</SPAN></P> </TD> <TD> <P><SPAN>String</SPAN></P> </TD> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>The name of the cluster this command statement belongs to</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>total_cost_by_hour</SPAN></P> </TD> <TD> <P><SPAN>Decimal</SPAN></P> </TD> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>Usage by SKU(e.g Compute Type)&nbsp; per Hour in dollars</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>total_dbus_by_hour</SPAN></P> </TD> <TD> <P><SPAN>Integer</SPAN></P> </TD> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>Usage by SKU(e.g Compute Type)&nbsp; per Hour in dbus</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>total_consumption_by_hour</SPAN></P> </TD> <TD> <P><SPAN>Integer</SPAN></P> </TD> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>diff(request.starttime - request_params.endttime)</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>total_consumption_by_user_in_hour</SPAN></P> </TD> <TD> <P><SPAN>Integer</SPAN></P> </TD> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>User’s total usage per hour</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>percent_of_consumption_by_user_in_hour</SPAN></P> </TD> <TD> <P><SPAN>Decimal</SPAN></P> </TD> <TD> <P><SPAN>derived field</SPAN></P> </TD> <TD> <P><SPAN>User’s total usage percentage per hour in the cluster</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>cost_by_user_in_hour</SPAN></P> </TD> <TD> <P><SPAN>Decimal</SPAN></P> </TD> <TD> <P><SPAN>derived field</SPAN></P> </TD> <TD> <P><SPAN>User’s total cost per hour in the cluster</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>tags</SPAN></P> </TD> <TD> <P><SPAN>MAP&lt;STRING, STRING&gt;</SPAN></P> </TD> <TD> <P><SPAN>system.billing.usage</SPAN></P> </TD> <TD> <P><SPAN>The user-supplied tags associated with this warehouse</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>executed_by</SPAN></P> </TD> <TD> <P><SPAN>String</SPAN></P> </TD> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>The ID of the principal who ran the commands</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>event_date</SPAN></P> </TD> <TD> <P><SPAN>Date</SPAN></P> </TD> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>The day when the command was executed</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>event_hour</SPAN></P> </TD> <TD> <P><SPAN>Integer</SPAN></P> </TD> <TD> <P><SPAN>system.access.audit</SPAN></P> </TD> <TD> <P><SPAN>The time when the command was executed</SPAN></P> </TD> </TR> </TBODY> </TABLE> <H1><SPAN><BR />Conclusion</SPAN></H1> <P><SPAN>In this blog post, we covered key tables needed to gather insights for cost attribution. The samples provided detailed cost analyses for DBSQL Warehouses, clusters, Workspaces, SKUs, and ysers. This allows organizations to see growing trends in their consumption, which will empower them to make informed decisions, optimize resource utilization, and effectively manage their Databricks consumption across different teams, workspaces, and use cases.&nbsp; Sharing this template with Lakeview Dashboards is the beginning of more templates to come as System Tables evolve.&nbsp; This template can be imported easily by downloading it from the public repo and </SPAN><A href="https://learn.microsoft.com/en-us/azure/databricks/dashboards/#:~:text=Export%2C%20import%2C%20or%20replace%20a%20dashboard" target="_blank" rel="noopener"><SPAN>importing</SPAN></A><SPAN> it into a Databricks Workspace with Unity Catalog enabled.&nbsp;&nbsp;</SPAN></P> Wed, 10 Jul 2024 13:45:47 GMT samer_zabaneh 2024-07-10T13:45:47Z https://community.databricks.com/t5/technical-blog/queries-for-cost-attribution-using-system-tables/ba-p/76558 <P>Many customers want visibility into how users contribute to cost. At the moment, users are not tracking cost, but there is a loose association that can be made with the Query History for Warehouses and Cluster records in the Audit Logs for Notebooks and Jobs.&nbsp; For Notebooks and Jobs, we are leveraging runCommand which requires verbose auditing.&nbsp;&nbsp;</P> Wed, 10 Jul 2024 13:45:47 GMT https://community.databricks.com/t5/technical-blog/queries-for-cost-attribution-using-system-tables/ba-p/76558 samer_zabaneh 2024-07-10T13:45:47Z