bigquery-sql

Files (2)

• OWNERS
• SKILL.md

SKILL.md (first lines)

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

From nguyenhungtran18/skill-and-tool-tracker, MIT.

More from nguyenhungtran18/skill-and-tool-tracker