Skip to content 
In cloud data platforms like Snowflake, optimising query performance and resource usage is critical for efficiency and cost control.
Snowflake, a fully managed cloud data warehouse, provides robust tools to monitor and optimise query performance through metadata. This method allows data engineers and analysts to identify bottlenecks, optimise performance, and fine-tune resource allocation.
This blog post draws on the latest documentation and best practices to explore how to use Snowflake metadata to pinpoint areas of optimisation. It will also show you actionable SQL queries and processes to help you boost precision.
Understanding Snowflake metadata
Snowflake’s architecture separates compute from storage, with a dedicated services layer that manages metadata. The metadata includes information about query execution, resource usage, and data organisation, which can be accessed through the Snowflake database, a system-defined, read-only shared database containing historical usage data and object metadata.
The Snowflake database includes schemas like ACCOUNT_USAGE and INFORMATION_SCHEMA, which are invaluable for performance analysis.
Metadata in Snowflake captures details such as:
- Query execution times and resource consumption;
- Data scanned, partitions accessed, and caching efficiency;
- Warehouse usage and queuing metrics.
By querying this metadata, you can uncover inefficiencies in query design, warehouse sizing, and data organisation, enabling targeted optimisations.
Here is exactly how to do that.
Step 1: Analyse query performance with query history
The first step in optimisation is to understand how your queries are performing. Snowflake’s ACCOUNT_USAGE.QUERY_HISTORY view provides detailed metrics on every query executed in your account over the past 365 days. This view includes execution time, data scanned, partitions used, and more, making it a powerful tool for identifying slow or resource-intensive queries.
SQL query to identify slow-running queries
Let’s start by identifying queries that take the longest to execute or scan excessive amounts of data. This query focuses on queries executed in the last 7 days with high execution times or large data scans:
What to look for
- High execution time: Queries with execution_time_seconds exceeding acceptable thresholds (e.g. 10 seconds) may indicate inefficient SQL logic or insufficient compute resources;
- Excessive data scanned: A high bytes_scanned value suggests the query is reading more data than necessary, potentially due to missing filters or poor data organisation;
- Partition efficiency: Compare partitions_scanned and partitions_total. If the ratio is high, Snowflake is not effectively pruning partitions, which could be improved with clustering keys.
Process: drill down with query profile
For each slow query identified, use Snowflake’s Query Profile in the Snowsight UI (or via the GET_QUERY_OPERATOR_STATS function) to analyse the execution plan. Look for:
- Most expensive nodes: Nodes like TableScan or Sort that consume significant time indicate where to focus optimisation efforts;
- Spilling to disk: If queries spill to remote disk (visible in the Query Profile), consider scaling up the warehouse to increase RAM and local disk capacity.
Step 2: Optimise data access with partition pruning
Snowflake organises data into micro-partitions, and its metadata tracks which partitions contain relevant data for a query. Effective partition pruning, where Snowflake skips irrelevant micro-partitions, can drastically reduce data scanned and improve query speed. However, poor pruning can lead to excessive scans, especially on large tables.
SQL query to check partition pruning efficiency
Use the QUERY_HISTORY view to identify queries with poor partition pruning:
Optimisation strategy: implement clustering keys
If a query scans a high percentage of partitions, consider defining a clustering key to improve data organisation.
Clustering keys physically sort data within micro-partitions, enhancing pruning efficiency. For example, if a table is frequently filtered by a sale_date column, you can cluster it as follows:
After clustering, monitor the partition_scan_percentage in subsequent queries to confirm improvement. Snowflake’s Automatic Clustering service will maintain the clustering in the background, though this incurs additional compute costs.
Step 3: Leverage caching for performance gains
Snowflake offers three caching layers that can significantly boost query performance:
- Result cache: Stores query results for 24 hours, returning results instantly for identical queries if the underlying data hasn’t changed;
- Local disk cache: Caches frequently accessed data in the warehouse’s SSD and memory;
- Metadata cache: Enables fast metadata operations, such as partition pruning.
SQL query to assess cache usage
To evaluate how often your queries benefit from caching, query the QUERY_HISTORY view for cache-related metrics:
Optimisation strategy: maximise cache hits
- Result cache: Ensure queries are deterministic by avoiding non-deterministic functions like CURRENT_TIMESTAMP() to leverage the result cache. If a query is not hitting the cache, check for underlying data changes or query variations;
- Local disk cache: Avoid frequent warehouse suspension, as suspending a warehouse (default after 10 minutes of inactivity) clears the local disk cache. Adjust the AUTO_SUSPEND setting to keep the warehouse active longer:
Step 4: Monitor warehouse usage and queuing
Inefficient warehouse sizing can lead to query queuing, where queries wait for compute resources, slowing down performance. Snowflake metadata can help identify queuing issues and guide warehouse optimisation.
SQL query to detect queuing
Use the WAREHOUSE_LOAD_HISTORY view in the ACCOUNT_USAGE schema to identify queuing events:
Optimisation strategy: adjust warehouse sizing
Scale up
If queries are spilling to remote disk (available in the Query Profile) or execution times are high, scale up the warehouse to increase compute resources:
Scale out
If queuing is frequent, enable multi-cluster warehouses (available in Enterprise Edition or higher) to handle concurrent workloads:
Step 5: Use metadata for data organisation insights
Snowflake’s metadata can also reveal opportunities to optimise data structures, such as using materialised views for frequently accessed aggregations or complex joins. Materialised views store precomputed results, reducing repetitive calculations.
SQL query to identify candidates for materialised views
Identify queries with expensive aggregations or joins that could benefit from a materialised view:
Optimisation strategy: create materialised views
For a query with frequent aggregations, create a materialised view to precompute the results:
Snowflake automatically maintains materialised views, refreshing them when the underlying data changes, which can significantly speed up subsequent queries.
Step 6: Enable search optimisation service for specific use cases
For tables with frequent point lookups or analytical queries, Snowflake’s search optimisation service (available in Enterprise Edition or higher) can improve performance by creating a persistent data structure called a search access path.
This structure allows Snowflake to skip irrelevant micro-partitions more efficiently. Use this feature cautiously, since this can cause an immediate increase in credit usage for large tables.
Process: enable search optimisation
Identify tables with slow point lookups (e.g. SELECT * FROM table WHERE id = 'specific_value'), then enable the service:
Monitor the search_optimization_progress column in the SHOW TABLES output to ensure the search access path is fully built before measuring performance improvements.
Best practices for metadata usage
- Avoid sensitive data in metadata: Snowflake documentation warns that metadata fields (e.g. in CREATE or ALTER statements) may be processed outside your region. Ensure no personal, sensitive, or regulated data is included in metadata fields;
- Regular monitoring: Set up automated alerts using Snowflake tasks to monitor QUERY_HISTORY for performance degradation, such as queries with increasing execution times or data scanned;
- Stay updated: Snowflake frequently releases new features (e.g. AI/ML capabilities). Regularly review Snowflake’s documentation to leverage the latest optimisations.
Getting started
Leveraging Snowflake metadata through the Snowflake database empowers data teams to identify and address performance bottlenecks with precision. You can significantly enhance query performance and resource efficiency by:
- Analysing query history
- Optimising partition pruning
- Maximising cache usage
- Fine-tuning warehouse sizing
- Utilising advanced features like materialised views and the search optimisation service
The SQL queries and processes outlined here provide a professional, actionable framework for optimisation, ensuring your Snowflake environment runs at peak performance while controlling costs.
For the latest updates, always refer to Snowflake’s official documentation and stay engaged with the community for emerging best practices.
If you have any questions Snowflake optimisations or use an alternative way of leveraging your metadata, drop us a line — we would love to hear from you.
Visit the Infinite Lambda blog for more insights on data, cloud, and AI technology.
