What is the difference between a view and a materialized view?

A view is a virtual table defined by a stored query, executed every time it's referenced, providing real-time data without storing results. A materialized view, conversely, physically stores the pre-computed results of a query, offering significantly faster read performance at the cost of potential data staleness and increased storage.

Views

Views act as a stored query that runs at read time against the underlying tables. They offer a powerful way to abstract complex joins, filter data for specific user roles (security via row/column-level access), and reuse common logic across multiple queries without duplicating code. Since no data is stored, views always reflect the most current state of the base tables, incurring no storage cost. However, their performance is directly tied to the complexity and efficiency of the underlying query, which can be slow for large datasets or expensive aggregations, as the query is re-executed on every read.

Materialized Views

Materialized views (MVs) store the physical result of a query, much like a regular table. This pre-computation significantly speeds up subsequent reads, making them ideal for dashboards, reporting, or analytical queries that frequently access expensive aggregations or complex joins. MVs require a refresh mechanism—either manual, on a schedule (e.g., hourly, daily), or sometimes automatically on commit/transaction (depending on the database system). This refresh process consumes compute resources and introduces a trade-off: the data in an MV can be stale, reflecting the state at its last refresh.

The core trade-offs revolve around performance, data freshness, and resource cost. Views provide real-time data with zero storage but can be slow. MVs offer blazing-fast reads and reduce load on source systems, but require storage, compute for refreshes, and introduce data latency. For instance, a daily sales dashboard querying billions of raw transactions would be prohibitively slow with a view. An MV pre-aggregating SUM(amount) by sale_date would provide near-instant results, even if the data is a few hours old.

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE_TRUNC('day', sale_timestamp) AS sale_date,
    SUM(amount) AS total_sales
FROM
    sales_transactions
GROUP BY
    1;

In the interview, also mention how different systems handle MVs (e.g., Snowflake's automatic clustering and micro-partitioning for MVs, or dbt's materialized='view' vs. materialized='table' or incremental' strategies).