Snowflake Query Forensics

Snowflake Query Forensics

Overview

Build a read-only query root-cause packet from platform evidence. Distinguish observed facts, derived metrics, and hypotheses; isolate one variable for any later experiment.

Problem: A slow elapsed time does not identify whether compilation, execution, queueing, blocking, spill, pruning, or query shape is responsible, and some diagnostic surfaces are delayed or unavailable for particular query classes.

Outcome: A query-scoped packet that ranks observed operator evidence, preserves competing explanations, and defines the next read-only check without changing SQL or compute.

Prerequisites

• An exact query ID, bounded candidate set, or sanitized exported evidence file.
• Sanitized output collected by an operator through an approved read-only Snowflake session, or an equivalent exported evidence bundle.
• A role with visibility to the selected history surface; OPERATE or MONITOR on the warehouse when operator statistics are required.
• A completed query within the documented operator-stat retrieval window for operator analysis.
• One Snowflake UUID query ID repeated on the anchor history, operator-statistics, and Query Insights rows, plus a positive source-freshness bound in seconds.
• A writable local working directory. Use Write only for new local evidence and report artifacts; never use it to alter SQL or Snowflake state.

Safety and evidence contract

• Read-only only. Do not cancel queries, resize/resume/suspend warehouses, alter clustering, enable acceleration/search optimization, modify SQL, or change session or account policy on the user's behalf.
• Do not invoke Snowflake authentication from this skill. The operator runs the bounded collection queries through an approved read-only session and supplies only sanitized results. Never request environment variables, connection files, tokens, passwords, or keys.
• Do not require ACCOUNTADMIN. Query-history visibility and operator-stat access depend on the approved role. GET_QUERY_OPERATOR_STATS requires OPERATE or MONITOR on the warehouse. Report missing access; do not grant it.
• Operator evidence exists only for completed queries and only within the platform's documented retrieval window. Do not invent operator findings for running, too-old, or inaccessible queries. Every nonterminal query is a partial packet even when no operator or insight rows were supplied. A terminal full-evidence packet requires at least one bound operator row; otherwise binding is incomplete, completeness is blocked, and confirmed/derived/hypothesis sections stay empty.
• History surfaces have different windows and latency. Account Usage query history can lag; Information Schema history is more immediate but narrower. Record which surface produced every field. The normalized metadata.history_source and role must exactly match the receipted anchor row and source; a digest cannot repair a semantic mismatch.
• Do not mix anchor evidence across query IDs. Snowflake documents query IDs as UUID text strings; require that form and reject a history, operator, or insight row that names another query. Exclude an operator or insight row with no query ID and block completeness until the evidence is recollected with its anchor. Aligned comparison runs legitimately use other Snowflake UUID query IDs; keep them in query_runs, never attribute them to the anchor, and require the explicit comparison-alignment fields.
• Query text is sensitive. Do not export it by default. Use query ID, hashes, sanitized operator attributes, and an operator-approved redacted SQL fragment only when needed. The analyzer rejects unsafe identifier fields and recursively redacts credential-bearing or raw-SQL-like scalar text before either JSON or Markdown output, using a syntax-aware boundary: any explicit Authorization/Proxy-Authorization value, plus headerless standardized schemes (including the registered SCRAM-SHA-1/SHA-256 family) only when the token shape or a recognized sensitive parameter supplies credential evidence (ordinary capability/status words stay visible), password/token tails, and tokenized Snowflake statement families (including chained diagnostic/statement labels, empty prefixes, positional or named binds, quoted file URIs, object modifiers, and DML scripting across the full recognized statement-verb family). Ordinary authentication/OAuth status evidence, request counters, and prose beginning with “Select” must remain visible. Credential-adjacent has_* metadata is safe only when its value is an actual boolean.
• No universal thresholds. Record exact queue time, spill bytes, partitions, row counts, and operator-time percentages. Compare against the same workload's baseline or a user-supplied objective; do not invent “slow,” “high,” or “bad” cutoffs.

Before collection, read references/history-and-collection.md. For a completed query, read references/operator-statistics.md. When Query Insights is available, read references/query-insights-boundaries.md.

For bounded live history collection, use the shared read-only collector with an approved Snowflake CLI profile:

python3 "${CLAUDE_SKILL_DIR}/scripts/collect_snowflake_evidence.py" \
  --surface query --connection <approved-readonly-profile> \
  --source-max-age-seconds <positive-incident-bound> \
  --output snowflake-query-collector.json

Use normalized input schema 2.0 and collector receipt schema 2. Before analysis, apply the complete contract in History and collection, including exact anchor UUID, source, role, source-specific terminal status, anchor-only maximum time, reviewed SQL cap, and an independently preserved input digest. Treat an embedded checksum as consistency evidence, never authenticity. A missing or mismatched receipt field, stale anchor, cap hit, truncation, or absent external digest blocks confirmed, completeness, comparison, regression, and ROI claims. Keep aligned comparison runs in query_runs; do not attribute them to the anchor. Narrow or partition any truncated collection before making workload or absence claims.

Instructions

Follow this sequence:

1. Anchor the investigation to a query ID or bounded candidate set.
2. Select the history surface and record its freshness boundary.
3. Have the operator collect the minimal redacted history, operator, and insight fields through the approved read-only session.
4. Assemble the normalized bundle and record its digest at the trusted local boundary.
5. Run the deterministic analyzer with the separately preserved digest.
6. Corroborate every causal hypothesis against a competing explanation.
7. Deliver the read-only root-cause packet and stop before mutation.

1. Anchor the investigation

Require at least one of:

• exact query ID;
• bounded UTC window plus user, warehouse, tag, or query hash;
• a sanitized exported evidence JSON file.

Capture the symptom, expected behavior, comparison query/run if available, account, role, warehouse, execution state, and source timestamps. If the user only says “queries are slow,” first identify a bounded candidate set; do not scan unbounded history.

2. Select the evidence surface

• Use the Information Schema query-history function for recent client-generated query discovery when its narrower retention and row behavior fit the task.
• Use SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for longer historical comparisons, while disclosing its documented latency.
• Use QUERY_INSIGHTS only as an additional platform-detected signal; absence of an insight does not prove absence of a problem.
• Use GET_QUERY_OPERATOR_STATS(QUERY_ID) only after confirming the query completed, is within the supported retrieval window, and the role has warehouse visibility.

Use only the source-specific terminal statuses listed in History and collection. Unknown, running, queued, blocked, and warehouse-resume states remain nonterminal and fail closed. The bundled collector receipts Account Usage; Information Schema discovery remains partial without its own reviewed matching receipt.

The detailed choice table and bounded SQL are in references/history-and-collection.md.

3. Collect a minimal redacted bundle

Collect only fields needed to distinguish:

• compilation, execution, queue-overload, provisioning, repair, and transaction-blocked time;
• bytes scanned/written and partitions scanned/total;
• local and remote spill;
• operator input/output rows and time breakdown;
• warehouse name/size as observed at execution time;
• query hash/parameterized hash for comparison;
• platform Query Insight type IDs and messages.

Exclude raw query text by default. If literals or object names are relevant, have the operator provide a redacted fragment separately.

4. Run the deterministic classifier

Normalize the bundle to the schema in references/operator-statistics.md, then run:

python3 "${CLAUDE_SKILL_DIR}/scripts/analyze_query_evidence.py" \
  --input query-evidence.json \
  --json-out query-forensics.json \
  --markdown-out query-forensics.md

The analyzer emits:

• Confirmed observations — raw, positive delay/spill/insight/operator-time evidence supplied by Snowflake.
• Estimated or derived metrics — deterministic ratios such as join output/input and partitions scanned/total; these remain contextual, not severity judgments.
• At-risk hypotheses — possible causes that require corroboration, such as query shape versus warehouse capacity for spill or expected full scans versus missed pruning.

It never rewrites SQL, chooses a warehouse size, or assigns a root cause from a single metric.