/bigquery-observability

name: bigquery-observability
metadata:
  version: "1.2.2"
  category: BigDataAndAnalytics
description: >-
  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).

BigQuery Observability

Attribution

**MUST READ**: Before executing any `gcloud`, REST API, or MCP command, you MUST read [Usage Attribution](references/attribution.md). 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

<!-- mdformat off -->

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

<!-- mdformat on -->

Prerequisites & Environment Setup

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

1. **Google Cloud SDK**: Ensure the [Google Cloud SDK](https://cloud.google.com/sdk/docs/install) is installed and configured. 2. **Project Selection**: Set the active Google Cloud project:

    gcloud config set project {project_id}

3. **API Enablement**: Ensure the BigQuery and Cloud Monitoring APIs are enabled:

    gcloud services enable bigquery.googleapis.com monitoring.googleapis.com

4. **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"`

5. **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.

6. **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

1. **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}

2. **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

1. **Region-Qualified Syntax:**

    `{project_id}`.`region-{region}`.INFORMATION_SCHEMA.{view}

*Example:* `` `my-project`.`region-us`.INFORMATION_SCHEMA.JOBS``

*A