/snowflake-query-optimization

name: snowflake-query-optimization
description: |
  End-to-end Snowflake query optimization — fetch, rewrite, prove parity, benchmark, report. Use when:
  (1) User wants an **end-to-end flow** (fetch + rewrite + parity + benchmark + report), not just a rewrite
  (2) Input is a `.sql` file path in the repo (optionally `:L10-L80` line range), OR a Snowflake `query_id` (UUID)
  (3) User expects a correctness proof (row-count + per-column signature + multiplicity) before accepting the rewrite
  (4) Task says "optimize this query end-to-end", "tune this file and prove it's safe", "speed up query_id X with a parity check"
  **Prefer `optimizing-query-text`** for a pasted SQL string with no parity requirement (returns rewritten SQL only).
  **Prefer `optimizing-query-by-id`** for a quick profile-driven rewrite on a `query_id` with no parity re-run.
  Target wall time: ≤5 min (parity exempt — see Time budget).

snowflake-query-optimization

**Input (one of)** → fetch SQL → optimize → parity-validate → benchmark → report.

Delegates warehouse work to `altimate-code` when available; falls back to direct Snowflake SQL otherwise. Does not reinvent query-profile parsing.

When NOT to use this skill

• Pasted SQL text, no parity needed → `optimizing-query-text`.
• Quick rewrite from a `query_id`, no re-run or parity → `optimizing-query-by-id`.
• Finding candidate queries to optimize → `finding-expensive-queries`.
• Refactoring a dbt model → `snowflake-dbt-model-refactoring`.

Prerequisites

Before invoking, verify what's actually available — do **not** fabricate results from file reads alone.

command -v altimate-code     # preferred delegate
command -v snowsql           # fallback client (any Snowflake client works)

Must have (one of):

• `altimate-code` installed and configured with a Snowflake connection, OR
• a working Snowflake client (`snowsql`, dbt adapter, Python with

`snowflake-connector-python`, etc.) and credentials for the account.

Grants required on the Snowflake role:

• `SELECT` on `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` (preferred), or

the ability to run `INFORMATION_SCHEMA.QUERY_HISTORY()` (fallback).

• `USAGE` on the warehouse referenced by the baseline query (needed to

re-run it for comparison).

If neither path is available, stop and tell the user.

Time budget

Target end-to-end runtime: **≤5 min** (300s), hard ceiling **6 min** (360s), **excluding Phase 4 parity + Phase 5 benchmark re-runs**, which scale with the baseline query's cost and are **exempt from the cap**. A query being optimized is often slow by nature — forcing parity into a 90s window would push the agent to skip the mandatory hard stop.

Per-phase caps (sum ≤ 300s, parity/benchmark separate): Phase 1 ≤30s · Phase 2 ≤45s · Phase 3 ≤75s · Phase 4 overhead ≤60s (plus re-run time) · Phase 5 overhead ≤30s (plus one re-run of the optimized query). If a non-parity phase exceeds its budget, stop the extra work and move on.

Inputs — pick ONE

1. **Repo path**: `path/to/file.sql` (optionally with `:L10-L80` line range) 2. **Snowflake `query_id`** (UUID)

If neither is given, ask the user once, then proceed.

Workflow

Phase 1 — Fetch the query (≤30s)

**Repo path:** use the Read tool with the line range (do NOT shell out to `sed`; the project convention is Read / `cat`). If the file is a dbt model (contains `{{ ref() }}`, `{{ source() }}`, `{{ config() }}`, or other Jinja), the raw text is NOT executable — run `dbt compile --select <model>` first and read the compiled SQL from `target/compiled/.../<model>.sql`. Only the compiled form can be sent to Snowflake.

**query_id:**

SELECT query_type, query_text, warehouse_name, warehouse_size,
       total_elapsed_time/1000 AS seconds,
       bytes_scanned/1e9       AS gb_scanned,
       bytes_spilled_to_local_storage/1e9  AS gb_spill_local,
       bytes_spilled_to_remote_storage/1e9 AS gb_spill_remote,
       partitions_scanned, partitions_total, rows_produced,
       start_time
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_id = '<query_id>';

**Fallback for recent baselines:** `ACCOUNT_USAGE.QUERY_HISTORY` lags up to ~45 min, so a `query_id` from the last 45 min may return zero rows. If the row count is zero (not just "not readable"), fall back to:

-- Scope to the user, not the current session — the baseline query_id
-- was almost certainly run in a different session.
SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(
  USER_NAME => CURRENT_USER(),
  END_TIME_RANGE_START => DATEADD(hour, -2, CURRENT_TIMESTAMP()),
  RESULT_LIMIT => 1000
))
WHERE query_id = '<query_id>';

If the baseline was run by another user, use `INFORMATION_SCHEMA.QUERY_HISTORY(END_TIME_RANGE_START => ..., RESULT_LIMIT => ...)` with the account-wide scope (requires the warehouse monitor privilege). Do NOT use `QUERY_HISTORY_BY_SESSION()` for a baseline fetched by `query_id` — it only returns the current session's queries, and the baseline is almost always from a different session.

**Single-session requirement for Phases 4 + 5.** The temp tables created in Phase 4 and the `ALTER SESSION SET USE_CACHED_RESULT = FALSE` setting are session-scoped. All of Phase 4 (parity) and Phase 5 (benchmark) must run in **one connected Snowflake session** — not separate `snowsql -q` invocations or separate altimate-code calls.

If the only available client is per-call (one shell per statement), use `scripts/parity_bench.sh`. The script has **four subcommands** — `materialise`, `names`, `bench`, `cleanup` — and persists table names to a state file under `$(git rev-parse --git-dir)/skill/sqo/`, so later subshell invocations can find them. State does not depend on an in-process shell variable surviving across Bash tool calls.

# Prerequisite: SNOWFLAKE_RUN_SQL must be configured to exit non-zero
# on SQL errors. For snowsql, that is `-o exit_on_error=true`:
export SNOWFLAKE_RUN_SQL='snowsql -c myconn -o exit_on_error=true'
export SQO_D