- 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 ```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** 1. Verify the specific columns needed for your downstream application to prune the SELECT list further. 2. Check if `project.dataset.events` is clustered by `user_id` to speed up the semi-join. 3. 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 `EXISTS` and `IN` optimizations to improve execution plans. - Enforces strict column pruning to eliminate `SELECT *` and reduce bytes scanned. - Converts precise aggregations to approximate functions like `APPROX_COUNT_DISTINCT` for 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.
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 any payment.
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.