page297
385
Operational considerations for clustering keys management include the following:
•
Clustering keys should be defined only for high-frequency queries
with matching query predicates.
•
Clustering key maintenance may not happen concurrent with
DML operation completion; maintenance requires a finite time to
complete.
•
Frequent DML operations may result in costly reclustering operations
and, in worst-case scenarios, constant micro-partition “churn.”
•
Clustering is most cost effective for low-volume DML and high-
volume query operations.
•
Reclustering invalidates cached results.
For those inclined to dive deeper into clustering, the Snowflake founders’ patent can
be found at
https://www.freepatentsonline.com/y2018/0068008.html
.
Please refer to Chapter 5 for a thorough investigation of clustering keys, paying
particular attention to clustering width and clustering depth.
Introspection Calls
In Snowflake, an introspection call is a SQL statement used to interrogate the Account
Usage Store or information schema of a particular database to identify metadata for
objects, columns, and their attributes.
Performance issues with introspection calls may be caused by the following:
•
Unexpected or unpredictable changes made to object definitions
causing ad hoc metadata changes
•
High numbers of nested roles or masking policies
•
High number of redundant cloned objects
As suggested by Nadir Doctor, queries using Account Usage Store can be significantly
improved via referencing a base local target table, created via a CTAS operation to
contain a backup of data from a source view.
ChapTer 10
OpTimizing perfOrmanCe