Cost Management in Databricks SQL

Key Takeaways

Managing costs in data bricks SQL is essential for optimizing performance and preventing unexpected expenses. In this demo, I'll show you how to track and control costs starting from the broadest level, your data bricks account, and drilling down into workspaces, SQL warehouses, and finally specific queries, users, and data assets. Let's get started. To track costs, you first need to enable system tables and ensure that you have the appropriate permissions. System tables store all of your accounts operational data, providing historical observability across all workspaces in your databicks account. You should have access to the compute query and billing table schemas. If you do not have access, please contact your account administrator to request the necessary permissions. You can query system tables to monitor resources, audit user activity, and manage costs across all products in your databicks account. However, in this demo, I want to focus specifically on data brick SQL. With system tables, you can monitor databicks SQL warehouses and answer key questions such as which warehouses are actively running and for how long? Are any warehouses upskilled longer than expected leading to unnecessary costs? And what is the daily spend across all warehouses in our datab bricks account? The ability to monitor, manage, and audit DBSQL objects is extensive, giving you full visibility into your usage and costs. At Datab Bricks, we prioritize transparency by providing clear and accessible metrics. At the same time, we're committed to making cost monitoring simple and intuitive for our customers. At the account level, we make it super easy to understand and track data brick SQL costs. The usage dashboard is a pre-built AIBI dashboard that provides a high-level overview of your organization SQL compute spending across all workspaces. Please know that you must be an account administrator to import the usage dashboard and the dashboard must be imported into a Unity catalog enabled workspace. By default, the usage dashboard provides usage breakdowns that can be grouped by product. Here we can see SQL specific usage. You can also group by skew name such as classic pro or serverless SQL warehouse SKs. Group by custom tags which are assigned to specific workspaces, projects or users. And group by workspace provides a clear breakdown of SQL compute spending across different environments. The information you are seeing in the usage dashboard can be found in the usage system table under the billing origin product skew name and warehouse ID columns. Monitoring SQL warehouse costs can be efficiently managed using serverless budget policies. These policies apply tags to serverless compute activity which are then logged in billing records allowing you to attribute usage to specific budgets. Tags enable query level cost attribution providing deeper insights into what is driving spend. Permissions can be assigned to users or managers where users can select a policy and apply it to notebooks, jobs, and specifically SQL warehouses. While managers can edit policies and manage permissions when configuring a SQL warehouse, you can now add the budget policy to monitor your costs in a scalable way. Similarly, you can monitor SQL warehouse costs by applying custom tags directly to each warehouse. Custom tags are specifically useful for users who want to differentiate use cases within a project, making it easier to track and understand data bricks usage. based on specific needs. Costs tracked by custom tags can be viewed through the usage dashboard as I showed earlier while costs tracked by budget policies can be accessed via the usage dashboard or the native budget policies UI. You can also find information about warehouse budget policies from the usage system table under the budget policy ID column. So far, we've covered how to monitor data bricks SQL costs at the account, workspace, and SQL warehouse levels. But what if you need to identify which users or teams are contributing the most to your bill and which tools are driving these costs? You can do this easily using the databick SQL granular cost monitoring dashboard and its materialized view. In the databicks labs GitHub, you can find a pre-created materialized view that will take into consideration all the system tables and schemas associated with the warehouse events, usage, and query history. The materialized view is set to refresh every day, but you can change it according to your needs. You can also find a pre-created AIBI dashboard template that you can import and publish in datab bricks. Once you do that, the time range selection specifies the period for viewing data brick SQL costs. By default, all workspaces, warehouses, users, sources, and objects are included, but you can refine your view using drop- down filters. The top end filters highlight key insights such as the top 20 users or top 10 objects. Currently, the dashboard uses the effective list price for dollars per DBU. But if your datab bricks contract includes a different rate, you can manually apply the implied discount. In an upcoming update, we will automatically reflect your contractual pricing instead of the list price. The dashboard includes three key charts to help analyze costs. First, we have cost by user, which identifies which users contribute the most to warehouse costs. We have cost by tool, which breaks down costs by application, whether from thirdparty BI tools like PowerBI or Tableau or even data bricks native tools like AIBI dashboards and alerts. And we have cost by object which highlights which data bricks objects like individual queries or recoccurring workloads generate the highest costs. You can refine your analysis by clicking on charts to apply cross filters or by using the filters at the top of the dashboard. The time series chart helps track cost trends across different periods from hourly to monthly. The statement level detail section at the bottom of the dashboard provides indepth insights into individual query runs. It includes key details such as query text, attributed usage, warehouse name, and query duration. You can also navigate to specific statements or objects to get more details. For example, you can view a query profile to analyze performance or check a dashboard's run schedule to make adjustments as needed. We've explored how to monitor and manage databick SQL costs starting from the account level, drilling down into workspaces, optimizing SQL warehouse configurations, and finally tracking costs at the query, user, and data asset level. By applying this structured approach, you can gain full visibility into your spending, prevent cost overruns, and optimize resource usage, ensuring that your SQL workloads run efficiently and cost-effectively.