bigquery-troubleshooting

Files (8)

• SKILL.md
• attribution.md · references/
• cost_compute_capacity.md · references/
• cost_compute_ondemand.md · references/
• cost_storage.md · references/
• performance_config_changed.md · references/
• performance_resource_contention.md · references/
• query_plan_execution_graph.md · references/

SKILL.md (first lines)

# BigQuery Troubleshooting

## 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-troubleshooting)"`
and append `agent-skills/1.0 (skill:bigquery-troubleshooting)` to `User-Agent` headers.

## Prerequisites & Environment Setup

Before running diagnostic queries or investigating incident telemetry:

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 BigQuery and Cloud Monitoring APIs are enabled:

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

4.  **Authentication**: Authenticate the environment:
    *   CLI commands (`bq show -j`): `gcloud auth login`
    *   SDKs and automated diagnostic scripts:
        `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`: Executing diagnostic queries.
        *   `roles/bigquery.resourceViewer` or `roles/bigquery.admin`:
            Inspecting reservation and job execution telemetry.
        *   `roles/monitoring.viewer`: Cloud Monitoring metrics.
        *   `roles/billing.viewer`: Cloud Billing reports and cost attribution.

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

    *(If `bigquery-observability` is not installed, use the self-contained
    baseline formulas and query templates provided directly in the reference
    sections below).*

## Workflow

1.  **Scope & Symptom Identification:** Identify the primary symptom, target
    `project_id`, `region`, `reservation_id`, or `job_id`, and domain
    (Performance, Compute Cost, or Storage Cost). If the request falls outside
    incident diagnosis or asks for a sibling domain, follow **Routing
    Boundaries** below.
2.  **Telemetry Tool Selection:** Follow the tool-selection guidance in
    [bigquery-observability](../bigquery-observability)
    (`bigquery_observability`) to select the appropriate telemetry interface
    (REST API `bq show --location={location} -j {project_id}:{job_id}` for
    single-job stage bottlenecks vs. `INFORMATION_SCHEMA` for system-wide
    factors). Diagnostic workflows, symptom-to-cause mappings, key
    tables/fields, CLI triage commands, and remediation levers are fully defined
    in this skill. For pre-composed SQL query templates and full schema
    dictionaries, consult `bigquery-observability`.
3.  **Open-Ended Triage (Stage 1 Baseline Scan & Conversational Gate):** When
    the user inquiry is open-ended or vague (e.g. *"Why is BigQuery slow
    today?"* or *"Why did my bill spike?"*), execute a bounded high-level
    baseline scan to isolate the affected domain before drilling into deep-dive
    diagnostics:
    *   **Bounded Initial Scan:** Follow the baseline scan guidance in the
        corresponding domain reference under **Domain References**. Ensure
        initial queries are strictly bounded (e.g. 7-day Period-over-Period with
        partition and job-type filters; for unspecified cost spikes, scan the 3
        primary vectors: On-Demand TiB, Capacity slot-hours, and Storage GiB) to
        keep diagnostic telemetry overhead minimal.
    *   **Conversational Gate:** Factually summarize high-level baseline
        findings first and propose 2–3 focused drill-down options rather than
        dumping downstream sub-vector queries unsolicited.
4.  **Domain Deep Dive & Comparative Analysis:** Execute the step-by-step
    diagnostic workflow defined in the corresponding domain reference file
    listed under **Domain References** below, then run the corresponding query
    from [bigquery-observability](../bigquery-observability) following its
    `INFORMATION_SCHEMA` best practices to isolate the root cause via
    comparative analysis against a normal baseline.

## Performance Context: The Relativity of "Slow"

Performance is relative. Always approach performance troubleshooting as a
comparative exercise: identify a comparable past execution, compare the
statistics, and isolate which dimension shifted between a fast baseline and the
slow execution:

-   **Data Processed:** Data volume increase, partition/cluster pruning changes,
    data skew, input record amplification.
-   **Underlying Definitions:** View changes, schema modifications.
-   **System Contention:** Noisy neighbors, saturated capacity (>95% slot
    utilization), idle slot availability, concurrent query spikes.
-   **Configuration Changes:** Slot capacity/autoscale max slots changes,
    expired capacity commitments, idle slot setting changes, or reservation
    reassignments.

## Cost Context: The 4-Step Diagnostic Funnel

Cost troubleshooting requires tracing physical resource consumption (Slot-Hours,
TiB Billed, GiB Stored) rather than fluctuating contract rates:

1.  **Gather:** Determine scope and pull 7-day PoP (or explicit MoM / 180-day)

From google/skills, Apache-2.0.

More from google/skills