π¦ Materialized Views in Snowflake
A Materialized View in Snowflake stores the physical result set of a SQL query and can automatically refresh when the base table changes (depending on your Snowflake edition). This helps improve query performance for complex or frequently accessed queries.
π§ What Is a Materialized View?β
A Materialized View is a database object that contains the results of a query. Unlike a regular view that executes its query each time it is accessed, a materialized view stores query results physically and refreshes automatically or manually depending on your Snowflake edition.
It is read-only and optimized for performance when querying aggregated or preprocessed data.
π Difference Between Materialized View and Other View Typesβ
| Feature | Standard View | Secure View | Materialized View |
|---|---|---|---|
| Stores Data | β No | β No | β Yes |
| Execution Cost | High (each time) | High (each time) | Low (cached results) |
| Supports DML | β No | β No | β No |
| Refreshable | β No | β No | β Yes (depending on edition) |
| Ideal Use Case | Logic encapsulation | Secure masking | Performance optimization |
π§ͺ Sample Table and Sample Dataβ
To demonstrate materialized views, start with this example table and data:
CREATE OR REPLACE TABLE sales (
sale_id INT,
region STRING,
sale_amount NUMBER,
sale_date DATE
);
INSERT INTO sales VALUES
(1, 'East', 1000, '2024-01-15'),
(2, 'West', 1500, '2024-01-17'),
(3, 'North', 800, '2024-02-01'),
(4, 'South', 2000, '2024-02-10'),
(5, 'East', 1200, '2024-02-15');
π οΈ Example: Creating a Materialized Viewβ
CREATE OR REPLACE MATERIALIZED VIEW monthly_sales_summary
AS
SELECT
TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,
region,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY TO_CHAR(sale_date, 'YYYY-MM'), region;
π¨ Do not include
AUTO_REFRESHif you are using the Enterprise Edition, as it is not supported.
π Refresh Behavior: Auto vs Manualβ
β Can You Control Refresh Behavior?β
| Snowflake Edition | Can Set AUTO_REFRESH? | Can Manually Refresh? |
|---|---|---|
| Standard | β No | β No |
| Enterprise (Your Edition) | β No | β No |
| Business Critical | β Yes | β Yes |
| Virtual Private Snowflake | β Yes | β Yes |
π How Materialized Views Refresh in Enterprise Editionβ
- You cannot control or disable auto-refresh.
- Snowflake auto-refreshes the materialized view silently, based on changes to base tables.
- You cannot use
ALTER MATERIALIZED VIEW ... REFRESHor theAUTO_REFRESHclause.
π§ Workaround: Manual Refresh with Tables and Tasksβ
If you want manual refresh control in Enterprise Edition, use a combination of tables and scheduled tasks:
Step 1: Create a Summary Tableβ
CREATE OR REPLACE TABLE monthly_sales_summary AS
SELECT
TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,
region,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY TO_CHAR(sale_date, 'YYYY-MM'), region;
Step 2: Create a Task to Refresh It Periodicallyβ
CREATE OR REPLACE TASK refresh_monthly_sales_summary
WAREHOUSE = compute_wh
SCHEDULE = 'USING CRON 0 6 * * * UTC' -- Every day at 6 AM
AS
BEGIN
TRUNCATE TABLE monthly_sales_summary;
INSERT INTO monthly_sales_summary
SELECT
TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,
region,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY TO_CHAR(sale_date, 'YYYY-MM'), region;
END;
ALTER TASK refresh_monthly_sales_summary RESUME;
π« Limitationsβ
| Limitation | Description |
|---|---|
| β Cannot reference other Materialized Views | Only base tables are allowed |
| β No non-deterministic functions | Functions like CURRENT_DATE, RANDOM() not allowed |
| β Joins limited | Complex joins across multiple tables may be unsupported |
| β DML not allowed | Cannot update, insert, or delete data directly |
| β Not suited for real-time feeds | Refresh is not instant; latency may exist |
π‘οΈ Security Requirementsβ
| Privilege | Description |
|---|---|
USAGE on Schema | Needed to reference the materialized view |
SELECT on Base Tables | Required to access source data |
CREATE MATERIALIZED VIEW | Needed to create the view |
DROP MATERIALIZED VIEW | Needed to remove the view |
π Monitoring Materialized Viewsβ
-
View all materialized views:
SHOW MATERIALIZED VIEWS; -
Check auto-refresh settings (if supported):
SELECT table_name, auto_refresh
FROM INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE table_schema = 'YOUR_SCHEMA'; -
Track refresh history (only available in some editions):
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE MATERIALIZED_VIEW_NAME = 'MONTHLY_SALES_SUMMARY';
β Use Cases for Materialized Viewsβ
| Scenario | Materialized View Recommended? |
|---|---|
| Dashboard aggregations | β Yes |
| Reusable summarized datasets | β Yes |
| Real-time streaming data | β No |
| Queries with non-deterministic logic | β No |
| Heavy joins and transformations | β οΈ Maybe (Test for performance) |
π Summary: Materialized Views at a Glanceβ
| Feature | Materialized View |
|---|---|
| Performance | π High (cached query results) |
| Auto-refresh | β Yes (edition-dependent) |
| Manual refresh | β Only in higher editions |
| Storage required | β Yes (stored results) |
| DML allowed | β No |
| Ideal for dashboards | β Yes |
| Refresh control in Enterprise | β No |
π Materialized Views in Snowflake offer fast, read-optimized access to precomputed query results. Theyβre perfect for dashboards and reports, but come with edition-specific refresh limitations. If full control is required, use summary tables and scheduled tasks instead.