Snowflake Views and Secure Views

A view is a saved query that behaves like a table. Analysts query the view by name and never repeat a long join or filter. Views simplify reports, hide complexity, and limit what each user sees. Secure views add an extra shield for sensitive logic and shared data.

The Window Frame Comparison

Imagine a large building with many rooms. A view works like a window cut into one wall. Visitors look through the window and see only the part of the room the owner wants to show. The room itself stays unchanged, and nothing gets copied. A standard view stores no data. Every query against the view runs the saved SQL against the live tables underneath.

HOW A VIEW WORKS
================

 Analyst query:   SELECT * FROM v_active_customers WHERE city = 'Pune'
                              |
                              v
 +-----------------------------------------------+
 | VIEW v_active_customers (saved SQL only)      |
 | SELECT id, name, city FROM customers          |
 | WHERE status = 'ACTIVE'                       |
 +----------------------+------------------------+
                        |
                        v
              +-------------------+
              |  CUSTOMERS table  |   real data lives here
              +-------------------+

Creating a View

CREATE OR REPLACE VIEW v_active_customers AS
SELECT customer_id, name, city
FROM customers
WHERE status = 'ACTIVE';

SELECT * FROM v_active_customers WHERE city = 'Pune';

-- Read the saved definition
SELECT GET_DDL('VIEW', 'v_active_customers');

Views run with the privileges of the view owner on the underlying tables. A user needs SELECT on the view and nothing on the base table. That rule lets administrators expose a safe slice of a table without granting access to the whole table.

Why Teams Use Views

• Simplicity: one friendly name replaces a ten-line query.
• Consistency: every report applies the same business rule, such as what counts as an active customer.
• Security: views hide columns and rows that a role should not see.
• Stability: a view keeps its name while engineers rebuild the tables behind it.

Secure Views

A standard view lets users with certain privileges read its definition, and the query optimizer sometimes pushes user filters into the view in ways that leak information through error messages or timing. A secure view closes both gaps. Snowflake hides the SQL text from everyone except the owner and disables optimizations that could expose hidden rows.

CREATE OR REPLACE SECURE VIEW partner_orders AS
SELECT order_id, order_date, amount
FROM orders
WHERE partner_id = 'ACME';

-- Check which views are secure
SHOW VIEWS LIKE 'PARTNER_ORDERS';

Data sharing requires secure views. A provider cannot add a normal view to a share. The secure label guarantees that consumers never see the query logic or the hidden rows.

Standard, Secure, and Materialized Views

TYPE            STORES DATA   HIDES SQL TEXT   BEST FOR
--------------  ------------  ---------------  ---------------------------
Standard view   No            No               Everyday simplification
Secure view     No            Yes              Sharing, sensitive logic
Materialized    Yes           No               Repeated heavy aggregations
view            (precomputed)                  on slowly changing tables

Changing a View Safely

CREATE OR REPLACE VIEW drops grants unless the statement includes COPY GRANTS. Add that clause to keep existing permissions.

CREATE OR REPLACE VIEW v_active_customers COPY GRANTS AS
SELECT customer_id, name, city, signup_date
FROM customers
WHERE status = 'ACTIVE';

Performance Notes

A view adds no storage and no magic speed. Query time depends on the SQL inside. Nested views, where one view calls another that calls another, confuse humans and complicate tuning. Keep chains to two or three levels. Secure views may run slower than standard views because Snowflake skips some optimizations, so reserve them for cases that need protection.

A Practical Scenario

A hospital analytics team stores patient visits in one table. Researchers need visit dates and diagnosis groups without names or addresses. A data steward creates a secure view that lists only the approved columns and rows. Researchers receive access to the view alone. The steward updates the view when policy changes, and every researcher sees the new rules on the following query.

Key Points

• A view saves a query and stores no data.
• Users need privileges on the view, and the owner's rights reach the base tables.
• Secure views hide their SQL and block optimizer shortcuts that could leak data.
• Data sharing uses secure views for filtered exposure.
• COPY GRANTS preserves permissions when a view is replaced.
• Short view chains keep queries easy to understand and tune.