Finding Expensive Queries: By Hand vs With an AI Agent

By hand, you find expensive queries by pulling your warehouse's query history, sorting by bytes scanned or compute time, and judging each result yourself. An agent does the same ranking continuously and hands you a prioritized list. Snowflake's QUERY_HISTORY view keeps 365 days of query records, so the raw data is rarely the problem. The problem is the recurring human effort of querying it, ranking it, and deciding which entries deserve engineering time.

Key Takeaways

• •The data needed to find expensive queries already exists: Snowflake's QUERY_HISTORY view retains 365 days of records, and BigQuery's jobs view retains 180 days.
• •The manual workflow is a recurring chore: query the history, rank by scan or compute spend, group similar queries, then decide what to fix. Each step is a handoff that people skip when busy.
• •An agent for this job identifies the costliest queries by scan and compute spend and ranks them for optimization. It does not rewrite your SQL on its own.
• •Humans still approve any change to production queries, warehouse settings or schedules. The agent's output is a ranked list, not a deployment.
• •Evaluate an agent on whether its ranking matches your own audit, whether it explains each rank, and whether its access is read-only by default.

The job, and who does it today

The job is simple to state: find the queries that cost the most, and rank them so someone fixes the right ones first. Cost here usually means bytes scanned, compute time, or credits consumed, depending on the warehouse. The goal is not a perfect cost model. It is a short list of queries where a rewrite, a clustering change or a schedule change would save the most.

Today that job usually lands on whoever feels the bill first. In a small team it is the data engineer who owns the warehouse. In a larger one it is a platform or FinOps-minded engineer who gets a message from finance and then spends an afternoon in the query history. Analysts rarely do it, because the history views need account-level privileges and a working knowledge of the warehouse's metadata.

Because nobody's role is "watch query cost every day," the work happens in bursts. A bill spikes, someone investigates, a handful of queries get fixed, and attention moves on. Questions such as "How much are we spending on Snowflake this month?" and "What's causing the warehouse queue to spike?" tend to arrive after the damage, not before it.

The manual workflow, step by step

The manual path has five steps. First, query the warehouse's history view for a time window. Second, filter out noise such as metadata commands and failed statements. Third, sort by the cost signal you trust: bytes scanned, execution time or credits. Fourth, group near-identical statements so one dashboard refreshing every ten minutes does not appear as hundreds of separate rows. Fifth, open the top entries one at a time and work out why each one is slow or wide.

• •Pull history: Snowflake exposes it through QUERY_HISTORY; BigQuery exposes job metadata through its INFORMATION_SCHEMA jobs views, which cover the past 180 days.
• •Rank: sort by scan volume or compute time, and decide which one matters for your pricing model.
• •Group: normalize query text so repeated runs collapse into one pattern with a total cost.
• •Attribute: map each pattern to a user, service account, dashboard or pipeline.
• •Hand off: file a ticket or message the owner with the evidence.

Each step is straightforward on its own. The cost is in repeating them, keeping the SQL you wrote last quarter up to date as schemas change, and remembering which thresholds you chose. Most teams keep a saved worksheet or notebook for this, which works until the person who wrote it moves teams.

Where the manual workflow breaks

The first break is cadence. A manual review happens when someone remembers or when a bill forces it. In between, an expensive query can run daily for weeks before anyone looks. Having 365 days of history, as the Snowflake docs describe, helps only if somebody reads it, and a retention window is a safety net for investigation, not a monitoring system.

The second break is ranking quality. Sorting by one metric hides important cases. A query that scans a lot but runs once may matter less than a moderately expensive query that runs every few minutes. Grouping by normalized text is fiddly, and every team ends up with slightly different rules. Two engineers auditing the same account can produce two different top-ten lists.

The third break is the handoff. Even a correct list is useless until the owner of each query sees it and agrees it matters. Attribution is manual, tickets go stale, and the evidence (the query ID, the cost pattern, the time window) often lives in a worksheet the owner cannot open. The engineer ends up as the integration layer between the history view and the people who can act on it.

What an agent does instead

The Cost Agent skill for this job, dw-finding-expensive-queries, is scoped narrowly: it identifies the costliest warehouse queries by scan and compute spend and ranks them for optimization. That is the whole claim. It reads the same history data you would read by hand, applies the ranking consistently, and returns a list ordered by how much each query costs rather than by how recently someone noticed it.

Because every agent is an MCP server, you ask for this from the client you already use. A prompt such as "How much are we spending on Snowflake this month?" or "What's causing the warehouse queue to spike?" can start from that ranked list instead of a blank worksheet. The Model Context Protocol specification defines how clients and servers exchange tools, so the same capability works in Claude Code, Cursor or any other MCP-compatible client.

What changes is cadence and consistency, not judgment. The ranking is produced the same way each time, so a query that climbs the list this week is comparable to last week's list. The agent does not decide that a query is wrong. It tells you which queries are the biggest spenders so a person can decide where optimization effort belongs, and it can chain with the other agents when the question widens to pipelines or usage.

Manual vs agent, side by side

The table below compares the two approaches on the dimensions that decide whether the work actually gets done. We describe effort qualitatively because we do not have a public benchmark for time spent on this task, and we would rather say so than invent a number.

Notice the last row. The agent shortens the path to a ranked list, but the decision and the change stay with your team. That is deliberate, and it is the next section.

What still needs a human approval

Ranking is read-only analysis, which is why it is safe to automate first. The open-source edition of Data Workers runs locally and is read-only by default, so identifying expensive queries needs no write access to your warehouse. Anything beyond that list crosses into change, and change needs a person.

Concretely, a human should approve rewriting a query, changing a clustering or partitioning scheme, resizing or suspending a warehouse, altering a schedule, or removing a dashboard that drives load. These changes affect people downstream, and a cost-optimal change can break a report someone depends on. Writes, approval workflows and audit trails belong to the Pro edition, so those steps leave a record of who approved what.

A human is also needed to judge business value. A costly query that feeds a revenue report may be worth every credit, while a cheap one that nobody reads is waste. The ranking tells you where the money goes. Whether it is well spent is a question for the owner of the query, and the agent should make it easy to ask rather than answer it for them.

How to evaluate an agent for this job

Start with a ground-truth test. Do the manual audit yourself on one week of history, write down your top ten, then run the agent on the same window and compare. You are not looking for an identical list. You are looking for the same expensive patterns near the top and a clear reason for any difference.

• •Does the ranking match your manual audit on the top entries, and can the agent explain each rank?
• •Does it collapse repeated runs into one pattern instead of listing hundreds of rows?
• •Is access read-only by default, and does your data stay in your infrastructure?
• •Does it name the query, the owner and the time window so someone can act without re-deriving the evidence?
• •Does it stop at recommendations and route any change through an approval step?

Be skeptical of any agent that claims a savings figure before it has seen your account. Costs depend on your pricing model, concurrency and data layout, and a vendor number from someone else's warehouse tells you little about yours. Measure savings yourself: record the cost of a fixed set of queries before a change and after it, using the same history view you used to find them.

Finally, test the failure mode. Ask a question the data cannot answer and check that the agent says so. A trustworthy cost agent reports what it could and could not see, such as a missing permission or a gap in the history window, instead of filling the gap with a plausible guess.

Frequently Asked Questions

Can an AI agent find expensive queries automatically?

Yes, for the identification and ranking step. The Cost Agent's skill identifies the costliest warehouse queries by scan and compute spend and ranks them for optimization. It works from the history data your warehouse already keeps. It does not change queries or settings without a person approving the change.

Do I still need to know SQL and warehouse internals?

You still need enough judgment to review a recommendation and approve a change. What you no longer need is to rebuild the history query, ranking rules and grouping logic every time you want the list. Your engineers keep the decisions, and the agent takes over the repetitive reading and sorting.

Is it safe to point an agent at my warehouse?

For this job it can be. Ranking queries is read-only analysis, and the open-source edition is read-only by default and runs locally, so data stays in your infrastructure. Check the permissions you grant, keep them scoped to the history views, and require approval and audit trails before enabling any write.