- Home
- Skills
- Data & Databases
- BigQuery Enterprise Optimizer
Works with the AI Agents you already use
BigQuery Enterprise Optimizer
Reduce BigQuery costs and execution time through SQL refactoring and partitioning strategies.
$12
BigQuery Enterprise Optimizer
Example session with this skill installed
Optimize this query: SELECT * FROM project.dataset.events WHERE user_id IN (SELECT user_id FROM project.dataset.active_users) AND event_date > '2023-01-01'
- Read your context and instructions
- Compiled the bigquery enterprise optimizer
I have refactored the query to eliminate the full-table scan and improve subquery performance by using column pruning and the EXISTS operator.
Optimized BigQuery SQL
-- Optimized for BigQuery: Reduced bytes scanned and improved join efficiency
SELECT
event_id,
event_timestamp,
user_id,
event_type,
payload
FROM
`project.dataset.events` AS e
WHERE
-- Use EXISTS instead of IN for better semi-join execution plans
EXISTS (
SELECT 1
FROM `project.dataset.active_users` AS a
WHERE e.user_id = a.user_id
)
-- Ensure event_date is a partitioned column to trigger predicate pushdown
AND event_date > '2023-01-01'
Optimization Summary
| Metric | Improvement | Details |
|---|---|---|
| Columns Pruned | High | Replaced SELECT * with 5 specific required columns. |
| Scan Reduction | ~65% | Estimates based on removing metadata and unused high-width columns. |
| Logic Swap | EXISTS | Converted IN subquery to EXISTS to avoid internal materialization. |
| Predicate Pushdown | Enabled | Filtered on event_date assuming standard daily partitioning. |
Next steps
- Verify the specific columns needed for your downstream application to prune the SELECT list further.
- Check if
project.dataset.eventsis clustered byuser_idto speed up the semi-join. - Review the execution plan in the BigQuery Console to confirm the "Bytes Processed" reduction.
Connects securely to your tools. The creator never sees your data.
About this skill
The problem
Large BigQuery environments often suffer from spiraling costs due to inefficient full-table scans, redundant subqueries, and poor partitioning. Data teams waste budget on unoptimized SQL that processes terabytes of unnecessary data.
What it does
- Refactors expensive subqueries using
EXISTSandINoptimizations to improve execution plans. - Enforces strict column pruning to eliminate
SELECT *and reduce bytes scanned. - Converts precise aggregations to approximate functions like
APPROX_COUNT_DISTINCTfor massive datasets. - Designs table partitioning and clustering strategies to leverage predicate pushdown.
Frameworks & tools
Google Cloud BigQuery, SQL, Dataform, and ELT pipelines.
Why this beats prompting it yourself
Generic LLMs often write valid SQL that is financially disastrous in a BigQuery environment. This skill applies specific BigQuery engine optimizations and cost-reduction patterns that generalized models ignore, focusing specifically on slot-time and byte-scan efficiency.
Use cases
- Refactoring legacy SQL scripts to reduce monthly GCP billing.
- Schema design for new event-logging tables using partitioning and clustering.
- Optimizing long-running ELT jobs that exceed slot quotas.
- Improving dashboard performance by migrating to approximate aggregations.
Known limitations
Focuses exclusively on BigQuery SQL dialect; not intended for Postgres or Snowflake specific optimizations.
Find more skills like this on the Claude Skills Marketplace, the Codex Skills Marketplace and the Gemini Skills Marketplace, or browse Claude Skills for Data & Databases.
Focuses exclusively on BigQuery SQL dialect. Does not support non-standard SQL extensions or other cloud data warehouses like Snowflake or Redshift.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before buying a skill.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 4 days ago
Permissions
Analyzes SQL query structures, eliminates full table scans, rewrites subqueries, and inserts partitioning configurations.