By Deiby Gómez
In this article, we’ll explore how to analyze the load and usage of a Snowflake warehouse to uncover opportunities for reducing credit waste and identifying overloaded warehouses. We’ll use the Prismafy tool, which generates detailed reports in just a few minutes. Prismafy is free to use and supports external authentication, including username/password and multi-factor authentication (MFA) for secure connections to Snowflake.
What is Prismafy?
Prismafy is an open-source tool developed in Python to analyze metadata for cloud-native data platforms. It provides charts and insights to help you detect and fix problems quickly.
How to use Prismafy to analyze a Specific Query
Follow these three simple steps:
- Download Prismafy: https://github.com/prismafy/prismafy
- Run Prismafy, specifying only the “Security” Section:
python prismafy.py -d snowflake -t password -a abc.us-east-2.aws -w Prismafy_wh -u Prismafy_user -m 6 -r Prismafy_role –s A
NOTE: Section “A” is for Computing analysis.
- Open the report: Navigate to your report at “prismafy-reports/prismafy-<date>/prismafy_index.html” report.
From the Index report you can access the following reports:
History Load Details
This report is very useful to detect specific times when your warehouse is overloaded, or underloaded. It also helps you identify if your warehouse has problems with provisioning or blocking queries. It includes the following information:
- running_load
- queued_load
- queued_provisioning_load
- blocked_load
In the following chart, you can see that the warehouse seems to have more load only on Mondays, and there is an event related to blocking queries.
Last Month Load Details
Personally, I really like this report, because, in seconds, you can identify exactly which query is having the most impact on your credits for the specific warehouse that you are analyzing. In the chart below, we can see that for the last months, there are 2 queries that consume more credits, except for the last month. During the last month, 3 new queries seem to have made a significant impact on credit usage.
Last Week Load Details
This report is similar to the previous one, but it breaks down the behavior of your warehouse by day for the last week. As you can see in the following chart, the behavior is completely different compared to the monthly report. From Wednesday to Sunday, the credits are mostly spent on 2 specific queries. But for Monday and Tuesday, the load is mainly built by different queries.
Warehouse events
Here you can see the historic events for each of your warehouses. The report provides the following information:
- Date of the Event
- Cluster number
- Event Name
- Event Reason
- Event State
- Username who started the event
- Role name used to start the event
- Query ID that started the event
- Warehouse Size
- Cluster count
This table makes it easy to identify patterns, such as when your warehouse is frequently resumed and suspended, for example.
Enable vs Querycount
This report is useful for detecting warehouses that waste credit by being active but not processing any queries. This chart provides 3 zones,
- The blue zone is when the warehouse is active,
- The red zone is when the warehouse is processing queries. It shows you how many queries the warehouse is processing which is shown on the Y-axis.
- The yellow zone shows you when your warehouse is active but not processing any queries.
If you’re aiming to have the Result Cache warm, the yellow zone can be useful, but this is where you decide what is optimal for your Snowflake environment. In the following chart, there is no yellow zone, that means the warehouse is only active when it has queries to process, and it is suspended as soon as possible.
History Size Changes
This report shows you if your warehouse is receiving resizes. It provides details on the size being used and the time of each resize. In the following chart, you can see that the warehouse has been using the size “Medium” and there have been no changes in the analyzed period.
Conclusion: Optimizing Your Snowflake Warehouse Performance
By using Prismafy, you can quickly analyze the performance and load of your Snowflake warehouse. This helps you identify areas to reduce credit waste and improve efficiency. Whether it’s detecting underutilized warehouses or pinpointing queries that consume the most resources, Prismafy provides valuable insights to optimize your Snowflake environment.
Helpful Resources
- The Definitive Guide to Analyzing Queries in Snowflake
- How to Detect Query Profile Changes in Snowflake
- Snowflake-related blogs
- Snowflake Developer Resources
Ready to unlock the full potential of your data? Our experts are here to help. Send us a message and see how we can transform your data into actionable insights.
Deiby Gómez was the first Oracle ACE Director of Guatemala (2015-2023). He holds 35 technology certifications, including the highest certifications for Oracle Database (Oracle Certified Master, OCM) and 3 Advanced Snowflake Certifications (Architect, Administrator, and Data Engineer). An engineer with 2 master’s degrees and a future lawyer, Deiby has been a speaker at technology events in more than 12 countries, including Oracle Open World in San Francisco, USA. He is the technical reviewer of the book Oracle Database 12cR2 Multitenant – Oracle Press and co-author of the book Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability – Oracle Press.
