Views and materialized views in Snowflake - Commands & Configuration

Introduction

Sometimes you want to save a query to use it again easily or speed up repeated data lookups. Views let you save a query as a virtual table, and materialized views save the query results physically for faster access.

When you want to reuse a complex query without rewriting it every time.

When you want to simplify data access for your team by creating a named query.

When you need faster query results on data that does not change often.

When you want to save storage by not duplicating data but still have easy access.

When you want to improve performance by precomputing and storing query results.

Commands

This command creates a view named example_view that shows only active customers with their id and name. It saves the query for easy reuse.

Terminal

CREATE OR REPLACE VIEW example_view AS SELECT id, name FROM customers WHERE active = TRUE;

Expected Output

Statement executed successfully.

This command runs the saved query in example_view and shows the current active customers. Views always show fresh data from the original table.

Terminal

SELECT * FROM example_view;

Expected Output

ID | NAME

1  | Alice

3  | Carlos

5  | Emma

This command creates a materialized view named example_mat_view that stores the results of the query physically for faster access.

Terminal

CREATE OR REPLACE MATERIALIZED VIEW example_mat_view AS SELECT id, name FROM customers WHERE active = TRUE;

Expected Output

Statement executed successfully.

This command reads data from the materialized view example_mat_view. It returns faster results because data is precomputed and stored.

Terminal

SELECT * FROM example_mat_view;

Expected Output

ID | NAME

1  | Alice

3  | Carlos

5  | Emma

This command updates the materialized view to reflect any changes in the original customers table. Materialized views do not update automatically.

Terminal

ALTER MATERIALIZED VIEW example_mat_view REFRESH;

Expected Output

Statement executed successfully.

Key Concept

If you remember nothing else from this pattern, remember: views save queries for reuse and always show fresh data, while materialized views save query results for faster access but need manual refresh.

Common Mistakes

Creating a materialized view but expecting it to update automatically with data changes.

Materialized views store data physically and do not refresh automatically, so they can show outdated data.

Run ALTER MATERIALIZED VIEW ... REFRESH to update the data when needed.

Using a view when you need faster query performance on large datasets.

Views run the query every time, which can be slow on big data.

Use a materialized view to store and quickly access precomputed results.

Trying to insert or update data directly in a view or materialized view.

Views and materialized views are not tables and do not support direct data changes.

Modify the underlying base tables instead.

Summary

Create views to save and reuse queries that always show current data.

Create materialized views to store query results for faster access but refresh them manually.

Use SELECT commands on views and materialized views to get data easily.