/bigquery-observability

name: bigquery-observability
metadata:
  version: v1
  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

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` (Monarch / 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.

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

*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}`).

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

3. **Dual-Scoped Views:** Views like `TABLES`, `COLUMNS`, `COLUMN_FIELD_PATHS`, `VIEWS`, `ROUTINES`, and `VECTOR_INDEXES` can be qualified with either `{dat