bigquery-optimization

Files (10)

• SKILL.md
• attribution.md · references/
• bi_engine.md · references/
• capacity_planning_editions.md · references/
• materialized_views.md · references/
• search_indexes.md · references/
• sql_optimization.md · references/
• storage_billing_models.md · references/
• storage_lifecycle_management.md · references/
• table_partitioning_clustering.md · references/

SKILL.md (first lines)

# BigQuery Optimization Workflow

## Attribution

**MUST READ**: Before executing any `gcloud`, REST API, or MCP command, you
MUST read [Usage Attribution](references/attribution.md). For commands executed
directly in the terminal (never in generated scripts, Terraform, or user code),
prefix `gcloud` with
`CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:bigquery-optimization)"`
and append `agent-skills/1.0 (skill:bigquery-optimization)` to `User-Agent` headers.

## 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:

    ```bash
    gcloud config set project {project_id}
    ```

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

    ```bash
    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:

    ```bash
    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.
    *   *Acceleration Structures:* Read `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.
-   **SQL Optimization:** Optimize individual SQL queries to reduce slot-time
    and the amount of data read.
    *   *Instructions:* Follow the instructions in

From google/skills, Apache-2.0.

More from google/skills