/bigquery-optimization

name: bigquery-optimization
metadata:
  version: "1.0.0"
  category: BigDataAndAnalytics
description: >-
  Provides workflows to optimize BigQuery environments (capacity planning,
  editions), storage assets (partitioning, clustering, storage lifecycles,
  billing models), and SQL queries. Use when optimizing cost, modeling Edition
  migrations, rightsizing reservations, evaluating logical vs. physical storage,
  designing table partitioning/clustering, generating table DDL, migrating
  unpartitioned tables, managing partition expiration, or optimizing individual
  SQL queries.

  Do not use for raw usage reporting (use bigquery-observability), query
  execution plan analysis, error troubleshooting, or diagnosing why a specific
  job was slow (use bigquery-troubleshooting).

BigQuery Optimization Workflow

Prerequisites & Environment Setup

Before executing optimization analyses, evaluating editions, or applying DDL modifications:

1. **Google Cloud SDK**: Ensure the [Google Cloud SDK](https://cloud.google.com/sdk/docs/install) is installed and configured. 2. **Project Selection**: Set the active Google Cloud project:

    gcloud config set project {project_id}

3. **API Enablement**: Ensure BigQuery and BigQuery Reservation APIs are enabled:

    gcloud services enable \
        bigquery.googleapis.com bigqueryreservation.googleapis.com

4. **Authentication**: Authenticate the environment:

• CLI tools and `bq` commands: `gcloud auth login`
• SDKs and automation: `gcloud auth application-default login`
• Service accounts: Set

`GOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"`

5. **Billing & IAM Roles**:

• Verify an active Google Cloud Billing account is attached to

`{project_id}`.

• Ensure appropriate IAM roles:
• `roles/bigquery.admin` or `roles/bigquery.resourceAdmin`:

Reservation and capacity commitment management.

• `roles/bigquery.dataEditor` or `roles/bigquery.admin`: Modifying

table schemas, partitioning, clustering, and storage billing models.

• `roles/bigquery.jobUser`: Running evaluation queries.

6. **Companion Skills Installation**: This skill is part of a 3-pillar operations suite (`bigquery-observability`, `bigquery-optimization`, `bigquery-troubleshooting`). If any companion skill is not yet installed in your environment, install the full suite:

    npx skills add google/skills --skill bigquery-observability --skill bigquery-optimization --skill bigquery-troubleshooting

*(If `bigquery-observability` is not installed, use the self-contained baseline formulas and query templates provided directly in the reference sections below).*

Workflows

Determine the optimization focus of the user's request and follow the relevant workflow:

• **Telemetry & Observability Baseline:** For direct raw usage telemetry,

`INFORMATION_SCHEMA` queries, and baseline metric calculations, consult [bigquery-observability](../bigquery-observability) (`bigquery_observability`). If the `bigquery-observability` companion skill is not available in the active environment, all optimization guidelines, DDL templates, and decision models across this skill and its reference guides are fully self-contained.

• **Capacity & Editions Modeling:** Evaluate the cost-efficiency of migrating

workloads from On-Demand to Editions, as well as rightsizing active Edition reservations, baseline commitments, and autoscaling caps.

• *Instructions:* Read `references/capacity_planning_editions.md` to

provide deep links to BigQuery's built-in recommendation UIs (e.g., Slot Estimator) and guide the user through UI navigation: 1. navigate to the Slot Estimator tab, 2. select 'On-Demand' as the source to analyze historical query volume, and 3. review the Cost-Optimized Recommendations and Slot Usage Chart.

• **Table & Storage Optimization:** Optimize storage costs from a billing

model, physical layout, and lifecycle perspective.

• *Billing Architecture:* Read `references/storage_billing_models.md` for

guidance on evaluating aggregate compression ratios (e.g. >2:1 threshold in US) to recommend Physical vs. Logical billing, noting that the break-even ratio depends on specific regional rates and custom enterprise contracts. When providing `TABLE_STORAGE` queries, always scope with `WHERE table_schema = '{dataset_id}'`, use the regional dataset view, and warn that 0 rows indicates a region mismatch or lack of native tables rather than zero billable usage.

• *Partitioning & Clustering Strategy:* Read

`references/table_partitioning_clustering.md` to generate production DDL templates (CREATE TABLE, CTAS migrations for unpartitioned tables, and modifying clustering specifications), enforce pruning with `require_partition_filter = true`, and manage partition limits (up to 10,000 partitions/table).

• *Lifecycle Management:* Read

`references/storage_lifecycle_management.md` to pinpoint inactive data and define precise Time-to-Live (TTL) partition expirations, dataset expirations, and Time Travel window reductions.

• **SQL Optimization:** Optimize individual SQL queries to reduce slot-time

and the amount of data read.

• *Instructions:* Follow the instructions in

`references/sql_optimization.md` to provide recommendations to the user on how to rewrite their SQL query to reduce slot-time and the amount of data read.

Execution Guardrails

• **Terminology & Cost Framing:** Never promise or guarantee "cost-reduction"

or "reducing expenditure." Always frame recommendations using the terminology **"optimizing your bill"** or **"improving cost-effic