Materialised views and caching

Lesson 3 of 5 in Analyzing Data.

There are four places an answer can be remembered on this platform, and the thing to notice is that every one of them is a trade of freshness for cost or speed — but each one invalidates on a different trigger, which is why "the dashboard is showing yesterday's number" has four possible explanations that look identical.

Redshift result caching — leader-node memory, on by default, free. Invalidated when the underlying tables change.
Materialized views — stored results in the warehouse, refreshed incrementally or fully, manually or automatically. Invalidated only by a refresh.
Athena query result reuse — opt-in, serves a stored result for an identical query within a maximum age. Invalidated only by that age, never by new data landing in S3.
QuickSight SPICE — an imported snapshot in the BI tool. Invalidated only by a refresh, and a failed refresh is invisible to the reader.

Read that list again as a staleness risk ranking and it reorders itself. The result cache is the safest because the data itself invalidates it. Materialized views are next, because you control the refresh and can reason about it. Athena result reuse and SPICE are the dangerous ones, because in both cases new data arriving changes nothing, and the only thing standing between a reader and a stale number is a setting somebody chose months ago.

The same four, as a reference. The column that decides a scenario is the third one — what has to happen before the reader sees new data.
Cache	On by default	Invalidated by	The staleness failure mode
Redshift result cache	yes, and free	any modification to the referenced tables or views	none — but it also never applies to Spectrum queries or to `GETDATE`-style functions
Materialized view	no — you create it	a refresh, manual or automatic	an auto refresh delayed by load, or a full recompute forced by `VACUUM` on a base table
Athena result reuse	no — opt in per query or workgroup	the configured maximum age, nothing else	new objects in S3 are invisible to it for up to 7 days
QuickSight SPICE	chosen per dataset against direct query	a successful scheduled or API refresh	a failed refresh serves the old snapshot with no visible error

The four-cache incident, and how to tell which one it is

A stakeholder says the number is wrong. The pipeline is green. Work the caches outward from the data, because each one has a different tell.

Query the base table directly, with a function the cache cannot serve — in Redshift, include GETDATE() in the select list, which disqualifies the result cache outright. If the base table has the right number, the defect is downstream of it and the pipeline is innocent.
Check the materialized view's last refresh. If the view is behind, ask why — a delayed automatic refresh and a VACUUM-forced full recompute look the same from outside and have different remedies.
Re-run the Athena query with result reuse disabled. If the number changes, the max age is your answer, and the question is what age that dashboard should actually have.
Check the SPICE refresh history, not the dashboard. A failed refresh is invisible in the dashboard by design.

The reason this ladder is worth memorising is that steps 3 and 4 produce the same symptom as a broken pipeline and are not a pipeline problem at all — which is why a freshness measurement published as a metric belongs at the consumer end, not just at the ingestion end.

Key terms: Materialized view, Redshift result caching, Athena query result reuse, SPICE, Freshness check

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.