snowflake-query-optimization

# 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. ```bash 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:** ```sql 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: ```sql -- 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. ```bash # 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_DEV_SCHEMA='DEV_<USER>' # NEVER a shared schema (PII) ``` Lifecycle: ```bash # 1. Materialise once. Prints ORIG_TABLE=/OPT_TABLE= on stdout, writes # state to disk. Banner noise from the SQL client goes to stderr. eval "$(scripts/parity_bench.sh materialise /tmp/sqo-orig.sql /tmp/sqo-opt.sql)" : "${ORIG_TABLE:?materialise failed}" : "${OPT_TABLE:?materialise failed}" # 2. In any later shell invocation, pick the names back up: eval "$(scripts/parity_bench.sh names)" # 3. Run parity + benchmark against $ORIG_TABLE / $OPT_TABLE. For each # benchmark query, use `bench <sql_file>` so USE_CACHED_RESULT=FALSE # is applied inside the SQL client's session (per-call clients open # a fresh session each time, so the materialise ALTER doesn't carry). scripts/parity_bench.sh bench /tmp/parity.sql scripts/parity_bench.sh bench /tmp/benchmark.sql # 4. Explicit cleanup — do NOT bind this to EXIT in the materialise # shell. (Why: the materialise script exits as soon as it finishes # writing, and `trap EXIT` would then DROP the tables before any # later `bench` call could see them.) scripts/parity_bench.sh cleanup ``` The script addresses the four prior failures: - **Shell-generated `RUN_ID` with real fallbacks.** The old form used a Snowflake session variable that didn't survive across sessions; and the previous shell `(uuidgen | tr ... || date +%s%N)` fallback never fired because the pipeline's exit status came from `tr`, not `uuidgen`. `materialise` uses an explicit `if command -v uuidgen / elif command -v python3 / else date` chain so a missing `uuidgen` actually falls through, and `date +%s%N` on macOS is handled too. - **State persisted across shells.** `materialise` writes the table FQNs to a per-run state file under `.git/skill/sqo/`. `names` retrieves them in any later shell. In-memory `$ORIG_TABLE` from an earlier `eval` would evaporate between Bash tool calls; this doesn't. - **Honest cold-cache benchmarks.** `bench <sql>` prepends `ALTER SESSION SET USE_CACHED_RESULT = FALSE;` to every call, so per-call clients that open a fresh session each time still measure cold timings. Setting it once in `materialise` is insufficient — that session dies before `bench` opens a new one. - **User dev schema only, validated.** `SQO_DEV_SCHEMA` is REQUIRED (fail-closed) and must match a safe identifier regex (quoted or unquoted Snowflake identifier, optionally `DB.SCHEMA`). A schema like `'DB.X;touch /tmp/pwn'` is refused before any SQL is sent. - **Signals exit cleanly.** INT/TERM/ERR traps all drop any partials AND exit with the right code (130/143/1 respectively). The old form dropped partials but let bash keep going, then printed the already-dropped table names on stdout. - **Stdout only contains the eval payload.** `$SNOWFLAKE_RUN_SQL`'s stdout (banners, `Statement executed.` rows) is redirected to stderr so `eval "$(...)"` only receives `ORIG_TABLE=/OPT_TABLE=` lines. **MANDATORY HARD STOP — safety guard against DML/DDL re-execution.** This skill re-executes the fetched SQL during parity (Phase 4) and benchmark (Phase 5). Re-running a non-SELECT would mutate the warehouse. - If input was a `query_id`: require `query_type = 'SELECT'`. If it is `INSERT`, `UPDATE`, `MERGE`, `DELETE`, `CREATE_TABLE_AS_SELECT`, `COPY`, `CALL`, or any other mutating type, **stop and tell the user**: this skill will not re-execute DML/DDL from history. - If input was a repo path: inspect the fetched SQL. If the leading statement is not `SELECT` / `WITH ... SELECT`, stop with the same message. Save the baseline metrics — the final report needs them. ### Phase 2 — Diagnose (≤45s) If `altimate-code` is installed, prefer it. Pass the SQL via a temp file (not a shell-interpolated string — SQL can contain `"`, `$`, backticks, and Snowflake quoted identifiers like `"Order Id"` that break double-quoted shell strings): ```bash if command -v altimate-code >/dev/null 2>&1; then cat > /tmp/sqo-prompt.txt <<'EOF' Diagnose Snowflake query_id <id>: fetch operator stats, flag no-pruning, spill, SELECT *, join explosion, repeated subquery, unnecessary ORDER BY. Return a short bullet list of actionable fixes only. EOF altimate-code run "$(cat /tmp/sqo-prompt.txt)" \ --yolo --output /tmp/diagnose.md --dir "$(pwd)" fi ``` Then Read `/tmp/diagnose.md`. Otherwise, pull operator stats directly: ```sql SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS('<query_id>')); ``` Scan for these fixable issues only (ignore everything else): | Signal | Fix | |---|---| | `partitions_scanned == partitions_total` | Add filter on a cluster key | | `gb_spill_* > 0` | Reduce intermediate size (project early, filter early) | | TableScan with `SELECT *` | Project only used columns | | Join row explosion (output >> input) | Add join predicate / pre-filter | | Repeated subquery | Fold into a CTE | | `ORDER BY` without `LIMIT` in a non-final context | Drop it | Repo-only input (no profile available): rely on the static patterns above. ### Phase 3 — Rewrite (≤75s) Rewrite the query applying only the fixes identified. Semantic rules: - Same SELECT list order, same column aliases, same row grain. - Do not add or remove `LIMIT`. - Do not change `UNION` to `UNION ALL` (dedup semantics differ). - Keep `ORDER BY` in the outermost SELECT if the original had one there. - Prefer CTEs over inline subqueries for readability. - Keep comments and formatting consistent with the source file. If a change would alter the result set, **skip it** and note why. ### Phase 4 — Parity validation — MANDATORY HARD STOP Prove the rewrite is semantically identical. Use the same per-column, NULL-safe approach as `snowflake-dbt-model-refactoring` — not `HASH(*)` (which is not a valid Snowflake expression; `HASH` requires explicit arguments). **Disable result cache for honest comparisons** (one session setting covers Phases 4 + 5): ```sql ALTER SESSION SET USE_CACHED_RESULT = FALSE; ``` **Materialize once per side to avoid re-running the slow baseline multiple times.** Signature and multiplicity both scan the same rows, so land each result into a session-scoped temp table and compute both checks over the temp tables: ```sql CREATE TEMPORARY TABLE _sqo_orig_<run_id> AS <original_query>; CREATE TEMPORARY TABLE _sqo_opt_<run_id> AS <optimized_query>; ``` `<run_id>` is a timestamp or random suffix to avoid session-level collisions. Temp tables are session-scoped and auto-cleaned at session end — no explicit DROP needed. **Step 1 — enumerate the column list from the SELECT.** Parse the original query's final projection to get the ordered list `[col1, col2, ... colN]` with their aliases. **Step 2 — row count + per-column signature (one query, both sides):** ```sql WITH orig_agg AS ( SELECT COUNT(*) AS n, HASH_AGG(col1, col2, /* ...colN... */) AS sig FROM _sqo_orig_<run_id> ), opt_agg AS ( SELECT COUNT(*) AS n, HASH_AGG(col1, col2, /* ...colN... */) AS sig FROM _sqo_opt_<run_id> ) SELECT o.n AS orig_rows, p.n AS opt_rows, o.sig AS orig_sig, p.sig AS opt_sig, (o.n = p.n AND o.sig = p.sig) AS parity_ok FROM orig_agg o, opt_agg p; ``` `HASH_AGG` is an order-independent, multiset-aware aggregate on the expanded column list — this catches duplicate-row cases that a plain `HASH(... ,...)` would miss and is NULL-safe per-column. **Step 3 — multiplicity sanity check** (catches the `{A,B}` vs `{A,B,B}` case which can collide if two aggregates coincidentally match). Group each side by every column to get per-distinct-row counts, then full-outer-join the grouped sets and flag any row whose counts differ or that only exists on one side: ```sql WITH orig_grouped AS ( SELECT col1, col2, /* ...colN... */ COUNT(*) AS n FROM _sqo_orig_<run_id> GROUP BY col1, col2 /* ...colN... */ ), opt_grouped AS ( SELECT col1, col2, /* ...colN... */ COUNT(*) AS n FROM _sqo_opt_<run_id> GROUP BY col1, col2 /* ...colN... */ ) SELECT COUNT(*) AS diff_rows FROM orig_grouped o FULL OUTER JOIN opt_grouped p ON o.col1 IS NOT DISTINCT FROM p.col1 AND o.col2 IS NOT DISTINCT FROM p.col2 /* ...one line per column... */ WHERE COALESCE(o.n, 0) <> COALESCE(p.n, 0); ``` `IS NOT DISTINCT FROM` treats `NULL = NULL` as true (confirmed Snowflake syntax). `diff_rows` must be **0** for parity to pass. **Hard stop:** if either check fails (`parity_ok = FALSE` or `diff_rows > 0`), discard the rewrite and report the divergence — do not present an unsafe optimization. If `altimate-code` is installed, prefer delegating this entire phase. **Never shell-interpolate the SQL** — the baseline comes from `QUERY_HISTORY` and may contain `$(...)`, backticks, or quoted identifiers like `"Order Id"` that break the command or execute under `--yolo`. Write both SQL files with the **Write tool** (not `printf`, not `echo`), then reference the file paths in the prompt: 1. Use Claude Code's `Write` tool to create `/tmp/sqo-orig.sql` containing the original SQL verbatim. 2. Use the `Write` tool to create `/tmp/sqo-opt.sql` containing the optimized SQL verbatim. 3. Then invoke altimate-code with a prompt (also written via `Write` or a quoted heredoc) that references the files: ```bash if command -v altimate-code >/dev/null 2>&1; then # Files /tmp/sqo-orig.sql and /tmp/sqo-opt.sql must already exist,