In a cloud warehouse, how does a result cache differ from a compute cluster's local data cache?

## Two caches, two layers A separated-architecture warehouse typically has at least two distinct caches on the read path, and confusing them is a common interview stumble because both make a repeated query faster. **The result cache** sits in the shared metadata/service tier, above compute. Its key is essentially the query plus the version of the data it read. On a hit, the stored result set is returned directly. No cluster needs to be running; no bytes are scanned; no compute time is billed. This is why an identical dashboard refresh can return in milliseconds while the cluster is suspended. **The local data cache** sits on each compute cluster, on the workers' attached NVMe SSDs (backed by in-memory page cache). It holds raw column chunks fetched from object storage. On a hit, the query still runs — plan, scan, filter, join, aggregate — it simply reads the bytes locally rather than over the network. Compute time is billed as normal; what you save is remote-read latency and throughput. Some engines add a third layer between them, caching intermediate or partially aggregated results, and all of them cache metadata and file statistics. The two above are the ones interviewers ask about. ## What invalidates each The result cache is invalidated by change to any table the query read. Because the metadata layer knows the current version of every table, a commit to an input table means the cached entry no longer matches the key and the query re-runs. Entries also age out on a retention policy. And a query is usually ineligible in the first place if it is not deterministic — calls to the current timestamp, random values, or session-dependent context mean the same text would not produce the same answer. Cache lookup is normally an exact-ish match on the query, so a changed literal, an added column, or in some engines even differing whitespace is a miss. The local data cache is invalidated by eviction and by cluster lifecycle. Entries are evicted when the working set exceeds the cluster's aggregate SSD capacity, typically on a least-recently-used basis. The whole cache is lost when the cluster suspends, and substantially disturbed when it is resized, because the machines change and file-to-node assignment shifts. New file versions simply become new cache entries; the old chunks age out rather than being explicitly purged. ## Why the difference is operationally visible Several familiar symptoms fall straight out of this: - **The same query is fast on cluster A and slow on cluster B.** Caches are per-cluster. A second cluster reading the same table starts cold even though the data is identical, because the object store is shared but the SSDs are not. - **A dashboard is instant for the first viewer and instant for everyone after, until someone loads data.** Result-cache hits, then the load commits a new table version and the next refresh recomputes. - **A tiny change to the query text costs a full scan.** Result-cache keys are match-based; adding a column or nudging a filter literal produces a key that has never been seen. - **A query that runs at wall-clock speed but bills almost no compute.** A result-cache hit. If your cost report shows a heavily-run query consuming little compute, suspect this rather than assuming the query is cheap to execute. ## Using them deliberately For repeated identical reads — a dashboard tile many users open — the result cache is the cheapest possible outcome, and the way to get it is to make the generated SQL stable: no injected timestamps, no per-user literals where a parameter is not needed, no random cache-busting comment. BI tools that stamp the current time into every query defeat it completely, and that is a real and fixable cost problem. For repeated scans of the same data with different filters or groupings — exploratory analysis, a family of related reports — the result cache never fires but the data cache does, so what matters is keeping the cluster alive and sized so its aggregate local SSD comfortably holds the working set. If the second run of a scan still reads mostly remote bytes, the working set is being evicted and you need a larger cluster, tighter pruning, or a smaller table footprint. A subtlety worth stating in an interview: these caches do not weaken consistency. Both are keyed to a committed table version, so a result-cache hit reflects a genuine committed state of the data, not a stale guess. The freshness question is whether the state is the *latest* one, and the metadata layer answers that on every lookup. ## Summary framing The result cache saves you from running the query. The data cache saves you from crossing the network. One is global and version-scoped; the other is per-cluster and lifecycle-scoped. Cost-wise the first is the one that shows up as compute you never paid for, and the second is the one that shows up as queries that finish in a third of the time on a warm cluster.