page293
381
Missing Joins
Identify missing join criteria as this is the most likely root cause. Note that missing
composite key attributes are far harder to identify than single attribute primary key/
foreign key relationships. A general rule of thumb is that the number of
AND
join
conditions should always be equal to the number of tables minus 1. This works for many
scenarios.
Type Casting
Multiple layers of type casting on join keys prevents static micro-partition pruning at
compile time. Consider adding typecast attributes at table creation time for population
by ingestion or curation processes. By way of example, add an “YYYYMM” attribute
where a
DATE
attribute would normally be used. The additional low-cardinality attribute
may be a suitable candidate for clustering key definition to enable more efficient
pruning.
Optimizing Joins
Numeric data type joins are the fastest of all. I prefer sequence generated surrogate
primary keys over natural or composite keys for all tables along with declared referential
integrity. Numeric data types are also preferred for clustering keys where the number
range is low cardinality. Sequences do not make good candidates for leading attributes
in clustering keys.
Dates and timestamps are stored internally in numeric format and therefore are
good candidates for both join conditions and clustering keys. I prefer to reduce the
cardinality of both date and timestamp attributes when used as leading attributes in
clustering keys; I suggest reducing cardinality to the year/month (YYYYMM) format to
improve pruning.
Cardinality can be determined from both metadata and Snowflake-supplied
nondeterministic estimation functions such as
HYPERLOGLOG
for which further
information can be found here at
https://docs.snowflake.com/en/sql-reference/
functions/hll
. Bitmaps can also be used to improve performance; see
https://docs.
snowflake.com/en/user-guide/querying-bitmaps-for-distinct-counts
.
Remove type casting from join key attributes; instead, pre-type cast into new
attributes at the point of data ingestion.
ChapTer 10
OpTimizing perfOrmanCe