page307
395
Most use cases do not require a clustering key. For those use cases where a clustering
key is defined, you must understand the attribute order and cardinality.
Views and Dynamic Tables
Both views and dynamic tables share the common feature of facilitating data model
denormalization by joining tables and applying filters and aggregates to provide a
composite representation of the result set. While the delivery mechanisms differ
insofar as a view is an abstracted query and a dynamic table is a periodically refreshed
abstracted query, the underlying principles for deriving data are the same.
Both views and dynamic tables may suffer from the same performance impacting
issues, I highlight this when optimizing code. I prefer to drill down into all child views,
building knowledge from the ground up and then deciding upon an appropriate course
of action. For complex relationships, I suggest an entity relationship diagram (ERD) will
assist in resolving performance issues.
While there are no additional storage costs for views, you can incur additional
storage costs along with serverless compute costs for provisioning dynamic tables. As
with standard tables, high-velocity, low-volume DML operations cause significant micro-
partition churn for dynamic tables.
Secure Views
Secure views prevent the view definition from being exposed to unauthorized users and
prevent access to the underlying SQL query for all roles except the role that owns the
secure view. In addition to the comments highlighted earlier when optimizing code, the
high-security profile for secure views restricts optimization to a subset of data points
available for optimizing normal views.
During query processing pushdown optimization, the query processor prefilters
rows by dynamically pruning micro-partitions to improve performance and reduce
memory consumption. With normal views, pushdown can allow confidential data to
be exposed indirectly. You can find more information on pushdown at
https://docs.
snowflake.com/en/developer-guide/pushdown-optimization
.
ChapTer 10
OpTimizing perfOrmanCe