An identical Snowflake query reruns hourly yet never reuses the result cache — what would you check?

## Why the checklist matters A result-cache hit is the cheapest outcome in Snowflake: no warehouse resumes, no warehouse credits. When a scheduled dashboard query misses every time, the account pays full compute for an answer it already has. The fix is almost always in the *query text or the loading pattern*, not in the warehouse. ## Condition 1 — syntactically identical text Snowflake matches the incoming statement against the stored one. A rendered date literal, an injected comment carrying a run id, a different alias, or a different amount of whitespace produces a different statement. This is the single most common cause in practice, because BI tools and schedulers love to template. ```sql -- Never reusable: a new literal every hour SELECT ... WHERE ts >= '2026-08-21 14:00:00'; -- Reusable across the whole day: a stable boundary SELECT ... WHERE ts >= DATE_TRUNC('day', '2026-08-21'::timestamp); ``` The practical remedy is to make the predicate stable at the granularity the business actually needs. An hourly dashboard that truncates to the day is reusable all day; one that pins to the current second never is. ## Condition 2 — no execution-time evaluation A result can only be reused if it would still be the same answer. Functions that must be evaluated when the query runs — `CURRENT_TIMESTAMP()` is the canonical example — disqualify it, as do non-deterministic user-defined functions and external functions. This is the second-most-common cause, and it hides inside otherwise innocent relative-time filters: ```sql -- Not reusable: evaluated at execution time WHERE event_ts >= DATEADD(hour, -24, CURRENT_TIMESTAMP()) ``` If the workload genuinely needs "last 24 hours", accept the miss or materialize a rolling aggregate the dashboard can read cheaply. ## Condition 3 — the data has not changed Any DML that changes a micro-partition of a referenced table invalidates stored results that depend on it, and so does background maintenance that rewrites micro-partitions. A table under continuous ingestion therefore invalidates results constantly. The architectural answer is to separate the *serving* object from the *landing* object: land into a raw table, refresh a stable aggregate on a schedule, and point the dashboard at the aggregate — which stays unchanged between refreshes and is therefore cacheable. ## Condition 4 — the session parameter `USE_CACHED_RESULT = FALSE` disables reuse. It is a legitimate benchmarking tool, and a classic accident: someone sets it in a session template, a role's parameters, or a connection's init statements while investigating performance and never puts it back. Check it explicitly with `SHOW PARAMETERS LIKE 'USE_CACHED_RESULT'`. ## Condition 5 — privileges of the executing role The cache is account-wide, but reuse requires the role running the query to hold the privileges needed on all tables the query references. A service role with narrower grants than the analyst who warmed the cache will not be served from it. Row access policies and other role-dependent security constructs likewise mean two roles are not asking the same question. ## Condition 6 — the retention window A result persists 24 hours from its last use, extended by each reuse up to 31 days from the original execution. A monthly report simply cannot hit the cache. A daily one is on the boundary; an hourly one is comfortably inside it — provided every other condition holds. ## How to confirm rather than guess Run the query twice by hand in one session and look at Query Profile: a hit renders as a single `QUERY RESULT REUSE` node. In `ACCOUNT_USAGE.QUERY_HISTORY`, a reused result shows essentially no bytes scanned and a millisecond-scale duration. If two supposedly identical runs both scan bytes, dump the two `QUERY_TEXT` values side by side — the difference is usually visible immediately, and usually a templated literal. ## What the fix is worth For a dashboard refreshed by many users against a table that changes a few times a day, converting a never-cacheable query shape into a cacheable one removes almost all of its compute cost, and it removes it without touching warehouse configuration at all. That is why "why isn't this cached?" is a better first question than "should we size up?".