page308
396
Secure views prevent the exposure of confidential information; by default, most
pushdown optimizations are disabled. These operations prevent pushdown, and there
may be more:
•
Arithmetic operations in query
WHERE
clauses
•
UNION
operations
•
Scalar functions that take a row or value and return a single value
As with all SQL statements, avoid complexity, and simplify code wherever possible.
There are no additional storage costs associated with secure views.
You can find more information on secure views at
https://docs.snowflake.com/
en/user-guide/views-secure
.
Materialized Views
As discussed in Chapter 3, a materialized view can be declared only on a single table
and is a way either to declare alternative clustering keys on a base table or to summarize
or aggregate data. Using materialized views facilitates micro-partition pruning, as
discussed in Chapter 4.
Materialized views incur maintenance, runtime, and storage costs. Before
implementing and using materialized views, a balance must be struck to ensure
optimally cost-effective solutions are developed and delivered. You can incur additional
storage costs along with serverless compute costs for provisioning materialized views. As
with standard tables, high-velocity, low-volume DML operations cause significant micro-
partition churn for materialized views.
You can find information on materialized views at
https://docs.snowflake.com/
en/user-guide/views-materialized.
I also found this article on the different types
of views by Minzhen Yang useful:
https://www.linkedin.com/pulse/materialized-
view-vs-secure-regular-minzhen-yang/
.
User-Defined Functions (UDFs)
In similar manner to views, UDFs provide the capability to implement bespoke
functionality callable using standard SQL. The key difference between a view and a
UDF is the degree of complexity that can be accomplished. UDFs offer a far wider range
ChapTer 10
OpTimizing perfOrmanCe