page296
384
Fully Sorted Table
When rebuilding tables, you may prefer to initialize the data by pre-ordering using
the explicit
ORDER BY
syntax. This approach works for both CTAS and
INSERT
OVERWRITE
. Pre-seeding data in an ordered manner may facilitate later creation of a
clustering key.
The initial creation of micro-partitions for pre-ordered data will be faster than
later relying upon the automated clustering service where a clustering key is defined.
However, spills to disk and OOMs may occur. Subsequent DML operations may result in
skewed data.
Clustering Keys
Clustering keys should not be your first consideration when performance tuning code.
Ensure all other options have been tried first.
Clustering key maintenance is impacted by high-velocity, low-volume Data
Manipulation Language (DML) operations where the asynchronous Automatic
Clustering service may not cope with the speed of change.
Clustering keys prefer low-cardinality leading attributes to maximize pruning, and
clustering keys should be limited to three or four attributes only. For String datatypes,
only the first five to six characters should be used to minimize cardinality; otherwise,
numeric datatypes with low cardinality are preferred.
Clustering keys support micro-partition pruning. Optimize key attributes for
maximum pruning effectiveness.
Most use cases do not require a clustering key. Snowflake suggests tables of 1TB or
greater should be considered as candidates for clustering key declarations. For those
use cases where a clustering key is defined, you must understand the attribute order and
cardinality, preferring low-cardinality attributes first.
Clustering keys alone are not a silver bullet. They are part of an overall
performance tuning strategy.
ChapTer 10
OpTimizing perfOrmanCe