analyzing-snowflake

name	analyzing-snowflake
description	Use when working with Snowflake — snowflake data warehouse analysis, query performance tuning, cost optimization, warehouse management, and schema inspection. Covers QUERY_HISTORY analysis, credit consumption, warehouse utilization, storage analysis, data sharing, and Time Travel. Read this skill before any Snowflake operations — it enforces two-phase execution, anti-hallucination rules, and read-only safety constraints.
connection_type	snowflake
preload	false

Snowflake Analysis Skill

Analyze and optimize Snowflake data warehouse — cost, performance, and schema.

MANDATORY: Two-Phase Execution

Always discover databases, schemas, and tables before querying. Never assume object names.

Phase 1: Discovery

#!/bin/bash

snow_cmd() {
    snowsql -a "$SNOWFLAKE_ACCOUNT" \
            -u "$SNOWFLAKE_USER" \
            -p "$SNOWFLAKE_PASSWORD" \
            --warehouse "$SNOWFLAKE_WAREHOUSE" \
            -o output_format=tsv \
            -o header=false \
            -o timing=false \
            -q "$1" 2>/dev/null
}

echo "=== Databases ==="
snow_cmd "SHOW DATABASES;" | awk '{print $2, $4, $5}' | head -20

echo ""
echo "=== Warehouses ==="
snow_cmd "SHOW WAREHOUSES;" | awk '{print $1, $2, $6}' | head -10

echo ""
echo "=== Current Role & Context ==="
snow_cmd "SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();"

echo ""
echo "=== Schemas in Target Database ==="
snow_cmd "SHOW SCHEMAS IN DATABASE ${SNOWFLAKE_DATABASE};" | awk '{print $2, $3}' | head -20

After Phase 1: Only reference databases, schemas, and warehouses confirmed above.

Helper Function

#!/bin/bash

snow_query() {
    snowsql -a "$SNOWFLAKE_ACCOUNT" \
            -u "$SNOWFLAKE_USER" \
            -p "$SNOWFLAKE_PASSWORD" \
            --warehouse "${SNOWFLAKE_WAREHOUSE}" \
            --database "${SNOWFLAKE_DATABASE:-}" \
            --schema "${SNOWFLAKE_SCHEMA:-}" \
            -o output_format=tsv \
            -o header=false \
            -o timing=false \
            -q "$1" 2>/dev/null
}

Anti-Hallucination Rules

• NEVER reference a table without first running SHOW TABLES IN SCHEMA <schema>
• NEVER reference a column without first running DESCRIBE TABLE <table>
• NEVER assume warehouse names — always list via SHOW WAREHOUSES
• ALWAYS use LIMIT on all SELECT queries — never unbounded scans on production tables
• AVOID SELECT * from large tables — always specify columns after inspecting schema

Safety Rules

• READ-ONLY by default: SELECT, SHOW, DESCRIBE, INFORMATION_SCHEMA queries only
• FORBIDDEN without explicit request: INSERT, UPDATE, DELETE, DROP, TRUNCATE, CREATE
• ALWAYS add LIMIT — default cap 1000 rows for data queries
• Use INFORMATION_SCHEMA for metadata queries over SHOW where possible (SQL-standard)
• Warehouse suspension: Never suspend/resize warehouses without explicit user request

Common Operations

Cost & Credit Analysis

#!/bin/bash
echo "=== Credit Consumption Last 30 Days ==="
snow_query "
    SELECT
        DATE_TRUNC('day', START_TIME) AS day,
        WAREHOUSE_NAME,
        ROUND(SUM(CREDITS_USED), 2) AS credits,
        COUNT(*) AS query_count
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
    GROUP BY 1, 2
    ORDER BY 1 DESC, 3 DESC
    LIMIT 60;" | column -t

echo ""
echo "=== Top Warehouses by Cost (30 days) ==="
snow_query "
    SELECT
        WAREHOUSE_NAME,
        ROUND(SUM(CREDITS_USED), 2) AS total_credits,
        ROUND(SUM(CREDITS_USED_COMPUTE), 2) AS compute_credits,
        ROUND(SUM(CREDITS_USED_CLOUD_SERVICES), 2) AS cloud_service_credits,
        COUNT(*) AS sessions
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 10;" | column -t

echo ""
echo "=== Storage Costs ==="
snow_query "
    SELECT
        DATE_TRUNC('month', USAGE_DATE) AS month,
        ROUND(AVG(STORAGE_BYTES)/POWER(1024,3), 2) AS avg_storage_tb,
        ROUND(AVG(STAGE_BYTES)/POWER(1024,3), 2) AS avg_stage_tb,
        ROUND(AVG(FAILSAFE_BYTES)/POWER(1024,3), 2) AS avg_failsafe_tb
    FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
    WHERE USAGE_DATE >= DATEADD('month', -3, CURRENT_DATE())
    GROUP BY 1
    ORDER BY 1 DESC;" | column -t

Query Performance Analysis

#!/bin/bash
echo "=== Slowest Queries (last 24h) ==="
snow_query "
    SELECT
        QUERY_ID,
        QUERY_TYPE,
        WAREHOUSE_NAME,
        USER_NAME,
        ROUND(TOTAL_ELAPSED_TIME/1000, 1) AS elapsed_sec,
        ROUND(BYTES_SCANNED/POWER(1024,3), 2) AS scanned_gb,
        ROUND(CREDITS_USED_CLOUD_SERVICES, 4) AS credits,
        LEFT(QUERY_TEXT, 80) AS query_preview
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= DATEADD('hour', -24, CURRENT_TIMESTAMP())
      AND EXECUTION_STATUS = 'SUCCESS'
    ORDER BY TOTAL_ELAPSED_TIME DESC
    LIMIT 15;" | column -t

echo ""
echo "=== Queries with Full Table Scans ==="
snow_query "
    SELECT
        USER_NAME,
        WAREHOUSE_NAME,
        ROUND(PARTITIONS_SCANNED/NULLIF(PARTITIONS_TOTAL,0)*100, 1) AS pct_partitions_scanned,
        ROUND(BYTES_SCANNED/POWER(1024,3), 2) AS scanned_gb,
        ROUND(TOTAL_ELAPSED_TIME/1000, 1) AS elapsed_sec,
        LEFT(QUERY_TEXT, 80) AS query_preview
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= DATEADD('hour', -24, CURRENT_TIMESTAMP())
      AND PARTITIONS_TOTAL > 10
      AND PARTITIONS_SCANNED/NULLIF(PARTITIONS_TOTAL,0) > 0.9
    ORDER BY BYTES_SCANNED DESC
    LIMIT 10;" | column -t

echo ""
echo "=== Failed Queries (last 24h) ==="
snow_query "
    SELECT
        ERROR_CODE,
        ERROR_MESSAGE,
        COUNT(*) AS count,
        USER_NAME,
        WAREHOUSE_NAME
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= DATEADD('hour', -24, CURRENT_TIMESTAMP())
      AND EXECUTION_STATUS = 'FAIL'
    GROUP BY 1, 2, 4, 5
    ORDER BY 3 DESC
    LIMIT 10;" | column -t

Warehouse Utilization

#!/bin/bash
echo "=== Warehouse Utilization ==="
snow_query "
    SELECT
        WAREHOUSE_NAME,
        COUNT(*) AS total_queries,
        ROUND(AVG(TOTAL_ELAPSED_TIME/1000), 1) AS avg_elapsed_sec,
        ROUND(AVG(QUEUED_OVERLOAD_TIME/1000), 1) AS avg_queue_sec,
        MAX(QUEUED_OVERLOAD_TIME/1000) AS max_queue_sec,
        ROUND(SUM(BYTES_SCANNED)/POWER(1024,4), 2) AS total_tb_scanned
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 10;" | column -t

echo ""
echo "=== Warehouse Auto-Suspend Opportunities ==="
snow_query "
    SELECT
        WAREHOUSE_NAME,
        ROUND(SUM(CREDITS_USED), 2) AS credits_used,
        COUNT(DISTINCT DATE_TRUNC('hour', START_TIME)) AS active_hours,
        ROUND(SUM(CREDITS_USED)/NULLIF(COUNT(DISTINCT DATE_TRUNC('hour', START_TIME)), 0), 2) AS credits_per_hour
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
    GROUP BY 1
    HAVING credits_per_hour < 0.1  -- Barely used warehouses
    ORDER BY 2 DESC;" | column -t

Schema Analysis

#!/bin/bash
DB="${1:-$SNOWFLAKE_DATABASE}"
SCHEMA="${2:-PUBLIC}"

echo "=== Tables in $DB.$SCHEMA ==="
snow_query "
    SELECT
        TABLE_NAME,
        ROW_COUNT,
        ROUND(BYTES/POWER(1024,3), 3) AS data_gb,
        CLUSTERING_KEY,
        IS_TRANSIENT
    FROM ${DB}.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '${SCHEMA}'
      AND TABLE_TYPE = 'BASE TABLE'
    ORDER BY BYTES DESC NULLS LAST
    LIMIT 25;" | column -t

echo ""
echo "=== Tables Missing Clustering Keys (large tables) ==="
snow_query "
    SELECT TABLE_NAME, ROW_COUNT, ROUND(BYTES/POWER(1024,3), 2) AS data_gb
    FROM ${DB}.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '${SCHEMA}'
      AND CLUSTERING_KEY IS NULL
      AND BYTES > 10*POWER(1024,3)  -- > 10GB
    ORDER BY BYTES DESC
    LIMIT 10;" | column -t

Data Freshness / Time Travel

#!/bin/bash
DB="${1:-$SNOWFLAKE_DATABASE}"
TABLE="${2:?Table name required}"
SCHEMA="${3:-PUBLIC}"

echo "=== Table: $DB.$SCHEMA.$TABLE ==="
snow_query "DESCRIBE TABLE ${DB}.${SCHEMA}.${TABLE};" | head -20

echo ""
echo "=== Row Count ==="
snow_query "SELECT COUNT(*) FROM ${DB}.${SCHEMA}.${TABLE};"

echo ""
echo "=== Recent Changes (Time Travel — if enabled) ==="
snow_query "
    SELECT
        QUERY_ID,
        QUERY_TYPE,
        ROUND(ROWS_PRODUCED, 0) AS rows_affected,
        START_TIME
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE QUERY_TEXT ILIKE '%${TABLE}%'
      AND QUERY_TYPE IN ('INSERT', 'UPDATE', 'DELETE', 'MERGE', 'COPY')
      AND START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
    ORDER BY START_TIME DESC
    LIMIT 10;" | column -t

User Activity Audit

#!/bin/bash
echo "=== Top Users by Query Count (30 days) ==="
snow_query "
    SELECT
        USER_NAME,
        COUNT(*) AS query_count,
        ROUND(SUM(TOTAL_ELAPSED_TIME/1000/3600), 1) AS total_hours,
        ROUND(SUM(BYTES_SCANNED)/POWER(1024,4), 2) AS total_tb_scanned
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 10;" | column -t

echo ""
echo "=== Login History (failed logins) ==="
snow_query "
    SELECT
        USER_NAME,
        CLIENT_IP,
        FIRST_AUTHENTICATION_FACTOR,
        ERROR_MESSAGE,
        EVENT_TIMESTAMP
    FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
    WHERE IS_SUCCESS = 'NO'
      AND EVENT_TIMESTAMP >= DATEADD('day', -7, CURRENT_TIMESTAMP())
    ORDER BY EVENT_TIMESTAMP DESC
    LIMIT 15;" | column -t

Output Format

Present results as a structured report:

Analyzing Snowflake Report
══════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Shortcut	Counter	Why
"I'll skip discovery and check known resources"	Always run Phase 1 discovery first	Resource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"	Follow the full discovery → analysis flow	Quick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"	Audit configuration explicitly	Defaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"	Always check relevant metrics when available	API/CLI responses show current state; metrics reveal trends and intermittent issues
"I don't have access to that"	Try the command and report the actual error	Assumed permission failures prevent useful investigation; actual errors are informative

Common Pitfalls

• ACCOUNT_USAGE latency: Data in ACCOUNT_USAGE views has a 45-minute to 3-hour delay — for real-time use INFORMATION_SCHEMA (14-day retention, no delay)
• Credit vs dollar cost: Credits vary by cloud/region pricing — multiply by rate for dollar estimate; never assume $3/credit
• PARTITIONS_SCANNED null: Can be null for metadata-only queries — use NULLIF() in calculations
• Case sensitivity: Snowflake objects default to UPPERCASE when unquoted — "MyTable" vs MYTABLE are different
• SHOW output format: SHOW commands output varies by client — prefer INFORMATION_SCHEMA for programmatic use
• Warehouse auto-resume: Warehouses auto-resume on query — don't assume current SHOW WAREHOUSES status reflects recent queries
• Clustering depth: Poor clustering causes high PARTITIONS_SCANNED/PARTITIONS_TOTAL ratio — key signal for missing clustering keys
• Time Travel period: Default 1 day, Enterprise 90 days — always check DATA_RETENTION_TIME_IN_DAYS before querying historical data
• Cloud Services credits: If >10% of credits come from cloud services, there may be small query storms — check QUERY_HISTORY for high-frequency tiny queries