bigquery-optimization
Skill1 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
- justvinhhere/bigquery-expertword for word, first seen Apr 9, 2026
Other versions3 projects use a version of this.
Word for word · 1
- justvinhhere/bigquery-expert★ 16100% 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.
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: line 35
Use LIKE instead of REGEXP_CONTAINS for simple wildcard matches.
- Distinctive: line 36
Place the largest table first in JOINs.
- Very specific: line 37
Always add LIMIT when using ORDER BY unless ordering is required for correctness.
- Very specific: line 38
Use ARRAY_AGG instead of ROW_NUMBER() for "latest record per group" patterns.
- Distinctive: line 41
Check the query against all 11 anti-patterns.
- Distinctive: line 44
Always preserve query semantics -- never change what data the query returns.
- Distinctive: line 45
If no anti-patterns are found, explicitly state: "No anti-patterns detected.
- Very specific: line 76
SimpleSelectStar: only applies to simple single-table queries.
- Distinctive: line 77
OrderByWithoutLimit: only applies to the outermost query.
≈ “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.
Files
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
LIKEinstead ofREGEXP_CONTAINSfor simple wildcard matches. - Place the largest table first in JOINs.
- Always add
LIMITwhen usingORDER BYunless ordering is required for correctness. - Use
ARRAY_AGGinstead ofROW_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 varfor single-value subqueries, orDECLARE var ARRAY<type>+UNNEST(var)for multi-value (IN) subqueries.
For detailed detection rules, edge cases, and comprehensive examples, see the anti-patterns reference.
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
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.