bigquery-observability

Files (11)

• SKILL.md
• attribution.md · references/
• capacity_and_configuration_queries.md · references/
• compute_capacity_billable.md · references/
• compute_ondemand_billable.md · references/
• job_performance_queries.md · references/
• resource_contention_queries.md · references/
• schema_compute.md · references/
• schema_others.md · references/
• schema_storage.md · references/
• storage_footprints.md · references/

SKILL.md (first lines)

# 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:

    ```bash
    gcloud config set project {project_id}
    ```

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

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

    ```bash
    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).

    ```bash
    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:**

    ```googlesql
    `{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:**

    ```googlesql
    `{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.

From google/skills, Apache-2.0.

More from google/skills