justvinhherejustvinhhere/bigquery-expert/skills/bigquery-optimization/SKILL.md

bigquery-optimization

Skill16 starsChanged 7 months agoApache-2.0View on GitHub

Use when writing, reviewing, or optimizing BigQuery SQL, asking about BigQuery best practices, working with .sql files targeting BigQuery, or troubleshooting slow/expensive BigQuery queries. Symptoms: high slot consumption, full table scans, expensive joins, slow queries, high bytes billed.

Deletes or force-pushes

3 other projects use a version of this.

Adopted by

Most recently taken up by

GoogleCloudPlatform/data-agent-kit-plugintheir own version, first seen Nov 1, 2026
google/skillstheir own version, first seen Sep 29, 2026
Ocean1346/bigquery-expertword for word, first seen Apr 9, 2026

Other versions3 projects use a version of this.
Word for word · 1
Ocean1346/bigquery-expert★ 1100% same
Edited · 0
None found.
Wrote their own version · 2
google/skills★ 24ksame name
GoogleCloudPlatform/data-agent-kit-plugin★ 240same name
How matches are found. Word for word: the exact same text. Edited: shares passages with this file (“99% same” means 99% of its five-word phrases also appear here; 10% or more counts). Their own version: similar meaning, or, weakest, the same name. We can’t tell which project had it first. Projects are counted among the 200 most-starred candidates, and mentions are links to the repository we found, up to 50 per site, so both counts are a floor.
In this repository4 other files it gives its agents.
- bigquery-cost-optimizationSkill
- bigquery-featuresSkill
- bigquery-query-generationSkill
- bigquery-schema-designSkill

The file

We split this skill into the rules it gives an agent

9 rules. About 0 are common, 5 are distinctive, and 4 are very specific to this project.

≈0
Common · estimate
Many files like this have a rule of this kind.
≈5
Distinctive · estimate
This author's own choices. Worth a look.
4
Very specific
Tied to their project's names, files or tools. Probably skip.

Common
Distinctive
Very specific

Very specific:
Use LIKE instead of REGEXP_CONTAINS for simple wildcard matches.
line 35
Distinctive:
Place the largest table first in JOINs.
line 36
Very specific:
Always add LIMIT when using ORDER BY unless ordering is required for correctness.
line 37
Very specific:
Use ARRAY_AGG instead of ROW_NUMBER() for "latest record per group" patterns.
line 38
Distinctive:
Check the query against all 11 anti-patterns.
line 41
Distinctive:
Always preserve query semantics -- never change what data the query returns.
line 44
Distinctive:
If no anti-patterns are found, explicitly state: "No anti-patterns detected.
line 45
Very specific:
SimpleSelectStar: only applies to simple single-table queries.
line 76
Distinctive:
OrderByWithoutLimit: only applies to the outermost query.
line 77

≈ “Common” is an estimate: at least 1 in 5 of the 350,495 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.

Files

SKILL.md

BigQuery SQL Optimization

You are a BigQuery SQL optimization expert. When you encounter BigQuery SQL, evaluate it against the 11 known anti-patterns documented in the references. When writing new SQL, proactively avoid all anti-patterns.

Anti-Pattern Quick Reference

#	Name	What to Look For	Quick Fix	Severity
1	SimpleSelectStar	`SELECT *` on single-table query without JOINs or GROUP BY	Specify only needed columns	High
2	SemiJoinWithoutAgg	`IN`/`NOT IN` subquery without `DISTINCT` or `GROUP BY`	Add `DISTINCT` to subquery	Medium
3	CTEsEvalMultipleTimes	CTE (WITH) alias referenced more than once	Convert to `CREATE TEMP TABLE`	High
4	OrderByWithoutLimit	Outermost `ORDER BY` without `LIMIT`	Add `LIMIT` clause	Medium
5	StringComparison	`REGEXP_CONTAINS` with simple `.pattern.`	Use `LIKE '%pattern%'` instead	Low
6	LatestRecordWithAnalyticFun	`ROW_NUMBER()`/`RANK()` + `WHERE rn = 1`	Use `ARRAY_AGG(... ORDER BY ... LIMIT 1)`	High
7	DynamicPredicate	Subquery inside WHERE predicate	Extract to `DECLARE` variable or CTE	Medium
8	WhereOrder	AND predicates not ordered by selectivity	Reorder: `=` > `>`/`<` > `>=`/`<=` > `!=` > `LIKE` (advisory -- BigQuery's optimizer may reorder independently)	Low
9	JoinOrder	Smaller table on the left side of JOIN	Place largest table first (advisory -- optimizer usually handles this)	Low
10	MissingDropStatement	`CREATE TEMP TABLE` without corresponding `DROP`	Add `DROP TABLE` at end of script	Low
11	ConvertTableToTemp	`CREATE TABLE` + `DROP TABLE` in same script	Use `CREATE TEMP TABLE` instead	Low

Behavioral Rules

When Writing New SQL

Proactively apply all best practices. Never generate SQL that contains known anti-patterns.
Select only the columns needed, not SELECT *.
Use LIKE instead of REGEXP_CONTAINS for simple wildcard matches.
Place the largest table first in JOINs.
Always add LIMIT when using ORDER BY unless ordering is required for correctness.
Use ARRAY_AGG instead of ROW_NUMBER() for "latest record per group" patterns.

When Reviewing Existing SQL

Check the query against all 11 anti-patterns.
Report findings grouped by severity: High, Medium, Low.
For each finding, provide a before/after code example showing the fix.
Always preserve query semantics -- never change what data the query returns.
If no anti-patterns are found, explicitly state: "No anti-patterns detected. This query follows BigQuery best practices."

Review Output Format

## BigQuery SQL Review

### Findings

**[HIGH]** PatternName: Description of the issue found.
**[MEDIUM]** PatternName: Description of the issue found.

### Recommended Fixes

#### Fix 1: PatternName

**Before:**
(original SQL snippet)

**After:**
(optimized SQL snippet)

**Why:** Explanation of the performance/cost improvement.

### Summary
X anti-pattern(s) found (Y high, Z medium, W low).

Important Notes

JoinOrder requires knowledge of table sizes. If table sizes are unknown, flag it as advisory and recommend the user verify which table is larger.
SimpleSelectStar only applies to simple single-table queries. SELECT * with JOINs or GROUP BY is not flagged.
OrderByWithoutLimit only applies to the outermost query. ORDER BY inside subqueries or CTEs is acceptable.
DynamicPredicate has two fix patterns: use DECLARE var for single-value subqueries, or DECLARE var ARRAY<type> + UNNEST(var) for multi-value (IN) subqueries.

For detailed detection rules, edge cases, and comprehensive examples, see the anti-patterns reference.

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

It worked
Partly
It didn't

What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

Check your setup, free