Skip to main content

2.4 Views & Materialized Views

Views act as virtual tables. They allow you to save a query definition and treat the result set as a table. This is highly useful for abstracting complex joins and business logic away from end-users.

Standard Views​

A standard view does not store any data physically. Every time you query a standard view, Snowflake executes the underlying SQL definition on the fly against the base tables.

CREATE VIEW VW_US_CUSTOMERS AS
SELECT customer_id, name, state
FROM CUSTOMERS
WHERE country = 'USA';

Secure Views​

Standard views expose their underlying SQL definitions. If you run GET_DDL on a view, you can see exactly how the query was built.

If your view includes sensitive filtering logic (e.g., Row Access Policies or complex entitlement logic) that you want to hide from users, you should create a Secure View.

CREATE SECURE VIEW VW_SALARY_MASKED AS
...
  • Security: Users cannot see the DDL definition of a secure view unless they own it.
  • Performance Impact: The query optimizer must sometimes choose less efficient execution paths to guarantee that no data leakage occurs through error messages or optimization shortcuts.

Materialized Views​

Unlike standard views, a Materialized View actually physically computes and stores the result set of the query.

When & Why to Use Them​

You should use Materialized Views when:

  • The query contains highly complex aggregates or joins.
  • The query is run extremely frequently.
  • The underlying base table data does not change very frequently.

When a user queries a Materialized View, the results return almost instantly because the math has already been done and saved to disk.

Refresh Behavior & Cost​

Materialized Views in Snowflake are automatically maintained!

  • If the base table is updated, Snowflake's background services will automatically spin up compute resources to refresh the materialized view.
  • Cost: You are billed for the compute credits used by these background services to keep the view up to date. Therefore, creating materialized views on base tables that update every 5 seconds is an anti-pattern that will result in massive credit burn!