/bigquery-troubleshooting

name: bigquery-troubleshooting
metadata:
  version: "1.0.0"
  category: BigDataAndAnalytics
description: >-
  Provides diagnostic workflows and step-by-step root-cause analysis
  procedures for actively broken, failing, or slow BigQuery jobs, execution
  graph and query plan stage bottlenecks, system performance issues, or
  unexpectedly expensive workloads. Use when interpreting symptoms, isolating
  bottlenecks, diagnosing cost spikes (on-demand query spend, capacity slot
  autoscaling, storage growth), execution graph stages or substep variables,
  identifying root causes, and determining remediation steps. Don't use for
  writing or optimizing SQL, proactive capacity planning, or storage layout
  design (use bigquery-optimization), or when the user already knows which
  telemetry they want and just needs the query (use bigquery-observability).

BigQuery Troubleshooting

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:

    gcloud config set project {project_id}

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

    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:

    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 reassig