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

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. Run the deterministic analyzer.
5. Corroborate every causal hypothesis against a competing explanation.
6. 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.

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.

5. Build competing explanations

For every hypothesis, include at least one competing explanation and the next read-only test. Examples:

• Remote spill: query shape or capacity pressure; compare the same query hash and data volume before proposing resize.
• Join expansion: valid many-to-many semantics or missing/incorrect join condition; inspect approved redacted predicates and baseline row counts.
• Full partition scan: required full-table workload or ineffective pruning; compare the filter and table layout without changing clustering.
• Queue time: concurrency pressure, warehouse provisioning, or workload placement; correlate the same interval before changing capacity.
• Transaction block: identify blocker/waiter evidence and ownership; do not terminate a session automatically.

6. Produce a query root-cause packet

Use references/output-contract.md. Required contents:

• query identity, execution state, collection role, and source freshness;
• timeline decomposition;
• top operators by observed time contribution;
• confirmed observations, derived metrics, and hypotheses in separate sections;
• Query Insights with their documented limitations;
• comparison to a baseline only when inputs are aligned;
• one-variable experiment plan with owner approval;
• explicit statement that no mutation occurred.

Validation

Before delivery, verify that the packet names the query ID and evidence surfaces, gives actual source timestamps, contains no raw query text or credentials, and keeps all three confidence classes separate. Re-run the analyzer on the saved normalized JSON; the machine-readable result must be identical for identical input. If a proposed experiment appears, confirm it changes one variable, uses a user-supplied success objective, names an approver, and has not been executed.

Output

Return query-forensics.json and query-forensics.md in the user's chosen working directory, plus the exact analyzer command used. The packet includes identity and freshness, confirmed observations, estimated/derived metrics, at-risk hypotheses, top operators, warnings, non-claims, and a one-variable experiment proposal only when the user supplies a success objective. Do not write runtime output into the skill directory.

Stop conditions

Return a partial or inconclusive packet rather than guessing when:

• the query ID is missing and the history request is unbounded;
• the query is running, older than the operator-stat window, or operator access fails;
• Account Usage is too delayed for the incident window;
• secure objects, Native Apps, reused results, multi-step plans, or other documented Query Insights exclusions apply;
• operator JSON is absent or malformed;
• the comparison run differs in data window, query hash, parameters, warehouse behavior, or cache state in ways that prevent a defensible conclusion;
• the next action would mutate production without new authorization.

Error Handling

Examples

“This query spilled remotely after yesterday’s release”

Confirm the query completed, collect operator statistics, and report the exact remote spill bytes and affected operator. Compare the same parameterized hash and aligned data volume. Return query-shape and capacity-pressure hypotheses separately; do not resize.

“Why did this MERGE wait for ten minutes?”

Decompose queue and transaction-blocked time from query history. If blocked time is present, identify the relevant transaction evidence through approved read-only surfaces. Do not cancel the blocker. The packet names the blocker owner and escalation path.

“Snowflake shows no Query Insights, so the query is healthy”

Reject that inference. Query Insights has documented exclusions. Use history and operator evidence, and report whether insights were unavailable, inapplicable, absent, or actually returned no rows.

Resources

• History and collection — source selection, latency, privileges, and redaction.
• Operator statistics — normalized fields and defensible interpretations.
• Query Insights boundaries — official insight types and exclusions.
• Output contract — root-cause packet structure and confidence labels.
• scripts/analyze_query_evidence.py — deterministic evidence validator and classifier.