A Materialized View is a database object that stores the results of a query as a physical table, rather than recomputing those results each time the query is executed. Unlike a standard (virtual) view, which is just a stored SQL query recomputed on every access, a materialized view persists data on storage. This makes it a powerful optimization for repeated analytical workloads but introduces the challenge of keeping the stored results synchronized with the underlying base tables.
Materialized Views vs. Standard Views
- Standard View: A saved SQL query alias. Every access re-executes the full underlying query. No storage overhead, but no performance benefit for complex queries.
- Materialized View: Results are pre-computed and stored as a physical Iceberg table. Queries are served instantly from the pre-computed data. Requires a refresh strategy (scheduled, incremental, or on-demand) to stay current.
Incremental Refresh
The key challenge with materialized views is refresh cost. Full refresh (recomputing from scratch) can be expensive for large tables. Incremental refresh (identifying and applying only the changes since the last refresh) is far more efficient but technically complex. Apache Iceberg's snapshot history enables efficient incremental refresh: by comparing the current snapshot to the snapshot at the last refresh, an engine can identify exactly which data changed and update only the affected rows in the materialized view.
Materialized Views in the Iceberg Lakehouse
The Apache Iceberg specification has included early materialized view definitions, and multiple engines are implementing full support. In Dremio, the equivalent concept is Data Reflections. In DuckDB and Trino, native SQL materialized view syntax is supported for Iceberg tables. The key advantage in an open lakehouse is that a materialized view created by one engine can be read by any other compatible engine, since it is stored as a standard Iceberg table.
