Snowflake Views
Snowflake · Views, Pipelines & Automation
Theory
A view is a named SELECT that you query like a table. A standard view stores only the query text and runs it each time it is queried, so it always reflects current data and costs nothing to store. A secure view hides its definition from users who do not own it and blocks optimizations that could reveal hidden rows, which is why it is required when sharing views with other accounts. A materialized view stores the precomputed result and a background service keeps it up to date as the base table changes.
For example, v_active_customers can wrap a WHERE is_active = TRUE filter, while mv_daily_revenue precomputes daily totals over a huge orders table.
Why we use it
Views let you reuse logic, simplify queries for users and control what columns and rows are exposed. Materialized views add speed for expensive, repeated aggregations on large tables.
When to use it
Use standard views for reusable logic, secure views when the definition or underlying data must be protected or shared, and materialized views (Enterprise edition) for frequent, costly queries on a single table that changes less often than it is read.
Standard Views
Re-run their underlying query every time they're queried, so they always reflect current data but add the query's cost each time.
Secure Views
Additionally hide their query definition and internals from users who can query them — useful for masking business logic or sensitive join conditions.
Materialized Views
Precompute and physically store their result, trading storage and a small refresh cost for much faster reads — best for expensive aggregations queried often but changing rarely.
How it works
- Need to expose or reuse a query
- Must the definition or hidden data be protected, or is it shared? Yes: Create a SECURE view. No: Consider a standard view.
- Is it an expensive, frequent query on one large table? Yes: Create a MATERIALIZED view. No: Use a standard view.
- Grant SELECT on the view, not the base table
- Users query the view like a table
Example
CREATE OR REPLACE VIEW v_active_customers AS
SELECT customer_id, first_name, email FROM customers WHERE is_active = TRUE;
CREATE OR REPLACE SECURE VIEW v_customer_summary AS
SELECT state, COUNT(*) AS customer_count FROM customers GROUP BY state;
CREATE OR REPLACE MATERIALIZED VIEW mv_daily_revenue AS
SELECT order_date, SUM(amount) AS total_revenue FROM orders GROUP BY order_date;Key points
- Secure views hide the definition and prevent data-leaking optimizations
- Secure views can be slower because some optimizations are disabled
- Materialized views are always current; Snowflake maintains them
- Materialized views cannot contain joins or window functions
- Standard views always run the query fresh