3 Strategies to Prevent Auto-Generated Queries from Impacting Cost and Performance

Most organizations run some kind of business intelligence, data modeling, data visualization, or AI/ML tool on top of Snowflake. For most of these integrations, engineers don’t write the SQL by hand. The tool generates it from what users click, filter, and request.

Most of that generated SQL is fine. Some isn’t, and because no one wrote it by hand, it’s harder to spot and harder to fix.

You usually can’t edit the generated SQL directly. The fixes have to happen in the model layer that writes the SQL, or in how the warehouse runs it. In this article, we’ll walk through three strategies to keep those queries from driving up cost and slowing down performance.

How Do Auto-Generated Queries Work?

Most platforms that integrate with Snowflake or other data cloud layers have their own mechanism for generating queries. For brevity, we’ll use one platform, Looker, as an example of how the process works.

Looker generates SQL through LookML, a modeling language that abstracts SQL complexity and builds queries based on user interactions:

• Looker uses LookML to define data relationships, dimensions, measures, and filters. This creates a blueprint for future query generation.
• When a user interacts with Looker, the platform uses this blueprint to generate a SQL query that includes the user’s selections.
• Throughout this process, Looker can create temporary or persistent derived tables, which pre-aggregate data to improve performance on complex queries.

What Are the Most Common Inefficiencies with Auto-Generated SQL Queries?

Tools like Looker are built to generate efficient queries, but some won’t be. Left unaddressed, those queries can lead to excess compute consumption, slow query performance, and concurrency pressure.

Excessive data fetching

Many auto-generated queries use SELECT *, which retrieves every column from a table. If you only need a few columns, the query reads data it doesn’t use. Snowflake stores data by column, so reading fewer columns means scanning less data. SELECT * gives up that advantage, and the query takes longer and uses more compute.

Inefficient joins

Another common issue with auto-generated queries is use of poorly optimized join strategies, like Cartesian joins or joins without proper keys. This can cause unnecessary complexAnother common issue is a poorly structured join, such as a Cartesian join or a join without proper keys. These add work to how data is retrieved and consume compute the query doesn’t need.

For example, the following query:

Gives Snowflake nothing to prune with, so no micro-partitions can be skipped.

Wraps order_details in a subquery. That’s mostly a readability problem, since Snowflake’s optimizer usually flattens it.

Has no filter on any table, so it reads every row of orders, customers, and order_details.

SELECT     orders.order_id,     orders.customer_id,     customers.customer_name,     SUM(od.quantity * od.unit_price) AS total_amount FROM orders JOIN customers ON orders.customer_id = customers.customer_id JOIN (     SELECT order_id, quantity, unit_price FROM order_details ) AS od ON orders.order_id = od.order_id  -- Unnecessary subquery GROUP BY orders.order_id, orders.customer_id, customers.customer_name;

If the dashboard only needs recent orders, a more efficient version looks like this:

SELECT     orders.order_id,     orders.customer_id,     customers.customer_name,     SUM(order_details.quantity * order_details.unit_price) AS total_amount FROM orders JOIN customers ON orders.customer_id = customers.customer_id JOIN order_details ON orders.order_id = order_details.order_id  -- Direct join, no subquery WHERE orders.order_date >= DATEADD(month, -3, CURRENT_DATE())  -- Filter lets Snowflake prune partitions GROUP BY orders.order_id, orders.customer_id, customers.customer_name;

This rewritten query:

• Removes the subquery wrapper around order_details
• Filters orders by date, so Snowflake can skip micro-partitions outside the range
• Keeps the same columns as the original

The date filter changes the result set, so it only applies when the dashboard needs a recent window. Pruning works best when the table’s data is naturally ordered or clustered by order_date.

Complex query logic

Auto-generated queries can also carry more logic than they need. Here’s an example with a few common issues:

• Uses DISTINCT even though GROUP BY already returns one row per order
• Filters on LIKE ‘%Smith%’, a leading wildcard that limits partition pruning
• Spells out the status filter with OR, which is harder to read than IN

SELECT DISTINCT     orders.order_id,     orders.customer_id,     customers.customer_name,     orders.order_date,     SUM(order_details.quantity * order_details.unit_price) AS total_amount FROM orders JOIN customers ON orders.customer_id = customers.customer_id JOIN order_details ON orders.order_id = order_details.order_id WHERE     (orders.status = 'Completed' OR orders.status = 'Shipped')     AND customers.customer_name LIKE '%Smith%'     AND orders.order_id IN (         SELECT order_id FROM shipments WHERE shipped_date IS NOT NULL     ) GROUP BY orders.order_id, orders.customer_id, customers.customer_name, orders.order_date;

Here are some ways to improve it:

• Remove DISTINCT. GROUP BY already guarantees one row per order, so DISTINCT adds a deduplication step for nothing.
• Use IN for the status filter. Snowflake treats it the same as the OR version, but it’s easier to read and maintain.
• If the business logic allows a prefix match, LIKE ‘Smith%’ prunes better than ‘%Smith%’. If you need true substring search, Snowflake’s Search Optimization Service is built for that pattern.
• Keep the shipments subquery. Snowflake runs IN (subquery) as a semi-join. Replacing it with a plain JOIN would duplicate rows, and inflate total_amount, for any order with more than one shipment.

SELECT     orders.order_id,     orders.customer_id,     customers.customer_name,     orders.order_date,     SUM(order_details.quantity * order_details.unit_price) AS total_amount FROM orders JOIN customers ON orders.customer_id = customers.customer_id JOIN order_details ON orders.order_id = order_details.order_id WHERE     orders.status IN ('Completed', 'Shipped')  -- Same result, easier to read     AND customers.customer_name LIKE 'Smith%'  -- Prefix match, only if the business logic allows it     AND orders.order_id IN (         SELECT order_id FROM shipments WHERE shipped_date IS NOT NULL     )  -- Semi-join, no duplicate rows GROUP BY orders.order_id, orders.customer_id, customers.customer_name, orders.order_date;

With generated SQL, you can’t make these edits by hand. You can change the model that writes the SQL, and you can change where and how it runs.

Problems With Inefficient Queries for Front-End Users

Inefficient queries usually show up for front-end users first. The common symptoms:

• Slow dashboards and lag when generating visualizations and reports
• Timeouts and queuing when a burst of heavy queries hits a warehouse sized or configured for lighter load
• Inconsistent results when the generated SQL doesn’t match the business logic the model was meant to encode

Problems With Inefficient Queries for Data Teams and Engineers

Inefficient queries are just as hard on data teams. They raise compute costs, and they pull engineers away from higher-priority work.

Some of the key challenges include:

• Limited control, because engineers can’t edit generated SQL directly
• Increased concurrency pressure, as heavy queries running in parallel slow down other workloads
• Slower debugging, because engineers can see the problem in Query Profile but have to fix it upstream in the tool’s model rather than in the SQL
• Schema design constraints, as engineers sometimes restructure tables (for example, denormalizing data, adding materialized views, or pre-aggregating data) so auto-generated queries perform acceptably

How to Keep Inefficient Queries from Impacting Cost & Performance

You can address each of these issues directly, but it takes time and effort. These fixes are also hard to keep current by hand as dashboards and data change.

Snowflake handles part of this natively. The result cache serves repeated dashboard queries without using warehouse compute. Query Acceleration Service offloads outlier scan-heavy queries. Multi-cluster warehouses absorb concurrency spikes. The gap is tuning all of it continuously as generated workloads shift.

Here are three strategies. The first is a configuration change your team can make in Snowflake. The other two are what Keebo automates today.

1. Match warehouses to workloads, not applications

Many teams give each application its own warehouse: one for Looker, one for Airbyte, one for Fivetran. That keeps cost attribution simple and isolates workloads. The trade-off is utilization. Each warehouse ends up sized for its heaviest queries, even when most of its traffic is light.

For example, say 85% of Fivetran’s queries are light and 15% need more compute. Instead of upsizing the whole Fivetran warehouse for that 15%, you can send the heavy jobs to a larger warehouse you already run, or enable Query Acceleration Service to absorb the outliers.

The split can live in configuration. dbt lets you set the warehouse per model, and a BI tool can use a separate connection for heavy dashboards.

The limit is that this mapping is static. As dashboards and pipelines change, someone has to revisit it.

2. Autonomous warehouse optimization

Even with a good warehouse mapping, some queries won’t get the resources they need to run efficiently. In that case, you’ll need to provision more resources by scaling up your warehouses.

Snowflake makes it easy to scale warehouses up and down based on demand. The challenge is doing it manually. Changing warehouse settings by hand takes engineering time away from other work, and high-demand queries arrive at all hours, while your engineers aren’t on call 24/7.

Keebo adjusts warehouse settings autonomously to reduce spend. Our algorithms learn from your workload and adapt as it changes. You keep fine-grained control over how Keebo optimizes your Snowflake warehouses and can adjust those settings as needed.

3. 360-degree observability

With auto-generated queries, it can take work to see which queries and applications drive the most cost.

Snowflake records what you need to trace this: the client application on each session, query tags, a parameterized hash that groups repeated generated queries, and per-query compute credits in QUERY_ATTRIBUTION_HISTORY. The challenge is turning that into a ranked view of which applications drive cost, and keeping it current as dashboards change.

Here’s a starting point that ranks the costliest generated query patterns from the last seven days by application and warehouse:

SELECT     s.client_application_id,     q.warehouse_name,     q.query_parameterized_hash,     COUNT(*) AS executions,     SUM(a.credits_attributed_compute) AS credits,     ANY_VALUE(LEFT(q.query_text, 200)) AS sample_query FROM snowflake.account_usage.query_history q JOIN snowflake.account_usage.query_attribution_history a   ON q.query_id = a.query_id JOIN snowflake.account_usage.sessions s   ON q.session_id = s.session_id WHERE q.start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP()) GROUP BY 1, 2, 3 ORDER BY credits DESC LIMIT 25;

One limit to keep in mind: attributed credits exclude warehouse idle time and very short queries, so this ranks query cost, not total warehouse cost.

Keebo Workload Intelligence builds on the same data to provide observability into spend, query, warehouse, storage, and data health. For example, if one of your applications consistently generates inefficient queries, Keebo can isolate those queries and recommend how to reduce their cost.

Final Thoughts on Addressing Inefficiencies from Auto-Generated Queries

To be clear, not every auto-generated query is less efficient than one that’s hand-written. Humans make mistakes, and sometimes generated SQL is better than what a person would write.

The problem isn’t human vs. machine. It’s visibility into how generated queries actually perform, and the ability to act on it as workloads change. Snowflake gives you the telemetry and the controls. Keebo watches the generated workload continuously and adjusts warehouse settings as it changes.