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.

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;
• warehouse load and queue correlation for the same interval;
• query-hash/parameterized-hash comparisons across aligned runs;
• pruning fractions plus Search Optimization Service (SOS) ROI only when before/after latency or scan evidence and maintenance credits are supplied;
• 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, reports evidence_binding: BOUND and source_freshness: FRESH before making a completeness claim, contains no raw query text or credentials, includes at least one operator row bound to a surface-compatible terminal query, 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.
• Load, hash, pruning, and SOS — aligned load, fingerprint, operator, pruning, and Search Optimization evidence.
• Output contract — root-cause packet structure and confidence labels.
• scripts/analyze_query_evidence.py — deterministic evidence validator and classifier.