Introduction to troubleshooting

This document provides an overview of the diagnostic tools, telemetry interfaces, and documentation resources available to help you troubleshoot issues in BigQuery.

When you encounter query failures, performance bottlenecks, permission errors, quota limits, or data ingestion issues, BigQuery provides built-in tools to help you identify root causes and resolve issues quickly.

Troubleshooting workflow

To troubleshoot an issue in BigQuery effectively, consider the following diagnostic criteria:

  1. Identify the symptom and failure mode. Review your task's results to determine whether the issue is a hard failure (such as an error code or job failure), a performance degradation (such as a slow query or slot starvation), a permission denial, or an unexpected cost discrepancy.
  2. Inspect job execution details. Use the Google Cloud console jobs explorer, the query execution graph, or command-line tools to examine stage-level timings, slot allocation, and error details. You can also ask Gemini Cloud Assist to investigate your issue.
  3. Analyze telemetry and metadata. Query information schema views or inspect Cloud Audit Logs to correlate job behavior with resource contention, reservation limits, or administrative changes.
  4. Apply targeted mitigations. Use category-specific troubleshooting guides or defensive SQL functions to remediate the underlying cause.

Distinguish troubleshooting from optimization

When you work with BigQuery, it's important to distinguish between troubleshooting, performance optimization, and best practices:

  • Troubleshooting. Focuses on diagnosing and resolving unexpected failures, runtime errors, broken pipelines, quota exhaustion, or unintended behavior that prevents jobs from completing successfully.
  • Performance optimization. Focuses on improving the execution speed, latency, or resource efficiency of queries and workloads that are already running successfully. For more information, see Optimize query performance.
  • Best practices. Focuses on architectural and design patterns for data modeling, storage, security, and cost management. For more information, see Introduction to best practices.

Diagnostic tools

The following sections describe several BigQuery interfaces and automated tools to help you diagnose issues across your workloads.

Visual and console tools

The following tools help you troubleshoot BigQuery from Google Cloud console.

  • Jobs Explorer. Search, filter, and inspect past and running jobs across projects or organizations without writing SQL queries. You can view error messages, slot usage, execution timelines, and job metadata. For more information, see Monitor jobs in Jobs Explorer.
  • Query execution graph. Inspect the visual stage-by-stage execution plan for a query. The execution graph helps you identify bottlenecks such as shuffle spills to disk, compute-bound stages, data skew, or input/output delays. For more information, see Troubleshoot query performance with the query execution graph.
  • Query Insights and resource charts. View real-time and historical graphs of slot utilization, job concurrency, and reservation allocations to diagnose capacity constraints. For more information, see Use administrative resource charts.
  • Gemini Cloud Assist in BigQuery. Get contextual, AI-assisted analysis of failed queries and performance bottlenecks. Gemini Cloud Assist explains error codes, highlights problematic SQL syntax, and suggests remediation steps directly in the Google Cloud console. For more information, see Troubleshoot queries using Gemini Cloud Assist.

Command-line and automated diagnostic tools

The following tools can help you diagnose BigQuery issues from a command-line interface.

  • bq command-line tool. Inspect detailed error structures, request IDs, and job metadata by using the bq show -j <var>JOB_ID</var> command or by adding the --format=prettyjson flag to query commands. For more information, see Troubleshooting CLI commands.
  • gcpdiag tool. Run automated diagnostics from the command line to detect common Google Cloud configuration issues, including IAM permission gaps, network restrictions, and service account errors. For more information, see Troubleshoot query failure using gcpdiag.

Metadata and telemetry views

Information schema views let you query real-time and historical metadata about jobs, capacity, streaming ingestion, and datasets using standard SQL. These views include the following:

For more information, see Introduction to BigQuery INFORMATION_SCHEMA.

Cloud Monitoring and Cloud Audit Logs

  • Cloud Audit Logs. Review Admin Activity and Data Access audit logs to trace who initiated specific operations, inspect caller identities, and diagnose PERMISSION_DENIED errors. For more information, see BigQuery audit logging reference.
  • Cloud Monitoring. Track metrics such as slot usage, query execution durations, and uploaded bytes, and configure alert policies to notify your team when thresholds or quotas are exceeded. For more information, see Monitor BigQuery using Cloud Monitoring.

Defensive SQL functions and debugging statements

To prevent queries from failing unexpectedly due to runtime data errors, use the following:

  • Safe expressions. Use SAFE_CAST(), SAFE_DIVIDE(), SAFE_OFFSET(), and SAFE_ORDINAL() to return NULL instead of generating runtime errors when data types or array bounds don't match. Most scalar functions support the SAFE. prefix. For more information, see Debugging functions and SAFE. prefix.
  • SQL assertions. Use the ASSERT statement in multi-statement transactions or scripts to enforce data validation conditions and fail with custom error messages before downstream operations execute. For more information, see Debugging statements.

Troubleshoot by issue category

Select a category from the following sections to view detailed error codes, root causes, and step-by-step resolution guides.

Query performance and execution

Diagnose queries that fail to run, time out, encounter resource constraints, or experience unexpected delays.

Identity and Access Management (IAM) and security

Diagnose access control failures, missing role assignments, and data governance policy blocks.

Quotas, rate limits, and reservations

Resolve issues when workloads exceed BigQuery service limits or capacity allocations.

Data ingestion, streaming, and transfers

Diagnose failures when loading data, streaming records, or syncing external sources.

External data sources and federated queries

Diagnose connectivity, authentication, and execution errors when querying data outside BigQuery.

Billing and cost discrepancies

Investigate unexpected charges and billing discrepancies across compute and storage.

Data loss mitigation

To recover historical data that was changed or deleted, or to maintain business continuity during a regional outage, use the following disaster recovery and data retention tools:

  • Restore data. Query or restore table data that was changed or deleted within your time travel window. For more information, see Restore data.
  • Time travel. Retain updated or deleted data in a dataset for a configured retention period to help protect against accidental modifications. For more information, see Time travel.
  • Regional failover. Promote a secondary replica to the primary role during a regional outage when using BigQuery-managed disaster recovery. For more information, see Regional failover.

Using APIs

When you interact with BigQuery programmatically, use the following resources to optimize request latency and manage upload workflows:

  • API performance tips. Follow best practices for making API calls, such as managing connection pools, using batch operations, and handling retries. For more information, see API performance tips.
  • API uploads. Troubleshoot and manage data ingestion using REST API resumable and multipart upload requests. For more information, see API uploads.

What's next