Skip to main content

πŸ“¦ 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​

FeatureStandard ViewSecure ViewMaterialized View
Stores Data❌ No❌ Noβœ… Yes
Execution CostHigh (each time)High (each time)Low (cached results)
Supports DML❌ No❌ No❌ No
Refreshable❌ No❌ Noβœ… Yes (depending on edition)
Ideal Use CaseLogic encapsulationSecure maskingPerformance 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_REFRESH if you are using the Enterprise Edition, as it is not supported.


πŸ”„ Refresh Behavior: Auto vs Manual​

❓ Can You Control Refresh Behavior?​

Snowflake EditionCan 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 ... REFRESH or the AUTO_REFRESH clause.

🚧 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​

LimitationDescription
❌ Cannot reference other Materialized ViewsOnly base tables are allowed
❌ No non-deterministic functionsFunctions like CURRENT_DATE, RANDOM() not allowed
❌ Joins limitedComplex joins across multiple tables may be unsupported
❌ DML not allowedCannot update, insert, or delete data directly
❌ Not suited for real-time feedsRefresh is not instant; latency may exist

πŸ›‘οΈ Security Requirements​

PrivilegeDescription
USAGE on SchemaNeeded to reference the materialized view
SELECT on Base TablesRequired to access source data
CREATE MATERIALIZED VIEWNeeded to create the view
DROP MATERIALIZED VIEWNeeded 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​

ScenarioMaterialized 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​

FeatureMaterialized 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.