Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    BigQuery Enterprise Optimizer

    1

    Reduce BigQuery costs and execution time through SQL refactoring and partitioning strategies.

    $12

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned
    BigQuery Enterprise Optimizer

    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. 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. 2

      Sign in to Agensi

      Your agent shows a sign-in link. Sign in and click Allow.

    3. 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

    Read Files
    Write Files

    Analyzes SQL query structures, eliminates full table scans, rewrites subqueries, and inserts partitioning configurations.

    Listed1 month ago
    Updated4 days ago

    What's inside

    Frequently Asked Questions

    This skill identifies and refactors SQL patterns that trigger excessive byte scans, such as redundant subqueries and full-table scans, directly lowering your BigQuery slot-time and monthly GCP bill.

    The skill is designed to work with any AI agent or platform that supports external skill integration and has access to your BigQuery environment or SQL scripts.

    No, this skill is purpose-built for the BigQuery engine and its unique pricing model; it is not recommended for use with other data warehouses like Snowflake, Redshift, or Postgres.

    You receive the full BigQuery optimization logic, pre-configured cost-reduction patterns, and documentation on how to integrate the skill into your existing data workflows or Dataform pipelines.

    Setup involves importing the skill into your preferred AI agent and providing it with your existing SQL scripts or schema definitions for analysis; no direct database write-access is required.

    Yes, buyers receive lifetime access to version updates, ensuring the optimizer stays current with new BigQuery features and evolving Google Cloud performance best practices.

    New in Data & Databases