# 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. > [!NOTE] > **Note:** If you have a specific HTTP status code, `ErrorProto` reason string, or API error message, see [Error messages](https://docs.cloud.google.com/bigquery/docs/error-messages.md.txt) for descriptions and recommended actions. ## 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](https://docs.cloud.google.com/bigquery/docs/admin-jobs-explorer.md.txt#get-job-details), the [query execution graph](https://docs.cloud.google.com/bigquery/docs/query-insights.md.txt), or [command-line tools](https://docs.cloud.google.com/bigquery/docs/troubleshoot-intro.md.txt#command-line_and_automated_diagnostic_tools) to examine stage-level timings, slot allocation, and error details. You can also ask [Gemini Cloud Assist](https://docs.cloud.google.com/bigquery/docs/use-cloud-assist.md.txt) to investigate your issue. 3. **Analyze telemetry and metadata.** Query [information schema views](https://docs.cloud.google.com/bigquery/docs/information-schema-intro.md.txt) 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](https://docs.cloud.google.com/bigquery/docs/troubleshoot-intro.md.txt#troubleshoot_by_issue_category) 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](https://docs.cloud.google.com/bigquery/docs/best-practices-performance-overview.md.txt). - **Best practices.** Focuses on architectural and design patterns for data modeling, storage, security, and cost management. For more information, see [Introduction to best practices](https://docs.cloud.google.com/bigquery/docs/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](https://docs.cloud.google.com/bigquery/docs/admin-jobs-explorer.md.txt). - **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 [Get query performance insights](https://docs.cloud.google.com/bigquery/docs/query-insights.md.txt). - **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](https://docs.cloud.google.com/bigquery/docs/admin-resource-charts.md.txt). - **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](https://docs.cloud.google.com/bigquery/docs/troubleshoot-queries.md.txt#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 JOB_ID` command or by adding the `--format=prettyjson` flag to query commands. For more information, see [Troubleshooting CLI commands](https://docs.cloud.google.com/bigquery/docs/reference/bq-cli-reference.md.txt#troubleshooting-bq). - **`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`](https://docs.cloud.google.com/bigquery/docs/troubleshoot-queries.md.txt#failed-query-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: - **Job execution telemetry.** Query [`INFORMATION_SCHEMA.JOBS`](https://docs.cloud.google.com/bigquery/docs/information-schema-jobs.md.txt) and [`INFORMATION_SCHEMA.JOBS_TIMELINE`](https://docs.cloud.google.com/bigquery/docs/information-schema-jobs-timeline.md.txt) to analyze slot-millisecond consumption, spilled bytes, queue times, and error codes across jobs. - **Reservations and capacity.** Query [`INFORMATION_SCHEMA.RESERVATIONS`](https://docs.cloud.google.com/bigquery/docs/information-schema-reservations.md.txt) and [`INFORMATION_SCHEMA.CAPACITY_COMMITMENTS`](https://docs.cloud.google.com/bigquery/docs/information-schema-capacity-commitments.md.txt) to diagnose slot allocation, reservation limits, and autoscaling behaviors. - **Streaming and ingestion.** Query [`INFORMATION_SCHEMA.STREAMING_TIMELINE`](https://docs.cloud.google.com/bigquery/docs/information-schema-streaming.md.txt) to identify ingestion latency and streaming rate limits. - **Storage and partition health.** Query [`INFORMATION_SCHEMA.TABLE_STORAGE`](https://docs.cloud.google.com/bigquery/docs/information-schema-table-storage.md.txt) to inspect physical table sizes, active versus long-term storage, and partition distribution. For more information, see [Introduction to BigQuery `INFORMATION_SCHEMA`](https://docs.cloud.google.com/bigquery/docs/information-schema-intro.md.txt). ### 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](https://docs.cloud.google.com/bigquery/docs/reference/auditlogs.md.txt). - **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](https://docs.cloud.google.com/bigquery/docs/monitoring.md.txt). ### 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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/debugging_functions.md.txt) and [`SAFE.` prefix](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/functions-reference.md.txt#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](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/debugging-statements.md.txt). ## 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. - **[Troubleshoot query issues](https://docs.cloud.google.com/bigquery/docs/troubleshoot-queries.md.txt).** Resolve `resourcesExceeded` errors, slow query execution, shuffle spill, out-of-memory conditions, and scheduled query failures. - **[Troubleshoot long query queue times](https://docs.cloud.google.com/bigquery/docs/query-queues.md.txt#troubleshooting_long_queue_times).** Diagnose concurrency bottlenecks and queries queued due to interactive or batch queue limits. - **[Error messages reference](https://docs.cloud.google.com/bigquery/docs/error-messages.md.txt).** Look up specific HTTP error codes, error reason strings, and recommended actions. ### Identity and Access Management (IAM) and security Diagnose access control failures, missing role assignments, and data governance policy blocks. - **[Troubleshoot IAM permissions in BigQuery](https://docs.cloud.google.com/bigquery/docs/troubleshoot-access-control.md.txt).** Diagnose permission denied errors, grant missing IAM roles, and use Policy Troubleshooter. - **[Troubleshoot VPC Service Controls](https://docs.cloud.google.com/vpc-service-controls/docs/troubleshooting.md.txt).** Identify and resolve perimeter violations and ingress or egress rule blocks. - **[Troubleshoot row-level and column-level security](https://docs.cloud.google.com/bigquery/docs/column-level-security.md.txt#troubleshoot).** Resolve access issues related to data policies, policy tags, and row-access filters. ### Quotas, rate limits, and reservations Resolve issues when workloads exceed BigQuery service limits or capacity allocations. - **[Troubleshoot quota and limit errors](https://docs.cloud.google.com/bigquery/docs/troubleshoot-quotas.md.txt).** Identify adjustable versus non-adjustable quotas, handle concurrent query limits, and resolve API rate-limit errors. - **[Troubleshoot BigQuery workload management](https://docs.cloud.google.com/bigquery/docs/troubleshoot-workload-management.md.txt).** Diagnose slot starvation, reservation assignment mismatches, capacity commitment delays, slot contention, and reservation monitoring issues. ### Data ingestion, streaming, and transfers Diagnose failures when loading data, streaming records, or syncing external sources. - **[Troubleshoot data transfers](https://docs.cloud.google.com/bigquery/docs/troubleshoot-data-transfers.md.txt).** Diagnose BigQuery Data Transfer Service issues, network connectivity for private transfers, and CSV data loading errors. - **[Troubleshoot transfer configurations](https://docs.cloud.google.com/bigquery/docs/transfer-troubleshooting).** Resolve BigQuery Data Transfer Service configuration errors across sources like Amazon Simple Storage Service (Amazon S3), Salesforce, Google Ads, and Cloud Storage. - **[Troubleshoot BigQuery Storage API](https://docs.cloud.google.com/bigquery/docs/troubleshoot-storage-api.md.txt).** Debug BigQuery Storage Read API, BigQuery Storage Write API (gRPC), and BigQuery Storage Write API (REST) streaming ingestion failures, row-level insertion errors, and throughput quotas. - **[Troubleshoot schema errors](https://docs.cloud.google.com/bigquery/docs/troubleshoot-schemas.md.txt).** Resolve schema mismatch errors from load, query, copy, and schema update operations (such as missing, duplicate, or incompatible column changes), and 15-level schema, file, and routine nesting depth limits. ### Pipelines and data lineage Diagnose issues with BigQuery pipeline runs and data lineage reporting. - **[Troubleshoot pipeline data lineage](https://docs.cloud.google.com/bigquery/docs/manage-pipelines.md.txt#troubleshoot-lineage).** Resolve missing lineage process nodes, service account permission errors, and Data Lineage API quota issues for pipelines. ### Data warehouse migration Diagnose issues when assessing, translating, or migrating external data warehouses to BigQuery. - **[Troubleshoot migration issues](https://docs.cloud.google.com/bigquery/docs/troubleshoot-migrations.md.txt).** Resolve migration assessment errors, interactive and batch SQL translation issues, and metadata extraction failures. ### External data sources and federated queries Diagnose connectivity, authentication, and execution errors when querying data outside BigQuery. - **[Troubleshoot Cloud SQL federated queries](https://docs.cloud.google.com/bigquery/docs/cloud-sql-federated-queries.md.txt#troubleshooting).** Resolve connection timeouts, instance configuration issues, and credential failures. ### Billing and cost discrepancies Investigate unexpected charges and billing discrepancies across compute and storage. - **[Troubleshoot BigQuery cost discrepancies](https://docs.cloud.google.com/bigquery/docs/best-practices-costs.md.txt#troubleshooting-bigquery-cost-discrepancies-and-unexpected-charges).** Identify the origin of unexpected charges, analyze on-demand bytes billed, and verify capacity commitment usage. ### 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](https://docs.cloud.google.com/bigquery/docs/access-historical-data.md.txt). - **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](https://docs.cloud.google.com/bigquery/docs/time-travel.md.txt). - **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](https://docs.cloud.google.com/bigquery/docs/managed-disaster-recovery.md.txt#initiate_a_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](https://docs.cloud.google.com/bigquery/docs/api-performance.md.txt). - **API uploads.** Troubleshoot and manage data ingestion using REST API resumable and multipart upload requests. For more information, see [API uploads](https://docs.cloud.google.com/bigquery/docs/reference/api-uploads.md.txt). ### Authentication and ADC For help troubleshooting common problems that you might encounter when using Application Default Credentials (ADC), see [Troubleshoot your ADC setup](https://docs.cloud.google.com/docs/authentication/troubleshoot-adc.md.txt). ## What's next - Learn more about [monitoring BigQuery](https://docs.cloud.google.com/bigquery/docs/monitoring.md.txt). - Explore the [BigQuery `INFORMATION_SCHEMA` reference](https://docs.cloud.google.com/bigquery/docs/information-schema-intro.md.txt). - Contact [Cloud Customer Care](https://cloud.google.com/support) for support with persistent or critical production issues.