Databricks SQL Query Toolkit

-- Count of active clusters by cluster_source

SELECT
  cluster_source,
  COUNT(*) AS active_clusters
FROM
  system.compute.clusters
WHERE
  delete_time IS NULL
GROUP BY
  cluster_source
ORDER BY
  cluster_source;

-- Count of active clusters by availability

SELECT
  "AWS" AS cloud,
  aws_attributes.availability AS availability,
  COUNT(*) AS active_clusters
FROM
  system.compute.clusters
WHERE
  delete_time IS NULL
  AND aws_attributes.availability IS NOT NULL
GROUP BY
  aws_attributes.availability
UNION ALL
SELECT
  "AZURE" AS cloud,
  azure_attributes.availability AS availability,
  COUNT(*) AS active_clusters
FROM
  system.compute.clusters
WHERE
  delete_time IS NULL
  AND azure_attributes.availability IS NOT NULL
GROUP BY
  azure_attributes.availability
UNION ALL
SELECT
  "GCP" AS cloud,
  gcp_attributes.availability AS availability,
  COUNT(*) AS active_clusters
FROM
  system.compute.clusters
WHERE
  delete_time IS NULL
  AND gcp_attributes.availability IS NOT NULL
GROUP BY
  gcp_attributes.availability;

-- Count of queries executed by user

SELECT
  executed_by AS user,
  COUNT(*) AS query_count
FROM
  system.query.history
GROUP BY
  executed_by
ORDER BY
  query_count DESC
LIMIT 10;

-- Most expensive cluster owners

SELECT
  c.owned_by,
  SUM(u.usage_quantity) AS total_cost
FROM
  system.billing.usage u
  JOIN system.compute.clusters c
ON
  u.usage_metadata.cluster_id = c.cluster_id
WHERE
  u.usage_start_time >= current_date() - interval 30 days
GROUP BY
  c.owned_by
ORDER BY
  total_cost DESC
LIMIT 20

-- Divide daily active users by monthly active users

WITH all_users AS (
  SELECT
    DATE(DATE_TRUNC('month', start_time)) AS usage_month,
    DATE(start_time) AS usage_date,
    executed_by
  FROM
    system.query.history
  WHERE
    start_time >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL 6 MONTH
),
distinct_users AS (
  SELECT
    DISTINCT executed_by
  FROM
    all_users
),
distinct_daily_users AS (
  SELECT
    DISTINCT usage_month,
    usage_date,
    executed_by
  FROM
    all_users
),
distinct_monthly_users AS (
  SELECT
    DISTINCT usage_month,
    executed_by
  FROM
    all_users
),
daily_active_users AS (
  SELECT
    DISTINCT usage_month,
    usage_date,
    COUNT(executed_by) OVER (PARTITION BY DATE(usage_month), DATE(usage_date)) AS dau
  FROM
    distinct_daily_users
),
monthly_active_users AS (
  SELECT
    DISTINCT usage_month,
    COUNT(executed_by) OVER (PARTITION BY DATE(usage_month)) AS mau
  FROM
    distinct_monthly_users
),
stickiness AS (
  SELECT
    dau.usage_month,
    ROUND(
      AVG(dau.dau) OVER (PARTITION BY DATE(dau.usage_month)),
      2
    ) AS avg_dau,
    mau.mau,
    ROUND(
      AVG(dau.dau) OVER (PARTITION BY DATE(dau.usage_month)) / mau.mau,
      2
    ) AS stickiness
  FROM
    daily_active_users dau
    JOIN monthly_active_users mau ON dau.usage_month = mau.usage_month
),
total_users AS (
  SELECT
    DISTINCT COUNT(executed_by) OVER () AS total_users
  FROM
    distinct_users
),
final AS (
  SELECT
    DISTINCT usage_month,
    (
      SELECT
        total_users
      FROM
        total_users
      LIMIT
        1
    ) AS total_users,
    mau,
    avg_dau,
    stickiness
  FROM
    stickiness
  ORDER BY
    usage_month
)
SELECT
  *
FROM
  final;

-- Total compute costs by SKU

SELECT
  t1.sku_name,
  SUM(t1.usage_quantity * lp.pricing.default) AS total_cost
FROM
  system.billing.usage t1
  INNER JOIN system.billing.list_prices lp ON t1.cloud = lp.cloud
  AND t1.sku_name = lp.sku_name
  AND t1.usage_start_time >= lp.price_start_time
  AND (
    t1.usage_end_time <= lp.price_end_time
    OR lp.price_end_time IS NULL
  )
GROUP BY
  t1.sku_name
ORDER BY
  total_cost DESC;

-- Total costs by product

WITH usage AS (
  SELECT
    bu.usage_metadata.job_id,
    bu.usage_metadata.cluster_id,
    bu.usage_metadata.warehouse_id,
    bu.billing_origin_product,
    bu.usage_quantity,
    bu.usage_start_time,
    bu.usage_end_time,
    bu.cloud,
    bu.sku_name
  FROM
    system.billing.usage bu
),
list_prices AS (
  SELECT
    lp.pricing.default AS price,
    lp.cloud,
    lp.price_start_time,
    lp.price_end_time,
    lp.sku_name
  FROM
    system.billing.list_prices lp
),
joined AS (
  SELECT
    u.billing_origin_product,
    SUM(u.usage_quantity * lp.price) OVER (PARTITION BY u.billing_origin_product) AS total_cost
  FROM
    usage u
    INNER JOIN list_prices lp ON u.cloud = lp.cloud
    AND u.sku_name = lp.sku_name
    AND u.usage_start_time >= lp.price_start_time
    AND (
      u.usage_end_time <= lp.price_end_time
      OR lp.price_end_time IS NULL
    )
  WHERE
    usage_start_time >= CURRENT_DATE() - INTERVAL 30 DAY
)
SELECT 
  DISTINCT billing_origin_product,
  total_cost
FROM
  joined
ORDER BY
  total_cost DESC;

-- Jos with the lowest CPU utilization 

with list_cost_per_job_cluster as (
 SELECT
   t1.workspace_id,
   t1.usage_metadata.job_id,
   t1.usage_metadata.cluster_id,
   COUNT(DISTINCT t1.usage_metadata.job_run_id) as runs,
   SUM(t1.usage_quantity * list_prices.pricing.default) as list_cost,
   first(identity_metadata.run_as, true) as run_as,
   first(t1.custom_tags, true) as custom_tags,
   MAX(t1.usage_end_time) as last_seen_date
 FROM system.billing.usage t1
 INNER JOIN system.billing.list_prices list_prices on
   t1.cloud = list_prices.cloud and
   t1.sku_name = list_prices.sku_name and
   t1.usage_start_time >= list_prices.price_start_time and
   (t1.usage_end_time <= list_prices.price_end_time or list_prices.price_end_time is null)
 WHERE
   t1.sku_name LIKE '%JOBS%'
 and t1.sku_name not ilike '%jobs_serverless%'
   AND t1.usage_metadata.job_id IS NOT NULL
   AND t1.usage_date >= CURRENT_DATE() - INTERVAL 30 DAY
 GROUP BY ALL
),
most_recent_jobs as (
 SELECT
   *,
   ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id ORDER BY change_time DESC) as rn
 FROM
   system.lakeflow.jobs QUALIFY rn=1
),


jobsrollingmonth as (
SELECT
   t2.name,
   t1.job_id,
   t1.workspace_id,
   t1.cluster_id,
   t1.runs,
   t1.run_as,
   SUM(list_cost) as list_cost,
   t1.last_seen_date
FROM list_cost_per_job_cluster t1
 LEFT JOIN most_recent_jobs t2 USING (workspace_id, job_id)
GROUP BY ALL
ORDER BY list_cost DESC ),


clusterutil as (SELECT * from
 system.compute.node_timeline
WHERE
 start_time >= CURRENT_DATE() - INTERVAL 30 DAY
),


jobswithutil as (
select jobsrollingmonth.workspace_id, jobsrollingmonth.name,
avg(cpu_user_percent + cpu_system_percent) as `Avg CPU Utilization`,
max(cpu_user_percent + cpu_system_percent) as `Peak CPU Utilization`,
 avg(cpu_wait_percent) as `Avg CPU Disk Wait`,
 max(cpu_wait_percent) as `Max CPU Disk Wait`,
 avg(mem_used_percent) as `Avg Memory Utilization`,
 max(mem_used_percent) as `Max Memory Utilization`
from clusterutil
join jobsrollingmonth
on clusterutil.cluster_id = jobsrollingmonth.cluster_id
group by 1, 2)


select *
from jobswithutil
order by `Avg CPU Utilization` asc
limit 10;

-- Jobs with highest memory utilization

with list_cost_per_job_cluster as (
  SELECT
    t1.workspace_id,
    t1.usage_metadata.job_id,
    t1.usage_metadata.cluster_id,
    COUNT(DISTINCT t1.usage_metadata.job_run_id) as runs,
    SUM(t1.usage_quantity * list_prices.pricing.default) as list_cost,
    first(identity_metadata.run_as, true) as run_as,
    first(t1.custom_tags, true) as custom_tags,
    MAX(t1.usage_end_time) as last_seen_date
  FROM system.billing.usage t1 
  INNER JOIN system.billing.list_prices list_prices on
    t1.cloud = list_prices.cloud and
    t1.sku_name = list_prices.sku_name and
    t1.usage_start_time >= list_prices.price_start_time and
    (t1.usage_end_time <= list_prices.price_end_time or list_prices.price_end_time is null)
  WHERE
    t1.sku_name LIKE '%JOBS%'
	and t1.sku_name not ilike '%jobs_serverless%'
    AND t1.usage_metadata.job_id IS NOT NULL
    AND t1.usage_date >= CURRENT_DATE() - INTERVAL 30 DAY 
  GROUP BY ALL
),
most_recent_jobs as (
  SELECT 
    *, 
    ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id ORDER BY change_time DESC) as rn
  FROM
    system.lakeflow.jobs QUALIFY rn=1
),

jobsrollingmonth as (
SELECT 
    t2.name,
    t1.job_id,
    t1.workspace_id,
    t1.cluster_id,
    t1.runs,
    t1.run_as,
    SUM(list_cost) as list_cost,
    t1.last_seen_date
FROM list_cost_per_job_cluster t1 
  LEFT JOIN most_recent_jobs t2 USING (workspace_id, job_id)
GROUP BY ALL
ORDER BY list_cost DESC ),

clusterutil as (SELECT * from 
	system.compute.node_timeline
WHERE
	start_time >= CURRENT_DATE() - INTERVAL 30 DAY 
),

jobswithutil as (
select jobsrollingmonth.workspace_id, jobsrollingmonth.name,
avg(cpu_user_percent + cpu_system_percent) as `Avg CPU Utilization`, 
max(cpu_user_percent + cpu_system_percent) as `Peak CPU Utilization`, 
	avg(cpu_wait_percent) as `Avg CPU Disk Wait`,
	max(cpu_wait_percent) as `Max CPU Disk Wait`,
	avg(mem_used_percent) as `Avg Memory Utilization`, 
	max(mem_used_percent) as `Max Memory Utilization`
from clusterutil 
join jobsrollingmonth
on clusterutil.cluster_id = jobsrollingmonth.cluster_id
group by 1, 2)

select *
from jobswithutil
order by `Avg Memory Utilization` desc
limit 20;

-- Most expensive jobs in past 30 days

with list_cost_per_job as (
 SELECT
   t1.workspace_id,
   t1.usage_metadata.job_id,
   COUNT(DISTINCT t1.usage_metadata.job_run_id) as runs,
   SUM(t1.usage_quantity * list_prices.pricing.default) as list_cost,
   first(identity_metadata.run_as, true) as run_as,
   first(t1.custom_tags, true) as custom_tags,
   MAX(t1.usage_end_time) as last_seen_date
 FROM system.billing.usage t1
 INNER JOIN system.billing.list_prices list_prices on
   t1.cloud = list_prices.cloud and
   t1.sku_name = list_prices.sku_name and
   t1.usage_start_time >= list_prices.price_start_time and
   (t1.usage_end_time <= list_prices.price_end_time or list_prices.price_end_time is null)
 WHERE
   t1.sku_name LIKE '%JOBS%'
   AND t1.usage_metadata.job_id IS NOT NULL
   AND t1.usage_date >= CURRENT_DATE() - INTERVAL 30 DAY
 GROUP BY ALL
),
most_recent_jobs as (
 SELECT
   *,
   ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id ORDER BY change_time DESC) as rn
 FROM
   system.lakeflow.jobs QUALIFY rn=1
)
SELECT
   t2.name,
   t1.job_id,
   t1.workspace_id,
   t1.runs,
   t1.run_as,
   SUM(list_cost) as list_cost,
   t1.last_seen_date
FROM list_cost_per_job t1
 LEFT JOIN most_recent_jobs t2 USING (workspace_id, job_id)
GROUP BY ALL
ORDER BY list_cost DESC
LIMIT 20

-- Top jobs growing in WoW costs

with job_run_timeline_with_cost as (
  SELECT
    t1.*,
    t1.usage_metadata.job_id as job_id,
    t1.identity_metadata.run_as as run_as,
    t1.usage_quantity * list_prices.pricing.default AS list_cost
  FROM system.billing.usage t1
    INNER JOIN system.billing.list_prices list_prices
      ON
        t1.cloud = list_prices.cloud AND
        t1.sku_name = list_prices.sku_name AND
        t1.usage_start_time >= list_prices.price_start_time AND
        (t1.usage_end_time <= list_prices.price_end_time or list_prices.price_end_time is NULL)
  WHERE
    t1.billing_origin_product = 'JOBS' AND
    t1.usage_date >= CURRENT_DATE() - INTERVAL 14 DAY
),
most_recent_jobs as (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id ORDER BY change_time DESC) as rn
  FROM
    system.lakeflow.jobs QUALIFY rn=1
)
SELECT
    t2.name
    ,t1.workspace_id
    ,t1.job_id
    ,t1.sku_name
    ,t1.run_as
    ,Last7DaySpend
    ,Last14DaySpend
    ,last7DaySpend - last14DaySpend as Last7DayGrowth
    ,try_divide( (last7DaySpend - last14DaySpend) , last14DaySpend) * 100 AS Last7DayGrowthPct
FROM
  (
    SELECT
      workspace_id,
      job_id,
      run_as,
      sku_name,
      SUM(list_cost) AS spend
      ,SUM(CASE WHEN usage_end_time BETWEEN date_add(current_date(), -8) AND date_add(current_date(), -1) THEN list_cost ELSE 0 END) AS Last7DaySpend
      ,SUM(CASE WHEN usage_end_time BETWEEN date_add(current_date(), -15) AND date_add(current_date(), -8) THEN list_cost ELSE 0 END) AS Last14DaySpend
    FROM job_run_timeline_with_cost
    GROUP BY ALL
  ) t1
  LEFT JOIN most_recent_jobs t2 USING (workspace_id, job_id)
ORDER BY
  Last7DayGrowth DESC
LIMIT 20

- Most expensive warehouses over past 30 days

SELECT
  w.warehouse_id,
  w.workspace_id,
  w.warehouse_name,
  w.warehouse_type,
  w.warehouse_size,
  w.min_clusters,
  w.max_clusters,
  SUM(bu.usage_quantity) AS total_usage_quantity,
  SUM(bu.usage_quantity * lp.pricing.default) AS total_cost
FROM
  system.compute.warehouses w
JOIN
  system.billing.usage bu 
ON  
  w.warehouse_id = bu.usage_metadata.warehouse_id
JOIN
  system.billing.list_prices lp
ON
  bu.cloud = lp.cloud
AND
  bu.sku_name = lp.sku_name
AND
  bu.usage_start_time >= lp.price_start_time
AND
  (bu.usage_end_time <= lp.price_end_time OR lp.price_end_time IS NULL)
WHERE
  bu.usage_date >= CURRENT_DATE() - INTERVAL 30 DAY
GROUP BY
  w.warehouse_id,
  w.workspace_id,
  w.warehouse_name,
  w.warehouse_type,
  w.warehouse_size,
  w.min_clusters,
  w.max_clusters

-- Most expensive queries in past 30 days

WITH usage_aggregates AS (
    SELECT
        bu.usage_metadata.warehouse_id,
        bu.usage_start_time AS wh_start_time,
        bu.usage_end_time AS wh_end_time,
        DATEDIFF(MILLISECOND, bu.usage_start_time, bu.usage_end_time) AS wh_duration_ms,
        bu.usage_quantity AS wh_usage_quantity,
        bu.sku_name AS sku_name
    FROM
        system.billing.usage bu
    WHERE
        bu.usage_start_time >= current_date() - INTERVAL 30 DAY
        AND bu.usage_metadata.warehouse_id IS NOT NULL
    GROUP BY
        bu.usage_metadata.warehouse_id,
        wh_start_time,
        wh_end_time,
        wh_usage_quantity,
        sku_name
),
query_count AS (
    SELECT
        statement_text,
        compute.warehouse_id AS warehouse_id,
        COUNT(*) AS count
    FROM
        system.query.history qh
    WHERE
        qh.start_time > CURRENT_TIMESTAMP() - INTERVAL 30 DAY
    GROUP BY  
        statement_text,
        warehouse_id
)
SELECT
   qh.statement_id,
   qh.statement_text,
    qc.count AS monthly_count,
    any_value(qh.compute) AS compute,
    qh.executed_by_user_id,
    qh.total_duration_ms AS query_duration_ms,
    qh.start_time AS query_start_time,
    qh.end_time AS query_end_time,
    qh.compute.warehouse_id,
    cw.warehouse_size,
    cw.warehouse_name,
    ua.sku_name,
    lp.pricing.default AS pricing_default,
    any_value(ua.wh_start_time) AS wh_start_time,
    any_value(ua.wh_end_time) AS wh_end_time,
    ua.wh_duration_ms,
    ua.wh_usage_quantity,
    try_divide(ua.wh_usage_quantity, ua.wh_duration_ms) AS usage_dbu_per_ms,
    SUM(qh.total_duration_ms * try_divide(ua.wh_usage_quantity, ua.wh_duration_ms) * lp.pricing.default) AS estimated_query_cost,
    SUM(qc.count * qh.total_duration_ms * try_divide(ua.wh_usage_quantity, ua.wh_duration_ms) * lp.pricing.default) AS estimated_30day_query_cost
FROM 
    system.query.history qh
JOIN    
    system.compute.warehouses cw
ON
    qh.compute.warehouse_id = cw.warehouse_id
JOIN
    usage_aggregates ua
ON
    qh.compute.warehouse_id = ua.warehouse_id
AND
    ua.wh_start_time <= qh.start_time
AND
    ua.wh_end_time >= qh.end_time
JOIN
    system.billing.list_prices lp
ON
    ua.sku_name = lp.sku_name
JOIN
    query_count qc
ON
    qh.statement_text = qc.statement_text
AND
    qh.compute.warehouse_id = qc.warehouse_id
WHERE
    qh.start_time >= current_date() - INTERVAL 30 DAY
GROUP BY
    qh.statement_id,
    qh.statement_text,
    qc.count,
    qh.compute.warehouse_id,
    qh.executed_by_user_id,
    qh.total_duration_ms,
    qh.start_time,
    qh.end_time,
    cw.warehouse_size,
    cw.warehouse_name,
    ua.sku_name,
    lp.pricing.default,
    ua.wh_duration_ms,
    ua.wh_usage_quantity
ORDER BY
    estimated_30day_query_cost DESC
LIMIT 20

-- Granular details about most expensive queries

WITH usage_aggregates AS (
    SELECT
        bu.usage_metadata.warehouse_id,
        bu.usage_start_time AS wh_start_time,
        bu.usage_end_time AS wh_end_time,
        DATEDIFF(MILLISECOND, bu.usage_start_time, bu.usage_end_time) AS wh_duration_ms,
        bu.usage_quantity AS wh_usage_quantity,
        bu.sku_name AS sku_name
    FROM
        system.billing.usage bu
    WHERE
        bu.usage_start_time >= current_date() - INTERVAL 30 DAY
        AND bu.usage_metadata.warehouse_id IS NOT NULL
    GROUP BY
        bu.usage_metadata.warehouse_id,
        wh_start_time,
        wh_end_time,
        wh_usage_quantity,
        sku_name
),
query_count AS (
    SELECT
        statement_text,
        compute.warehouse_id AS warehouse_id,
        COUNT(*) AS count
    FROM
        system.query.history qh
    WHERE
        qh.start_time > CURRENT_TIMESTAMP() - INTERVAL 30 DAY
    GROUP BY  
        statement_text,
        warehouse_id
)
SELECT
   qh.statement_id,
   qh.statement_text,
    qc.count AS monthly_count,
    any_value(qh.compute) AS compute,
    qh.executed_by_user_id,
    qh.total_duration_ms AS query_duration_ms,
    qh.start_time AS query_start_time,
    qh.end_time AS query_end_time,
    qh.compute.warehouse_id,
    cw.warehouse_size,
    cw.warehouse_name,
    ua.sku_name,
    lp.pricing.default AS pricing_default,
    any_value(ua.wh_start_time) AS wh_start_time,
    any_value(ua.wh_end_time) AS wh_end_time,
    ua.wh_duration_ms,
    ua.wh_usage_quantity,
    try_divide(ua.wh_usage_quantity, ua.wh_duration_ms) AS usage_dbu_per_ms,
    SUM(qh.total_duration_ms * try_divide(ua.wh_usage_quantity, ua.wh_duration_ms) * lp.pricing.default) AS estimated_query_cost,
    SUM(qc.count * qh.total_duration_ms * try_divide(ua.wh_usage_quantity, ua.wh_duration_ms) * lp.pricing.default) AS estimated_30day_query_cost
FROM 
    system.query.history qh
JOIN    
    system.compute.warehouses cw
ON
    qh.compute.warehouse_id = cw.warehouse_id
JOIN
    usage_aggregates ua
ON
    qh.compute.warehouse_id = ua.warehouse_id
AND
    ua.wh_start_time <= qh.start_time
AND
    ua.wh_end_time >= qh.end_time
JOIN
    system.billing.list_prices lp
ON
    ua.sku_name = lp.sku_name
JOIN
    query_count qc
ON
    qh.statement_text = qc.statement_text
AND
    qh.compute.warehouse_id = qc.warehouse_id
WHERE
    qh.start_time >= current_date() - INTERVAL 30 DAY
GROUP BY
    qh.statement_id,
    qh.statement_text,
    qc.count,
    qh.compute.warehouse_id,
    qh.executed_by_user_id,
    qh.total_duration_ms,
    qh.start_time,
    qh.end_time,
    cw.warehouse_size,
    cw.warehouse_name,
    ua.sku_name,
    lp.pricing.default,
    ua.wh_duration_ms,
    ua.wh_usage_quantity
ORDER BY
    estimated_30day_query_cost DESC
LIMIT 20

-- Most expensive APC clusters in past 30 days

SELECT
  usage_metadata.cluster_id AS cluster_id,
  identity_metadata.created_by AS created_by,
  u.sku_name,
  SUM(u.usage_quantity * lp.pricing.default) AS total_cost,
  c.cluster_name,
  c.owned_by
FROM
  system.billing.usage u
  INNER JOIN system.billing.list_prices lp ON u.cloud = lp.cloud
  AND u.sku_name = lp.sku_name
  AND u.usage_start_time >= lp.price_start_time
  AND (u.usage_end_time <= lp.price_end_time OR lp.price_end_time IS NULL)
JOIN system.compute.clusters c ON usage_metadata.cluster_id = c.cluster_id
WHERE
  u.usage_metadata.cluster_id IS NOT NULL
  AND u.usage_date >= CURRENT_DATE() - INTERVAL 30 DAY
  AND u.sku_name LIKE "%ALL_PURPOSE%"
GROUP BY
  usage_metadata.cluster_id,
  identity_metadata.created_by,
  u.sku_name,
  c.cluster_name,
  c.owned_by
ORDER BY
  total_cost DESC
LIMIT 20

-- APC clusters with low CPU utilization in past 30 days

SELECT
  nt.cluster_id,
  nt.cpu_user_percent,
  nt.cpu_system_percent,
  nt.mem_used_percent,
  nt.mem_swap_percent,
  bu.sku_name,
  SUM(bu.usage_quantity * lp.pricing.default) AS total_cost,
  cc.cluster_name,
  cc.owned_by
FROM
  system.compute.node_timeline nt
JOIN
  system.compute.clusters cc
ON
  nt.cluster_id = cc.cluster_id
AND
  nt.workspace_id = cc.workspace_id
JOIN
  system.billing.usage bu
ON 
  nt.cluster_id = bu.usage_metadata.cluster_id
AND
  nt.workspace_id = bu.workspace_id
JOIN
  system.billing.list_prices lp
ON
  bu.cloud = lp.cloud
AND
  bu.sku_name = lp.sku_name
AND
  bu.usage_start_time >= lp.price_start_time
AND
  (bu.usage_end_time <= lp.price_end_time OR lp.price_end_time IS NULL)
WHERE
  bu.usage_date >= CURRENT_DATE() - INTERVAL 30 DAY
  AND bu.sku_name LIKE "%ALL_PURPOSE_COMPUTE%"
GROUP BY
  nt.cluster_id,
  nt.cpu_user_percent,
  nt.cpu_system_percent,
  nt.mem_used_percent,
  nt.mem_swap_percent,
  bu.sku_name,
  cc.cluster_name,
  cc.owned_by
LIMIT 20

-- Most expensive delta live tables clusters

SELECT
  usage_metadata.cluster_id AS cluster_id,
  identity_metadata.created_by AS created_by,
  u.sku_name,
  SUM(u.usage_quantity * lp.pricing.default) AS total_cost
FROM
  system.billing.usage u
  INNER JOIN system.billing.list_prices lp ON u.cloud = lp.cloud
  AND u.sku_name = lp.sku_name
  AND u.usage_start_time >= lp.price_start_time
  AND (u.usage_end_time <= lp.price_end_time OR lp.price_end_time IS NULL)
WHERE
  u.usage_metadata.cluster_id IS NOT NULL
  AND u.usage_date >= CURRENT_DATE() - INTERVAL 30 DAY
  AND u.sku_name LIKE "%DLT%"
GROUP BY
  usage_metadata.cluster_id,
  identity_metadata.created_by,
  u.sku_name
ORDER BY
  total_cost DESC
LIMIT 20

-- Notebooks that have consumed the most DBUs

SELECT
  usage_metadata.notebook_id AS notebook_id,
  usage_metadata.notebook_path AS notebook_path,
  SUM(usage_quantity) AS total_usage
FROM
  system.billing.usage
WHERE
  usage_metadata.notebook_id IS NOT NULL
GROUP BY
  notebook_id,
  notebook_path
ORDER BY
  total_usage DESC
LIMIT 10;

-- Slowest queries based upon total duration

SELECT
  statement_id,
  executed_by,
  statement_text,
  total_duration_ms,
  execution_duration_ms,
  compilation_duration_ms,
  waiting_for_compute_duration_ms,
  waiting_at_capacity_duration_ms,
  start_time,
  end_time
FROM
  system.query.history
ORDER BY
  total_duration_ms DESC
LIMIT 10;

-- Most Used Data Sources by Jobs

SELECT
  coalesce(source_table_full_name, source_path) source,
  count(distinct entity_id) distinct_jobs
from
  system.access.table_lineage
where
  entity_type = 'JOB'
  and coalesce(source_table_full_name, source_path) is not null
group by
  1
order by
  2 desc
limit 10;

-- Data Lineage (Table Sources)

SELECT
  distinct target_table_full_name target,
  coalesce(source_table_full_name, source_path) source
from
  system.access.table_lineage
where
  entity_type = 'JOB'
  and coalesce(source_table_full_name, source_path) is not null
  and target_table_full_name is not null
order by
  1 asc;