How can I identify runaway queries in Snowflake?

You can query the QUERY_HISTORY account usage view to find queries that run for an abnormally long time, consume high partition scanning, or have zero compilation cache reuse. Setting up active monitors or using tools like SnowOps helps automate detection.

What is the recommended auto-suspend setting in Snowflake?

For development and testing workloads, we recommend setting the auto-suspend threshold to 60 seconds (or 1-2 minutes). Setting auto-suspend to 'Never' or high values is a common cause of credit leaks.

Snowflake Cost Optimization: The Ultimate Guide for Data Teams

Managing Snowflake compute costs is one of the most critical challenges facing data teams today. Because Snowflake separates storage from compute, credit consumption can scale exponentially if not monitored correctly. An unoptimized query or a warehouse left running indefinitely can lead to unexpected budget spikes.

In this guide, we walk through actionable strategies to optimize your Snowflake environment and reduce monthly credits without impacting query performance.

1. Right-Sizing Warehouses & Auto-Suspend

A common mistake is assigning workloads to excessively large warehouses. While Snowflake allows instant scaling, doubling a warehouse size (e.g., from Medium to Large) doubles the credit-per-hour rate. If a warehouse finishes a task in 5 seconds but remains active for another 10 minutes due to long idle timeouts, credits are wasted.

Action Plan:

Set Suspend Limits: Configure development and testing warehouses to auto-suspend after 60 seconds of inactivity.
Audit Idle Timeouts: Use metadata queries to identify warehouses that spend most of their uptime idle.

-- Query to audit warehouses with auto-suspend configs
SHOW WAREHOUSES;
SELECT 
  "name" AS WAREHOUSE_NAME,
  "size" AS WAREHOUSE_SIZE,
  "auto_suspend" AS SUSPEND_TIMEOUT_SEC
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "auto_suspend" IS NULL OR "auto_suspend" > 300;
        

2. Tracking & Stopping Runaway Queries

A runaway query is a workload that continues to execute far beyond its expected duration. This is usually caused by cartesian products (cross joins), unindexed massive scans, or loop conditions in stored procedures.

By querying active query history, administrators can detect long-running jobs and programmatically cancel them before they consume daily budget allowances.

-- Query to find active queries running longer than 1 hour
SELECT 
  QUERY_ID,
  USER_NAME,
  WAREHOUSE_NAME,
  TOTAL_ELAPSED_TIME / 1000 / 60 AS DURATION_MINUTES,
  QUERY_TEXT
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE EXECUTION_STATUS = 'RUNNING'
  AND TOTAL_ELAPSED_TIME > 3600000;
        

💡 Optimize with SnowOps Intelligence

The SnowOps platform provides a native Cost Guardian dashboard. It acts as an automated buffer, scanning running tasks in real time and alerting team members via Slack or Teams when compute usage breaches set thresholds.

3. Optimizing Data Pruning and Partitioning

Snowflake dynamically prunes partitions during query execution if filters are placed on the table's clustering keys. If filters are not used or queries request full table scans, Snowflake must read all micro-partitions, increasing scan times and warehouse workload.

Best Practices:

Use Filter Conditions: Always include WHERE clauses targeting date columns or clustering keys in large tables.
Limit Select Columns: Avoid SELECT *. Only query the columns necessary to minimize data volume scanned.
Leverage Query Cache: Snowflake caches query results. Repeated queries with identical syntax against unchanged tables consume zero credits.

Conclusion

Implementing these steps will immediately reduce compute waste. For automated oversight, we recommend deploying an open-source tool like SnowOps Intelligence inside your Snowflake perimeter, giving you unified dashboard visibility and burst alerts.