Skip to main content

Materialized Views for Transformation

In Snowflake, views are typically used to simplify complex SQL logic or enforce column-level security. However, a Standard View is just a saved query; every time a user queries it, Snowflake must execute the underlying SQL against the base tables.

If that query involves massive aggregations across billions of rows, it will be slow and expensive. To solve this, Snowflake offers Materialized Views.

Topics Covered​

  • Standard Views vs Tables vs Materialized Views
  • Auto-Refresh Behavior and Costs
  • Limitations and Supported SQL
  • Using Materialized Views for Query Rewrite

Comparison Matrix​

FeatureStandard ViewStandard TableMaterialized View
Storage CostZero (Just metadata)High (Stores all data)High (Stores the pre-computed results)
Compute CostBilled on every readBilled once on writeBilled automatically in background during data changes
Query SpeedSlow for complex aggregationsExtremely FastExtremely Fast
Data Freshness100% Real-timeRequires manual updatesNear Real-time (Automated)

Hands-On: Creating a Materialized View​

Let's assume a telecommunications company in Chennai has a massive table containing billions of Call Detail Records (CDRs). The BI team frequently queries the total call duration per region. Running this aggregation repeatedly is incredibly slow.

1. Database Setup​

(Run this in your Snowflake worksheet)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_TELECOM_DB;
USE SCHEMA BEEMS_TELECOM_DB.PUBLIC;

-- 1. Create the massive base table
CREATE OR REPLACE TABLE RAW_CALL_DATA (
CALL_ID STRING,
REGION STRING,
DURATION_MINUTES NUMBER(10,2),
CALL_DATE TIMESTAMP
);

2. Creating the Materialized View​

We create the materialized view to pre-aggregate the data.

(To verify that you have the CREATE MATERIALIZED VIEW privilege in this schema, you can check your current role with SELECT CURRENT_ROLE();)

CREATE OR REPLACE MATERIALIZED VIEW REGION_CALL_SUMMARY AS
SELECT
REGION,
SUM(DURATION_MINUTES) AS TOTAL_DURATION,
COUNT(CALL_ID) AS TOTAL_CALLS
FROM RAW_CALL_DATA
GROUP BY REGION;

Auto-Refresh Behavior and Costs​

Unlike Dynamic Tables where you explicitly set a TARGET_LAG, Materialized Views are completely managed by Snowflake's internal services.

  1. The Background Service: When a new row is inserted into RAW_CALL_DATA, a Snowflake background service detects the change and incrementally updates the REGION_CALL_SUMMARY materialized view.
  2. Cost: You do not use your own Virtual Warehouse to update the view. You are billed serverless compute credits for the background maintenance.
  3. Query Consistency: If a user queries the Materialized View before the background service has finished updating it with the newest rows, Snowflake automatically combines the pre-computed view data with the raw delta from the base table. The user always gets 100% up-to-date, mathematically correct results.

The "Query Rewrite" Superpower​

The most powerful feature of Materialized Views in Snowflake is the Query Rewrite Engine (Available in Enterprise Edition and higher).

If a business analyst writes a query against the base table:

SELECT REGION, SUM(DURATION_MINUTES) 
FROM RAW_CALL_DATA
GROUP BY REGION;

Snowflake's optimizer is smart enough to realize: "Wait! I already pre-computed this exact aggregation in the REGION_CALL_SUMMARY materialized view!"

Snowflake will automatically redirect the analyst's query to scan the materialized view instead of the base table, accelerating the query from minutes to milliseconds, completely invisibly to the user.

Limitations and Restrictions​

Because Snowflake must guarantee that it can incrementally update the view in the background, Materialized Views have strict SQL limitations:

  • You cannot query more than one table (No JOINs).
  • You cannot use window functions (ROW_NUMBER()).
  • You cannot use HAVING or ORDER BY.
  • You cannot use MIN() or MAX() on string columns (only numeric/dates).

(If you need to perform JOINs or complex transformations, you must use a Dynamic Table instead).

Summary​

  • Materialized Views store pre-computed aggregations, trading storage costs for massive query performance gains.
  • They are automatically maintained by Snowflake's background serverless compute.
  • They guarantee 100% real-time data consistency when queried.
  • Enterprise editions automatically rewrite queries against base tables to use the materialized view.
  • They are strictly limited to single-table queries. Use Dynamic Tables if you need JOINs.