cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

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

VIRALKUMAR
Contributor II

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

2 REPLIES 2

katefray
New Contributor II

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!

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

 

 

VIRALKUMAR_0-1724776377452.png

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group