Performance Management

Question 1

Clustering in Snowflake

Answer 1

As data is loaded into Snowflake, it is micro-partitioned and these partitions are clustered based on its metadata (by similarity). This is done automatically and requires no user intervention/management.

Question 2

Can Materialized Views be clustered?

Answer 2

Yes

Question 3

Cluster Key

Answer 3

To manually improve clustering, Snowflake supports the addition of a Cluster Key to a table. A clustering key is deigned to co-locate data in a table in the same micro-partition. This improves overall clustering and query performance.

(Generally Snowflake does a good job doing this, but at times it can be better to manually intervene)

Question 4

How to determine if a table is clustered?

Answer 4

A table is considered to be clustered if a clustering key is present.

Question 5

Should materialized views be clustered?

Answer 5

x

Question 6

How does clustering work in the background?

Answer 6

As data is inserted into a table, Clustering metadata is collected and recorded for each micro-partition created during the process. Snowflake leverages this clustering information to avoid unnecessary scanning of micro-partitions, and also uses this info for clustering key purposes.

Question 7

When can clustering keys be defined?

Answer 7

CREATE TABLE

ALTER TABLE

Question 8

What should determine whether to define a clustering key?

Answer 8

• Queries on the table are running slower than expected/degraded over time.
• The clustering depth for a table is large.

Question 9

Impact of Clustering on Query Pruning

Answer 9

Micro-partition metadata maintained by Snowflake enables precise and optimal pruning of columns in micro-partitions, including columns with semi-structured data.

Question 10

Clustering Query Pruning Example

Answer 10

If a query filters on value X, and value X is only present in 10% of all micro-partitions, a well clustered table will only scan 10% of its micro-partitions and ignore the rest of the micro-partitions.

Question 11

What keys are best clustered? (in order of priority)

Answer 11

1. Cluster columns that are most actively used by select filters
2. Columns frequently joined on

Question 12

Recommended Maximum Columns per Cluster Key

Answer 12

4

Question 13

What qualities make for a good cluster key

Answer 13

A column with low cardinality is a bad candidate, and a column with high cardinality is a bad candidate as well.

A column in the middle in terms of cardinality is best.

Question 14

What is a View?

Answer 14

A view is a table-like object in Snowflake derived from a query. A view functions effectively like a table and can be used interchangeably with a table.

Question 15

What is a Materialized View?

Answer 15

Identical to a normal view except that the predefined query is pre-computed and the output is stored for later use when the view is called.

Question 16

Are materialized views faster than regular queries or views?

Answer 16

Yes, because the compute on a materialized view is done ahead of time unlike the compute for a regular query and view.

Question 17

Search Optimization

Answer 17

Search Optimization is a service within Snowflake that aims to improve performance of selective lookup queries on tables. A point lookup query returns only one or a small number of distinct rows.

Question 18

When is Search Optimization useful?

Answer 18

• Business users need fast response times for critical dashboards with highly selective features
• Data scientists exploring large volumes of data and are looking for specific subsets of data

Question 19

Data types supported by Search Optimization

Answer 19

• Fixed-point numbers (INT/NUMERIC)
• DATE, TIME, and TIMESTAMP
• VARCHAR
• BINARY

Question 20

Search Optimization works best to improve the performance of a query when the following is true

Answer 20

For tables:

• The table is not clustered
• The table is frequently queried on columns other than the primary cluster key

For queries:

• The query typically runs for 10+ seconds
• At least one column accessed through the filter has 100k+ distinct values

Question 21

Alternative Solutions to Search Optimization

Answer 21

• Clustering
• Materialized Views
• Caching

Question 22

Clustering a table can speed up which of the following? (Assuming they are on the clustering key)

Answer 22

• Range searches

- Equality searches

Question 23

Granting access to Search Optimization

Answer 23

You must have OWNERSHIP on the table you want to optimize.

You must have the ADD SEARCH OPTIMIZATION privilege on the schema that contains the table.

Question 24

What factors impact query performance?

Answer 24

• Query Queueing
• Disk Spillage
• Control Join Order
• Unsatisfactory Pruning
• Recognizing Row Explosion

Question 25

What (quantifiable) factors can impact query queueing?

Answer 25

- QUEUED_LOAD - QUEUED_RESUMING - QUEUED_REPAIR

Question 26

Where are the three factors that detail query queueing pulled from?

Answer 26

INFORMATION_SCHEMA.QUERY_HISTORY view

Question 27

QUEUED_LOAD / QUEUED_OVERLOAD_TIME

Answer 27

Details the current load on a warehouse, can often describe whether or not there is capacity for a new query and details whether the warehouse is overloaded. (This is the most common condition)

Question 28

QUEUED_RESUMING

Answer 28

Details whether or not the warehouse is currently resuming, say from auto-suspension or a restart. A query will execute once the warehouse is available.

Question 29

QUEUED_REPAIR

Answer 29

A faulty server within your warehouse is being replaced by a healthy one. This is very rare and typically only happens in the instance of hardware failure.

Question 30

What function is used to monitor activity history for a warehouse?

Answer 30

WAREHOUSE_LOAD_HISTORY() function

Question 31

Disk Spilling

Answer 31

When a Snowflake cannot fit an operation into memory (like tables/data that are being used to compute a query result), this data will spill into memory. If memory and local disk becomes full, the query data will spill into remote storage. As data spills over into each new layer downstream, the slower the query becomes.

Question 32

Order of disk spillage layers

Answer 32

Descending by speed: - Memory - Local disk (SSD) - Remote storage (S3/Blob storage)

Question 33

Where can you see whether a query has disk spillage in the Snowflake UI?

Answer 33

Profile tab in the web interface, look for the "Spilling" section which dictates the number of bytes spilled to local storage.

Question 34

Control Join Order

Answer 34

In general, the SQL query optimizer chooses the correct order for joining tables, but in rare cases it does not it may be advisable to specify/correct the order in which queries join tables. When tables are not joined in an optimal order this can impact performance.

Question 35

How to manually control the join order of a query?

Answer 35

Specifying the join order in the query itself won't change anything as the query optimizer will just decide for you in the backend, regardless of your specification in the query. To do so, you must write several temp tables and join them in that order.

Question 36

Unsatisfactory Pruning

Answer 36

This is when unnecessary partitions are being scanned to compute a query, resulting in slower execution times.

Question 37

Solutions to Unsatisfactory Pruning

Answer 37

Improve clustering (clustering keys, etc) and pruning will improve.

Question 38

Row Explosion

Answer 38

Due to unanticipated data duplication due to joins on a query, more rows may be exploded out of a query than originally anticipated. This can exponentially increase compute and storage time/resources to complete a query.

Question 39

Row Explosion Solution

Answer 39

Double check to ensure your join statements are correct. At times there is no way to fix this if your joins are absolutely necessary.

Question 40

Query Profile Tool

Answer 40

The Query Profile tool provides execution details for a query as well as a graphical representation of the main components of the plan to process a query along with statistics for each component. This tool helps identify common query performance issues mentioned in prior cards.

Question 41

Operator Trees

Answer 41

A query is divided into steps, with each step containing an Operator Tree. An Operator Tree contains the main elements/operator nodes and the relationships between each operator that comprise of a step within a query.

Question 42

Optimizing Query Workloads in Snowflake

Answer 42

Because Snowflake allows for simple creation and replication of data warehouses, it is best to segment query workloads based on their use case. This allows for optimal caching, clustering, and concurrency. i.e the marketing department should have their own warehouse separate from finance. Each data science team should have their own warehouse specific to their objective/subject matter.