Resource Monitors & Query History in Snowflake
1️⃣ Resource Monitors
🔹 What is it?
Resource Monitors in Snowflake help manage and control the consumption of compute credits by setting thresholds for usage.
They allow administrators to prevent runaway costs and ensure that no warehouse exceeds budgeted usage.
🔹 Why is it required?
- ✅ Cost Management: Keep compute usage under control to avoid surprises in billing.
- ✅ Governance: Enforce usage limits for different teams or workloads.
- ✅ Alerting & Enforcement: Alert when thresholds are reached or suspend warehouses to prevent further costs.
🔹 Associated SQL
Create a Resource Monitor:
CREATE RESOURCE MONITOR my_monitor
WITH CREDIT_QUOTA = 100
TRIGGERS ON 80 PERCENT DO NOTIFY
TRIGGERS ON 100 PERCENT DO SUSPEND;
Assign a Resource Monitor to a Warehouse:
ALTER WAREHOUSE my_warehouse
SET RESOURCE_MONITOR = my_monitor;
View Resource Monitors:
SHOW RESOURCE MONITORS;
🔹 Use Case
A department has a budget of 100 credits/month. If they reach 80 credits, an alert is sent. If they reach 100, the warehouse is suspended to avoid overages.
🔹 Best Practices
- ✅ Set realistic credit quotas based on historical usage.
- ✅ Use multiple triggers (e.g., notify at 80%, suspend at 100%).
- ✅ Regularly review usage via
ACCOUNT_USAGEviews. - ✅ Assign Resource Monitors at the warehouse level for granular control.
🔹 Limitations
❌ Only applies to warehouse credit usage (not storage or cloud services). ❌ No fine-grained control over suspensions for specific queries. ❌ Manual management of thresholds required.
🔹 Advantages
- ✅ Prevents unexpected cost overruns.
- ✅ Automated alerts & suspensions improve governance.
- ✅ Granular control at the warehouse level.
🔹 Disadvantages
❌ May cause warehouse suspensions mid-job if thresholds are too tight. ❌ Not suitable for ad-hoc, unpredictable workloads without careful planning.
🔹 Required Roles
- ACCOUNTADMIN can create and assign Resource Monitors.
🔹 Impact on Performance Tuning
- ✅ Helps identify inefficient warehouses (e.g., always hitting limits).
- ✅ Encourages right-sizing warehouses to avoid excessive suspensions.
- ✅ Supports performance vs. cost trade-offs by monitoring compute usage.
2️⃣ Query History
🔹 What is it?
The Query History in Snowflake provides a log of executed queries (successes and failures), including:
- Query text
- Start/End times
- Execution status
- Bytes scanned & data processed
- Warehouse used
- User & role information
🔹 Why is it required?
- ✅ Troubleshooting: Investigate failed queries or performance issues.
- ✅ Auditing: Track who ran what queries for compliance.
- ✅ Performance Tuning: Identify slow or resource-heavy queries.
🔹 Associated SQL
View Query History (last 1 hour example):
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD('HOUR', -1, CURRENT_TIMESTAMP());
Or for better visibility:
SELECT
QUERY_ID,
USER_NAME,
WAREHOUSE_NAME,
EXECUTION_STATUS,
START_TIME,
TOTAL_ELAPSED_TIME,
BYTES_SCANNED,
ERROR_MESSAGE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
ORDER BY START_TIME DESC
LIMIT 100;
🔹 Use Case
A data engineer uses QUERY_HISTORY to identify which queries are failing repeatedly or consuming excessive resources—key for troubleshooting and optimization.
🔹 Best Practices
- ✅ Use filters (
USER_NAME,WAREHOUSE_NAME,ERROR_MESSAGE) to narrow focus. - ✅ Combine with
WAREHOUSE_LOAD_HISTORYto correlate load and query impact. - ✅ Build dashboards to visualize query performance trends over time.
🔹 Limitations
❌ 7-day retention in SNOWFLAKE.ACCOUNT_USAGE (can be longer in ORGANIZATION_USAGE).
❌ Query text might be truncated for very long queries.
❌ Can be large dataset for busy environments—requires efficient filtering.
🔹 Advantages
- ✅ Central, unified log of all queries.
- ✅ Rich metadata for analysis and performance tuning.
- ✅ Supports troubleshooting, compliance, and auditing.
🔹 Disadvantages
❌ Large volume of data might require warehouse resources to query. ❌ No built-in anomaly detection—you must build your own logic.
🔹 Required Roles
- SECURITYADMIN or roles with access to ACCOUNT_USAGE views.
🔹 Impact on Performance Tuning
- ✅ Identify slow queries for rewriting or optimization.
- ✅ Spot data skew or high I/O queries.
- ✅ Evaluate warehouse sizing to match query workloads.
- ✅ Find concurrency issues (too many simultaneous heavy queries).
- ✅ Inform clustering and partitioning decisions (e.g., large table scans).
🚀 Summary Table
| Feature | Purpose | Role Required | Impact on Performance Tuning |
|---|---|---|---|
| Resource Monitors | Control & limit warehouse credit usage | ACCOUNTADMIN | Avoid suspensions mid-job, guide warehouse sizing |
| Query History | Log & analyze executed queries | SECURITYADMIN | Find slow queries, improve warehouse sizing |