What you will be able to do
- Explain how a regular (non-materialized) view runs its query and how it controls access
- Decide when a view should be secure, and what that costs in performance and visibility
- Weigh the storage and serverless maintenance costs of a materialized view against faster reads
1.Regular views: a named query
A view lets you query the result of a query as if it were a table. Snowflake has two kinds of view. Non-materialized views, usually just called views, are the most common. The other kind is materialized views. A regular view stores nothing. Each time a query references it, Snowflake runs the view's defining query, so its results are always current but slower to produce than reading stored results.
Views have three main uses. They split up wide tables: the documentation's hospital example gives doctors one view and accountants another over the same table. They make SQL modular, because you can stack views on top of other views and debug each piece separately. They also control access, because you can grant privileges on a view to a role that has no privileges on the underlying table. One condition applies: the view's owner role must have access to the underlying table, or no one can query the view. If a view's definition doesn't name a schema for a table, Snowflake assumes the table is in the view's own schema. A non-materialized view can also be recursive.
CREATE VIEW doctor_view AS SELECT patient_ID, patient_name, diagnosis, treatment FROM hospital_table;Checkpoint 1 of 4· Check yourself
The billing role is granted SELECT on accountant_view but has no privileges on hospital_table. What happens when someone in that role queries the view?
Granting access to a view without granting access to its table is exactly what views are for. It works as long as the view's owner role can read the table.
“without the grantee role having privileges on the table(s) underlying the view.”Source: docs.snowflake.com
Sources1
2.Secure views: closing the leaks
SELECT *
FROM widgets_view
WHERE 1/iff(color = 'Purple', 0, 1) = 1;A regular view has two weaknesses. First, some of Snowflake's internal optimizations need access to the base table's data, and that access can let user code, such as a UDF or the query above, infer rows the view hides. Second, by default other users can see the view's definition. A secure view turns off those optimizations and shows its definition only to users granted the owning role. Unauthorized users don't see it through SHOW VIEWS, GET_DDL or the Information Schema VIEWS view. Query Profile hides a secure view's internals from everyone, including the owner.
To create one, add the SECURE keyword to CREATE VIEW or CREATE MATERIALIZED VIEW. ALTER VIEW can set or unset SECURE on an existing view. For non-materialized views, the IS_SECURE column shows whether a view is secure. Secure views can be slower, so use them for data privacy and not for views that exist only to make queries more convenient.
Checkpoint 2 of 4· Check yourself
A team creates a view only to simplify a complex join for analysts. Nothing in it is sensitive. Should it be secure?
Secure views give up some optimizations and can run more slowly. The documentation says not to use them for views that exist only for query convenience.
“Secure views should not be used for views that are defined solely for query convenience”Source: docs.snowflake.com
Sources2
3.Materialized views: stored results, ongoing cost
A materialized view is called a view, but in many ways it behaves like a table: its results are stored. Reading stored results is faster, but that speed comes with two costs.
- Storage: every materialized view keeps its query results, which adds to your account's monthly storage. - Compute: when the base table changes, a background service updates every materialized view defined on it, using compute resources provided by Snowflake. These updates can use significant resources, and they are billed only for actual usage, in 1-second increments. No tool estimates this cost in advance. In general it grows with the number of materialized views on each base table and with how much data changes in them.
The optimizer can also use a materialized view without you naming it. It can internally rewrite a query against the base table to read from a materialized view whose definition covers it, even when the query groups more coarsely than the view does. Materialized views also have restrictions that regular views do not. The sources for this lesson say those limitations exist and may change, but they don't list them, so check the Working with Materialized Views page for the current list. Like regular views, materialized views can be made secure.
create materialized view mv4 as select column_1, column_2, sum(column_3) from table1 group by column_1, column_2;| Property | Non-materialized view | Materialized view |
|---|---|---|
| Results | Computed each time the view is referenced | Stored, much like a table |
| Read performance | Slower | Faster |
| Extra cost | None for storage or maintenance | Storage plus background maintenance compute |
| Can be secure | Yes | Yes |
Checkpoint 3 of 4· Match them up
Match each view type to its defining trait
Tap a term, then the definition that fits it.
A regular view is just a named query. A materialized view stores its results and pays for storage and maintenance. SECURE can be added to either kind for privacy.
“This allows faster access, but requires storage space and active maintenance, both of which incur additional costs.”Source: docs.snowflake.com
Checkpoint 4 of 4· Exam question
A team writes a Python UDTF to compute a running summary inside each `region` group. Select TWO statements that correctly describe how the handler class works when the function is called with `OVER (PARTITION BY region)`.(Select 2)
Correct answers: A, D — The optional `end_partition` method is called after the last row of a partition so it can emit summary rows; The `process` method is called once for every input row in the partition and yields output tuples
- A. end_partition runs once per partition after all rows have been processed, which suits aggregate-style output such as totals.
- B. end_partition fires once per partition, not per row; per-row work belongs in process.
- C. Snowflake honors PARTITION BY and creates handler state per partition, so rows from different regions are processed separately.
- D. process receives the argument values of each row and can yield zero or more result tuples, which become output rows.
- E. A standard UDTF handler is a class whose methods yield tuples; returning a DataFrame is only part of vectorized handlers, not a general rule.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Making every view secure is free extra protection.Why is that wrong?
Secure views turn off some optimizations and can run more slowly. Use them for data privacy, not for convenience views.
Covered in Secure views: closing the leaks
2.A materialized view costs nothing beyond the queries that read it, just like a regular view.Why is that wrong?
A materialized view adds storage, and it consumes serverless compute whenever its base table changes, because Snowflake keeps it up to date in the background.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A non-materialized view’s results are created by executing the query at the time that the view is referenced in a query.”
↩︎ Regular views: a named query“then the user cannot query the view unless the owner role of the view has access to the underlying table.”
↩︎ Regular views: a named query“materialized views have some restrictions that non-materialized views do not have.”
↩︎ Materialized views: stored results, ongoing cost“without the grantee role having privileges on the table(s) underlying the view.”
↩︎ Checkpoint“This allows faster access, but requires storage space and active maintenance, both of which incur additional costs.”
↩︎ Checkpoint - 2.
“Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.”
↩︎ Secure views: closing the leaks“To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MATERIALIZED VIEW command.”
↩︎ Secure views: closing the leaks“This is the case even for the owner of the secure view”
↩︎ Secure views: closing the leaks“Secure views can execute more slowly than non-secure views.”
↩︎ Exam trap 1“For a non-secure view, internal optimizations can indirectly expose data.”
↩︎ Prediction“Secure views should not be used for views that are defined solely for query convenience”
↩︎ Checkpoint - 3.
“Storage: Each materialized view stores query results, which adds to the monthly storage usage for your account.”
↩︎ Materialized views: stored results, ongoing cost“There are no tools to estimate the costs of maintaining materialized views.”
↩︎ Materialized views: stored results, ongoing cost“These updates can consume significant resources, resulting in increased credit usage.”
↩︎ Exam trap 2