Staring at your Snowflake bill and wondering what went wrong and how did these numbers manage to get so big? It may not feel like it, but you’re actually in luck. There’s one small change you can make to your account that’ll slash those numbers:
Snowflake query optimization.
I’ve been helping clients cut their Snowflake costs by up to 50% without sacrificing an inch of efficiency for years.
And I’m not one to keep industry secrets.
Read on to learn my top 10 favorite tips for Snowflake query optimization – and stick around for one last bonus tip that I’ve yet to see stated anywhere else.
Why bother with query optimization
Snowflake stands apart from other on-premise databases like SQL Server and Oracle because Snowflake query optimization actually delivers results. And when I say results, I mean it. Snowflake query optimization means saving both time and money – when done correctly.
Why does query optimization work when it comes to Snowflake but not Oracle or SQL Server? Snowflake charges you per second when it comes to compute resources. So the longer the query takes, the more money you pay, and vise versa.
Which also means that, with just a few tweaks, you can take an ordinary query setup and optimize it into a lean, mean, cost-optimized machine.
How does Snowflake query optimization work
Snowflake query optimization techniques fall into three categories:
- Optimization for data read to reduce the time queries spend reading data from table storage and increase TableScan speed.
- Optimization for data processing to reduce bottlenecks caused by Joins, Sorts, and Aggregates and improve efficiency.
- Optimization for warehouse configuration to either increase warehouse size or cluster count, or change scaling policies to better handle larger and higher concurrency workloads.
Identifying bottlenecks
Before you dive in and start applying these Snowflake query optimization tactics, think about what’s hampering your workflow. What operations are consistently slowing you down? You’ll need these answers before you get into the nitty-gritty of optimization strategies.
Identify bottlenecks by using:
- Query history storage of Snowflake’s past queries to review how efficiency and update future performance.
- Snowflake automatic data partition which is something that Snowflake does automatically that can be useful, but often hurts niche applications and specialized teams.
- Query profiling tools from Snowflake can help you analyze what processes are most likely to result in slowdowns, inefficiencies, and other errors.
- Monitoring tools like Yuki to identify inefficiencies to optimize Snowflake queries. These tools let you set hands-off automations to improve your Snowflake costs without ever needing to write a new line of code.
Now that you’ve identified your specific bottlenecks using the methods above, read on to learn my favorite targeted optimization techniques to fix any issues you have with data reading, processing, or warehouse configuration.
10 Snowflake query optimization tactics
I’ve broken these down according to the three tactics I’ve covered above, so feel free to skip to the section that’s causing the worst bottleneck in your setup.
Data read optimization
Optimization tip #1: Use fewer columns
Yes, this is simple, and yes, it will save you time and money. Query requirements aren’t consistent, so some columns that were once required might now be redundant.
Review your column setup by using Snowflake’s micro-partitions file format. Snowflake uses micro-partitions to reduce the amount of data it has to read from storage. You can download this data in a process called scanning. Review any columns that are no longer of use, and delete.
Optimization tip #2: Use pre-aggregated tables or materialized views
Use “rollup” or “derived” tables since they contain fewer rolls of data.
Pre-aggregated tables are usually designed to give queries the information they need but with far less storage space, making them much faster to query. Here’s an example of what the SQL looks like for a pre-aggregated table:
Retail businesses, for example, will use a daily orders rollup table for financial reporting.
Optimization tip #3: Always, always, always do query pruning
Query pruning lets you reduce the amount of data scanned during a query execution by excluding any irrelevant micro-partitions. You can do this by using metadata like column range values to determine what micro-partitions are necessary for the query, and what can be excluded.
Wondering if you’ve pruned enough or not? Take a look at your query profile’s Partitions scanned and Partitions total stats. If you find that Partitions scanned are closer to Partitions total, keep pruning. You’re not done yet.
You can also use Yuki’s query analysis, which will automatically identify which queries are in most need of a pruning restructure.
Pro tip: Avoid using functions in where conditions as these prevent Snowflake from pruning micro-partitions.
Data processing optimization tips
Optimization tip #4: Simplify your query structure
Every query operation takes a little more time, which means a little more money. Look for places you can consolidate or even remove unnecessary operations to improve network transfer times.
Simpler queries also means for simpler debugging, so both your team and Snowflake can work a bit faster.
Pro tip: Make use of CTEs and subqueries to help with readability since they don’t impact performance.
Optimization tip #5: Use filtering to reduce processed data
Avoid pulling full data sets and trimming them after the fact by applying WHERE clauses and filters. The less data Snowflake has to process, the lower your bill will be.
Filtering early also means Snowflake’s optimizing can skip unnecessary micro-partitions, so you’re really getting a double win here.
Pro tip: Be careful of how you structure filter conditions. Snowflake’s optimizer sometimes can’t infer that WHERE col = 5 or col = 6 is the same as col IN (5,6). Stick with the latter. It’s easier to prune.
Optimization tip #6: Avoid irrelevant sorts and joins
Sorting is expensive. Review your queries to remove any sorts that aren’t absolutely necessary.
Same with joins, especially when it comes to large tables. A table with a billion rows can take up 500+ gigabytes of space. Joining that large dataset with another can create a bottleneck that just sucks up time and money.
Optimization tip #7: Use window functions instead of self-joins
Instead of self-joins, use window functions when possible. Like I said above, self-joins are expensive since they result in a join explosion, so window functions can help you save here.
Here’s an SQL sample of what the code would look like:
Pro tip: Yuki’s pattern recognition will actually do this for you! It can automatically detect self-joins and replace them with the much more efficient window functions.
Warehouse configuration optimization tips
Optimization tip #8: Optimize your warehouse size
Increasing your warehouse size so your query elapsed time goes from four hours to 60 seconds might seem like the best way to improve performance… but warehouse size has a happy medium you’ll have to test your way into. And remember: the larger the warehouse, the larger the fees, so proceed with caution.
Thankfully there is one way to be certain that you’ve found your optimal warehouse size, no testing needed: Yuki.
Yuki will review your queries in real-time and route you to the best warehouse size so you never need to sacrifice efficiency while still optimizing for cost.
Optimization tip #9: Use cache queries the right way
Every virtual warehouse node has a local storage disk for caching micro-partitions read from storage. This means that if you have multiple queries using the same set of data in a table, those queries can scan the data from the local disk cache instead of remote storage – which means a faster, cheaper query.
The only drawback with this is that when the warehouse gets suspended, Snowflake doesn’t guarantee that the cache will persist once the warehouse starts up again. This cache loss means queries will have to rescan data instead of reading from a faster local cache. If you’re running into this issue, increase your auto-suspend threshold.
Snowflake has a global result cache which results in identical queries executed within 24 hours – assuming the data in the queries tables is identical.
Pro tip: Check that you’re hitting your global cache as expected. Sometimes you’ll run into blocks if your query has a non-deterministic function. Or you might just be running into a bug.
Optimization tip #10: Adjust your cluster scaling policy
This tip is for those with Enterprise edition and above who have access to multi-cluster warehouses. Adjusting the scaling policy within your multi-cluster resources is a great way to optimize your queries and improve resource allocation and query load.
Do this by fine-tuning your warehouse’s capacity to meet your queries needs using one of these techniques:
- Responsiveness (standard policy) to scale up or down based on workload demands to prioritize query responsiveness and throughput. Best used when dealing with large, complex queries or user-facing applications.
- Cost (economy policy) to focus on minimizing price for queue-tolerant workloads like data loading.
Bonus tip: Use monitoring tools to always improve settings
If you truly want to optimize your Snowflake queries for success, this won’t be a one and done process. The most cost-effective teams use Yuki to continuously monitor query performance, automatically routing queries to optimal warehouses based on real-time data.
Yuki tracks Snowflake inefficiencies, adjusting on the fly based on your personal information. It identifies:
- Slow queries
- Inefficient warehouse sizes
- Cache misses
- Unnecessary scans
And Yuki does all of this with a plug and play automation and user-friendly dashboard that anyone can use. No need for extra dev or setup costs. Or even query rewriting!
Just faster, cheaper data operations with a single click.
Integrated automated optimization with Yuki
Query optimization isn’t just a nice to have. It’s the difference between unnecessarily burning a budget and building a sustainable data operation.
Ready to get started with your Snowflake query optimization process? Get started with a free cost analysis report today.
