What you will be able to do
- Choose between a regular, secure and materialized view for a given privacy and performance need
- Explain how a secure view stops definition exposure and optimizer-based data leaks
- Predict the ongoing costs of a materialized view and what suspends one
1.Regular views: a stored query
A view lets you query the result of a query as if it were a table, including joins across several tables or other views. A regular view stores nothing. Every query against it reruns the definition on a warehouse. That keeps it cheap to create and always current, but it is only as fast as the query underneath. A materialized view makes the opposite trade.
| Object type | Pros | Cons |
|---|---|---|
| View | Simple, easily defined, consumes no storage | Inflexible, slow, requires compute to generate results |
| Materialized View | Fast results retrieval; relatively simple definition; always up to date | Incurs compute to keep up to date; consumes storage |
A regular view also has two openings that matter when it exists to restrict data. Its definition text is visible to other users, which reveals the base tables and the filter logic. And some of the optimizer's internal shortcuts touch base-table data before the view's filter applies, so user code such as a UDF can sometimes see rows the view was supposed to hide.
Checkpoint 1 of 3· Check yourself
A team builds a regular (non-secure) view that filters a sales table down to one region. They grant it to analysts who must not see any other region's rows. What is true about this setup?
A regular view stores nothing and reruns its query each time. Its definition is visible to other users, and some optimizations read base-table data before the filter applies, so user code such as a UDF can expose hidden rows. Hiding the definition and blocking those optimizations is what secure views are for.
“For a non-secure view, internal optimizations can indirectly expose data.”Source: docs.snowflake.com
2.Secure views: hiding the definition and the data
SELECT *
FROM widgets_view
WHERE 1/iff(color = 'Purple', 0, 1) = 1;If a purple row exists anywhere underneath, IFF returns 0 and the query fails with division by zero, which tells the user something they were never meant to see. A secure view closes this hole by giving up the optimizations that touch hidden data. You create one with CREATE SECURE VIEW (or CREATE SECURE MATERIALIZED VIEW), and you can switch an existing view either way with ALTER VIEW SET or UNSET SECURE.
Only users with the role that owns a secure view can see its definition. Other users get nothing from SHOW VIEWS, GET_DDL or the Information Schema VIEWS view. Roles with IMPORTED PRIVILEGES on the SNOWFLAKE database, ACCOUNTADMIN or the SNOWFLAKE.OBJECT_VIEWER database role can still read it through the Account Usage VIEWS view. Query Profile hides a secure view's internals even from the owner, because non-owners may be able to see the owner's profiles. The IS_SECURE column shows whether a view is secure.
Security costs speed: secure views can run more slowly. Use them for views built to protect sensitive data. For views that only make queries easier to write, they slow things down and protect nothing.
Checkpoint 2 of 3· Check yourself
An architect proposes marking every view in the account SECURE as a blanket best practice, including views built only to simplify queries for analysts. What is wrong with this plan?
Secure views give up optimizations that could expose hidden data, so they can be slower. They belong on views that protect sensitive data, not on views that only make queries easier to write. ALTER VIEW can unset SECURE, and the owning role can still see the definition.
“Secure views can execute more slowly than non-secure views.”Source: docs.snowflake.com
Sources2
3.Materialized views: stored results with a running bill
A materialized view computes its result in advance and stores it, so reading it is faster than querying the base table. The gain is largest for expensive aggregation, projection and selection over large tables that run often. Where that fits among other tuning options is covered in query performance optimization.
The speed has an ongoing cost. Stored results add to monthly storage. Whenever the base table changes, a Snowflake-managed background service updates every materialized view on it, and that compute is billed per second against a warehouse called MATERIALIZED_VIEW_MAINTENANCE. No tool estimates this cost ahead of time. It grows with the number of materialized views on each table, the amount of data that changes, and clustering: a view clustered differently from its base table may rewrite far more micro-partitions than the table did. Resource monitors cannot cap that warehouse. You can suspend a view yourself and resume it later, but that only defers maintenance, and the backlog grows the longer it waits. Snowflake advises creating a few views first and watching MATERIALIZED_VIEW_REFRESH_HISTORY.
Changes to the base table can also suspend a materialized view, and this is a different situation from a manual suspend:
- Adding a column to the base table is safe.
- Changing or dropping an existing column suspends every materialized view on the table, even views that never used that column. Unlike a manual suspend, this one cannot be undone: the view can be neither used nor maintained, and
RESUMEis not allowed. Recreate it withCREATE OR REPLACE MATERIALIZED VIEW ... COPY GRANTS. - Dropping, renaming or swapping the base table also suspends the view without dropping it.
This is why Snowflake advises against SELECT * in a materialized view definition.
Checkpoint 3 of 3· Check yourself
Requirement: a dashboard runs an expensive aggregation over a large sales table many times an hour. The result includes sensitive margin data, and analysts must not see the definition or reach base-table rows outside it. Which object fits?
Precomputed results suit an expensive aggregation that runs often, so a materialized view is the right object. Because it protects sensitive data, it should also be secure. The SECURE keyword works with CREATE MATERIALIZED VIEW as well as CREATE VIEW, and storing results does not hide the definition.
“To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MATERIALIZED VIEW command.”Source: docs.snowflake.com
Sources
(3)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“a view allows the result of a query to be accessed as if it were a table”
↩︎ Regular views: a stored query“querying a materialized view is faster than executing a query against the base table of the view.”
↩︎ Materialized views: stored results with a running bill - 2.
“This access might allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions”
↩︎ Regular views: a stored query“Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.”
↩︎ Secure views: hiding the definition and the data“specify the SECURE keyword in the CREATE VIEW or CREATE MATERIALIZED VIEW command.”
↩︎ Secure views: hiding the definition and the data“If an unauthorized user uses any of the following commands or interfaces, the view definition is not displayed”
↩︎ Secure views: hiding the definition and the data“Secure views can execute more slowly than non-secure views.”
↩︎ Secure views: hiding the definition and the data“Secure views should not be used for views that are defined solely for query convenience”
↩︎ Exam trap 2“For a non-secure view, internal optimizations can indirectly expose data.”
↩︎ Checkpoint“To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MATERIALIZED VIEW command.”
↩︎ Checkpoint - 3.
“all materialized views defined on the table are updated by a background service”
↩︎ Materialized views: stored results with a running bill“you cannot use them to control credit usage for the Snowflake-provided warehouses, including the MATERIALIZED_VIEW_MAINTENANCE warehouse”
↩︎ Materialized views: stored results with a running bill“You can also control costs by suspending or resuming the materialized view”
↩︎ Materialized views: stored results with a running bill“If a base table is altered so that existing columns are changed or dropped, then all materialized views on that base table are suspended”
↩︎ Materialized views: stored results with a running bill“the materialized views cannot be used or maintained”
↩︎ Materialized views: stored results with a running bill“You cannot RESUME that materialized view. If you want to use it again, you must recreate it.”
↩︎ Materialized views: stored results with a running bill“Snowflake recommends not using SELECT * in the definition of a materialized view.”
↩︎ Materialized views: stored results with a running bill“suspending maintenance typically only defers costs, rather than reducing them”
↩︎ Exam trap 1