Troubleshooting BigQuery: Common Issues and Solutions

Introduction to Troubleshooting BigQuery

Google BigQuery is a powerful serverless data warehouse that handles petabyte-scale analytics with ease. However, like any complex distributed system, it comes with its own set of quirks, error messages, and performance pitfalls. Whether you are running ad-hoc queries, building pipelines, or optimizing dashboards, you will eventually encounter issues that require careful diagnosis.

This tutorial walks through the most common BigQuery problems developers face, explains why they happen, and provides practical solutions with code examples you can apply immediately.

Why Troubleshooting BigQuery Matters

BigQuery charges by the byte scanned and by slot usage, so inefficient queries are not just slow — they are expensive. A single malformed query can scan terabytes of unnecessary data, blow through your budget, and degrade performance for everyone sharing your reservation. Understanding how to diagnose and fix issues quickly protects both your wallet and your team's productivity.

Issue 1: Query Exceeds Resource Limits

One of the most frequent errors is the dreaded "Resources exceeded during query execution." This typically happens when a query requires more memory than a single slot can provide, often due to large joins, window functions, or massive aggregations.

Common Causes

• Joining two large tables without a proper join strategy
• Using ORDER BY on a massive result set
• Window functions with large partition sizes
• Too many columns in a SELECT combined with heavy aggregation

Solution: Partition and Cluster Your Tables

The most effective fix is to reduce the data each worker processes. Partitioning splits your table by date or integer range, while clustering physically sorts data by specified columns. Together, they dramatically reduce scan volume.

-- Create a partitioned and clustered table
CREATE TABLE my_dataset.events (
  event_id STRING,
  event_type STRING,
  user_id STRING,
  event_timestamp TIMESTAMP,
  payload STRING
)
PARTITION BY DATE(event_timestamp)
CLUSTER BY event_type, user_id;

-- Query only the partition and cluster you need
SELECT
  event_type,
  COUNT(*) AS event_count
FROM my_dataset.events
WHERE DATE(event_timestamp) BETWEEN '2024-01-01' AND '2024-01-31'
  AND event_type = 'click'
GROUP BY event_type;

Solution: Break Large Joins into Stages

If a single join is too large, materialize intermediate results into temporary tables. This lets BigQuery distribute the work more effectively.

-- Step 1: Pre-aggregate the large table
CREATE OR REPLACE TEMP TABLE user_clicks AS
SELECT
  user_id,
  COUNT(*) AS click_count
FROM my_dataset.events
WHERE DATE(event_timestamp) = '2024-01-15'
  AND event_type = 'click'
GROUP BY user_id;

-- Step 2: Join with the smaller table
SELECT
  u.user_id,
  u.user_name,
  c.click_count
FROM my_dataset.users u
JOIN user_clicks c
  ON u.user_id = c.user_id
ORDER BY c.click_count DESC
LIMIT 100;

Issue 2: High Query Costs from Full Table Scans

BigQuery bills based on bytes read, not rows returned. A query that selects one column from a billion-row table is cheap, but a SELECT * on the same table can be enormously expensive.

Solution: Select Only What You Need

-- BAD: Scans every column, including large nested fields
SELECT *
FROM my_dataset.events
WHERE event_type = 'click';

-- GOOD: Scans only the columns referenced
SELECT
  event_id,
  user_id,
  event_timestamp
FROM my_dataset.events
WHERE event_type = 'click';

Solution: Use the Query Validator

Before running any query, check the validator in the BigQuery console. It shows exactly how many bytes the query will process. You can also retrieve this information programmatically using a dry run.

from google.cloud import bigquery

client = bigquery.Client()

job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)

query = """
SELECT event_id, user_id
FROM my_dataset.events
WHERE DATE(event_timestamp) = '2024-01-15'
"""

dry_run_job = client.query(query, job_config=job_config)
print(f"This query will process {dry_run_job.total_bytes_processed} bytes.")

Issue 3: Slot Contention and Slow Queries

When multiple queries compete for limited slots, performance degrades across the board. This is especially common in shared project environments or when using on-demand pricing during peak hours.

Diagnosing Slot Usage

Use the INFORMATION_SCHEMA.JOBS view to inspect query performance and slot consumption.

SELECT
  job_id,
  creation_time,
  query,
  total_slot_ms,
  total_bytes_processed,
  state,
  error_result
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND state = 'DONE'
ORDER BY total_slot_ms DESC
LIMIT 20;

Solution: Use Query Priorities

BigQuery supports two query priorities: INTERACTIVE (runs immediately) and BATCH (queued and runs when resources are available). Batch queries cost less and reduce contention.

from google.cloud import bigquery

client = bigquery.Client()

job_config = bigquery.QueryJobConfig(
    priority=bigquery.QueryPriority.BATCH
)

query = """
SELECT
  event_type,
  COUNT(*) AS cnt
FROM my_dataset.events
GROUP BY event_type
"""

query_job = client.query(query, job_config=job_config)
print(f"Batch job submitted: {query_job.job_id}")

Issue 4: Streaming Insert Failures

When using the BigQuery Storage Write API or legacy streaming inserts, you may encounter errors related to schema mismatches, quota limits, or malformed data.

Common Streaming Errors

• invalid — the row does not match the table schema
• quotaExceeded — you exceeded the streaming insert quota
• stopped — the job was stopped due to too many errors

Solution: Validate and Retry with Error Handling

from google.cloud import bigquery
import time

client = bigquery.Client()
table_id = "my_project.my_dataset.events"

rows_to_insert = [
    {"event_id": "evt_001", "event_type": "click", "user_id": "user_123"},
    {"event_id": "evt_002", "event_type": "view", "user_id": "user_456"},
]

def insert_with_retry(rows, max_retries=3):
    for attempt in range(max_retries):
        errors = client.insert_rows_json(table_id, rows)
        if not errors:
            print(f"Successfully inserted {len(rows)} rows.")
            return True
        else:
            print(f"Attempt {attempt + 1} errors: {errors}")
            time.sleep(2 ** attempt)
    print("Max retries reached. Rows not inserted.")
    return False

insert_with_retry(rows_to_insert)

Issue 5: Schema Evolution Problems

As your data model evolves, you may need to add columns or change types. BigQuery supports limited schema modifications, but certain changes can break existing pipelines.

Solution: Safe Schema Updates

You can add nullable columns without breaking existing queries, but you cannot remove or rename columns directly. For complex changes, create a new table and migrate.

-- Add a new nullable column (safe, non-breaking)
ALTER TABLE my_dataset.events
ADD COLUMN session_id STRING;

-- For breaking changes, create a new table and copy data
CREATE TABLE my_dataset.events_v2 (
  event_id STRING,
  event_type STRING NOT NULL,
  user_id STRING,
  event_timestamp TIMESTAMP,
  session_id STRING
) PARTITION BY DATE(event_timestamp)
CLUSTER BY event_type;

INSERT INTO my_dataset.events_v2 (event_id, event_type, user_id, event_timestamp)
SELECT
  event_id,
  event_type,
  user_id,
  event_timestamp
FROM my_dataset.events;

Issue 6: Unexpected NULL Results

BigQuery's handling of NULLs can surprise developers coming from other SQL dialects. NULLs propagate through arithmetic, comparisons, and aggregations in ways that may not be intuitive.

Solution: Use COALESCE and IFNULL

-- NULL in arithmetic produces NULL
SELECT
  100 + NULL AS result;  -- Returns NULL

-- Use COALESCE to provide a default
SELECT
  COALESCE(revenue, 0) AS safe_revenue,
  COALESCE(discount, 0) AS safe_discount,
  COALESCE(revenue, 0) - COALESCE(discount, 0) AS net_revenue
FROM my_dataset.sales;

-- Use IFNULL for simpler cases
SELECT
  IFNULL(user_name, 'anonymous') AS display_name
FROM my_dataset.users;

-- Be careful with COUNT: COUNT(column) ignores NULLs, COUNT(*) does not
SELECT
  COUNT(*) AS total_rows,
  COUNT(user_email) AS rows_with_email
FROM my_dataset.users;

Issue 7: Array and Struct Query Errors

BigQuery's nested and repeated fields (ARRAY and STRUCT types) are powerful but can cause confusing errors if you forget to unnest them before joining or filtering.

Solution: Properly Unnest Arrays

-- Given a table with a repeated field 'orders' containing STRUCTs
SELECT
  u.user_id,
  o.order_id,
  o.order_amount
FROM my_dataset.users u
CROSS JOIN UNNEST(u.orders) AS o
WHERE o.order_amount > 100;

-- Alternative: use a LEFT JOIN to keep users with no orders
SELECT
  u.user_id,
  o.order_id,
  o.order_amount
FROM my_dataset.users u
LEFT JOIN UNNEST(u.orders) AS o
ORDER BY u.user_id;

Best Practices for Avoiding BigQuery Issues

Design for Efficiency from the Start

• Always partition large tables by date — this is the single biggest cost saver
• Cluster tables by columns you frequently filter or join on
• Avoid SELECT * in production queries and dashboards
• Use materialized views for frequently run aggregations
• Set up table expiration for temporary and staging tables

Monitor and Alert Proactively

-- Find the most expensive queries in the last 7 days
SELECT
  job_id,
  user_email,
  query,
  total_bytes_processed,
  total_bytes_billed,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_seconds,
  total_slot_ms / 1000 AS slot_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND state = 'DONE'
  AND total_bytes_processed > 0
ORDER BY total_bytes_billed DESC
LIMIT 50;

Use Dry Runs in CI/CD

Integrate dry runs into your deployment pipeline to catch expensive queries before they reach production. Any query that exceeds a byte threshold should trigger a review.

import sys
from google.cloud import bigquery

client = bigquery.Client()
MAX_BYTES = 10 * 1024 * 1024 * 1024  # 10 GB limit

def validate_query(query_sql):
    job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
    job = client.query(query_sql, job_config=job_config)
    bytes_processed = job.total_bytes_processed

    if bytes_processed > MAX_BYTES:
        print(f"FAIL: Query would process {bytes_processed} bytes (limit: {MAX_BYTES})")
        sys.exit(1)
    else:
        print(f"PASS: Query would process {bytes_processed} bytes")

validate_query("SELECT * FROM my_dataset.events WHERE DATE(event_timestamp) = '2024-01-15'")

Conclusion

Troubleshooting BigQuery effectively comes down to understanding how the engine processes data and where costs accumulate. By partitioning and clustering your tables, selecting only necessary columns, monitoring slot usage, handling streaming errors gracefully, and integrating dry runs into your workflows, you can avoid the vast majority of common issues. The key is to treat query design as an engineering discipline rather than an afterthought — every query you write has a cost, and the habits you build around validation, monitoring, and optimization will pay dividends as your data grows. Keep the INFORMATION_SCHEMA views bookmarked, run dry runs before executing anything unfamiliar, and your BigQuery experience will be far smoother and more predictable.