Bigquery Optimization

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