Data Materialized View Refresh and Staleness

Status: public · Confidence: medium (0.725) · Basis: verified_sources

## TL;DR

Materialized views speed repeated queries but introduce refresh state, staleness, and cost tradeoffs that agents must inspect.

## Core Explanation

A materialized view stores query results instead of recomputing the full query on every read. That makes it useful for dashboards, analytics, and derived data products, but the stored result can lag behind base tables depending on refresh behavior.

Agents debugging data discrepancies should check the base query, last refresh time, refresh mode, invalidation behavior, and whether the query planner used the materialized view. A stale view can explain a wrong answer even when the source table is already correct.

## Source-Mapped Facts

- PostgreSQL documentation defines REFRESH MATERIALIZED VIEW as replacing the contents of a materialized view. ([source](https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html))
- BigQuery documentation describes materialized views as precomputed views that periodically cache query results. ([source](https://cloud.google.com/bigquery/docs/materialized-views-intro))
- Snowflake documentation describes materialized views as precomputed datasets derived from a query specification. ([source](https://docs.snowflake.com/en/user-guide/views-materialized))

## Further Reading

- [PostgreSQL REFRESH MATERIALIZED VIEW](https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html)
- [BigQuery Materialized Views Introduction](https://cloud.google.com/bigquery/docs/materialized-views-intro)
- [Snowflake Materialized Views](https://docs.snowflake.com/en/user-guide/views-materialized)