11 metrics. Every BigQuery
project. One setup.

WITH
  main_metric AS (
    SELECT
      project_id,
      ROUND(SUM(total_slot_ms) / 3600000.0, 2) AS METRIC_VALUE
    FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
    WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 25 HOUR)
      AND creation_time <  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
      AND state = 'DONE'
    GROUP BY project_id
  ),
  baseline_windows AS (
    SELECT
      TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL (day_offset + 1) DAY) AS window_start,
      TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL day_offset DAY)       AS window_end
    FROM UNNEST(GENERATE_ARRAY(1, 30)) AS day_offset
  ),
  baseline_metric AS (
    SELECT
      j.project_id,
      w.window_start,
      ROUND(SUM(j.total_slot_ms) / 3600000.0, 2) AS METRIC_VALUE
    FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT` j
    JOIN baseline_windows w
      ON j.creation_time >= w.window_start
     AND j.creation_time <  w.window_end
    WHERE j.state = 'DONE'
    GROUP BY j.project_id, w.window_start
  ),
  baseline AS (
    SELECT
      project_id,
      MAX(METRIC_VALUE)       AS COMPARED_VALUE,
      MAX(METRIC_VALUE) * 1.3 AS COMPARED_VALUE_THRESHOLD
    FROM baseline_metric
    GROUP BY project_id
  )
SELECT
  m.project_id,
  m.METRIC_VALUE,
  ROUND(b.COMPARED_VALUE, 2)           AS COMPARED_VALUE,
  ROUND(b.COMPARED_VALUE_THRESHOLD, 2) AS COMPARED_VALUE_THRESHOLD,
  CASE WHEN m.METRIC_VALUE > b.COMPARED_VALUE_THRESHOLD
    THEN 'SPIKE' ELSE 'Normal'
  END AS status
FROM main_metric m
LEFT JOIN baseline b ON m.project_id = b.project_id
ORDER BY m.METRIC_VALUE DESC
LIMIT 1000

WITH
  main_metric AS (
    SELECT
      user_email,
      ROUND(SUM(total_bytes_billed) / POW(10, 12), 4) AS METRIC_VALUE
    FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
    WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 25 HOUR)
      AND creation_time <  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
      AND state = 'DONE'
      AND total_bytes_billed > 0
    GROUP BY user_email
  ),
  baseline_windows AS (
    SELECT
      TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL (day_offset + 1) DAY) AS window_start,
      TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL day_offset DAY)       AS window_end
    FROM UNNEST(GENERATE_ARRAY(1, 30)) AS day_offset
  ),
  baseline_metric AS (
    SELECT
      j.user_email,
      w.window_start,
      ROUND(SUM(j.total_bytes_billed) / POW(10, 12), 4) AS METRIC_VALUE
    FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT` j
    JOIN baseline_windows w
      ON j.creation_time >= w.window_start
     AND j.creation_time <  w.window_end
    WHERE j.state = 'DONE'
      AND j.total_bytes_billed > 0
    GROUP BY j.user_email, w.window_start
  ),
  baseline AS (
    SELECT
      user_email,
      MAX(METRIC_VALUE)       AS COMPARED_VALUE,
      MAX(METRIC_VALUE) * 1.3 AS COMPARED_VALUE_THRESHOLD
    FROM baseline_metric
    GROUP BY user_email
  )
SELECT
  m.user_email,
  m.METRIC_VALUE                          AS METRIC_VALUE_TB,
  ROUND(m.METRIC_VALUE * 6.25, 2)         AS ESTIMATED_COST_USD,
  ROUND(b.COMPARED_VALUE, 4)              AS COMPARED_VALUE,
  ROUND(b.COMPARED_VALUE_THRESHOLD, 4)    AS COMPARED_VALUE_THRESHOLD
FROM main_metric m
LEFT JOIN baseline b ON m.user_email = b.user_email
WHERE m.METRIC_VALUE > COALESCE(b.COMPARED_VALUE_THRESHOLD, 0)
ORDER BY m.METRIC_VALUE DESC
LIMIT 1000

SELECT
  table_catalog AS project_id,
  table_schema  AS dataset_id,
  COUNT(*)      AS table_count,
  ROUND(SUM(total_logical_bytes)     / POW(1024, 3), 2) AS total_gb,
  ROUND(SUM(active_logical_bytes)    / POW(1024, 3), 2) AS active_gb,
  ROUND(SUM(long_term_logical_bytes) / POW(1024, 3), 2) AS long_term_gb,
  ROUND(SAFE_DIVIDE(
    SUM(long_term_logical_bytes),
    SUM(total_logical_bytes)
  ) * 100, 1) AS long_term_pct
FROM `region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT`
WHERE total_logical_bytes > 0
GROUP BY 1, 2
ORDER BY total_gb DESC
LIMIT 1000

SELECT
  table_catalog AS project_id,
  table_schema  AS dataset_id,
  table_name,
  ROUND(total_logical_bytes     / POW(1024, 3), 3) AS total_gb,
  ROUND(long_term_logical_bytes / POW(1024, 3), 3) AS long_term_gb,
  ROUND(SAFE_DIVIDE(
    long_term_logical_bytes,
    total_logical_bytes
  ) * 100, 1) AS long_term_pct
FROM `region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT`
WHERE total_logical_bytes     > POW(1024, 3) * 10     -- tables > 10 GB
  AND long_term_logical_bytes > total_logical_bytes * 0.9
ORDER BY long_term_gb DESC
LIMIT 1000

SELECT
  job_id,
  user_email,
  job_type,
  TIMESTAMP_DIFF(end_time, start_time, MINUTE)    AS duration_minutes,
  ROUND(total_bytes_processed / POW(1024, 3), 2)  AS bytes_processed_gb,
  ROUND(total_slot_ms / 1000.0, 1)                AS slot_seconds,
  query
FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
  AND creation_time <  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND state = 'DONE'
  AND TIMESTAMP_DIFF(end_time, start_time, MINUTE) > 30
ORDER BY duration_minutes DESC
LIMIT 1000

WITH
  main_metric AS (
    SELECT
      job_id,
      user_email,
      TIMESTAMP_DIFF(start_time, creation_time, SECOND) AS METRIC_VALUE
    FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
    WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
      AND creation_time <  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
      AND state = 'DONE'
      AND start_time > creation_time
  ),
  baseline_windows AS (
    SELECT
      TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL (day_offset + 1) DAY) AS window_start,
      TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL day_offset DAY)       AS window_end
    FROM UNNEST(GENERATE_ARRAY(1, 30)) AS day_offset
  ),
  baseline_metric AS (
    SELECT
      w.window_start,
      MAX(TIMESTAMP_DIFF(j.start_time, j.creation_time, SECOND)) AS METRIC_VALUE
    FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT` j
    JOIN baseline_windows w
      ON j.creation_time >= w.window_start
     AND j.creation_time <  w.window_end
    WHERE j.state = 'DONE'
      AND j.start_time > j.creation_time
    GROUP BY w.window_start
  ),
  baseline AS (
    SELECT
      MAX(METRIC_VALUE)       AS COMPARED_VALUE,
      MAX(METRIC_VALUE) * 1.3 AS COMPARED_VALUE_THRESHOLD
    FROM baseline_metric
  )
SELECT
  m.job_id,
  m.user_email,
  m.METRIC_VALUE                AS wait_seconds,
  b.COMPARED_VALUE,
  b.COMPARED_VALUE_THRESHOLD
FROM main_metric m
CROSS JOIN baseline b
WHERE m.METRIC_VALUE > b.COMPARED_VALUE_THRESHOLD
ORDER BY m.METRIC_VALUE DESC
LIMIT 1000

WITH
  main_metric AS (
    SELECT
      COUNTIF(error_result IS NOT NULL) AS failed_count,
      COUNT(*)                          AS total_count,
      ROUND(SAFE_DIVIDE(
        COUNTIF(error_result IS NOT NULL),
        COUNT(*)
      ) * 100, 2) AS METRIC_VALUE
    FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
    WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
      AND creation_time <  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
      AND state = 'DONE'
  ),
  baseline_windows AS (
    SELECT
      TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL (day_offset + 1) DAY) AS window_start,
      TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY),
        INTERVAL day_offset DAY)       AS window_end
    FROM UNNEST(GENERATE_ARRAY(1, 7)) AS day_offset
  ),
  baseline_metric AS (
    SELECT
      w.window_start,
      ROUND(SAFE_DIVIDE(
        COUNTIF(j.error_result IS NOT NULL),
        COUNT(*)
      ) * 100, 2) AS METRIC_VALUE
    FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT` j
    JOIN baseline_windows w
      ON j.creation_time >= w.window_start
     AND j.creation_time <  w.window_end
    WHERE j.state = 'DONE'
    GROUP BY w.window_start
  ),
  baseline AS (
    SELECT
      MAX(METRIC_VALUE)       AS COMPARED_VALUE,
      MAX(METRIC_VALUE) * 1.3 AS COMPARED_VALUE_THRESHOLD
    FROM baseline_metric
  )
SELECT
  m.METRIC_VALUE,
  m.failed_count,
  m.total_count,
  b.COMPARED_VALUE,
  b.COMPARED_VALUE_THRESHOLD
FROM main_metric m
CROSS JOIN baseline b
LIMIT 1000

SELECT
  job_id,
  user_email,
  job_type,
  creation_time,
  end_time,
  error_result.reason  AS error_reason,
  error_result.message AS error_message,
  query
FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
  AND creation_time <  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND state = 'DONE'
  AND error_result IS NOT NULL
ORDER BY creation_time DESC
LIMIT 1000

WITH last_referenced AS (
  SELECT
    ref.project_id AS table_project,
    ref.dataset_id  AS table_dataset,
    ref.table_id    AS table_name,
    MAX(j.creation_time) AS last_referenced_at
  FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT` j,
  UNNEST(j.referenced_tables) AS ref
  WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 DAY)
    AND j.state = 'DONE'
  GROUP BY 1, 2, 3
)
SELECT
  s.table_catalog   AS project_id,
  s.table_schema    AS dataset_id,
  s.table_name,
  ROUND(s.total_logical_bytes / POW(1024, 3), 3) AS storage_gb,
  s.row_count,
  r.last_referenced_at,
  DATE_DIFF(CURRENT_DATE(), DATE(r.last_referenced_at), DAY) AS days_since_last_use
FROM `region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT` s
LEFT JOIN last_referenced r
  ON  s.table_catalog = r.table_project
  AND s.table_schema  = r.table_dataset
  AND s.table_name    = r.table_name
WHERE (
  r.last_referenced_at IS NULL
  OR r.last_referenced_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
)
  AND s.total_logical_bytes > 0
ORDER BY days_since_last_use DESC NULLS FIRST
LIMIT 1000

SELECT
  TIMESTAMP_TRUNC(period_start, HOUR)           AS hour_bucket,
  ROUND(SUM(period_slot_ms) / 3600000.0, 2)    AS slot_hours,
  COUNT(DISTINCT job_id)                         AS job_count,
  ROUND(AVG(period_slot_ms) / 3600000.0, 4)    AS avg_slot_hours_per_job
FROM `region-us.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT`
WHERE period_start >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 1 DAY)
  AND period_start <  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
GROUP BY 1
ORDER BY slot_hours DESC
LIMIT 1000

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_processed / POW(10, 12), 4)  AS bytes_processed_tb,
  ROUND(total_bytes_processed / POW(10, 12) * 6.25, 2) AS estimated_cost_usd,
  ROUND(total_slot_ms / 1000.0, 1)               AS slot_seconds,
  TIMESTAMP_DIFF(end_time, start_time, SECOND)   AS duration_seconds,
  query
FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 25 HOUR)
  AND creation_time <  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND state = 'DONE'
  AND total_bytes_processed > 100 * POW(1024, 3)  -- > 100 GB processed
ORDER BY bytes_processed_tb DESC
LIMIT 1000