Materialized Views for Precomputed Query Acceleration

Automatically maintain precomputed query results for faster repeated access.

A materialized view stores the result of a query and keeps it current automatically as the base table changes.

A materialized view is like a running total kept updated in real time on a whiteboard, versus a regular view which is a formula you have to fully recalculate by hand every single time someone asks for the answer.

Key Concepts

    CREATE MATERIALIZED VIEW daily_totals AS
      SELECT order_date, SUM(total) AS revenue, COUNT(*) AS orders
      FROM orders GROUP BY order_date;

Three things this sits between.

    regular view      re-runs the query every time      always current, always costs
    summary table     you write the refresh logic       fast, and yours to keep correct
    materialized view Snowflake maintains it            fast, and maintained for you

The maintenance is incremental, not a rebuild. When the base table changes, a background service reprocesses only the affected micro-partitions rather than recomputing the whole aggregate. That is what makes it viable on a large table.

The restrictions are strict, and they follow from that. Snowflake must be able to compute the delta cheaply, so it allows only what it can maintain incrementally:

  - one table in the `FROM` clause — no joins
  - a limited set of aggregate functions
  - no `ORDER BY`, no window functions, no `UNION`
  - cannot reference another view or materialized view

FROMORDER BYUNION

Most interesting queries involve a join, so the first reaction is usually that it does not fit. That reaction is correct more often than not.

The optimiser will use it without being asked. This is the genuinely useful part. A query against the base table that could be answered from the materialized view is rewritten to use it:

    SELECT order_date, SUM(total) FROM orders
     WHERE order_date > '2026-01-01' GROUP BY order_date;
    -- served from daily_totals, though it names orders

So existing queries and dashboards get faster without being changed.

You are paying for the background service. Maintenance consumes serverless credits in proportion to how much the base table changes. On a table written continuously, that cost is continuous — and it can exceed what you saved on reads.

So check the cheaper options first. If the same query runs repeatedly against unchanged data, the result cache already returns it in milliseconds for free. If a dashboard runs hourly against a table loaded nightly, a scheduled task writing a summary table is cheaper and has no restrictions.

A materialized view earns its cost in a narrower case: a large, slowly-changing table, a genuinely expensive aggregation, and many queries with different filters — so the result cache misses, but the aggregate is reusable.

What the interviewer is probing.1. "What is the restriction worth knowing?" Probing: the limits. Stalls: "None." Moves up: single table only, no joins, and a limited set of aggregates — which rules out most reporting queries.

2. "How is it maintained?" Probing: the mechanism. Stalls: "On refresh." Moves up: Snowflake refreshes it automatically with serverless credits as the base table changes, so a high-churn table is expensive.

3. "Does a query have to reference the view?" Probing: the nice property. Stalls: "Yes." Moves up: no — the optimiser can rewrite a query against the base table to use it.

4. "What is the usual cheaper alternative?" Probing: judgement. Stalls: "Nothing." *Moves up:* a table refreshed by a task and a stream, which costs less and has no feature restrictions.