page316
404
In addition to the advice and guidance presented in this book, Snowflake presents
several features aimed at remediating performance issues. Knowing when and how to
enable Snowflake features is not just an important aspect of performance tuning; there is
an implicit assumption that all code has been optimized before arriving at this section.
All features referenced in the following sections make use of serverless compute;
see
https://docs.snowflake.com/en/user-guide/cost-understanding-
compute#serverless-features
.
Let’s now investigate these features.
Automatic Clustering
Automatic clustering works well for large tables; Snowflake recommends adding
clustering keys for tables of 1TB or larger, but this is not a hard-and-fast rule. For
optimal micro-partition pruning, clustering keys should match query predicates. Where
date ranges are frequently selected, reducing the cardinality of dates to months by
adding an extra attribute during data load may provide a performance benefit when
predicates match.
Snowflake implements asynchronous automatic clustering via a background process
that periodically reorganizes a small set of micro-partitions to achieve an acceptable
performance standard.
Automatic clustering does not work well for query predicates that do not match
the clustering keys. High-velocity, low-volume DML operations may overwhelm
the automatic clustering service capability to re-cluster before the next batch of
changes arrive.
Re-clustering incurs compute cost, and significant additional storage costs may
accrue as micro-partitions are immutable.
Refer to Chapter 5 for a detailed investigation of automatic clustering.
Materialized Views
Currently, a materialized view can exist on a single table only. Materialized views work
well for tightly focused subsets of data. Materialized views created for pre-aggregated
or prefiltered data sets prevent expensive warehouse operations where the data is
frequently accessed. Query plans may prefer a materialized view over base table access.
ChapTer 10
OpTimizing perfOrmanCe