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!
