Terminal
Terminal/Skill
Guides BigQuery cost and performance work: capacity and editions, partitioning and clustering, table DDL, storage billing and SQL query tuning.
By Google
Works with
npx skills add google/skills --skill bigquery-optimizationBefore executing optimization analyses, evaluating editions, or applying DDL modifications:
Google Cloud SDK: Ensure the Google Cloud SDK is installed and configured.
Project Selection: Set the active Google Cloud project:
gcloud config set project {project_id}
API Enablement: Ensure BigQuery and BigQuery Reservation APIs are enabled:
gcloud services enable \
bigquery.googleapis.com bigqueryreservation.googleapis.com
Authentication: Authenticate the environment:
bq commands: gcloud auth logingcloud auth application-default loginGOOGLE_APPLICATION_CREDENTIALS="/path/to/key.json"Billing & IAM Roles:
{project_id}.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.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).
Determine the optimization focus of the user's request and follow the relevant workflow:
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.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.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.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).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.references/search_indexes.md to decide
when to propose a search index for selective lookups over STRING or
JSON data, write the CREATE SEARCH INDEX DDL, and tell the user how
to verify index coverage and usage. Read
references/materialized_views.md to decide when to propose a
materialized view for repeated aggregations or joins over large base
tables, write the CREATE MATERIALIZED VIEW DDL, and tell the user how
to verify that smart tuning uses it. Read references/bi_engine.md to
decide when to propose BI Engine vs. Materialized Views for BI dashboard
acceleration, diagnose PARTIAL or DISABLED BI Engine fallback
reasons (bi_engine_statistics), and combine BI Engine with
materialized views that pre-join or pre-aggregate the data.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.project_id and region at the very top of your response so the user
immediately knows the exact scope being evaluated. Follow this 3-tier
resolution hierarchy:
europe-west1).us / region-us, explicitly state
that us was assumed as the default, and instruct the user to
substitute their region if their resources reside elsewhere. Region
Formatting: In Cloud Console deep links, use the region identifier
directly (e.g., region=us, region=europe-west1). In SQL queries
against INFORMATION_SCHEMA, use the regional dataset qualifier (e.g.,
region-us, region-europe-west1).TABLE_STORAGE with WHERE table_schema = '{dataset_id}' returns 0 rows, do not proceed with an empty
or zero-usage evaluation. Treat this as an indicator that the dataset may
reside in a different region or have no native tables; stop and prompt the
user to confirm the dataset's regional location.project_id and region values directly into the
code and links. Never leave literal {project_id} or {location}
placeholders for the user to manually edit.gcloud or bq shell commands like bq update --storage_billing_model=...) designed to autonomously purchase
annual commitments, alter edition tier bindings, or mutate storage billing
models. Always guide the user to execute commitment purchases, reservation
changes, and storage billing model updates manually via the Cloud Console
UI.