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:

   bashCopy code
   gcloud config set project {project_id}
   

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

   bashCopy code
   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:

   bashCopy code
   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.

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) baseline metrics.
2. Isolate: Pinpoint whether spend surged from query volume, a single "Bully Query", BQML 50x multipliers, uncovered PAYG baselines, autoscaling bursts, 90-day storage timer resets, or physical Fail-Safe retention drain.
3. Explain: Correlate with administrative events (INFORMATION_SCHEMA.RESERVATION_CHANGES, INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT, INFORMATION_SCHEMA.SCHEMATA_OPTIONS, or actor user_email / query_hash).
4. Remediate: Deliver actionable levers (partition filter enforcement, query caps, commitment purchases, or Time Travel reduction).

Domain References

Performance Troubleshooting

• Resource Contention & Performance Slowness (references/performance_resource_contention.md): Diagnostic workflows for isolating single-job stage bottlenecks (slot_contention, spill_to_disk), cohort baseline comparisons (normalized_literals), incident window discovery, 1-second reservation slot saturation, timeframe contention comparisons, fleet performance variance, and table-level concurrency.
• Capacity & Configuration Changes (references/performance_config_changed.md): Diagnostic workflows for auditing reservation slot_capacity and autoscale.max_slots edits, tracking active capacity commitment timelines, diagnosing reservation assignment modifications, and evaluating autoscaling headroom saturation.
• Execution Graph & Query Plan Troubleshooting (references/query_plan_execution_graph.md): Diagnostic workflows for investigating single-job stage bottlenecks (bq show point-lookups), isolating slowest stages (end_ms - start_ms), substep intermediate variable disambiguation ($1, $2), mandatory bytes scanned vs records read corrections, and UI execution graph grounding concepts.

Cost Troubleshooting

• On-Demand Compute Costs (references/cost_compute_ondemand.md): Diagnostic workflows for unpartitioned runaway scans (the "Bully Query"), hidden Row-Level Security (RLS) redaction gaps, BigQuery ML (BQML) 50x model training rate multipliers, and user/service account query quotas.
• Capacity (Editions) Compute Costs (references/cost_compute_capacity.md): Diagnostic workflows for uncovered baseline slot penalties (baseline > commitments), reservation baseline reductions triggering autoscale surges (RESERVATION_BASELINE_CHANGED), autoscaler thrashing from batch cron spikes, and serverless Apache Spark stored procedure slot-hours.
• Storage Footprint & Retention Costs (references/cost_storage.md): Diagnostic workflows for historical partition 90-day timer resets (the DML trap), unpartitioned table active data traps, physical Time Travel and Fail-Safe churn on daily overwrites, and dropped table Fail-Safe drain periods.

Routing Boundaries

If a user request shifts outside incident diagnosis during troubleshooting, execute the corresponding handoff:

• SQL Query Optimizations: When the user asks to optimize the SQL query (e.g., rewriting joins or eliminating SELECT *), hand off to bigquery-optimization.
• Raw Telemetry & Schema Retrieval: When the user asks for standalone INFORMATION_SCHEMA queries without an active performance regression or incident (e.g. general telemetry queries), hand off to bigquery-observability.
• Proactive Capacity & Storage Planning: When the user requests future reservation sizing, commitment purchasing, or storage billing model evaluations, hand off to bigquery-optimization.