How to Monitor and Optimise a Snowflake Warehouse

Maximising performance in Snowflake is not just a technical necessity but a strategic advantage.

Proper performance tuning ensures lightning-fast query execution, cost-effective operations, and efficient use of resources. Without it, even basic queries can slow down critical decision-making and drive up cloud spend.

In this article, we will show you how to monitor and optimise a Snowflake warehouse to improve performance and make the most of your resources.

Understanding warehouses in Snowflake

In Snowflake, a warehouse refers to a virtual compute cluster that handles query processing. Think of each warehouse as a powerful computing engine made up of multiple CPU cores, memory, and temporary storage.

The performance of your workloads is closely tied to how effectively you configure and scale these warehouses.

In this guide

Here, we will explore proven strategies for tuning performance in Snowflake, with a special focus on warehouse optimisation. Whether you are facing slow-running queries or proactively looking to improve your data platform’s efficiency, the best practices you will find will help you unlock the full potential of your Snowflake solution.

Monitoring warehouse performance in Snowflake

To effectively monitor warehouse activity and performance, ensure that your user role is granted the necessary permissions:

Cost management (Admin > cost management)

From Admin, navigate to Cost Management.

When you are in the Cost Management dashboard in Snowflake, you can track:

• Credit consumption to understand how many compute credits are being used and by which workloads;
• Query execution time to identify long-running or resource-intensive queries to fine-tune performance.

Warehouse overview (Admin > Warehouse)

From Admin, navigate to Warehouse.

Here, you get a real-time snapshot of warehouse configurations and activity, including:

• Warehouse size: See if your compute resources align with your workload demands;
• Multi-cluster settings: Check if auto-scaling is configured for peak concurrency;
• Current state: View whether the warehouse is active, suspended, or resizing;
• Running vs. queued queries: Monitor workload pressure and detect potential bottlenecks.

Having these built-in tools at your disposal, you to proactively manage compute resources, optimise cost, and maintain consistent performance across all environments.

Common issues and solutions

Memory and processing pressure

From the Snowflake Query Profile, there are three obvious aspects to indicate how much memory pressure the warehouse is under:

• Bytes spilled to storage: high number of spilled GBs indicates that the warehouse’s memory cannot handle the data amount on its own and needs low-speed storage;
• Partition scanned vs. total: Low ratio indicates that you are working on a large subset of a table;
• Percentage scanned from cache: Low percentage indicates that Snowflake could not reuse its already-computed information from cache. Hence,
• Snowflake needs to re-calculate these logics, leading to longer time to complete.

Minimise column SELECTION

Be mindful of the number of columns you select from upstream tables and only pull the ones you truly need. Snowflake’s columnar storage is great for efficient data retrieval, but selecting unnecessary columns can increase memory usage and slow down performance.

Split the logic and materialisation

Have a look at the following example in dbt:

If the Snowflake Query Profile shows that view2 is consuming too many resources, causing performance issues, consider changing the materialisation of view2 to a table. This way, Snowflake can:

• Focus solely on processing view2 at that moment;
• Use the pre-computed results from storage, and leaving more resources available for the rest of the logic.

Take advantage of partitioned and clustered column

Snowflake automatically detects, partitions and clusters your data. Try to leverage it as much as possible to reduce strains on your warehouse.

Transactional table

This type of table usually involves a date/time column. Add it to your query:

About SCD table

A slowly-changed dimension (SCD) type-2 table keeps track of your historical change. Take advantage of the date elements to quickly select your data, e.g. the latest record:

Cluster categorical columns (with countries, departments etc.)

Columns like departments, countries, or teams are great for clustering in Snowflake and can really cut down the amount of data your warehouse needs to scan. Let’s see how to make the most of them.

One of the easiest wins is adding filters on dashboards using these columns. That way, Snowflake only pulls the relevant slice of data instead of scanning the whole table, which keeps things fast and efficient.

Scale up your warehouse

If you are working with a large volume of data and the earlier tips are not enough, it might be time to scale up your warehouse. With dbt, you can easily assign a bigger (and more powerful) warehouse to just the jobs that actually need it, no need to overspend across the board.

Here is a helpful way to think about it: a larger warehouse might cost more per second, but if it finishes the job significantly faster, it could end up costing you less overall than a smaller, slower one.

Concurrency

If you are seeing a low ratio of RUNNING to QUEUED queries and your warehouse is only using a single cluster, that is a clear sign your pipeline is under strain.

It likely means you need to add more clusters or scale out to handle the load and avoid bottlenecks.

Scale out

Ensure that you are running on Snowflake Enterprise Edition.

There are two scaling policies to consider, namely Standard and Economical. Let’s compare them briefly:

If your warehouse is struggling to handle the complexity or size of your queries, consider scaling up to a larger size. On the other hand, if the issue is too many queries running at once, try scaling out by adding more clusters of the same size to handle the concurrency.

Just remember that each strategy is designed for a different kind of problem. Scaling up will not fix concurrency issues, and scaling out will not help if a single query needs more power. Use the right approach for the right situation.

Suspension policy

To save credits, you can choose to have Snowflake automatically suspend your warehouse when the latter is not in use.

However, keep in mind that the suspension policy can affect the performance of any running queries.

By default, Snowflake bills for at least 60 seconds of warehouse usage. It is best not to set auto-suspend below that threshold. You might also want to consider adjusting the timeout settings to find a balance between cost and performance, depending on your workload.

Best practices for optimising a Snowflake warehouse

1. Always monitor usage and execution patterns with proper permissions;
2. Query only what you need; avoid SELECT *;
3. Break complex views into smaller, materialised components;
4. Filter with partitioned/clustered columns;
5. Match scale strategy to workload: scale up for size, out for concurrency;
6. Optimise auto-suspend settings to balance performance and cost.

As a Snowflake Elite Services Partner, we have extensive experience optimising warehouses on the AI Data Cloud. Get in touch if you have challenges with your project and we will be happy to have a look.

Visit the Infinite Lambda Blog for more insights into cutting-edge data and AI technology.