How to Improve Snowflake Query Performance with Query Routing

Conventional wisdom says Snowflake query performance and cost optimization are a zero-sum game. On the surface, this makes sense. Improved performance typically requires more compute resources, which in turn requires more Snowflake credits.

But if you go a layer deeper, the reality is more complicated and nuanced. In fact, some Snowflake optimization practices give you the best of both worlds. One of these is query routing. In this article, we’ll discuss the pros and cons of query routing and proven strategies for optimizing Snowflake query performance more broadly.

What is Snowflake query performance?

Snowflake query performance refers to the efficiency and speed of query execution within the Snowflake data cloud. Performance is dependent on a number of factors, including how the query is written, the resources provisioned to run it, and other concurrent queries that compete for those resources.

Why do organizations struggle to maintain Snowflake query performance?

Maintaining high Snowflake query performance is easier said than done. Here’s why it can be a challenge for many data teams.

Lack of adequate Snowflake monitoring tools

Optimizing Snowflake query performance first requires you to monitor and benchmark your queries. While Snowsight’s monitoring tools provide a good starting point, you’ll likely need deeper insights to surface trends, patterns, and recommended actions:

Ongoing query patterns to surface performance trends
Heavy-hitter users and queries that are driving up costs
Warehouse-specific details to help identify whether your resources are adequately provisioned

Tools like Keebo’s Snowflake Workload Intelligence provide the insights listed above and more.

Cloud resource under-provisioning

Another common reason for poor Snowflake query performance is resource under-provisioning. Warehouses simply don’t have the compute power to handle queries, leading to slower execution times, latency, extended queuing, etc.

Often under-provisioning is a form of cost control. Ironically, it can lead to ballooning costs. If Snowflake query performance struggles to the point of extended query execution times and spillage, you can actually end up spending more than originally intended. More on that below.

Distributed business logic

The most common struggle when it comes to managing Snowflake query performance, however, is distributed business logic.

From a warehouse management perspective, best practice is to have a homogenous workload on any given warehouse. Small warehouses run small queries, large warehouses run larger queries. This logic can help ensure adequate compute resources for each query.

This workload configuration, while it works great for controlling Snowflake costs, doesn’t always align with business logic. Some examples:

Service-level agreements that require certain resources to be available in certain formats
Client-specific warehouses that simplify billing, data management, and privacy and security
Specific tools configure a single warehouse for a workload—this is especially common with BI tools like Tableau, Looker, Sigma, etc. All queries, large and small, are processed by this single warehouse
Scalable data warehouses, where it becomes difficult to account for all the various query sizes across all your applications and clients

Ideally, to truly optimize performance and cost, users need to make decisions for queries from different workloads. Embedding the performance logic into the code is very difficult, especially considering that customers usually have a number of different workloads.

Maintaining and optimizing that logic becomes extremely challenging when it’s distributed across multiple ETL jobs, pipeline definitions, and application business logic.

Does Snowflake query performance optimization increase costs?

Yes, Snowflake query performance impacts your spend. Sometimes poor performance saves you. But sometimes it actually costs you. Consider the following examples:

A query running on an XS warehouse can run in half the time on a Medium warehouse—you’re getting 2X performance at 4X the cost, which looking at the cost alone isn’t a fair trade
A query running on a Medium warehouse can run in 25% of the time on a Large—you’re getting 4X performance at 2X the cost, which is a fair trade

Example #2 shows how you’re actually saving resources while improving performance. Example #1 shows how you’re spending more compute resources while improving performance. Although this hypothetical is very simplistic—perhaps to the point of being contrived—it illustrates that it’s not a straightforward either-or.

Spillage monitoring is particularly important to keep in mind here. Let’s say you have a warehouse with insufficient memory to perform calculations, Snowflake may offload some data to the hard disk (local spillage) or to remote locations (remote spillage). In both cases, query execution times increase dramatically, requiring more compute resources to process.

What’s more, if you’re running multiple concurrent queries, they must either share available resources, or wait until a warehouse has sufficient resources to begin processing them. In some cases, running a query on a smaller warehouse could then take more than twice as long as running it on a larger warehouse.

What is query routing?

Query routing is a Snowflake optimization solution that allows you to match queries to the appropriate warehouse no matter how your Snowflake architecture is configured. It’s a scalable, flexible approach that helps decouple business logic from warehouse management logic.

Query routing works exactly as the name suggests. When a query comes into the system, it is automatically analyzed by an AI algorithm and sent to the warehouse with the best available resources to handle it.

How does query routing work?

Let’s walk through how we handle query routing in Keebo. By accessing query history, Keebo gains significant insight into the length of time similar queries have run, plus how much data they’ve processed. This, then, enables us to make significant decisions about the query at hand.

Additionally, Keebo has access to real-time data around queries that can inform query routing decisions:

1. Real-time information about each query attribute:

QUERY TEXT: The content of the query as received by KQR
QUERY TEMPLATE: The query template (sanitized query hash) of the query as received by KQR
TIMESTAMP: The timestamp of the query’s submission
USER NAME: The user executing the query
ROLE NAME: The role associated with the user
ORIGINAL WAREHOUSE: The name of the original warehouse

Examples:

Routing queries to smaller warehouses during off-peak hours
Prioritizing queries from certain users and sending them to larger warehouses

2. Real-time information about warehouses and the system:

NUMBER_OF_RUNNING_QUERIES: The current count of queries being processed by a warehouse
LAST_QUERY_END_TIME: The timestamp of the last completed query in UTC
LAST_QUERY_EXECUTION_STATUS: The execution status of the most recent query
AVG_EXECUTION_TIME_LAST_FIVE_MINUTES: The average execution time of all queries processed by a warehouse in the past 5 minutes
NUMBER_OF_PROCESSED_QUERIES_LAST_FIVE_MINUTES: The total number of queries processed by a warehouse in the last 5 minutes
NUMBER_OF_FAILED_QUERIES_LAST_FIVE_MINUTES: The total number of queries that had a “Failed” status when processed by the Target warehouse in the last 5 minutes

Examples:

Knowing that there are 5 queries already running in a warehouse, we can route new queries to a different warehouse
Knowing that the last query ended more than 5 minutes ago (which is greater than the auto-suspend value), we might conclude that the warehouse is suspended and decide not to wake it up to run a query on a different warehouse

Keebo can combine query history-based metrics (e.g. average bytes scanned by similar queries) and real-time query and system information when making routing decisions.

What’s more, Keebo can analyze queries and see which tables they’re going to process. Some tables may have billions of rows of data, so a query searching that table will necessarily be slow. Anything that touches that massive table, then, could result in automatic query routing to a larger warehouse.

What are the benefits of query routing for the business?

There are a number of benefits businesses realize from query routing that not only improve Snowflake query performance, but benefit the organization more broadly.

Snowflake query performance

The first and most obvious benefit of query routing is an improvement in Snowflake query performance. By sending queries to warehouses with available resources, queries can execute more quickly and efficiently.

Snowflake cost reduction

An additional benefit of query routing is a reduction in overall Snowflake spend. With query routing you don’t have to provision additional compute resources to handle queries, as in many cases you’re simply using resources you’ve already provisioned.

This can be especially useful when you have an SLA that requires certain performance benchmarks (e.g. fixed guarantees that queries must run within a certain timeframe). Warehouse optimization, a common cost optimization practice, sometimes runs the risk of sacrificing performance for cost savings. With query routing, you get the added benefit of the best of both worlds.

In fact, using warehouse optimization in tandem with query routing can be a powerful cost control strategy. Query routing helps you maximize the efficiency of the resources you’ve already provisioned, while warehouse optimization can provision more resources by scaling up warehouses only when needed.

Save engineering time and effort

Query routing is an automated process. Engineers no longer need to spend excessive resources building out their cloud infrastructure only to have to rearrange their warehouses as demands change and their infrastructure continues to scale.

Customer satisfaction and retention

Because query routing improves Snowflake query performance, your front-end applications will run faster and more smoothly. This prevents customer churn and ensures a positive experience while using your products.

Other strategies for improving Snowflake query performance

Query routing is one of many tools that data teams have for optimizing Snowflake query performance. Let’s take a quick look at some of the others now.

Warehouse optimization

We’ve already talked about warehouse optimization. Basically, this is a dynamic approach to resource provisioning where warehouses are scaled up or down depending on their workloads. Additionally, warehouse optimization can include adjusting your auto-suspend so you’re not running your warehouses when there are no queries. That’s why Keebo advocates that Snowflake optimization be handled by AI, not human data engineers.

One of the limitations of warehouse optimization is that you only have control over resource provisioning, not the queries themselves. So if you have an SLA that mandates a certain performance level, you’re limited in how well you can control costs.

This is why combining warehouse optimization with query routing is an excellent way to control your costs. Query routing offers a way to complement warehouse optimization by providing a level of individual, query-by-query control that the former tool doesn’t.

Query optimization

Another way to improve Snowflake query performance is to optimize the queries themselves. This involves simplifying your use of SQL so you’re only grabbing the information you need. One basic example is to use the SELECT command to name only the specific columns of data you need, rather than use SELECT *

But there are plenty of areas in which query optimization isn’t the best option. For example, if you’re using Looker’s drag-and-drop interface that generates a query via machine learning, you really don’t have the opportunity to optimize that query. In that case, the better solution is to route the query to the warehouse that can run it the fastest and most cheaply.

Performance tuning

Finally, you can engage in a range of Snowflake performance tuning tactics to improve query performance. Data loading and transformation are two common ways to supplement the optimization approaches listed so far.

Snowflake query performance FAQs

How do you optimize query performance in Snowflake?

To optimize query performance in Snowflake without using too many engineering resources, use an AI-powered tool to automatically scale warehouses up and down, dynamically adjust auto-suspends, and route queries to the warehouses best resourced to handle them.

Why are some Snowflake queries slow?

Query latency can happen for a number of reasons. Under-provisioned warehouses, poorly written queries, and inefficient data cloud architecture can all contribute to Snowflake query latency.

What is the query limitation of Snowflake?

Within Snowlake, query text is limited to 1MB per statement. This includes any literals contained within the statement.

Final thoughts on Snowflake query performance

Snowflake query performance doesn’t have to come at the expense of cost savings. In fact, the two can mutually reinforce each other. So there’s no reason to keep overspending on Snowflake.

Learn more about Keebo’s AI-powered Snowflake optimization tools here.