How to Debug Queries in BigQuery

How do I debug slow or failing queries in BigQuery?

Debugging in BigQuery means inspecting execution plans, cost estimates, and job metadata with EXPLAIN, EXPLAIN ANALYZE, dry runs, and INFORMATION_SCHEMA views.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why is my BigQuery query slow?

Start with EXPLAIN to view the logical and distributed execution plan. The plan highlights stage count, read volume, and shuffle size so you can spot expensive joins or scans quickly.

How do I estimate cost before running?

Append OPTION (dry_run=TRUE) or use the CLI flag --dry_run.BigQuery parses the statement, shows the bytes it will read, and skips execution, keeping your bill safe.

When should I use EXPLAIN ANALYZE?

Use EXPLAIN ANALYZE after a query finishes.It reruns the statement, captures stage-level timings, shuffle sizes, and slot utilization, then returns a detailed JSON plan.

Example output fields

start_time, end_time, slots_allocated, shuffle_output_bytes, and wait_ratio_avg reveal parallelism and bottlenecks.

How can I view job history?

Query region-us.INFORMATION_SCHEMA.JOBS_BY_USER to list your last 1,000 jobs with text, status, total_slot_ms, and error messages. Filter by creation_time to find problematic runs fast.

Which stage is the bottleneck?

Join JOBS_TIMELINE with JOBS_BY_JOB on job_id.Stage durations, pending ratios, and shuffles point to skewed joins, missing partitions, or blown-up cross joins.

How do I debug out-of-memory errors?

Check the EXPLAIN plan for distributedStageCount.High shuffle or bytes spilled to temp suggest adding filters earlier, using clustered tables, or breaking the query into smaller steps.

Best practices for faster debugging

2 Keep result sets small with LIMIT during tests.2 Materialize subqueries to temp tables for isolated inspection.2 Add ORDER BY NULL when ordering is unnecessary.2 Always compare dry-run bytes before and after changes.

Why How to Debug Queries in BigQuery is important

How to Debug Queries in BigQuery Example Usage


-- Find expensive stage in a slow join
EXPLAIN ANALYZE
SELECT c.name, SUM(oi.quantity*p.price) AS lifetime_value
FROM `mydb.Customers` c
JOIN `mydb.Orders` o ON o.customer_id = c.id
JOIN `mydb.OrderItems` oi ON oi.order_id = o.id
JOIN `mydb.Products` p ON p.id = oi.product_id
GROUP BY c.name
ORDER BY lifetime_value DESC;

How to Debug Queries in BigQuery Syntax


-- EXPLAIN logical plan
EXPLAIN SELECT o.id, c.name, o.total_amount
FROM `mydb.Orders` o
JOIN `mydb.Customers` c USING (customer_id);

-- EXPLAIN ANALYZE with detailed metrics
EXPLAIN ANALYZE SELECT oi.order_id, p.name, oi.quantity, (oi.quantity*p.price) AS line_total
FROM `mydb.OrderItems` oi
JOIN `mydb.Products` p USING (product_id);

-- Dry run to estimate cost
DECLARE _ UNUSED INT64;
SELECT _ FROM `mydb.Orders` OPTIONS (dry_run=TRUE);

-- View last 100 failed jobs
SELECT job_id, error_result.message
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE state = 'DONE' AND error_result IS NOT NULL
ORDER BY creation_time DESC
LIMIT 100;

Common Mistakes

Ignoring EXPLAIN and relying on guesswork. Without the plan, you miss shuffle size and stage-level costs. Always run EXPLAIN first to target the real hotspot.
Running full data tests instead of dry runs. Executing the entire dataset for each tweak wastes slots and money. Use OPTION (dry_run=TRUE) to validate syntax and scan size before full runs.

Frequently Asked Questions (FAQs)

Can I debug without rerunning the query?

Yes. Use job history views. JOBS_BY_JOB stores the execution plan and statistics of completed jobs, so you can inspect them later without re-execution.

Does EXPLAIN ANALYZE cost extra?

It reruns your query, so you pay for the additional slot time and bytes processed. Use it only after narrowing the problem with EXPLAIN or dry runs.

How do I share a plan with teammates?

Click "Query Plan" in the BigQuery UI and copy the JSON. Or save the JSON returned by EXPLAIN ANALYZE into Galaxy Collections for collaborative review.