topic Re: How to Determine the Cost for Each Query Run Against SQL Warehouse Serverless? in Data Engineering

How to Determine the Cost for Each Query Run Against SQL Warehouse Serverless?

VIRALKUMAR — Tue, 27 Aug 2024 16:01:49 GMT

Hello Everyone.

First of all, I would like to thank you to databricks to enable system tables for customers. It does help a lot. I am working on cost optimization topic. Particularly sql warehouse serverless. I am not sure all of you have tried system tables. my question is

is it possible to find out cost per query (Sql Warehouse Serverless) using system tables?

I have started writing query combining system tables. unfortunately it is not giving expected results.

Thanks

Re: how to find out cost for each query run against (sql warehouse serverless) ?

katefray — Tue, 27 Aug 2024 16:01:08 GMT

Hey VIRALKUMAR,

I recommend using the billing usage system table to find total DBUs by SKU (SQL) and the pricing system table to find the appropriate price. You can use the sample queries in those pages to get started.

Hope that's helpful!

Re: how to find out cost for each query run against (sql warehouse serverless) ?

VIRALKUMAR — Tue, 27 Aug 2024 16:51:13 GMT

Hi Katefray

Thank you for the reply and documentation links. I have already tried with billing usage system table (system.billing.usage) and pricing system table (system.billing.list_prices) but unfortunately billing usage table doesn't have direct reference to system.query.history. So we can't join them and get the DBU usage and calculate the query cost.

Here is sample query. (It is not giving expected result)

SELECT q.workspace_id, q.compute.warehouse_id, q.compute.type, q.read_files, q.read_rows, q.statement_text, q.statement_type, q.total_task_duration_ms, b.usage_date, b.usage_unit, b.usage_quantity, b.sku_name, (b.usage_quantity * l.pricing.default) AS total_cost, q.executed_by FROM system.query.history q JOIN system.billing.usage b ON q.compute.warehouse_id = b.usage_metadata.warehouse_id AND q.workspace_id = b.workspace_id JOIN system.billing.list_prices l ON l.sku_name = b.sku_name WHERE q.workspace_id = "xxxxxxxxxxxx " AND b.usage_date >= (current_date() - INTERVAL '1' DAY) AND l.price_end_time IS NULL

Here is an example

1. Statement Type is SHOW. It took 0 milli seconds. the usage is showing 1.09 DBU, cost per DBU is 0.95 and total cost is 1.03 It doesn't make sense show sql statement is costing $1.03.

Thanks

Re: How to Determine the Cost for Each Query Run Against SQL Warehouse Serverless?

sanjeevktm — Wed, 18 Jun 2025 07:54:11 GMT

Yes, it does not make sense to see that high cost per query, please note that the below table contains hourly spend, so you cant get query wise usage.

system.billing.usage

Re: How to Determine the Cost for Each Query Run Against SQL Warehouse Serverless?

VIRALKUMAR — Wed, 18 Jun 2025 08:10:58 GMT

Yes, You are absolutely right. If you don't want to consider ideal time of databricks Sql warehouse. You can apply filter to remove ideal time of Databricks Sql warehouse and consider only query execution time and multiply with cost_per_minutes, Ideal time of Sql warehouse you can consider into another Category.

Re: How to Determine the Cost for Each Query Run Against SQL Warehouse Serverless?

skumarraj — Wed, 01 Oct 2025 17:21:20 GMT

Can you Share the query that you used ?