# Bigquery SQL

> Provides BigQuery SQL query optimization techniques, execution best practices, and performance tuning rules for high-efficiency querying. Use when optimizing BigQuery SQL queries, reducing query costs, or designing performant SQL transformations.

- Skill: `gemini-cli-extensions-data-agent-kit-starter-pack/bigquery-sql` (Agent Skill)
- Install (CLI): `npx skillmds@latest add gemini-cli-extensions-data-agent-kit-starter-pack/bigquery-sql`
- Raw SKILL.md: https://api.skillmd.com/api/skills/gemini-cli-extensions-data-agent-kit-starter-pack/bigquery-sql/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: gemini-cli-extensions (https://skillmd.com/u/gemini-cli-extensions-data-agent-kit-starter-pack)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/gemini-cli-extensions-data-agent-kit-starter-pack/bigquery-sql

---


# BigQuery SQL Optimization

Performance and efficiency guidelines for BigQuery SQL queries. Includes rules
for column pruning, predicate pushdown, join optimization, and materialization
strategies.

## SQL Optimization Rules

> [!TIP] Always include a **"Summary of Optimizations"** section listing only
> the optimizations applied.

### Always Apply (Automatic)

-   **Column Pruning**: Remove unnecessary columns from all query stages.
-   **Common Subexpression Reuse**: Factor out identical expressions to avoid
    redundant computation.
-   **Predicate Pushdown**: Apply `WHERE` filters as early as possible.
-   **Early Aggregation**: Perform `GROUP BY` before joins when possible.
-   **Intermediate Materialization**: Choose `VIEW` vs `TABLE` for intermediate
    nodes based on efficiency.

#### Intermediate Node Strategy

-   **`VIEW`**: Small datasets or simple transformations.
-   **`TABLE`**: Large datasets, expensive computations, or nodes reused
    multiple times.

### Always Rewrite (Mandatory)

-   **`WHERE <col> IN (SELECT ...)`**: Replace With `WHERE EXISTS (SELECT 1 FROM
    ...)`
-   **`WHERE (SELECT COUNT(*) ...) > 0`**: Replace With `WHERE EXISTS (SELECT 1
    FROM ...)`

### Propose with Confirmation (Conditional)

-   **`UNION` → `UNION ALL`**: Faster (skips deduplication), but permits
    duplicate rows.
-   **`COUNT(DISTINCT)` → `APPROX_COUNT_DISTINCT`**: Faster and lower memory,
    but approximate.