Snowflake’s advanced architecture makes it easy to build a database that caters to your every need.
However, without proper optimization, this flexibility can lead to endlessly draining budgets.
Continuous monitoring and tuning are key to keeping costs down while maintaining high performance.
Anyone who’s worked with Snowflake knows how queries and improperly sized warehouses can lead to sluggish performance and ballooning resource consumption.
In this article, I’ll walk you through proven techniques to optimize your Snowflake implementation.
- Common Snowflake performance issues you’re likely experiencing
- Six optimization techniques to improve performance
- My favorite tools and techniques to monitor Snowflake setup
- When to bring in the expert
Now, let’s dive into my top Snowflake performance optimization tips to keep performance high and budget low:
Common Snowflake performance issues
First, before we get into how to make your Snowflake set up better, let’s talk about what’s causing the problem in the first place.
The biggest culprit behind your Snowflake performance is slow-running queries.
Despite popular belief, physical hardware resources don’t impact execution time. Snowflake is in the cloud. It doesn’t rely on physical hardware resources to work faster. Network latency isn’t what’s causing your query problem either.
No, slow-running queries happen because of insufficient warehouse resources and poorly written queries.
Let’s take a look at both of these problems so you can see what’s tripping up your system:
Inefficient joins or large scans
If your query is relying heavily on joins, especially those that produce a large number of rows, resulting in a “row explosion”, you can bet that your query is going to be significantly slower.
I’ve actually spoken before about how to optimize query joins to improve performance. If you’re interested in how exactly this issue might be impacting your Snowflake setup, you can read more about Snowflake join optimization here.
Warehouse over- or under-sizing
Yes, warehouses, much like Goldilocks, can be either too big or too small. Manually figuring out how to get things “just right” can take a lot of trial and error on your part, but I’ll get into how you can address that part of the equation with my tool recommendations.
A too large warehouse might be appealing because you think it’ll run faster, but depending on your query needs, you might be spending more than your system is calling for. On the other hand, a too small warehouse will leave you with local and remote query spillage.
6 Key areas of Snowflake optimization
Snowflake performance optimization tip #1: Use Query Acceleration Services (QAS)
If you’re an Enterprise Edition or higher user in Snowflake, then you should be making use of Snowflake query acceleration yesterday.
Snowflake Query Acceleration Service (QAS) is a Snowflake Virtual Warehouse native feature designed to improve query performance by automatically adding additional compute resources to help finish large table scans.
Think of QAS like a booster for queries so they can finish massive data scans.
Snowflake performance optimization tip #2: Always do warehouse tuning
Like I’ve already said, “right-sizing” your Snowflake warehouse is critical. Picking the best size means reducing costs and improving query performance. No pressure.
The key to picking the best warehouse size for your current configuration? Your queries.
If you’re looking at data heavy pulls, you’ll want a larger warehouse. If things are relatively simple, don’t be afraid to start small.
Best practice is to start with the smallest warehouse size, X-SMALL, and work your way up. Remember: you’ll know if your warehouse is too small if you’re seeing a lot of local and remote spillage.
Snowflake performance optimization tip #3: Monitor & review queuing
Queuing happens when your warehouse is stuck executing other queries, so your incoming queries are left to wait until resources are available.
Unfortunately, those queued queries will still consume compute resources.
You can check your query history to see what your current queuing situation is by using the QUERY_HISTORY_BY_SESSION function.
But how do you fix this issue? Use timeout parameters. This parameter can be set at a session, warehouse, user, or even account level, so you have maximum flexibility here.
Snowflake performance optimization tip #4: Make use of single-cluster vs multi-cluster warehouse
Consider changing your single-cluster warehouse to a multi-cluster configuration. With single-cluster warehouses, new queries must wait until resources become available. Multi-cluster warehouses, on the other hand, spin a new cluster for each new query until they reach the maximum concurrency level.
This level doesn’t limit your total number of queries but keeps your warehouse from being too overburdened with too many queries. I usually recommend sticking with eight, which is the default and can be modified at any time.
Snowflake performance optimization tip #5: Snowflake search optimization service
Use Snowflake’s Search Optimization Service (SOS) This is another Enterprise edition or higher feature. Search optimization service is a background maintenance service that helps optimize lookups and analytical queries.
The data structures it uses serve as an optimized path called a “search access path”. This search access path scans table micro-partitions and records partition metadata, storing it for later to make it easier for Snowflake to build the best search access path for your queries to reach table data.
Snowflake performance optimization tip #6: Pay attention to materialized views & caching
Materialized views use precomputed views to make any relevant queries faster.
Materialized views can help your setup if you fall into any of these categories:
- Your data’s views doesn’t change often but are used on a regular basis
- You’re executing your queries on file-based data like JSON or CSV
- Your query uses time-consuming computations and aggregations
Best tools and techniques for continuous monitoring
Snowflake performance optimization isn’t a one and done job. You’ll need to continuously monitor your setup to make sure a rogue query doesn’t throw everything awry. Here are the best tools I would recommend adding to your tech stack:
Snowflake Query Profile
Query Profile is Snowflake’s built-in visualization tool. It shows you exactly how each of your queries are executed, breaking down:
- Each step in the query execution
- Amount of data processed at each stage
- Time spent on individual operations
- Any and all data distribution patterns
Regularly review Query Profiles for your high-budget and frequent-use queries so you can identify hidden bottlenecks and optimization opportunities.
Snowflake Resource Monitors
Resource Monitors is your best friend when it comes to stopping unexpected Snowflake bills in their tracks. Use resource monitors to set credit usage thresholds and actions. This tool can help you:
- Notify admins when credit usage reaches pre-defined thresholds
- Automatically suspend warehouses when they reach their credit limits
- Customize monitoring parameters for each warehouse
- Implement graduated responses based on previous consumption history
Try to use three tiers of monitoring for each of your critical warehouses to ensure maximum efficiency and budget optimization. You’ll want the following notifications:
- Warning notification when your warehouse reaches 70% of usage
- Urgent alert for 85% of usage
- Suspension protocols at 100% of usage
Set alerts for long-running or expensive queries
This technique sets you up for success with proactive query monitoring. For best results, you should set up alerts for:
- Queries scanning excessive data volumes (i.e., over 1TB)
- Queries scanning predetermined runtime thresholds (about 10-15 minutes)
- Repeated query failure
- Sudden increases in queuing time
Third-party Snowflake optimization tools
Snowflake’s tools are excellent at providing visibility, but third-party solutions are where the real savings are.
Yuki combines automated monitoring with intelligent optimization. That warehouse right-sizing testing I was talking about before? You don’t have to be the one to do that. Yuki can adjust your warehouse size in real-time based on the needs of the specific query being run.
Yuki won’t just proactively alert you to a problem. It’ll solve it. Yuki uses your historical data to learn what the optimal setup is for your specific query set, and will adjust performance accordingly.
When to Bring in Snowflake Performance Optimization Experts
If you’re implemented all of these best practices but are still running into query performance issues, it’s time for expert intervention.
If you’re stuck in any of these situations, it might be time to call in the calvary:
- Snowflake costs keep spiking by 25% or more without a corresponding increase in your workload or users.
- Your team is overwhelmed with troubleshooting, management, or optimization.
- You keep experiencing unexplained slowdowns that are likely indicative of a larger issue like suboptimal table design or even a hidden data skew.
If you’re in any of these situations, or even just ready to seek outside help, here’s what Yuki can do for you:
- Comprehensive performance audits that thoroughly examine your Snowflake implementation from warehouse configuration to query performance to data architecture. I’ve seen audits like these uncover up to 30-40% in potential savings thanks to performance improvements.
- Immediate, automated improvements thanks to Yuki’s plug-and-play ability. All you have to do is set up the connection between Yuki and your Snowflake platform, no dev work needed, and you can start seeing results.
- Complete guarantees because we’re committed to our improvement metric-backed SLA. You get accountability and promised results.
- Knowledge transfer from our team to yours on all things Snowflake. We’re here to train up your team so you understand the problems your Snowflake setup is facing.
Stop watching your Snowflake costs climb while performance suffers. Schedule your free consultation today, and discover how Yuki can reduce your Snowflake spend by up to 70% while boosting query performance.
