data agent's cache serving yesterday's aggregates: ttl and invalidation

TL;DR

The agent's cache serves yesterday's aggregates because the TTL outlives the refresh cycle and nothing invalidates on load. Fix it by setting the TTL to match the data refresh cadence and by invalidating the cache explicitly every time the underlying table finishes loading.

Symptom

A data agent caches aggregate query results, but the cached values go stale: answers quote yesterday's numbers even though the underlying table refreshed hours ago. The cache TTL was set once and never revisited, and no invalidation fires when new data lands.

When to use

• Dashboards or agent answers show yesterday's numbers
• Cache TTLs were set once and never revisited
• Refreshes land but cached values do not move

When not to use

• The numbers are wrong but fresh (thats a computation bug, not caching)
• There is no cache involved (check the query path first)
• Staleness is acceptable and documented (then it is a feature, not a bug)

Steps

1. Find the current TTL and compare it against the actual refresh cadence:

print(cache_config["ttl_seconds"])  # compare with the warehouse refresh schedule

Expected output: the mismatch becomes obvious, e.g. a 24-hour TTL on data that refreshes hourly. The TTL must be shorter than the refresh interval, not longer.

2. Set the TTL to a fraction of the refresh cadence so stale windows stay small:

cache_config["ttl_seconds"] = 1800  # 30 min TTL on hourly-refreshed data

Expected output: the worst case staleness drops to the TTL value. TTL alone is a backstop, not the primary mechanism.

3. Invalidate explicitly when the load completes, instead of waiting for expiry:

def after_load(table):
    cache.delete_pattern(f"agg:{table}:*")

Expected output: the first query after a refresh recomputes from fresh data. Hook this into the same orchestrator step that marks the load complete.

4. Key cached aggregates by the data version, not just the query text:

key [your value]

Expected output: new data automatically misses the old cache entry. Version-keyed caches cannot serve stale data by construction.

Variant phrasings

cached dashboard numbers lag the warehouse

Same fix. Dashboards usually cache aggressively; align their TTL with the refresh job and invalidate on completion.

agent answers differ from direct queries

The agent reads the cache while you query the table directly. Check the cache key and TTL before doubting the agent's logic.

Why it happens

Caching and refreshing are owned by different parts of the system: the cache was configured when the data refreshed daily, then the refresh moved to hourly and nobody updated the TTL. No invalidation hook exists because the original design assumed expiry was enough. The gap between refresh cadence and cache lifetime is where stale answers live.

Edge cases

• Invalidating on load completion requires the loader to signal completion reliably. A loader that silently fails leaves the old cache in place indefinitely; pair invalidation with load monitoring.
• Partial refreshes (one partition reloaded) need partition-scoped invalidation, or the cache mixes fresh and stale partitions.
• Very hot keys may need a short stale-while-revalidate window instead of hard invalidation to avoid thundering herds on every refresh.

Provenance

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