Hi@anmolhhns,
There is no direct per-query cost field in Databricks system tables today. Here's why, and how to estimate it.
The gap:
- system.billing.usage โ records DBU consumption at the warehouse level per hour (keyed by usage_metadata.warehouse_id + hourly time window). No statement_id field.
- system.query.history โ has detailed per-query metrics (statement_id, durations, bytes read, etc.) but no cost/DBU column.
There's no direct join key between billing and an individual query.
How to estimate per-query cost (proportional allocation):
The best proxy for "compute work done" by a single query is total_task_duration_ms โ defined as "the sum of all task durations across all cores of all nodes". You can allocate the hourly warehouse DBU cost proportionally to each query's share of total task time in that hour:
WITH hourly_cost AS (
SELECT
usage_metadata.warehouse_id AS warehouse_id,
DATE_TRUNC('hour', usage_start_time) AS usage_hour,
SUM(usage_quantity) AS total_dbus,
SUM(usage_quantity * lp.pricing.default) AS total_cost_usd
FROM system.billing.usage u
LEFT JOIN system.billing.list_prices lp
ON u.sku_name = lp.sku_name AND lp.price_end_time IS NULL
WHERE usage_metadata.warehouse_id IS NOT NULL
AND usage_date >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY 1, 2
),
hourly_query_work AS (
SELECT
compute.warehouse_id AS warehouse_id,
DATE_TRUNC('hour', start_time) AS query_hour,
statement_id,
executed_by,
statement_text,
total_task_duration_ms,
total_duration_ms,
SUM(total_task_duration_ms) OVER (
PARTITION BY compute.warehouse_id, DATE_TRUNC('hour', start_time)
) AS total_work_in_hour
FROM system.query.history
WHERE start_time >= CURRENT_DATE - INTERVAL 7 DAY
AND execution_status = 'FINISHED'
AND total_task_duration_ms > 0
)
SELECT
q.statement_id,
q.executed_by,
q.query_hour,
q.total_duration_ms,
q.total_task_duration_ms,
-- Proportional share of the hour's DBU cost
ROUND(
c.total_dbus * (q.total_task_duration_ms / q.total_work_in_hour), 4
) AS estimated_dbus,
ROUND(
c.total_cost_usd * (q.total_task_duration_ms / q.total_work_in_hour), 4
) AS estimated_cost_usd,
LEFT(q.statement_text, 100) AS query_preview
FROM hourly_query_work q
JOIN hourly_cost c
ON q.warehouse_id = c.warehouse_id
AND q.query_hour = c.usage_hour
ORDER BY estimated_cost_usd DESC
LIMIT 50
Important caveats:
- This is an estimate, not exact billing. Warehouse DBUs include idle time, cluster startup, and shared overhead that can't be attributed to any single query.
- Pro/Classic warehouses bill based on cluster uptime (not per-query), so a lightweight query running alone on an idle warehouse "absorbs" the full hourly cost. Serverless SQL is closer to per-query billing but still doesn't expose it at statement granularity.
- total_task_duration_ms is the best available proxy because it reflects actual CPU-seconds consumed across all cores, unlike wall-clock duration which includes queueing.
- Retention: system.query.history retains 30 days; system.billing.usage retains 365 days.
Feature request: If exact per-query cost attribution is critical for your use case (chargeback, showback), I'd recommend filing a request on the Databricks Ideas Portal โ it's a frequently requested feature.
If my answer was helpful, please consider marking it as accepted solution!
