BigQuery INFORMATION_SCHEMA.JOBS Cost, Slot & Spill Profiler
-- BigQuery 30-Day Query Fingerprint FinOps, Slot-Hours & Shuffle Spill Profiler
-- Partition-pruned on creation_time so this audit query itself scans minimal metadata bytes
DECLARE lookback_days INT64 DEFAULT 30;
DECLARE on_demand_tib_usd FLOAT64 DEFAULT 6.25;
WITH job_metrics AS (
SELECT
COALESCE(query_info.query_hashes.normalized_literals, job_id) AS query_fingerprint,
user_email,
statement_type,
total_bytes_billed,
total_slot_ms,
TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS elapsed_ms,
(
SELECT COALESCE(SUM(s.shuffle_output_bytes_spilled), 0)
FROM UNNEST(job_stages) AS s
) AS spilled_bytes,
query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
AND job_type = 'QUERY'
AND state = 'DONE'
AND error_result IS NULL
AND cache_hit IS NOT TRUE
)
SELECT
query_fingerprint,
ANY_VALUE(statement_type) AS stmt_type,
COUNT(*) AS executions_30d,
COUNT(DISTINCT user_email) AS distinct_principals,
ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3) AS total_tib_billed,
ROUND((SUM(total_bytes_billed) / POW(1024, 4)) * on_demand_tib_usd, 2) AS est_ondemand_usd,
ROUND(SUM(total_slot_ms) / (1000 * 3600), 2) AS total_slot_hours,
ROUND(APPROX_QUANTILES(elapsed_ms, 100)[OFFSET(95)] / 1000.0, 2) AS p95_duration_sec,
ROUND(SUM(spilled_bytes) / POW(1024, 3), 2) AS total_shuffle_spilled_gib,
SUBSTR(REGEXP_REPLACE(ANY_VALUE(query), r'\s+', ' '), 1, 220) AS sample_sql
FROM job_metrics
GROUP BY query_fingerprint
ORDER BY total_tib_billed DESC, total_slot_hours DESC
LIMIT 25;