Data Movement · Topic 14 of 17
Views and Row-Level Filtering
Choose between standard, secure and materialized views and filter rows per user with row access policies, mapping tables and secure views.
- 5 min
- Standard, secure, materialized and recursive views exist. Secure views hide the definition and are the safe choice for sharing.
- Materialized views store results, need Enterprise Edition and cost serverless maintenance credits.
- Views run with the owner's rights, so consumers need privileges on the view only.
What it is
A view is a stored query that consumers read like a table. Row-level filtering limits which rows each user or role can see, either inside a view or with a row access policy attached to a table or view.
How it works
View types
| Type | Stores data | Key points |
|---|---|---|
| Standard view | No | Definition is visible to roles with access to the view; always reads current data |
Secure view (CREATE SECURE VIEW) | No | Hides the definition from non owners and prevents optimizer shortcuts that could leak data; recommended for sharing logic |
| Materialized view | Yes, maintained by Snowflake | Precomputes results of a query on one table; Enterprise Edition; serverless maintenance cost |
| Recursive view | No | Uses a recursive CTE |
CREATE SECURE VIEW eu_orders AS
SELECT order_id, customer_id, amount
FROM orders
WHERE region = 'EU';
- Secure views trade some performance for protection. Use them for shares, sensitive filters and cases where the definition is itself confidential.
- Materialized views speed up repeated queries on a single table and are kept current in the background. A view with joins is not eligible. Pipelines and performance chapters cover them further.
CREATE OR REPLACE VIEW ... COPY GRANTSkeeps existing grants on the replaced view.ALTER VIEW ... SET SECUREandUNSET SECUREchange the type. UseGET_DDL('VIEW', 'name')to read a definition, andSHOW VIEWSto list them.- A view reads the base table with the owner's privileges, so consumers need a grant on the view but not the table.
When the exam asks how to give a consumer a filtered slice without giving the base table, the answer is a view, and a secure view if the logic or data must not leak.
Row-level filtering with row access policies
A row access policy is a schema level object with a Boolean expression. A row is returned only when the expression is TRUE for the querying user.
CREATE ROW ACCESS POLICY region_policy AS (row_region STRING) RETURNS BOOLEAN ->
CURRENT_ROLE() = 'ADMIN_ROLE'
OR EXISTS (SELECT 1 FROM entitlements e
WHERE e.role_name = CURRENT_ROLE() AND e.region = row_region);
ALTER TABLE orders ADD ROW ACCESS POLICY region_policy ON (region);
- The policy is evaluated at query time, and the filter applies to every query, including those through views built on the table.
- A mapping table (the
entitlementstable above) lets you change who sees what by editing rows, without rewriting the policy. - A table or view can have only one row access policy at a time. Row access policies are an Enterprise Edition feature.
- Useful context functions are
CURRENT_ROLE(),IS_ROLE_IN_SESSION()andCURRENT_USER().IS_ROLE_IN_SESSION()respects role hierarchy, whichCURRENT_ROLE()does not. - If a table has both a row access policy and masking policies, the row access policy is evaluated first.
Filtering in views and shares
The older pattern is a secure view with a filter on a context function and a mapping table.
CREATE SECURE VIEW my_orders AS
SELECT o.* FROM orders o
JOIN account_map m ON o.partner_id = m.partner_id
WHERE m.account_name = CURRENT_ACCOUNT();
In a data share, CURRENT_ROLE() and CURRENT_USER() do not describe the provider's intent, because the consumer's roles are in the consumer account. The standard pattern is a secure view that uses CURRENT_ACCOUNT() with a mapping table that says which consumer account may see which rows. Share the secure view and not the base table.
| Need | Use |
|---|---|
| Different roles in the same account see different rows | Row access policy with a mapping table |
| Hide the query logic, share a filtered slice | Secure view |
| Each consumer account sees only its rows through a share | Secure view with CURRENT_ACCOUNT() and a mapping table |
Do not rely on a standard view to hide data from a consumer who can read its definition. Use a secure view or a policy.
Common mistakes
- Sharing a base table when consumers should see only a slice. Share a secure view.
- Using
CURRENT_USER()in a view that is shared with other accounts. UseCURRENT_ACCOUNT(). - Using
CURRENT_ROLE()whereIS_ROLE_IN_SESSION()is needed, which misses roles granted through the hierarchy. - Hard coding role names in a policy. Use a mapping table.
- Adding a second row access policy to a table that already has one. Only one is allowed.
- Expecting a materialized view over joins. It supports a single table.
What to remember for the exam
- Standard, secure, materialized and recursive views exist. Secure views hide the definition and are the safe choice for sharing.
- Materialized views store results, need Enterprise Edition and cost serverless maintenance credits.
- Views run with the owner's rights, so consumers need privileges on the view only.
- Row access policy: schema level object, Boolean result,
ALTER TABLE ... ADD ROW ACCESS POLICY ... ON (cols), one per table or view. - Mapping tables keep entitlements in data, not in code.
- For shares use a secure view with
CURRENT_ACCOUNT().
Check yourself
Each consumer account of a share must see only its own partner's rows. How?
Share a secure view that joins a mapping table and filters with
CURRENT_ACCOUNT().Which function respects role hierarchy inside a row access policy?
IS_ROLE_IN_SESSION().Why use a secure view instead of a standard view for sensitive logic?
It hides the definition from non owners and blocks optimizer behavior that could leak filtered data.
Topic 14 of 17 in Data Movement
Test yourself on Data Movement
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.