Skip to content
Skills Library
Data & analytics · Review & audit

bigquery-query-optimization

/bigquery-query-optimization

Diagnoses and rewrites slow BigQuery SQL using the query plan (stages, shuffle, workers) — partitioning/clustering pruning, JOIN ordering and broadcast vs. shuffle joins, skewed JOINs, WHERE clause selectivity, and approximate/SQL-vs-JS function choices. Use when a BigQuery query is slow, expensive, or hitting Resources Exceeded, or when reviewing a query plan.

Licence
Apache-2.0
Last repository change
19.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
3
Scripts
1
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 19.10.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What to check before installing
  • 1 scripts in this package. Review them before installing, as Claude may execute them.

Scripts: scripts/lint_sql_query.py

What it does and when to use it

Diagnoses and rewrites slow BigQuery SQL using the query plan (stages, shuffle, workers) — partitioning/clustering pruning, JOIN ordering and broadcast vs. shuffle joins, skewed JOINs, WHERE clause selectivity, and approximate/SQL-vs-JS function choices. Use when a BigQuery query is slow, expensive, or hitting Resources Exceeded, or when reviewing a query plan.

Original author description

Diagnoses and rewrites slow BigQuery SQL using the query plan (stages, shuffle, workers) — partitioning/clustering pruning, JOIN ordering and broadcast vs. shuffle joins, skewed JOINs, WHERE clause selectivity, and approximate/SQL-vs-JS function choices. Use when a BigQuery query is slow, expensive, or hitting Resources Exceeded, or when reviewing a query plan.

How it works

  1. Overview
  2. When to Use
  3. Process
  4. Usage
  5. Red Flags
  6. Verification
  7. References
Original SKILL.md headings · 82 lines, 694 words
SKILL.md preview
---
name: bigquery-query-optimization
description: Diagnoses and rewrites slow BigQuery SQL using the query plan (stages, shuffle, workers) — partitioning/clustering pruning, JOIN ordering and broadcast vs. shuffle joins, skewed JOINs, WHERE clause selectivity, and approximate/SQL-vs-JS function choices. Use when a BigQuery query is slow, expensive, or hitting Resources Exceeded, or when reviewing a query plan.
---

# BigQuery Query Optimization Skill

## Overview
This skill diagnoses and optimizes slow, expensive, or resource-constrained Google Cloud BigQuery SQL queries. It acts as a BigQuery Performance Specialist operating under the core principle: **less work → faster query**. It focuses on reducing I/O (bytes read), shuffle (data transfer across stages), sorting/grouping overhead, and slot CPU consumption by analyzing execution stages, worker distributions, and pruning mechanics rather than guessing from SQL text alone.

## When to Use
### Trigger Scenarios
- BigQuery queries that run slowly, consume excessive slot time, or hit `Resources Exceeded` errors.
- Diagnosing execution stage statistics, shuffle bottlenecks, and data skew across workers.
- Optimizing table scans via partition pruning, clustering alignment, and column selection.
- Statically scanning SQL files and embedded application queries for BigQuery anti-patterns.

### When NOT to Use
- **Traditional relational databases (PostgreSQL, MySQL, SQL Server)**: Route to `sql-query-optimization`.
- **High-level GCP lakehouse and storage design**: Route to `gcp-data-engineering`.
- **CDC and dimensional SCD Type 2 modeling**: Route to `cdc-scd-patterns`.

## Process
### Phase 1: Diagnosing the Execution Plan
Inspect stage-level execution statistics via BigQuery UI or `INFORMATION_SCHEMA.JOBS`:
1. **Significant gap between average and max worker compute time**: Indicates data skew. Confirm skew with `APPROX_TOP_COUNT` on the suspect key and pre-filter or split queries.
2. **High wait/read time in intermediate stages**: Indicates excessive data passing through stages. Push filters earlier to reduce shuffle volume.
3. **High CPU time relative to I/O**: Check for expensive JavaScript UDFs, unneeded regex evaluation, or exact `COUNT(DISTINCT)` on massive cardinalities.

### Phase 2: Query Optimization Rules
1. **Column Pruning**: Replace `SELECT *` with explicit columns or `SELECT * EXCEPT(...)`.
2. **Partition & Cluster Pruning**: Filter on the partitioned date/timestamp and order `WHERE`/`JOIN` conditions to match clustered columns.
3. **Nested & Repeated Fields**: Model one-to-many relationships as `ARRAY<STRUCT>` instead of flat tables requiring expensive `GROUP BY` operations.
4. **JOIN Ordering (BigQuery-Specific)**: Place the **largest table first** in the `FROM` clause, followed by decreasing table sizes (allowing BigQuery to broadcast the smaller tables to worker slots).
5. **Filter Before JOINs**: Push filters into explicit subqueries before joining; do not rely solely on optimizer pushdown.
6. **ORDER BY with LIMIT**: Unbounded `ORDER BY` serializes data onto a single worker node and causes `Resources Exceeded`. Always supply a `LIMIT`.
7. **Approximate Aggregations**: Use `APPROX_COUNT_DISTINCT(col)` instead of `COUNT(DISTINCT col)` when ~1% error margin is acceptable.
8. **Native SQL UDFs**: Avoid JavaScript UDFs (which instantiate V8 subprocesses per call) in favor of native SQL UDFs.

### Phase 3: Automated Static Anti-Pattern Linting
Scan project SQL files or embedded SQL strings in application code (`.py`, `.js`, `.ts`, `.java`, `.go`):
```bash
python3 ./skills/bigquery-query-optimization/scripts/lint_sql_query.py [optional_path]
```

## Usage
### Commands & Automation Scripts
```bash
# Scan whole repository or specific SQL directory for BigQuery anti-patterns
python3 ./skills/bigquery-query-optimization/scripts/lint_sql_query.py .
python3 ./skills/bigquery-query-optimization/scripts/lint_sql_query.py queries/

# Dry run a rewritten query to verify byte reduction
bq query --use_legacy_sql=false --dry_run < optimized_query.sql
```

### Example Prompts
- *"This BigQuery query is throwing 'Resources Exceeded' during a large JOIN — diagnose the query plan and rewrite it."*
- *"Scan our repository for SQL anti-patterns like SELECT * or ORDER BY without LIMIT."*
- *"Rewrite this JavaScript UDF as a native BigQuery SQL function to reduce slot time."*

### Host Execution Instructions
- **Claude Code**: Run `lint_sql_query.py` in the workspace shell and execute dry-run estimations with `bq`.
- **Antigravity**: Analyze queries using `lint_sql_query.py` and inspect query execution jobs via GCP tools.

## Red Flags
- Putting small tables first in `FROM` clauses (violates BigQuery broadcast join optimization).
- Executing unbounded `ORDER BY` queries on multi-million row datasets.
- Querying partition-enabled tables without a filter on the partition column in `WHERE`.
- Using `REGEXP_CONTAINS` when a simple `LIKE` wildcard search is sufficient.
- Recomputing expensive, repetitive aggregations instead of leveraging Materialized Views.

## Verification
- [ ] Automated SQL linter reports zero high-severity anti-patterns:
  ```bash
  python3 ./skills/bigquery-query-optimization/scripts/lint_sql_query.py .
  ```
- [ ] BigQuery dry-run demonstrates measurable byte reduction compared to original query.
- [ ] No unbounded `ORDER BY` queries without `LIMIT`.
- [ ] Largest table appears first in multi-table `JOIN` statements.

## References
For before/after SQL query pairs and execution plan blueprints:
- [BigQuery Optimization Patterns Reference](references/bigquery-optimization-patterns.md)

Files in this package (3)
  • SKILL.md 6 KB
  • references/bigquery-optimization-patterns.md 6 KB
  • scripts/lint_sql_query.py 5 KB

Versions

RepositoryScoreLicence
This page · jggomez/expert-ai-developer-skills100Apache-2.0
jggomez/expert-ai-developer-skills100Apache-2.0

Identical content appears in 2 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version 182da3398edf (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100bigquery-patterns
    BigQuery SQL discipline: dry runs, byte caps, pruning, safe joins, integrity checks. Use when writing, reviewing or optimizing BigQuery SQL, or when a query is slow or expensive.
  • 100sql-query-optimization
    Diagnoses and rewrites slow SQL on traditional engines (Postgres, MySQL, SQL Server, etc.) using EXPLAIN/EXPLAIN ANALYZE — indexing strategy, WHERE clause selectivity, avoiding functions on indexed columns, JOIN vs. subquery vs. CTE tradeoffs, and pagination. Use for any query…
  • 100bigquery-data-warehouse-queries
    Use this skill when running petabyte-scale analytical SQL queries with partition and clustering optimizations in BigQuery.
  • 90bigquery-sql
    Provides BigQuery SQL query optimization techniques, execution best practices, and performance tuning rules for high-efficiency querying. Use when optimizing BigQuery SQL queries, reducing query costs, or designing performant SQL transformations.
  • 100spark-sql-catalyst-optimizer-tuning
    Use this skill when tuning Spark SQL query performance, broadcast joins, adaptive query execution (AQE), and shuffle partitions.
  • 100optimizing-pyspark-jobs
    Optimize slow or failing PySpark and Spark SQL jobs — partitioning and repartitioning, data skew, shuffles, broadcast joins, caching, Adaptive Query Execution, and avoiding driver collects and Python UDFs. Use when a Spark job is slow, spills, OOMs, has skewed tasks, runs a huge…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.