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

[VIRALKUMAR]

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

[katefray]

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!

[VIRALKUMAR]

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

[sanjeevktm]

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

[VIRALKUMAR]

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.