Snowflake Query Profile, EXPLAIN, Partition Pruning and Clustering Keys

1.Finding the slow query and opening its Query Profile

Before you can tune anything, you need to find the query worth tuning. In Snowsight, the Query History page has a Duration column. Sort it to bring the longest-running queries to the top, then filter by user or warehouse. When you open a query, its Query Profile shows where the time went. The profile breaks the query into operator nodes, and a Most Expensive Nodes pane points you straight at the operators that took longest. You can drill into each node to see what share of its time went to each category of processing. To get the same statistics in SQL, call the GET_QUERY_OPERATOR_STATS function.

For trends across many queries, query the ACCOUNT_USAGE views. By default only ACCOUNTADMIN can read them. A user without that access can still see recent queries through the Information Schema QUERY_HISTORY table functions. These views lag behind real time, so to check a query you just ran, use Snowsight instead. The QUERY_HISTORY view also has a query_hash column. Grouping by it shows queries that are cheap on each run but costly in total because they run so often.

Sometimes a query is slow but the query itself is not the problem: it spent its time waiting in a queue. The Warehouse Activity chart in Snowsight shows whether queries were queued. The query below finds days in the last month when a warehouse had any queued load. Snowflake's documentation says trends like these can help you decide to resize a warehouse or move some queries to another warehouse.

SELECT TO_DATE(start_time) AS date,
  warehouse_name,
  SUM(avg_running) AS sum_running,
  SUM(avg_queued_load) AS sum_queued
FROM snowflake.account_usage.warehouse_load_history
WHERE TO_DATE(start_time) >= DATEADD(month,-1,CURRENT_TIMESTAMP())
GROUP BY 1,2
HAVING SUM(avg_queued_load) >0;

Sources1

2.Reading the plan before running it: EXPLAIN

The Query Profile shows what a query did after it ran. EXPLAIN returns the logical execution plan before the query runs: the table scans, joins and filters Snowflake would perform. It uses no compute credits, though compilation does use Cloud Services credits. The plan can depend on warehouse size. If you run EXPLAIN without a current warehouse, Snowflake builds the plan as if for an XSMALL warehouse. You choose the output format, and TABULAR is the default. For a query you have already run, SYSTEM$EXPLAIN_PLAN_JSON returns the same plan as JSON.

EXPLAIN [ USING { TABULAR | JSON | TEXT } ] <statement>

SELECT SYSTEM$EXPLAIN_PLAN_JSON(LAST_QUERY_ID()) AS explain_plan;

Comparing partitionsAssigned with partitionsTotal shows how well the filter prunes at compile time. Treat these numbers as an upper bound: runtime optimizations such as join pruning can cut them further. The plan is logical, too. The order of operations it shows is not necessarily the order in which they actually run.

Sources2

3.Partition pruning: what the metadata lets Snowflake skip

Every Snowflake table is automatically split into micro-partitions. Each one holds 50 to 500 MB of uncompressed data, stored by column. For every micro-partition, Snowflake records the range of values in each column, the number of distinct values, and other properties. When a query filters on a column, Snowflake compares the filter with those ranges and skips any partition that cannot hold a match. Then, within the partitions it keeps, it reads only the columns the query references. Ideally, a filter that selects 10% of a value range scans only 10% of the micro-partitions. The closer the scanned share is to the selected share, the better the pruning.

Not every predicate can prune. A predicate that contains a subquery does not prune micro-partitions, even when the subquery returns a constant.

In the Query Profile, the Pruning statistics show Partitions scanned next to Partitions total. If a selective filter still scans most of the table's partitions, pruning is not helping and the scan is a likely bottleneck. The profile also splits execution time into categories: Processing, Local Disk IO, Remote Disk IO, Network Communication, Synchronization and Initialization. A large share in one category shows what the query was waiting on. The Spilling statistics show bytes spilled to local and remote storage, which happens when intermediate results do not fit in memory. Spilling is a memory problem rather than a pruning problem, and the next section covers how to address it.

Sources34

4.When pruning degrades: clustering depth and clustering keys

Pruning only works if similar values sit in the same micro-partitions. Snowflake partitions data in the order it was loaded, so data that arrives sorted by date clusters well by date. Heavy DML on a very large table can wear that ordering down. Clustering depth measures this: it is the average depth of overlapping micro-partitions for the columns you specify. The smaller the depth, the better clustered the table is, and an empty table has a depth of 0. Depth is only a guide, though. The best measure is still query performance. If queries slow down over time, the table may need clustering.

A clustering key names the columns or expressions Snowflake should use to keep similar rows together. You can set it with CREATE TABLE or ALTER TABLE. After that, Snowflake maintains the clustering automatically. That maintenance uses credits, and Snowflake reclusters only when the table will benefit, so rows are not necessarily rewritten right after you define the key.

A table is a good candidate when it has many micro-partitions (typically multiple terabytes), its queries are selective or sort the data, and most of its queries filter or sort on the same few columns. The more often a table changes, the more it costs to keep clustered. If a busy table must be clustered anyway, run its DML in large, infrequent batches. Snowflake recommends at most 3 or 4 columns per key. Put columns used in selective filters first, then columns used in joins. Cardinality matters in both directions. A Boolean column prunes very little. A nanosecond timestamp has too many distinct values, so cluster on an expression over it that keeps the original order but reduces the number of distinct values.

Clustering does not fix every slow query. Spilling is a warehouse memory problem, not a pruning problem. When a warehouse runs out of memory, bytes spill onto storage (first local disk, then remote storage), and the query runs substantially slower. Snowflake's warehouse tuning guidance lists this as the "Resolve memory spillage" strategy: adjust the memory available to the warehouse, which in practice means a larger warehouse, since the larger a warehouse, the more compute resources it has. Reclustering the table will not add memory. If bytes spilled to remote storage is high in the Query Profile, look at the warehouse, not the clustering key.

Sources356

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Filtering a column against a subquery that returns one value prunes just as well as filtering against a literal.Why is that wrong?

   Snowflake does not prune on a predicate that contains a subquery, even when the subquery returns a constant. Use a literal or a separately computed value instead.

   Covered in Partition pruning: what the metadata lets Snowflake skip

2. 2.Defining a clustering key immediately rewrites the table, and maintaining it is free.Why is that wrong?

   Rows are not necessarily reclustered right away, because Snowflake reclusters only when the table will benefit. Automatic maintenance also uses credits, and frequent DML makes it more expensive.

   Covered in When pruning degrades: clustering depth and clustering keys