bigquery-query-optimization

BigQuery Query Optimization Skill

Overview

Diagnoses and optimizes slow, expensive, or slot-constrained Google Cloud BigQuery SQL under the principle: less work → faster query. Reduces scanned bytes, stage shuffle, and slot CPU via partition/cluster pruning, broadcast JOIN ordering, and native SQL functions while enforcing concise artifact caps and fail-honest --dry_run reporting.

When to Use

Trigger Scenarios

• BigQuery queries hitting high byte scans, excessive slot time, shuffle bottlenecks, worker skew, or Resources Exceeded.
• Optimizing partition pruning, cluster filter alignment, and multi-table JOIN/window patterns.
• Scanning repositories (.sql, .py, .js, .ts, .java, .go) for BigQuery SQL anti-patterns.

When NOT to Use

• Traditional OLTP databases (PostgreSQL, MySQL, SQL Server): Route to sql-query-optimization.
• Lakehouse architecture & service selection: Route to gcp-data-engineering.
• Datastream CDC & SCD Type 2 merges: Route to cdc-scd-patterns.

Process

Phase 1: Static Linting & Fail-Honest Plan Diagnostics

1. Static Scan: Run python3 ./skills/bigquery-query-optimization/scripts/lint_sql_query.py [optional_path] across target directories.
2. Stage & Dry-Run Inspection: Check bq query --use_legacy_sql=false --dry_run and INFORMATION_SCHEMA.JOBS for max-vs-avg worker skew (APPROX_TOP_COUNT), intermediate shuffle volume, and CPU-heavy UDFs/distincts.
3. Fail-Honest Fallback (STATIC_ANALYSIS_ONLY / BLOCKED): If bq query --dry_run fails due to missing GCP ADC credentials or permissions, explicitly mark the report STATIC_ANALYSIS_ONLY / BLOCKED: Live plan not executed — <exact error> and NEVER invent byte-scan numbers or slot times.

Phase 2: BigQuery Optimization Rules

1. Column, Partition & Cluster Pruning: Replace SELECT * with explicit columns; filter on partition columns and align WHERE/JOIN predicates with clustered keys.
2. Broadcast JOIN Ordering & Early Filtering: Place the largest table first in FROM followed by decreasing sizes; push filters into CTEs/subqueries before joining; model 1:N structures with ARRAY<STRUCT> where appropriate.
3. Bounded Sorts, Approx Aggs & SQL UDFs: Always pair ORDER BY with LIMIT; use APPROX_COUNT_DISTINCT when ~1% tolerance fits; replace JS UDFs with native SQL UDFs.