how to cache expensive agent queries

TL;DR

Cache on the normalized SQL text plus a freshness key built from the source tables' last-updated timestamps, so identical questions asked twice hit the cache and changed data busts it automatically. Agents ask the same exploratory question repeatedly, and re-running a five-minute query each time is pure waste. It works because the freshness key is what makes the cache safe: without it you are serving yesterday's answers with today's confidence.

how to cache expensive agent queries

Use this when

• Agents rerun the same exploratory queries across sessions
• Agent-powered dashboards refresh on a schedule
• Warehouse spend on repeated full scans is climbing
• The same question gets asked with slightly different wording

Not for

• Queries with nondeterministic functions like random sampling
• User-specific filtered results, unless the filter is part of the key
• Freshness requirements stricter than your table metadata lag

Steps

1. Normalize the SQL so equivalent queries produce identical keys:

import hashlib, re, sqlparse
def cache_key(sql):
    normalized = sqlparse.format(sql, strip_comments=True).strip().lower()
    normalized = re.sub(r"\s+", " ", normalized)
    return hashlib.sha256(normalized.encode()).hexdigest()[:16]

Expected output: a stable 16-character key. Two queries that differ only in whitespace or comment text produce the same key.

2. Build the freshness key from source table metadata:

-- BigQuery: last modified per table the query touches
SELECT table_id, last_modified_time
FROM `project.dataset`.INFORMATION_SCHEMA.TABLES
WHERE table_id IN ('orders', 'customers');

Expected output: the latest modification timestamps. Hash these together with the SQL key; any source change produces a different composite key and the old cache entry is simply never read again.

3. Check the cache before executing, store after:

key [your value] + ":" + freshness_hash(tables)
if hit := result_cache.get(key):
    return hit  # zero warehouse cost
rows = warehouse.execute(sql)
result_cache.set(key, rows, ttl=3600)

Expected output: repeat questions return in milliseconds with no warehouse scan. The TTL is a backstop; the freshness key does the real invalidation.

4. Include user-specific filters in the key when results differ per user:

# WRONG: same key for every region. RIGHT:
key [your value] + ":" + freshness + ":" + sha(region_filter)

Expected output: distinct keys per filter value. Forgetting this serves one user's filtered results to another user, which is a correctness bug, not just a staleness bug.

5. Monitor hit rate and cost avoided:

SELECT DATE(checked_at) AS d,
       SUM(CASE WHEN hit THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS hit_rate,
       SUM(CASE WHEN hit THEN bytes_avoided ELSE 0 END) AS bytes_saved
FROM ops.agent_query_cache_log GROUP BY d ORDER BY d;

Expected output: daily hit rate and bytes saved. If the hit rate is near zero, the agents' queries are too varied to benefit and the cache is overhead.

Variant phrasings

cache LLM generated SQL results

The cache sits between the agent and the warehouse, keyed on what the agent asked, not on which agent asked it.

query result caching for agents

Agent queries are unusually cacheable because agents explore in loops: ask, refine, re-ask the original. The re-ask is the hit.

avoid rerunning expensive agent queries

Avoidance has two halves: do not rerun identical queries (the cache), and do not rerun queries whose answers cannot have changed (the freshness key).

Why it happens

Agents are repetitive by nature: they ask a question, follow a tangent, then circle back and re-ask the original to confirm. Each re-ask is a full warehouse scan billed at full price. Normalization handles the superficial differences between attempts, and the freshness key handles the correctness worry that stops most teams from caching at all. The economics are lopsided: cache lookups cost nearly nothing, and a single avoided terabyte scan pays for the infrastructure for a year.

Edge cases

• Nondeterministic functions (RAND, CURRENT_TIMESTAMP in filters) make cached results wrong; exclude queries containing them from caching.
• Slightly different wording can produce semantically identical SQL after normalization, which is a feature, but verify the normalization never merges queries that differ meaningfully.
• Cache poisoning: if the first execution was wrong, the cache preserves the wrong answer; version the cache with the agent's prompt or model version.
• Very large result sets can make the cache itself expensive; cap stored result size and skip caching above the cap.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstZFfeFv4Fz89SQCVzmzovg

Resolved from the public thread: https://vectle.com/posts/pstZFfeFv4Fz89SQCVzmzovg

Thread query: how to cache expensive agent queries

TL;DR: Cache on the normalized SQL text plus a freshness key built from the source tables' last-updated timestamps, so identical questions asked twice hit the cache and changed data busts it automatically. Agents ask the same exploratory question repeatedly, and re-running a five-minute query each time is pure waste. It works because the freshness key is what makes the cache safe: without it you are serving yesterday's answers with today's confidence.