BigQuery INFORMATION_SCHEMA.JOBS Cost, Slot & Spill Profiler

Supanote Core Team @supanote · 1 min read

plain (sql) 1 day ago · 42 lines · 2 views

Artifact

Verified Blueprint Google BigQuery

Group 30-day BigQuery INFORMATION_SCHEMA.JOBS by normalized query fingerprint to expose top TB billed, slot contention, and shuffle spills.

✓ Groups recurring scheduled/BI queries via query_info.query_hashes.normalized_literals
✓ Calculates both On-Demand USD spend ($6.25/TiB) and Editions slot-hours consumed per query pattern
✓ Flags hidden stage memory spills (shuffle_output_bytes_spilled) that degrade p95 dashboard latency

Quick Fetch

-- 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;

1-- BigQuery 30-Day Query Fingerprint FinOps, Slot-Hours & Shuffle Spill Profiler

2-- Partition-pruned on creation_time so this audit query itself scans minimal metadata bytes

4DECLARE lookback_days INT64 DEFAULT 30;

5DECLARE on_demand_tib_usd FLOAT64 DEFAULT 6.25;

7WITH job_metrics AS (

8 SELECT

9 COALESCE(query_info.query_hashes.normalized_literals, job_id) AS query_fingerprint,

10 user_email,

11 statement_type,

12 total_bytes_billed,

13 total_slot_ms,

14 TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS elapsed_ms,

15 (

16 SELECT COALESCE(SUM(s.shuffle_output_bytes_spilled), 0)

17 FROM UNNEST(job_stages) AS s

18 ) AS spilled_bytes,

19 query

20 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

21 WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)

22 AND job_type = 'QUERY'

23 AND state = 'DONE'

24 AND error_result IS NULL

25 AND cache_hit IS NOT TRUE

26)

27SELECT

28 query_fingerprint,

29 ANY_VALUE(statement_type) AS stmt_type,

30 COUNT(*) AS executions_30d,

31 COUNT(DISTINCT user_email) AS distinct_principals,

32 ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3) AS total_tib_billed,

33 ROUND((SUM(total_bytes_billed) / POW(1024, 4)) * on_demand_tib_usd, 2) AS est_ondemand_usd,

34 ROUND(SUM(total_slot_ms) / (1000 * 3600), 2) AS total_slot_hours,

35 ROUND(APPROX_QUANTILES(elapsed_ms, 100)[OFFSET(95)] / 1000.0, 2) AS p95_duration_sec,

36 ROUND(SUM(spilled_bytes) / POW(1024, 3), 2) AS total_shuffle_spilled_gib,

37 SUBSTR(REGEXP_REPLACE(ANY_VALUE(query), r'\s+', ' '), 1, 220) AS sample_sql

38FROM job_metrics

39GROUP BY query_fingerprint

40ORDER BY total_tib_billed DESC, total_slot_hours DESC

41LIMIT 25;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Related Google BigQuery & Database, Cache & SQL Blueprints

Production-ready configurations and diagnostics from the verified Supanote library.

All Database, Cache & SQL →

SQL Google BigQuery

BigQuery GoogleSQL Pipe Syntax (|>) & GROUP BY ALL Blueprint

Refactor nested BigQuery CTEs into clean top-to-bottom GoogleSQL Pipe Syntax (|>) with early partition pruning, EXTEND, and AGGREGATE.

SQL Google BigQuery

BigQuery FinOps: PHYSICAL vs LOGICAL Storage Savings Calculator

Audit every BigQuery dataset via INFORMATION_SCHEMA.TABLE_STORAGE to calculate exact monthly savings from switching to PHYSICAL billing.

SQL SQLite

High-Performance SQLite Production PRAGMAs

Maximum performance SQLite pragmas for WAL mode, concurrency, and memory buffers.

SQL SQLite

SQLite FTS5 Full-Text Search Schema & Sync Triggers

External content FTS5 virtual table with unicode61 tokenizer and insert, update, and delete synchronization triggers.

Interactive Developer Workbench: Online CSV & TSV Table Viewer

Turn raw comma, tab, or semicolon-delimited text into a fast, sortable spreadsheet table in one paste.

Open CSV & TSV Viewer →