Monitoring Queries: Query History, Warehouse Utilization & Cost Analysis
Architecture Diagram 1: Monitoring Architecture
┌─────────────────────────────────────────────────────────────────────────────┐
│ SNOWFLAKE MONITORING ARCHITECTURE │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ DATA SOURCES │
│ ═════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ System Views & Tables: │ │ │
│ │ │ │ │ │
│ │ │ INFORMATION_SCHEMA: │ │ │
│ │ │ • QUERY_HISTORY (Recent queries) │ │ │
│ │ │ • WAREHOUSE_LOAD_HISTORY (Warehouse metrics) │ │ │
│ │ │ • SESSIONS (Active sessions) │ │ │
│ │ │ • DATABASES (Database metadata) │ │ │
│ │ │ │ │ │
│ │ │ SNOWFLAKE.ACCOUNT_USAGE: │ │ │
│ │ │ • QUERY_HISTORY (Historical queries) │ │ │
│ │ │ • WAREHOUSE_METERING (Credit consumption) │ │ │
│ │ │ • STORAGE_USAGE (Storage metrics) │ │ │
│ │ │ • LOGIN_HISTORY (Authentication logs) │ │ │
│ │ │ • ACCESS_HISTORY (Data access patterns) │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ │ Data Collection │
│ ▼ │
│ MONITORING PIPELINE │
│ ═══════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Real-time Monitoring: │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ • Active query monitoring │ │ │ │
│ │ │ │ • Warehouse utilization tracking │ │ │ │
│ │ │ │ • Session activity monitoring │ │ │ │
│ │ │ │ • Error rate tracking │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ Batch Analytics: │ │ │ │
│ │ │ │ • Daily usage reports │ │ │ │
│ │ │ │ • Weekly performance trends │ │ │ │
│ │ │ │ • Monthly cost analysis │ │ │ │
│ │ │ │ • Quarterly capacity planning │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ │ Analysis & Alerting │
│ ▼ │
│ ANALYTICS & DASHBOARDS │
│ ═══════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Performance Dashboards: │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ Query Performance: │ │ │ │
│ │ │ │ • Average query duration │ │ │ │
│ │ │ │ • Slow query identification │ │ │ │
│ │ │ │ • Query throughput (queries/hour) │ │ │ │
│ │ │ │ • Queue depth trends │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ Warehouse Utilization: │ │ │ │
│ │ │ │ • CPU/Memory utilization │ │ │ │
│ │ │ │ • Cluster scaling events │ │ │ │
│ │ │ │ • Auto-suspend/resume patterns │ │ │ │
│ │ │ │ • Credit consumption by warehouse │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ Cost Analysis: │ │ │ │
│ │ │ │ • Daily/monthly credit usage │ │ │ │
│ │ │ │ • Cost by user/workload │ │ │ │
│ │ │ │ • Optimization recommendations │ │ │ │
│ │ │ │ • Budget forecasting │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Alerting: │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ • Query queue depth > 10 │ │ │ │
│ │ │ │ • Query duration > 5 minutes │ │ │ │
│ │ │ │ • Credit usage > 80% of budget │ │ │ │
│ │ │ │ • Error rate > 5% │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Architecture Diagram 2: Query History Analysis
┌─────────────────────────────────────────────────────────────────────────────┐
│ QUERY HISTORY ANALYSIS │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ QUERY EXECUTION TIMELINE │
│ ═════════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Query ID: 01234567-89ab-cdef-0123-456789abcdef │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Execution Phases: │ │ │
│ │ │ │ │ │
│ │ │ 00:00:00 ─── Query Submitted │ │ │
│ │ │ │ │ │ │
│ │ │ ▼ │ │ │
│ │ │ 00:00:01 ─── Queued (Waiting for warehouse) │ │ │
│ │ │ │ Queue Depth: 5 │ │ │
│ │ │ │ Queue Time: 2.3 seconds │ │ │
│ │ │ ▼ │ │ │
│ │ │ 00:00:03 ─── Compilation │ │ │
│ │ │ │ Compilation Time: 0.5 seconds │ │ │
│ │ │ ▼ │ │ │
│ │ │ 00:00:04 ─── Execution │ │ │
│ │ │ │ Execution Time: 15.2 seconds │ │ │
│ │ │ │ Rows Scanned: 1,000,000 │ │ │
│ │ │ │ Partitions Scanned: 50 │ │ │
│ │ │ │ Partitions Total: 200 │ │ │
│ │ │ │ Pruning Efficiency: 75% │ │ │
│ │ │ ▼ │ │ │
│ │ │ 00:00:19 ─── Result Return │ │ │
│ │ │ │ Result Rows: 10,000 │ │ │
│ │ │ │ Result Size: 2.5 MB │ │ │
│ │ │ ▼ │ │ │
│ │ │ 00:00:19 ─── Query Complete │ │ │
│ │ │ Total Time: 19.5 seconds │ │ │
│ │ │ Status: SUCCESS │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ │ Analysis │
│ ▼ │
│ QUERY METRICS TABLE │
│ ════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Metric │ Value │ Status │ │ │
│ │ │ ────────────────────────┼────────────┼──────────────────── │ │ │
│ │ │ Total Duration │ 19.5 sec │ ✓ Normal │ │ │
│ │ │ Queue Time │ 2.3 sec │ ✓ Normal │ │ │
│ │ │ Compilation Time │ 0.5 sec │ ✓ Fast │ │ │
│ │ │ Execution Time │ 15.2 sec │ ✓ Normal │ │ │
│ │ │ Partition Pruning │ 75% │ ⚠ Could improve │ │ │
│ │ │ Rows Scanned │ 1,000,000 │ ✓ Acceptable │ │ │
│ │ │ Bytes Scanned │ 500 MB │ ✓ Within limits │ │ │
│ │ │ CPU Time │ 45.6 sec │ ✓ Normal │ │ │
│ │ │ Memory Usage │ 2.1 GB │ ✓ Within limits │ │ │
│ │ │ Spillage │ 0 bytes │ ✓ No spill │ │ │
│ │ │ Result Set Size │ 2.5 MB │ ✓ Small │ │ │
│ │ │ Credit Usage │ 0.0023 │ ✓ Low │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ │ Optimization Recommendations │
│ ▼ │
│ OPTIMIZATION INSIGHTS │
│ ═════════════════════ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Finding: Partition pruning could improve from 75% to 90% │ │ │
│ │ │ │ │ │
│ │ │ Recommendation: │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ 1. Add clustering key on frequently filtered columns │ │ │ │
│ │ │ │ 2. Review query patterns to identify optimal keys │ │ │ │
│ │ │ │ 3. Consider materialized views for complex joins │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ │ │ │ │
│ │ │ Potential Savings: │ │ │
│ │ │ • Reduce scanned data by 20% │ │ │
│ │ │ • Reduce execution time by 15% │ │ │
│ │ │ • Reduce credit usage by 18% │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Architecture Diagram 3: Cost Analysis Dashboard
┌─────────────────────────────────────────────────────────────────────────────┐
│ COST ANALYSIS DASHBOARD │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ MONTHLY COST OVERVIEW │ │
│ │ ═══════════════════════ │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ │ │ │
│ │ │ Total Credits Used: 15,450 / 20,000 budget │ │ │
│ │ │ ████████████████████░░░░░░░░░░░ 77.25% │ │ │
│ │ │ │ │ │
│ │ │ Estimated Cost: $46,350 (at $3/credit) │ │ │
│ │ │ Remaining Budget: $13,650 (22.75%) │ │ │
│ │ │ Days Remaining: 12 │ │ │
│ │ │ Projected Month-End: 18,900 credits (94.5% of budget) │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Cost Breakdown by Warehouse: │ │ │
│ │ │ │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ Warehouse │ Credits │ Cost │ % Total │ Trend │ │ │ │
│ │ │ │ ──────────────┼─────────┼─────────┼─────────┼────── │ │ │ │
│ │ │ │ ANALYTICS_WH │ 6,200 │ $18,600 │ 40.1% │ ↑ 12% │ │ │ │
│ │ │ │ ETL_WH │ 4,800 │ $14,400 │ 31.1% │ ↓ 5% │ │ │ │
│ │ │ │ REPORTING_WH │ 2,500 │ $7,500 │ 16.2% │ ↑ 8% │ │ │ │
│ │ │ │ DEV_WH │ 1,200 │ $3,600 │ 7.8% │ ↓ 15% │ │ │ │
│ │ │ │ OTHER │ 750 │ $2,250 │ 4.8% │ → 0% │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ Daily Credit Consumption Trend: │ │ │
│ │ │ │ │ │
│ │ │ Credits │ │ │
│ │ │ 800 ┤ │ │ │
│ │ │ │ ╭─╮ │ │ │
│ │ │ 600 ┤ ╭╯ ╰╮ ╭─╮ │ │ │
│ │ │ │ ╭╯ ╰╮ ╭╯ ╰╮ ╭─╮ │ │ │
│ │ │ 400 ┤ ╭╯ ╰╮╭╯ ╰╮ ╭╯ ╰╮ ╭─╮ │ │ │
│ │ │ │╭╯ ╰╯ ╰╮╭╯ ╰╮ ╭╯ ╰╮ │ │ │
│ │ │ 200 ┤╯ ╰╯ ╰╮╭╯ ╰╮ │ │ │
│ │ │ │ ╰╯ ╰╮ │ │ │
│ │ │ 0 ┼────┬────┬────┬────┬────┬────┬────┬────┬────┬────┬─ │ │ │
│ │ │ 1 5 10 15 20 25 30 35 40 45 50 │ │ │
│ │ │ Day of Month │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ COST OPTIMIZATION RECOMMENDATIONS │ │
│ │ ═════════════════════════════════ │ │
│ │ │ │
│ │ ┌──────────────────────────────────────────────────────────────┐ │ │
│ │ │ │ │ │
│ │ │ High Priority (Save ~2,000 credits/month): │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ 1. Right-size ANALYTICS_WH from XL to L │ │ │ │
│ │ │ │ • Current: 40% of credits │ │ │ │
│ │ │ │ • Potential savings: 1,200 credits │ │ │ │
│ │ │ │ • Impact: Minimal (queue depth < 5) │ │ │ │
│ │ │ │ │ │ │ │
│ │ │ │ 2. Enable auto-suspend for ETL_WH │ │ │ │
│ │ │ │ • Current: Always running (24/7) │ │ │ │
│ │ │ │ • Potential savings: 800 credits │ │ │ │
│ │ │ │ • Impact: None (scheduled tasks only) │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ │ │ │ │
│ │ │ Medium Priority (Save ~1,000 credits/month): │ │ │
│ │ │ ┌────────────────────────────────────────────────────────┐ │ │ │
│ │ │ │ 3. Optimize REPORTING_WH queries │ │ │ │
│ │ │ │ • 15 queries have poor partition pruning │ │ │ │
│ │ │ │ • Add clustering keys to improve performance │ │ │ │
│ │ │ │ • Potential savings: 600 credits │ │ │ │
│ │ │ │ │ │ │ │
│ │ │ │ 4. Consolidate DEV_WH usage │ │ │ │
│ │ │ │ • 3 underutilized warehouses │ │ │ │
│ │ │ │ • Merge into single development warehouse │ │ │ │
│ │ │ │ • Potential savings: 400 credits │ │ │ │
│ │ │ └────────────────────────────────────────────────────────┘ │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Account Usage Views are system-defined views providing metadata on queries, sessions, storage, and compute. They retain historical data for up to 365 days and enable monitoring of query performance, resource consumption, and usage patterns across the account.
Warehouse Metering History tracks credit consumption per warehouse over time. It provides hourly granularity for cost attribution, enabling identification of expensive queries, idle warehouses, and optimization opportunities.
Monitor queue time consistently > 5s — indicates warehouse undersizing. Use QUERY_HISTORY to identify top consumers by credits and execution time. Set up alerts for credit consumption exceeding daily budget thresholds.
- Account Usage: Query_history, warehouse_metering_history, login_history for visibility
- Query profiling: EXPLAIN plans + performance metrics for optimization
- Cost tracking: Credit consumption per warehouse, per query, per user
- Alerting: Warehouse errors, long-running queries, high queue times
- Retention: Account Usage views retain 365 days; Information Schema retains 7 days
Detailed Explanation
Query History Analysis
Snowflake maintains comprehensive query history through the QUERY_HISTORY system view, providing detailed information about every query executed against your account. This history includes execution times, resource consumption, error details, and performance metrics. Analyzing query history is essential for identifying performance bottlenecks, optimizing resource usage, and troubleshooting issues.
Key metrics in query history include execution time (total time from submission to completion), compilation time (SQL parsing and optimization), execution time (actual data processing), queue time (waiting for warehouse resources), and bytes scanned (data volume processed). These metrics enable identification of slow queries, resource-intensive operations, and optimization opportunities.
Query history also provides partition pruning efficiency (percentage of micro-partitions eliminated), spillage (memory overflow to disk), and result set size (output volume). Poor pruning efficiency indicates suboptimal clustering or query patterns, while spillage suggests memory constraints that may require warehouse scaling.
Warehouse Utilization Monitoring
Warehouse utilization monitoring tracks compute resource usage across your Snowflake account. Key metrics include CPU utilization (processor usage), memory consumption (RAM utilization), concurrent queries (simultaneous executions), and queue depth (queries waiting to execute). These metrics indicate whether warehouses are appropriately sized and whether scaling policies are effective.
Cluster scaling events show when multi-cluster warehouses automatically added or removed clusters in response to load. Analyzing these events helps optimize MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT settings. Frequent scaling indicates under-provisioning, while infrequent scaling suggests over-provisioning.
Auto-suspend and auto-resume patterns reveal warehouse activity cycles. Warehouses that rarely suspend may be over-provisioned, while frequently suspending and resuming warehouses may indicate inconsistent workloads that could benefit from different sizing.
Cost Analysis and Optimization
Snowflake costs are driven by compute credits (warehouse usage) and storage costs (data storage). Compute credits are consumed based on warehouse size and execution time, with larger warehouses consuming more credits per second. Understanding cost drivers enables targeted optimization efforts.
Cost attribution tracks credit consumption by warehouse, user, and workload. This visibility enables departmental chargebacks, workload prioritization, and budget management. Resource monitors can automatically suspend warehouses when credit limits are reached, preventing unexpected cost overruns.
Optimization recommendations identify specific actions to reduce costs without impacting performance. Common recommendations include right-sizing warehouses, optimizing queries for better partition pruning, consolidating underutilized warehouses, and implementing appropriate auto-suspend settings.
Performance Dashboards
Effective monitoring requires visual dashboards that aggregate and display key metrics. Common dashboard components include query performance trends (execution times over time), warehouse utilization heatmaps (usage patterns by hour), cost breakdown charts (credit consumption by category), and error rate monitors (failure tracking).
Dashboards should support drill-down capabilities, allowing users to investigate specific queries, warehouses, or time periods. Real-time dashboards provide current system status, while historical dashboards enable trend analysis and capacity planning.
Alerting and Proactive Monitoring
Proactive monitoring uses alerts to notify administrators of potential issues before they impact users. Common alerts include queue depth thresholds (indicating warehouse overload), query duration limits (identifying slow queries), credit consumption warnings (budget management), and error rate spikes (system issues).
Snowflake provides built-in alerting through resource monitors and custom alerts using the ALERT feature. External monitoring integration enables centralized alerting through tools like PagerDuty, Slack, or email systems.
Key Concepts Table
| Metric | Description | Warning Threshold | Critical Threshold |
|---|---|---|---|
| Query Queue Depth | Queries waiting to execute | > 5 | > 10 |
| Query Duration | Total execution time | > 300s | > 600s |
| Partition Pruning | Data elimination efficiency | < 70% | < 50% |
| Credit Consumption | Daily/hourly usage | > 80% budget | > 95% budget |
| Error Rate | Failed queries percentage | > 1% | > 5% |
| Dashboard Component | Refresh Rate | Use Case |
|---|---|---|
| Real-time Status | 1 minute | Current system health |
| Hourly Trends | 1 hour | Short-term pattern analysis |
| Daily Reports | 1 day | Operational management |
| Weekly Analytics | 1 week | Performance optimization |
| Monthly Summaries | 1 month | Capacity planning |
| Alert Type | Trigger Condition | Response |
|---|---|---|
| Queue Depth | > 10 queries | Scale warehouse |
| Query Timeout | > 600 seconds | Investigate query |
| Credit Limit | > 80% budget | Review usage |
| Error Spike | > 5% error rate | Investigate system |
Code Examples
-- Example 1: Query history analysis
SELECT
query_id,
query_text,
user_name,
warehouse_name,
execution_status,
start_time,
end_time,
DATEDIFF(second, start_time, end_time) as duration_seconds,
bytes_scanned / 1024 / 1024 as mb_scanned,
partitions_scanned,
partitions_total,
(partitions_scanned / partitions_total) * 100 as pruning_percentage
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND execution_status = 'SUCCESS'
ORDER BY duration_seconds DESC
LIMIT 100;
-- Example 2: Identify slow queries
SELECT
query_id,
query_text,
user_name,
warehouse_name,
DATEDIFF(second, start_time, end_time) as duration_seconds,
bytes_scanned / 1024 / 1024 as mb_scanned,
compilation_time_ms,
execution_time_ms,
queue_time_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP())
AND execution_status = 'SUCCESS'
AND DATEDIFF(second, start_time, end_time) > 300
ORDER BY duration_seconds DESC;
-- Example 3: Warehouse utilization analysis
SELECT
warehouse_name,
warehouse_size,
DATE_TRUNC('hour', start_time) as hour_bucket,
AVG(queries_completed) as avg_queries,
AVG(queued_overload_queries) as avg_queued,
AVG(execution_time_ms) / 1000 as avg_exec_seconds,
SUM(credits_used) as total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY hour_bucket DESC, total_credits DESC;
-- Example 4: Cost analysis by warehouse
SELECT
warehouse_name,
DATE_TRUNC('day', start_time) as usage_date,
SUM(credits_used) as daily_credits,
SUM(credits_used) * 3 as daily_cost_usd,
AVG(queries_completed) as avg_queries_per_hour
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY usage_date DESC, daily_credits DESC;
-- Example 5: Monitor active queries
SELECT
query_id,
query_text,
user_name,
warehouse_name,
session_id,
DATEDIFF(second, start_time, CURRENT_TIMESTAMP()) as running_seconds,
bytes_scanned / 1024 / 1024 as mb_scanned,
partition_scanned_count,
partition_total_count
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE execution_status = 'RUNNING'
ORDER BY start_time;
-- Example 6: Resource monitor setup
CREATE RESOURCE MONITOR monthly_budget_monitor
WITH
CREDIT_QUOTA = 20000
FREQUENCY = MONTHLY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 75% DO NOTIFY
ON 90% DO SUSPEND
ON 95% DO SUSPEND_IMMEDIATELY;
-- Example 7: Create monitoring alert
CREATE OR REPLACE ALERT slow_query_alert
WAREHOUSE = 'monitoring_wh'
SCHEDULE = 'USING CRON */5 * * * * America/New_York'
IF (
SELECT COUNT(*)
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(minute, -5, CURRENT_TIMESTAMP())
AND execution_status = 'RUNNING'
AND DATEDIFF(second, start_time, CURRENT_TIMESTAMP()) > 300
) > 0
THEN
CALL SYSTEM$SEND_EMAIL(
'alert_channel',
'admin@company.com',
'Slow Query Alert',
'Queries running longer than 5 minutes detected.'
);
-- Example 8: Partition pruning analysis
SELECT
table_name,
partition_key,
total_partitions,
avg_partition_depth,
clustering_depth,
CASE
WHEN clustering_depth < 1.5 THEN 'Excellent'
WHEN clustering_depth < 2.5 THEN 'Good'
ELSE 'Needs Improvement'
END as clustering_quality
FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_INFORMATION())
WHERE table_name IN ('SALES_DATA', 'CUSTOMER_DATA', 'PRODUCT_DATA')
ORDER BY clustering_depth;
-- Example 9: User activity analysis
SELECT
user_name,
COUNT(*) as total_queries,
AVG(execution_time_ms) / 1000 as avg_exec_seconds,
SUM(bytes_scanned) / 1024 / 1024 / 1024 as total_gb_scanned,
COUNT(DISTINCT warehouse_name) as warehouses_used
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
AND user_name != 'SYSTEM'
GROUP BY 1
ORDER BY total_queries DESC;
-- Example 10: Storage usage analysis
SELECT
table_name,
database_name,
schema_name,
row_count,
bytes / 1024 / 1024 / 1024 as size_gb,
created_on,
last_altered,
DATEDIFF(day, last_altered, CURRENT_TIMESTAMP()) as days_since_modified
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE deleted_on IS NULL
ORDER BY bytes DESC
LIMIT 100;
Performance Metrics
| Metric | Target | Warning | Critical |
|---|---|---|---|
| Query Queue Depth | < 5 | 5-10 | > 10 |
| Average Query Duration | < 60s | 60-300s | > 300s |
| Partition Pruning Efficiency | > 80% | 60-80% | < 60% |
| Daily Credit Consumption | < 80% budget | 80-95% | > 95% |
| Error Rate | < 1% | 1-5% | > 5% |
Best Practices
-
Implement comprehensive monitoring: Track query performance, warehouse utilization, and cost metrics continuously.
-
Set up proactive alerts: Configure alerts for queue depth, query duration, and credit consumption thresholds.
-
Regular performance reviews: Conduct weekly query performance reviews to identify optimization opportunities.
-
Optimize slow queries: Prioritize optimization of queries with poor partition pruning or excessive execution times.
-
Right-size warehouses: Use utilization metrics to right-size warehouses and eliminate over-provisioning.
-
Monitor cost trends: Track daily and monthly cost trends to identify anomalies and optimize spending.
-
Implement resource monitors: Use resource monitors to prevent unexpected cost overruns.
-
Analyze user activity: Track user query patterns to identify training opportunities and resource allocation needs.
-
Capacity planning: Use historical trends to forecast future resource needs and plan capacity upgrades.
-
Document monitoring procedures: Create runbooks for common monitoring tasks and alert responses.
See Also
- PySpark Iceberg - Spark monitoring patterns
- Delta Lake on Databricks - Delta Lake monitoring
- Data Warehouse Concepts - Data warehouse design principles