Bigquery Observability

GoogleOfficialPublished by Google

Skill
Tool integration
Data and AI
Apache-2.0
No issues found

From the author

Provides data-retrieval best practices, tool selection guidance, and performant SQL query syntax for BigQuery telemetry across INFORMATION_SCHEMA, Cloud Monitoring, and the REST API. Use when the telemetry to fetch is already known, selecting telemetry tools, writing performant INFORMATION_SCHEMA queries, retrieving telemetry for diagnosing single-job performance bottlenecks, investigating slot contention, job concurrency and queue latency, analyzing reservation capacity, utilization and autoscaling saturation, or auditing capacity-based and on-demand compute and storage resource billable usage. Don't use for root-cause diagnosis or symptom troubleshooting when the cause is unknown (use bigquery-troubleshooting first), or for writing or optimizing business logic SQL (use bigquery-optimization).

— the description by Google

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

BigQuery Observability offers a structured approach to monitoring and analyzing BigQuery performance and resource usage. It guides users in selecting the right telemetry tools—ranging from INFORMATION_SCHEMA for historical analysis to the REST API for real-time job lookups—and provides optimized SQL templates for auditing compute, storage, and capacity. The skill emphasizes efficient data retrieval, least-privilege access, and performance guardrails to ensure accurate telemetry without unnecessary overhead.

What it does

Provides SQL query templates for INFORMATION_SCHEMA views.
Offers guidance on choosing between INFORMATION_SCHEMA, Cloud Monitoring, and REST APIs.
Includes best practices for performant telemetry retrieval and cost management.
Details schema dictionaries for compute, storage, and capacity metrics.
Defines workflows for diagnosing job bottlenecks and resource contention.
Enforces usage attribution for all Google Cloud CLI and API interactions.

When to use it

When you need to audit billable compute or storage resource usage.
When investigating job performance bottlenecks or slot contention.
When analyzing reservation capacity, utilization, or autoscaling saturation.
When you need to perform historical analysis or cohort comparisons of jobs.
When selecting the appropriate telemetry tool for real-time vs. historical monitoring.

Advantages

Reduces query overhead by providing optimized, partition-filtered SQL patterns.
Ensures least-privilege access by recommending specific view variants.
Standardizes diagnostic workflows across single-job and system-level issues.
Provides clear guidance on avoiding common pitfalls like full metadata scans.

Usage examples

“How do I check for slot contention in my BigQuery project?”: The agent provides a query template using the JOBS_TIMELINE view, filtered by the relevant time window, to identify periods of high slot usage and queue latency.
“I need to audit my on-demand compute spend for the last 30 days.”: The agent provides a structured SQL query using INFORMATION_SCHEMA.JOBS, including logic to deduplicate script wrappers and calculate billable bytes.

Install

Download the skill as a ZIP:
https://mp-staging.autogineer.com/agent-skills/google/bigquery-observability/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip bigquery-observability.zip -d .agents/skills

Documentation by Google

BigQuery Observability

Attribution

MUST READ: Before executing any gcloud, REST API, or MCP command, you MUST read Usage Attribution. For commands executed directly in the terminal (never in generated scripts, Terraform, or user code), prefix gcloud with CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:bigquery-observability)" and append agent-skills/1.0 (skill:bigquery-observability) to User-Agent headers.

Tool Selection

Tool	Primary Use Cases	Strengths & Capabilities	When to Avoid / Limitations
`INFORMATION_SCHEMA` (`I_S`)	Historical analysis, cohort comparison (`normalized_literals`), discovery of fast/slow windows, reservation/project timelines, multi-job aggregates, cost/billing tracing.	Flexible SQL querying across `JOBS`, `JOBS_TIMELINE`, and `RESERVATIONS`; supports custom time windows and grouping.	Avoid for high-frequency real-time polling or single-job point-lookups (can consume slots and take seconds to execute).
REST API (`jobs.api` / `reservation.api`)	Single-job point-lookup, real-time stage bottleneck diagnosis, automated pipeline status checks, reservation/capacity commitment configuration inspection (`reservations.get`, `reservations.list`).	Zero-SQL overhead, fast REST/CLI point-lookups (`bq show -j`, `bq show --reservation`), instant access to `performanceInsights`, `queryPlan`, and structural metadata.	Avoid for aggregate analysis across thousands of jobs, cross-project historical comparison, or system timeline aggregations.
`Cloud Monitoring` (Metrics Explorer / Charts)	Real-time alerting, fleet-wide dashboards, continuous slot utilization tracking, high-level SLA/SLO monitoring.	Out-of-the-box charts for slot utilization, query throughput, `PENDING` queue depth, and execution latency; low-latency alerting without running queries.	Avoid for SQL-level debugging, individual query text inspection, or stage-level execution detail.

Prerequisites & Environment Setup

Before retrieving telemetry or running observability queries, ensure the Google Cloud environment and project are configured:

Google Cloud SDK: Ensure the Google Cloud SDK is installed and configured.
Project Selection: Set the active Google Cloud project:
```
gcloud config set project {project_id}
```
API Enablement: Ensure the BigQuery and Cloud Monitoring APIs are enabled:
```
gcloud services enable bigquery.googleapis.com monitoring.googleapis.com
```
Authentication: Authenticate the environment:
- CLI queries and bq commands: gcloud auth login
- SDKs and automated client tools: gcloud auth application-default login
- Service accounts: Set GOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"
Billing & IAM Roles:
- Verify an active Google Cloud Billing account is attached to {project_id}.
- Ensure appropriate IAM roles:
  - roles/bigquery.jobUser: Running telemetry queries.
  - roles/bigquery.resourceViewer or roles/bigquery.admin: Organization-level jobs and reservation telemetry.
  - roles/monitoring.viewer: Cloud Monitoring metrics.
Companion Skills Installation: This skill is part of a 3-pillar operations suite (bigquery-observability, bigquery-optimization, bigquery-troubleshooting). If any companion skill is not yet installed in your environment, install the full suite:
```
npx skills add google/skills --skill bigquery-observability --skill bigquery-optimization --skill bigquery-troubleshooting
```

Workflow

Single-Job Point-Lookup (Zero-SQL Overhead): For single-job slowness or inspection, always prioritize the REST API or CLI (bq show -j) first. It provides zero-SQL overhead and fast point-lookups for internal stage bottlenecks (performanceInsights, queryPlan, shuffle spill).
```
bq show --location={location} -j {project_id}:{job_id}
```
Diagnostic Transition Logic: If no job-level issues are found (e.g. no clear internal bottlenecks), the investigation should transition to system-level INFORMATION_SCHEMA queries (such as JOBS_TIMELINE or RESERVATIONS_TIMELINE) to check for broader issues like slot contention, queueing delay, or noisy neighbors.

Best Practices for Writing `INFORMATION_SCHEMA` Queries

Every query against a BigQuery INFORMATION_SCHEMA view must be qualified with either a region qualifier or a dataset qualifier, optionally prefixed by a project qualifier.

Qualification Syntax & Scope Matching

Region-Qualified Syntax:
```
`{project_id}`.`region-{region}`.INFORMATION_SCHEMA.{view}
```
Example: `my-project`.`region-us`.INFORMATION_SCHEMA.JOBS
Applies to: Regional telemetry views (JOBS*, JOBS_TIMELINE*, RESERVATIONS*, CAPACITY_COMMITMENTS*, TABLE_STORAGE*, STREAMING_TIMELINE*). The client query execution location MUST match the region-{region} qualifier (or BigQuery throws: Not found: Table {project_id}:region-{region}.INFORMATION_SCHEMA.{view} was not found in location {location}).
Dataset-Qualified Syntax:
```
`{project_id}`.`{dataset_id}`.INFORMATION_SCHEMA.{view}
```
Example: `my-project`.`analytics`.INFORMATION_SCHEMA.TABLES
Applies to: Dataset-scoped views (PARTITIONS, SEARCH_INDEXES*, ROW_ACCESS_POLICIES). Never use region- with dataset views.
Dual-Scoped Views: Views like TABLES, COLUMNS, COLUMN_FIELD_PATHS, VIEWS, ROUTINES, and VECTOR_INDEXES can be qualified with either {dataset_id} or region-{region} depending on whether dataset or region-wide analysis is required.
Project Qualifier ({project_id}): Optional. If omitted, queries default to the project in which the query is executing. Specifying a project qualifier on organization-level views (e.g. JOBS_BY_ORGANIZATION) has no impact on results.

Principle of Least Privilege & Scope Selection

When constructing INFORMATION_SCHEMA queries, always select the scope and view variant with the least IAM permission requirement that satisfies the analytical need:

User-Level over Project-Level (_BY_USER): When diagnosing queries or sessions executed by the current user, use _BY_USER (e.g. JOBS_BY_USER, SESSIONS_BY_USER). This requires only bigquery.jobs.list (granted via roles/bigquery.user or roles/bigquery.jobUser), avoiding the need for bigquery.jobs.listAll or roles/bigquery.admin.
Dataset-Level over Region/Project-Level: When querying table metadata, columns, or views for a specific dataset, qualify with {dataset_id} rather than region-{region} when project-level metadata access is restricted. Dataset-scoped queries require permissions only on that target dataset.
Project-Level over Org/Folder-Level (_BY_PROJECT): Always start with project-scoped views before escalating to _BY_FOLDER or _BY_ORGANIZATION. Folder and organization queries require broad folder/org IAM permissions (bigquery.jobs.listAll or bigquery.tables.list at the Org/Folder node).
Metadata Roles over Data Roles: For table and storage introspection, prefer roles/bigquery.metadataViewer (which provides bigquery.tables.get and bigquery.tables.list) over roles/bigquery.dataViewer or roles/bigquery.dataOwner when data read access (bigquery.tables.getData) is not needed. (Note: INFORMATION_SCHEMA.PARTITIONS uniquely requires bigquery.tables.getData).

Execution Guardrails & Query Invariants

Mandatory Partition & Time Filtering: Always filter on creation_time (e.g., creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)) or usage_date to avoid full metadata table scans.
Script Wrapper Exclusion: Add AND (statement_type != 'SCRIPT' OR statement_type IS NULL) when aggregating compute spend to avoid double-counting parent scripts and child jobs.
Column Pruning: Never use SELECT * against INFORMATION_SCHEMA; only project required columns.
Dry Run & Cost Estimation: Use a dry run (bq query --dry_run --use_legacy_sql=false "{query}" or API dryRun=true) before executing complex queries, multi-view joins, or large scans to validate syntax and estimate totalBytesProcessed at zero cost.
Empty Regional Scope (0 Rows): If the execution location matches the qualifier, but the project has no datasets or jobs in that region, the query succeeds and returns 0 rows. Never assume 0 rows means 0 usage—always verify the target dataset locations.
Non-Hierarchical Region Scope: Region qualifiers are not hierarchical. Multi-regions do not encompass single regions (e.g. region-us returns only multi-region US metadata and does not include single regions like region-us-central1).
No Multi-Region Aggregation in SQL: Region qualifiers cannot be joined cross-region in a single query (e.g. region-us cannot join region-eu).
Uncached Execution & Minimum Scan Size: INFORMATION_SCHEMA query results are never cached. On-demand queries incur a minimum of 10 MB of data processing charges per execution.

Domain References & SQL Queries

Telemetry Query Guides

On-Demand Compute: Billed Bytes (references/compute_ondemand_billable.md): Authoritative Golden CTE (bytes_billed_cte), timezone-aligned billing date extraction (PST8PDT), BQML CREATE_MODEL 50x multiplier rules, script wrapper deduplication, and row-level security (RLS) masking checks.
Capacity Compute: Billable Slots & Commitments (references/compute_capacity_billable.md): Query templates for auditing billable capacity hours across 1-Year/3-Year commitments, uncovered baseline PAYG slots, and dynamic autoscaling hours.
Storage Footprints & Usage (Bytes Stored) (references/storage_footprints.md): Storage snapshot queries, compression ratio calculations, Time Travel / Fail-Safe churn, daily average GiB time-integrals, billing model evaluation, and search index coverage.

Performance & Troubleshooting Guides

Job Performance Queries (references/job_performance_queries.md): Queries for evaluating individual and aggregate job performance, stage bottleneck flags, comparable jobs via normalized literals (query_info.query_hashes.normalized_literals), BI Engine acceleration, metadata cache (cmeta) acceleration, search index usage, materialized view rewrite usage, and execution variance outliers.
Resource Contention Queries (references/resource_contention_queries.md): Queries for diagnosing slot contention, queue latency, per-minute concurrency/queue timelines, and 1-second reservation slot saturation.
Capacity & Configuration Queries (references/capacity_and_configuration_queries.md): Queries for evaluating second-by-second baseline/max capacity ceilings, autoscaling saturation timelines, and auditing configuration changes (RESERVATION_CHANGES_BY_PROJECT, ASSIGNMENT_CHANGES_BY_PROJECT).

Schema Dictionaries (Column Definitions & Units)

Compute & Capacity Schema Dictionary (references/schema_compute.md): Complete column dictionary, physical units, and least-privilege IAM roles for all compute, job, session, reservation, capacity commitment, and assignment views (JOBS*, JOBS_TIMELINE*, SESSIONS_BY_USER, SESSIONS_BY_PROJECT, RESERVATIONS*, RESERVATION_CHANGES*, RESERVATIONS_TIMELINE*, CAPACITY_COMMITMENTS*, CAPACITY_COMMITMENT_CHANGES_BY_PROJECT, ASSIGNMENTS*, ASSIGNMENT_CHANGES_BY_PROJECT).
Storage & Data Catalog Schema Dictionary (references/schema_storage.md): Complete column dictionary, physical units, and least-privilege IAM roles for all table storage, partition, column, snapshot, dataset, constraint, and replication views (TABLE_STORAGE*, TABLE_STORAGE_USAGE_TIMELINE*, TABLES*, TABLE_OPTIONS, COLUMNS, COLUMN_FIELD_PATHS, PARTITIONS, VIEWS, MATERIALIZED_VIEWS, TABLE_SNAPSHOTS*, TABLE_CONSTRAINTS, KEY_COLUMN_USAGE, SCHEMATA*, SCHEMATA_OPTIONS, SCHEMATA_REPLICAS*, SCHEMATA_LINKS, SHARED_DATASET_USAGE).
Platform, Governance & Ingestion Schema Dictionary (references/schema_others.md): Complete column dictionary, physical units, and least-privilege IAM roles for all remaining views including Access Control (OBJECT_PRIVILEGES, ROW_ACCESS_POLICIES, ROW_ACCESS_POLICY_OPTIONS), Streaming Ingestion (STREAMING_TIMELINE_BY_PROJECT*, WRITE_API_TIMELINE_BY_PROJECT*), Configuration Options (PROJECT_OPTIONS*, EFFECTIVE_PROJECT_OPTIONS, ORGANIZATION_OPTIONS*, ORGANIZATION_OPTIONS_CHANGES), Insights & Recommendations (RECOMMENDATIONS*, INSIGHTS), and Indexes/BI Engine/Routines (SEARCH_INDEXES*, SEARCH_INDEX_COLUMNS, SEARCH_INDEX_OPTIONS, VECTOR_INDEXES*, VECTOR_INDEX_COLUMNS, VECTOR_INDEX_OPTIONS, BI_CAPACITIES, BI_CAPACITY_CHANGES, ROUTINES*, ROUTINE_OPTIONS, PARAMETERS).

Files

ATTRIBUTION.mdAdded by AutoGineer862 B
LICENSELicence file added by AutoGineer11.1 KiB
SKILL.md14.3 KiB
references/attribution.md1.9 KiB
references/capacity_and_configuration_queries.md6.6 KiB
references/compute_capacity_billable.md6.2 KiB
references/compute_ondemand_billable.md5.7 KiB
references/job_performance_queries.md11.1 KiB
references/resource_contention_queries.md9.3 KiB
references/schema_compute.md40.4 KiB
references/schema_others.md32.0 KiB
references/schema_storage.md34.3 KiB
references/storage_footprints.md10.1 KiB

references/job_performance_queries.md

Query Performance Queries

Telemetry and SQL queries for evaluating individual and aggregate job performance, discovering comparable job executions, analyzing slot consumption, identifying query/stage bottlenecks, inspecting table-level job activity, and verifying BI Engine / metadata cache (cmeta) acceleration.

Single Job Performance & Stage Bottleneck Flags (Lines 27-76)
REST API Single-Job Point-Lookup & Stage Bottlenecks (Lines 77-97)
Finding Comparable Jobs (Lines 98-129)
BI Engine Acceleration Usage (Lines 130-152)
Table Metadata Cache (cmeta) Usage (Lines 153-179)
Search Index Usage (Lines 180-208)
Materialized View Rewrite Usage (Lines 209-244)
Query Performance Variance & Outlier Discovery (Lines 245-279)
Stage Row Expansion, Shuffle Spill & Join Insights (Lines 280-338)

Single Job Performance & Stage Bottleneck Flags

Retrieve execution duration, queuing delay, compute usage, bytes processed, historical average duration, and stage bottleneck flags for a specific job.

SELECT
  job_id,
  user_email,
  state,
  creation_time,
  start_time,
  end_time,
  TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms,
  TIMESTAMP_DIFF(start_time, creation_time, MILLISECOND) AS pending_ms,
  total_slot_ms,
  ROUND(
    SAFE_DIVIDE(
      total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)),
    1) AS avg_slots_used,
  total_bytes_processed,
  total_bytes_billed,
  reservation_id,
  query_info.performance_insights.avg_previous_execution_ms
    AS avg_previous_execution_ms,
  query_info.performance_insights.avg_previous_slot_ms AS avg_previous_slot_ms,
  query_info.performance_insights.avg_previous_bytes_processed
    AS avg_previous_bytes_processed,
  query_info.performance_insights.num_previous_successful_jobs
    AS num_previous_successful_jobs,
  (
    SELECT LOGICAL_OR(slot_contention)
    FROM
      UNNEST(
        query_info.performance_insights.stage_performance_standalone_insights)
  ) AS has_slot_contention,
  (
    SELECT LOGICAL_OR(insufficient_shuffle_quota)
    FROM
      UNNEST(
        query_info.performance_insights.stage_performance_standalone_insights)
  ) AS has_insufficient_shuffle_quota,
  error_result.message AS error_message
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_id = '{job_id}';

REST API Single-Job Point-Lookup & Stage Bottlenecks

Use the REST API for fast, zero-SQL point-lookups to extract stage-level execution graphs, wait ratios, and performance insights.

bq show --location={location} -j {project_id}:{job_id}

Key fields in JSON response:

statistics.query.performanceInsights: Execution bottleneck diagnostics (e.g., stagePerformanceStandaloneInsights[].slotContention, stagePerformanceStandaloneInsights[].insufficientShuffleQuota, stagePerformanceChangeInsights[].inputDataChange, avgPreviousExecutionMs).
statistics.query.queryPlan[]: Stage-by-stage stats including slotMs, shuffleOutputBytes, recordsRead, recordsWritten, computeRatioAvg, computeRatioMax, waitRatioAvg, waitRatioMax, readRatioAvg, readRatioMax, writeRatioAvg, writeRatioMax.

Finding Comparable Jobs

Use query_info.query_hashes.normalized_literals to discover past runs of the same query template and rank by duration to find fast baseline executions.

SELECT
  job_id,
  creation_time,
  TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms,
  total_slot_ms,
  ROUND(SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)), 1)
    AS avg_slots_used,
  total_bytes_processed,
  total_bytes_billed,
  reservation_id,
  (
    SELECT LOGICAL_OR(slot_contention)
    FROM UNNEST(query_info.performance_insights.stage_performance_standalone_insights)
  ) AS has_slot_contention
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
  AND state = 'DONE'
  AND query_info.query_hashes.normalized_literals = '{target_normalized_hash}'
ORDER BY
  duration_ms ASC
LIMIT 20;

BI Engine Acceleration Usage

Verify whether queries leveraged BI Engine memory acceleration and diagnose acceleration rejection reasons.

SELECT
  job_id,
  creation_time,
  total_slot_ms,
  bi_engine_statistics.bi_engine_mode,
  bi_engine_statistics.acceleration_mode,
  bi_engine_statistics.bi_engine_reasons
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND bi_engine_statistics IS NOT NULL
ORDER BY
  creation_time DESC
LIMIT 50;

Table Metadata Cache (cmeta) Usage

Inspect whether queries leveraged table metadata caching (cmeta), check staleness, and diagnose why metadata caching was unused.

SELECT
  job_id,
  creation_time,
  total_slot_ms,
  t.table_reference.project_id AS table_project_id,
  t.table_reference.dataset_id AS table_dataset_id,
  t.table_reference.table_id,
  t.unused_reason,
  t.explanation,
  t.staleness_seconds,
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(metadata_cache_statistics.table_metadata_cache_usage) AS t
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
ORDER BY
  creation_time DESC
LIMIT 50;

Search Index Usage

Verify whether queries used a search index (index_usage_mode is UNUSED, PARTIALLY_USED, or FULLY_USED) and diagnose why an index was not used. index_unused_reasons is populated only for UNUSED and PARTIALLY_USED, so the LEFT JOIN keeps FULLY_USED jobs in the result.

SELECT
  job_id,
  creation_time,
  total_slot_ms,
  total_bytes_processed,
  search_statistics.index_usage_mode,
  unused_reason.code AS unused_reason_code,
  unused_reason.base_table.table_id AS base_table_id,
  unused_reason.index_name
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
LEFT JOIN
  UNNEST(search_statistics.index_unused_reasons) AS unused_reason
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND search_statistics IS NOT NULL
ORDER BY
  creation_time DESC
LIMIT 50;

Materialized View Rewrite Usage

Verify whether queries used a materialized view (chosen), including queries that smart tuning rewrote from the base tables, and diagnose why a candidate view was rejected (rejected_reason, for example NO_DATA, COST, or BASE_TABLE_DATA_CHANGE). A view that is not listed in materialized_view_statistics did not match the query shape. If many views are considered, the list might be incomplete. Automatic refresh jobs have materialized_view_refresh in the job ID, so the query excludes them; to see refresh cost instead, query JOBS_BY_PROJECT in the view's project without the UNNEST, filter on job_id LIKE '%materialized_view_refresh_%', and read total_slot_ms, total_bytes_processed, and materialized_view_statistics.materialized_view[SAFE_OFFSET(0)].rejected_reason.

SELECT
  job_id,
  creation_time,
  total_slot_ms,
  total_bytes_processed,
  mv.table_reference.dataset_id AS mv_dataset_id,
  mv.table_reference.table_id AS mv_table_id,
  mv.chosen,
  mv.estimated_bytes_saved,
  mv.rejected_reason
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(materialized_view_statistics.materialized_view) AS mv
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND job_id NOT LIKE '%materialized_view_refresh_%'
ORDER BY
  creation_time DESC
LIMIT 50;

Query Performance Variance & Outlier Discovery

Identify queries that experienced the highest execution variance compared to their historical baseline duration (avg_previous_execution_ms).

SELECT
  job_id,
  user_email,
  reservation_id,
  creation_time,
  ROUND(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) / 1000.0, 1) AS duration_sec,
  ROUND(query_info.performance_insights.avg_previous_execution_ms / 1000.0, 1)
    AS baseline_avg_duration_sec,
  ROUND(
    SAFE_DIVIDE(
      TIMESTAMP_DIFF(end_time, start_time, MILLISECOND),
      query_info.performance_insights.avg_previous_execution_ms),
    2) AS variance_ratio,
  total_slot_ms,
  total_bytes_billed
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time
    BETWEEN TIMESTAMP('{target_start_timestamp}')
    AND TIMESTAMP('{target_end_timestamp}')
  AND state = 'DONE'
  AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
  AND query_info.performance_insights.avg_previous_execution_ms IS NOT NULL
ORDER BY
  variance_ratio DESC
LIMIT 50;

Stage Row Expansion, Shuffle Spill & Join Insights

Unnest job_stages in INFORMATION_SCHEMA.JOBS_BY_PROJECT across recent jobs (or filter by job_id = '{job_id}' for a single job) to detect join row explosions (records_written >> records_read caused by missing ON conditions, unintentional CROSS JOINs, or non-selective/many-to-many join predicates), shuffle spills to disk (shuffle_output_bytes_spilled > 0), worker scheduling wait delays (wait_ratio_avg, wait_ms_avg), and engine-surfaced join/skew insights (high_cardinality_joins, partition_skew). Note that job_stages is empty for queries that read from tables with row-level access policies.

SELECT
  j.job_id,
  j.user_email,
  j.creation_time,
  j.total_slot_ms,
  stage.id AS stage_id,
  stage.name AS stage_name,
  stage.records_read,
  stage.records_written,
  ROUND(
    SAFE_DIVIDE(stage.records_written, NULLIF(stage.records_read, 0)), 2)
    AS row_expansion_ratio,
  stage.shuffle_output_bytes,
  stage.shuffle_output_bytes_spilled,
  stage.slot_ms AS stage_slot_ms,
  stage.wait_ratio_avg,
  stage.wait_ratio_max,
  stage.wait_ms_avg,
  stage.wait_ms_max,
  stage.compute_ratio_avg,
  stage.compute_ratio_max,
  insight.slot_contention,
  insight.insufficient_shuffle_quota,
  insight.high_cardinality_joins,
  insight.partition_skew
FROM
  `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS j,
  UNNEST(j.job_stages) AS stage
LEFT JOIN
  UNNEST(
    j.query_info.performance_insights.stage_performance_standalone_insights)
    AS insight
  ON stage.id = insight.stage_id
WHERE
  j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND j.job_type = 'QUERY'
  AND j.state = 'DONE'
  AND (
    stage.records_written > stage.records_read * 10
    OR stage.shuffle_output_bytes_spilled > 0
    OR ARRAY_LENGTH(insight.high_cardinality_joins) > 0
    OR ARRAY_LENGTH(insight.partition_skew.skew_sources) > 0)
ORDER BY
  row_expansion_ratio DESC,
  stage.slot_ms DESC
LIMIT 50;

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Needs: Needs a command-line tool, Needs Node.js

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Published by

Google

Publisher

Google (Official)

Source

google/skills · skills/cloud/bigquery-observability · commit 2194d3b

Licence

Apache License 2.0 (redistribution allowed)

LICENSE (added by AutoGineer from LICENSE)
ATTRIBUTION.md (written by AutoGineer)

Synced

2026-11-08

AutoGineer is not affiliated with or endorsed by Google.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download bigquery-observability.zip