Bigquery Troubleshooting

BigQuery Troubleshooting

Prerequisites & Environment Setup

Before running diagnostic queries or investigating incident telemetry:

1. Google Cloud SDK: Ensure the Google Cloud SDK 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) 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 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.