topic Re: How to extract table-level execution time and resource allocation within a multi-table Job? in Data Engineering

How to extract table-level execution time and resource allocation within a multi-table Job?

Dolly0503 — Mon, 07 Sep 2026 05:36:11 GMT

Hi everyone,

I am working on calculating accurate compute costs and execution times for individual tables within our Databricks environment, but I am running into an issue with metric granularity.

Currently, we are fetching execution data based on job_id. The problem is that a single job_id often executes multiple tables of varying sizes. Right now, our logic can only derive an average execution time across the entire job, which is highly inaccurate for attributing costs to a specific table.

To get precise, table-level cost metrics, we need to move away from job-level averages. Specifically, I am trying to find a way to extract:

Table-Specific Timestamps: The exact execution start_time and end_time for an individual table running within a larger job.
Granular Resource Allocation: The exact number of compute resources (DBUs, allocated vs. free resources) consumed specifically during the time that individual table is actively running.

Are there specific System Tables (e.g., within system.information_schema or system.access), REST API endpoints, or Spark listener configurations that expose this level of granular, table-specific execution data? and also below am attaching my query.

WITH cost_agg AS (
SELECT job_id, day, SUM(cost_consumed) AS cost_val
FROM costing --Internal table
GROUP BY job_id, day
--goes to costing dashboard table for each job, for each day - adds up all the cost. Result is one row per job per day with total cost
),
base_data AS (
SELECT job_id, task_key,
to_date(period_start_time) as execution_date,
execution_duration_seconds
FROM system.lakeflow.job_task_run_timeline

),
task_avgs AS (
SELECT job_id, task_key, execution_date, avg(execution_duration_seconds) as avg_task_exec_for_day
FROM base_data
GROUP BY job_id, execution_date, task_key
--For each task within each job on each day, averages the execution time across all runs of that task that day.
-- e.g. prd-customers ran 3 times: 100s + 120s + 80s → avg = 100s
),
job_totals as (
select job_id, execution_date, greatest(sum(avg_task_exec_for_day),1) as total_task_seconds_for_day
from task_avgs
group by job_id, execution_date
--Sums all task averages per job per day to get the denominator for weightage.
),
table_costs as ( SELECT
ta.job_id, ta.execution_date, ta.task_key,
SPLIT_PART(ta.task_key, '-', 1) AS ctlg,
SPLIT_PART(ta.task_key, '-', 2) AS db_name,
SPLIT_PART(ta.task_key, '-', 3) AS tbl_name,
round(ta.avg_task_exec_for_day/jt.total_task_seconds_for_day, 5),
round(c.cost_val, 5) as job_cost_usd,
round((ta.avg_task_exec_for_day/jt.total_task_seconds_for_day) * c.cost_val, 5) as table_cost_usd
from task_avgs ta
join job_totals jt on ta.job_id = jt.job_id AND ta.execution_date = jt.execution_date
left join cost_agg c on c.job_id = ta.job_id AND c.day = ta.execution_date
)

select ctlg, db_name, tbl_name,
array_join(collect_set(cast(job_id as string)),',') as jobs_id,
count(distinct job_id) as jobs_count,
count(*) as times_run,
count(distinct execution_date) as days_run,
min(execution_date) as first_run_date,
max(execution_date) as last_run_date,
round(sum(table_cost_usd),5) as total_cost_usd,
ROUND(SUM(table_cost_usd) / NULLIF(COUNT(DISTINCT execution_date), 0), 5) AS avg_daily_cost_usd, -- ← replaced here
round(min(table_cost_usd),5) as min_daily_cost_usd,
round(max(table_cost_usd),5) as max_daily_cost_usd
from table_costs
where execution_date <= '2026-03-04' and ctlg = 'prd'
group by tbl_name, ctlg, db_name
order by total_cost_usd desc

Any guidance, query examples, or best practices would be greatly appreciated.

Thank you!

Re: How to extract table-level execution time and resource allocation within a multi-table Job?

Satyasai — Mon, 07 Sep 2026 05:57:03 GMT

https://community.databricks.com/t5/data-engineering/how-to-calculate-cost-of-each-table-for-the-specific-databricks/td-p/167086,

This above may help you.

OR use below Query for Reference

WITH table_queries AS (
SELECT
statement_id,
job_id,
task_id,
compute_id AS cluster_id,
-- Extract target table name or catalog.schema.table from query history
coalesce(executed_as_table, regexp_extract(query_text, '(?i)(?:INTO|UPDATE|TABLE|MERGE INTO)\\s+([a-zA-Z0-9_\\.-]+)', 1)) AS target_table,
from_unixtime(start_time_ms / 1000) AS table_execution_start,
from_unixtime(end_time_ms / 1000) AS table_execution_end,
(end_time_ms - start_time_ms) / 1000.0 AS execution_duration_seconds
FROM system.query.history
WHERE start_time_ms IS NOT NULL
AND end_time_ms IS NOT NULL
AND statement_type IN ('INSERT', 'MERGE', 'CREATE_TABLE_AS_SELECT', 'COPY')
),

cluster_hourly_costs AS (
SELECT
usage.cluster_id,
usage.usage_start_time,
usage.usage_end_time,
SUM(usage.usage_quantity * prices.price_start_price) AS cluster_cost_usd,
SUM(usage.usage_quantity) AS total_dbus
FROM system.billing.usage usage
JOIN system.billing.list_prices prices
ON usage.sku_name = prices.sku_name
AND usage.usage_start_time >= prices.price_start_time
AND (prices.price_end_time IS NULL OR usage.usage_start_time < prices.price_end_time)
GROUP BY usage.cluster_id, usage.usage_start_time, usage.usage_end_time
)

SELECT
q.target_table,
q.job_id,
q.task_id,
MIN(q.table_execution_start) AS exact_start_time,
MAX(q.table_execution_end) AS exact_end_time,
SUM(q.execution_duration_seconds) AS total_active_seconds,
-- Calculate proportional DBU and USD cost attributed strictly to this table's query duration
ROUND(SUM(q.execution_duration_seconds / 3600.0 * c.total_dbus), 4) AS allocated_dbus,
ROUND(SUM((q.execution_duration_seconds / 3600.0) * c.cluster_cost_usd), 4) AS allocated_table_cost_usd
FROM table_queries q
JOIN cluster_hourly_costs c
ON q.cluster_id = c.cluster_id
AND q.table_execution_start >= c.usage_start_time
AND q.table_execution_end <= c.usage_end_time
WHERE q.target_table IS NOT NULL AND q.target_table != ''
GROUP BY q.target_table, q.job_id, q.task_id
ORDER BY allocated_table_cost_usd DESC;

Re: How to extract table-level execution time and resource allocation within a multi-table Job?

Dolly0503 — Mon, 07 Sep 2026 06:03:25 GMT

Unable to access the link @Satyasai

Re: How to extract table-level execution time and resource allocation within a multi-table Job?

Satyasai — Mon, 07 Sep 2026 06:22:14 GMT

Try this link
https://community.databricks.com/t5/data-engineering/how-to-calculate-cost-of-each-table-for-the-specific-databricks/td-p/167086