From snowflake-pack
Sets up Snowflake observability with ACCOUNT_USAGE SQL queries for query performance, errors, credits, storage trends, plus alerts and external dashboard integrations.
How this skill is triggered — by the user, by Claude, or both
Slash command
/snowflake-pack:snowflake-observabilityThis skill is limited to the following tools:
The summary Claude sees in its skill listing — used to decide when to auto-load this skill
Set up comprehensive observability for Snowflake using built-in ACCOUNT_USAGE views, Snowflake Alerts, and integration with external monitoring systems.
Set up comprehensive observability for Snowflake using built-in ACCOUNT_USAGE views, Snowflake Alerts, and integration with external monitoring systems.
SNOWFLAKE.ACCOUNT_USAGE (ACCOUNTADMIN or granted)-- === QUERY PERFORMANCE ===
-- Average query time by warehouse (last 7 days)
SELECT warehouse_name,
COUNT(*) AS query_count,
ROUND(AVG(total_elapsed_time) / 1000, 1) AS avg_seconds,
ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_elapsed_time) / 1000, 1) AS p95_seconds,
ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_elapsed_time) / 1000, 1) AS p99_seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(days, -7, CURRENT_TIMESTAMP())
AND execution_status = 'SUCCESS'
AND query_type = 'SELECT'
GROUP BY warehouse_name
ORDER BY avg_seconds DESC;
-- === ERROR RATE ===
-- Error rate by hour
SELECT DATE_TRUNC('hour', start_time) AS hour,
COUNT_IF(execution_status = 'SUCCESS') AS success,
COUNT_IF(execution_status = 'FAIL') AS failures,
ROUND(COUNT_IF(execution_status = 'FAIL') * 100.0 /
NULLIF(COUNT(*), 0), 2) AS error_rate_pct
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
GROUP BY hour
ORDER BY hour;
-- === CREDIT CONSUMPTION ===
-- Hourly credit usage
SELECT DATE_TRUNC('hour', start_time) AS hour,
warehouse_name,
SUM(credits_used) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
GROUP BY hour, warehouse_name
ORDER BY hour DESC, credits DESC;
-- === STORAGE GROWTH ===
-- Daily storage trend
SELECT usage_date,
ROUND(storage_bytes / 1e12, 3) AS storage_tb,
ROUND(stage_bytes / 1e12, 3) AS stage_tb,
ROUND(failsafe_bytes / 1e12, 3) AS failsafe_tb
FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
WHERE usage_date >= DATEADD(days, -30, CURRENT_DATE())
ORDER BY usage_date;
-- Alert: High error rate
CREATE OR REPLACE ALERT high_error_rate_alert
WAREHOUSE = ANALYTICS_WH
SCHEDULE = '15 MINUTE'
IF (EXISTS (
SELECT 1
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(minutes, -15, CURRENT_TIMESTAMP())
GROUP BY ALL
HAVING COUNT_IF(execution_status = 'FAIL') * 100.0 / COUNT(*) > 5
))
THEN
CALL SYSTEM$SEND_EMAIL(
'ops_notifications',
'[email protected]',
'Snowflake: Error rate > 5%',
'Query error rate exceeded 5% in the last 15 minutes.'
);
-- Alert: Warehouse stuck running (no auto-suspend)
CREATE OR REPLACE ALERT warehouse_running_alert
WAREHOUSE = ANALYTICS_WH
SCHEDULE = '60 MINUTE'
IF (EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.WAREHOUSES
WHERE state = 'STARTED'
AND DATEDIFF('hour', COALESCE(resumed_on, created_on), CURRENT_TIMESTAMP()) > 4
))
THEN
CALL SYSTEM$SEND_EMAIL(
'ops_notifications',
'[email protected]',
'Snowflake: Warehouse running > 4 hours',
'A warehouse has been running for over 4 hours. Check auto-suspend settings.'
);
-- Alert: Task failures
CREATE OR REPLACE ALERT task_failure_alert
WAREHOUSE = ANALYTICS_WH
SCHEDULE = '10 MINUTE'
IF (EXISTS (
SELECT 1
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
SCHEDULED_TIME_RANGE_START => DATEADD(minutes, -10, CURRENT_TIMESTAMP())
))
WHERE state = 'FAILED'
))
THEN
CALL SYSTEM$SEND_EMAIL(
'ops_notifications',
'[email protected]',
'Snowflake: Task Failure',
'One or more Snowflake tasks failed. Check TASK_HISTORY for details.'
);
-- Resume all alerts
ALTER ALERT high_error_rate_alert RESUME;
ALTER ALERT warehouse_running_alert RESUME;
ALTER ALERT task_failure_alert RESUME;
// src/snowflake/metrics-exporter.ts
// Export Snowflake metrics to Prometheus/Datadog
interface SnowflakeMetrics {
queryCount: number;
errorRate: number;
avgLatencyMs: number;
p95LatencyMs: number;
creditsUsed: number;
activeWarehouses: number;
}
async function collectSnowflakeMetrics(
conn: snowflake.Connection
): Promise<SnowflakeMetrics> {
const [queryStats] = await query(conn, `
SELECT
COUNT(*) AS query_count,
COUNT_IF(execution_status = 'FAIL') * 100.0 / NULLIF(COUNT(*), 0) AS error_rate,
AVG(total_elapsed_time) AS avg_latency,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_elapsed_time) AS p95_latency
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(minutes, -5, CURRENT_TIMESTAMP())
`).then(r => r.rows);
const [creditStats] = await query(conn, `
SELECT COALESCE(SUM(credits_used), 0) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= CURRENT_DATE()
`).then(r => r.rows);
const [whStats] = await query(conn, `
SELECT COUNT_IF(state = 'STARTED') AS active
FROM INFORMATION_SCHEMA.WAREHOUSES
`).then(r => r.rows);
return {
queryCount: queryStats.QUERY_COUNT,
errorRate: queryStats.ERROR_RATE,
avgLatencyMs: queryStats.AVG_LATENCY,
p95LatencyMs: queryStats.P95_LATENCY,
creditsUsed: creditStats.CREDITS,
activeWarehouses: whStats.ACTIVE,
};
}
// Prometheus exposition format
function formatPrometheus(metrics: SnowflakeMetrics): string {
return [
`snowflake_queries_total ${metrics.queryCount}`,
`snowflake_error_rate_percent ${metrics.errorRate}`,
`snowflake_avg_latency_ms ${metrics.avgLatencyMs}`,
`snowflake_p95_latency_ms ${metrics.p95LatencyMs}`,
`snowflake_credits_used_today ${metrics.creditsUsed}`,
`snowflake_active_warehouses ${metrics.activeWarehouses}`,
].join('\n');
}
-- Pipeline health dashboard
SELECT
'Tasks' AS component,
COUNT_IF(state = 'started') AS running,
COUNT_IF(state = 'suspended') AS suspended,
(SELECT COUNT_IF(state = 'FAILED')
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
SCHEDULED_TIME_RANGE_START => DATEADD(hours, -24, CURRENT_TIMESTAMP())
))) AS failures_24h
FROM INFORMATION_SCHEMA.TASKS
UNION ALL
SELECT 'Pipes',
COUNT_IF(is_autoingest_enabled = 'true'), 0,
0 -- Check PIPE_USAGE_HISTORY for errors
FROM INFORMATION_SCHEMA.PIPES
UNION ALL
SELECT 'Streams',
COUNT_IF(stale = FALSE),
COUNT_IF(stale = TRUE), 0
FROM INFORMATION_SCHEMA.STREAMS;
| Issue | Cause | Solution |
|---|---|---|
| ACCOUNT_USAGE latency | Views have up to 45min lag | Use INFORMATION_SCHEMA for real-time data |
| Alert not firing | Alert suspended | ALTER ALERT x RESUME |
| Metrics gaps | Warehouse suspended | Only active warehouses report metrics |
| Email not delivered | Notification integration misconfigured | Check ALLOWED_RECIPIENTS |
For incident response, see snowflake-incident-runbook.
npx claudepluginhub jeremylongshore/claude-code-plugins-plus-skills --plugin snowflake-packRuns Snowflake incident triage with status checks, SQL diagnostics for query/task failures, and decision tree. For outages, pipeline issues, and postmortems.
Monitors Databricks jobs, clusters, SQL warehouses, and costs using Unity Catalog system tables with SQL queries for health, performance, and billing insights.
Automates Snowflake data warehouse operations: list databases, schemas, tables, execute SQL, and manage workflows via Composio MCP.