Micro-partition Pruning and Clustering Information for Slow Queries

1.How micro-partition metadata decides what a query scans

When a query on an existing table gets slow, start by asking how much data it reads. Snowflake divides every table into micro-partitions automatically. These are contiguous units of storage, each holding 50 MB to 500 MB of uncompressed data and organised by column. No one defines them. Tables are partitioned in the order the data arrives, so a table's physical layout follows its load history.

For every micro-partition, Snowflake records metadata that includes the range of values in each column and the number of distinct values. At query time it uses this metadata in two steps. First it drops whole micro-partitions whose value ranges cannot satisfy the filter. Then, inside the micro-partitions that remain, it reads only the columns the query references. The documentation gives an example: a year of data with date and hour columns, distributed evenly. A query for one hour would ideally scan 1/8760th of the micro-partitions.

That explains the first two things to check. One is the predicate itself: not every expression can be used to prune, and a filter written as a subquery cannot, even when the subquery always returns the same constant. The other is how the data is laid out. Pruning only works if each micro-partition's value ranges are narrow for the column you filter on. Data that arrives sorted along a natural dimension, such as date, prunes well on that dimension. Data that arrives unsorted, or that DML has rearranged over time, leaves wide, overlapping ranges, and then many micro-partitions might contain any given value. The documentation warns that unsorted or partly sorted data can hurt query performance, "particularly on very large tables".

Sources1

2.Measuring clustering health with system functions

You suspect the layout has degraded. To measure it, Snowflake keeps clustering metadata for each table: the total number of micro-partitions, how many of them overlap in value range for a chosen set of columns, and how deep that overlap goes. Clustering depth is the average overlap depth, and it is always 1 or more for a populated table. The smaller it is, the better the table is clustered on those columns. An empty table has a depth of 0. When no micro-partitions overlap at all, they are in a constant state and reclustering cannot improve them.

Two system functions expose this metadata: SYSTEM$CLUSTERING_DEPTH, and SYSTEM$CLUSTERING_INFORMATION, which also returns the depth. SYSTEM$CLUSTERING_INFORMATION takes a table name plus either a list of columns or a number of errors to return, but not both. For a table without a clustering key, the column list is required. For a table with a clustering key, you can leave it out and the defined key is used. You can also pass candidate columns that are not the current key, which lets you test a layout before committing to it.

SELECT SYSTEM$CLUSTERING_INFORMATION('t1', 5);

The documented example on table test2 shows a poorly clustered table: 0 constant micro-partitions out of 1156, an average overlap of about 117, an average depth of about 64, most micro-partitions in the 64 and 128 histogram buckets, and a clustering error saying the clustering service had been disabled. The notes field can also contain suggestions, such as a warning that the clustering column's cardinality is extremely high. Some limits apply. For tables using Optima Clustering, the constant-partition count and the histogram are not returned. For tables with more than 2 million partitions, results are based on a subset of partitions. Finally, treat depth as a guide, not a verdict. The documentation says depth is not a precise measure and that query performance is the best indicator.

Sources12

3.When a clustering key is the right fix

Once the diagnosis points to layout, the documented fix is a clustering key: a subset of columns or expressions that Snowflake uses to keep similar rows in the same micro-partitions. You could re-sort and re-insert rows yourself, but the documentation calls that "cumbersome and expensive". Once a key is defined, Snowflake maintains the clustering automatically. The documentation names two general signs that a table may need a key: queries are slower than expected or have degraded over time, and the table's clustering depth is large.

A clustering key costs credits, both for the initial clustering and for ongoing maintenance. It pays off only when the faster queries justify that spend, or when you need the fastest possible response times regardless of cost. The benefit comes from queries that filter or sort on the key, including sorts for ORDER BY, GROUP BY and some joins. Frequent querying increases the value. Frequent changes increase the cost of keeping the table clustered. Two further limits: hybrid tables cannot have clustering keys, because they are always ordered by primary key, and a new key does not rewrite rows immediately, because Snowflake reclusters only when the table will benefit.

Sources3

4.Optimization techniques: find the costly queries, then fix storage or compute

A clustering key is only one fix. A practical troubleshooting loop first finds which queries deserve attention, then decides whether the bottleneck is storage layout or compute.

To find candidates, query the ACCOUNT_USAGE QUERY_HISTORY view. Its partitions_scanned and partitions_total columns show how much of a table a query read, which ties back to pruning. Its query_hash column groups repeated queries, so you can identify the ones to optimize first: a query that is cheap on any single run can still be costly when it repeats often. To see where a single query spends its time, open its Query Profile in Snowsight. The Most Expensive Nodes pane identifies the operator nodes that take the longest to execute.

If pruning is poor, the storage fixes from the previous sections apply. If the query scans little data but is still slow, look at the warehouse. Check its load. If the running load is high or queries are queued, start a separate warehouse for some of the work, or add clusters to a multi-cluster warehouse to handle higher concurrency. If the running load is low and the query is slow, resize the warehouse to give it more compute. A larger warehouse helps larger, more complex queries most, and may not speed up small, simple ones. If a query runs twice as fast on the next size up, the cost of running it stays the same. If the warehouse was running and consuming credits but the total load stayed below 1 for long periods, it is inefficient, and you can decrease its size or, for a multi-cluster warehouse, lower MIN_CLUSTER_COUNT. Limit who can resize warehouses, because a forgotten upsize leads to unexpected costs.

ALTER WAREHOUSE my_wh SET WAREHOUSE_SIZE = large;

Sources45

5.Monitoring and alerting: views, resource monitors, alerts and event tables

Troubleshooting needs data, and Snowflake offers it from two places. The ACCOUNT_USAGE schema in the SNOWFLAKE database includes dropped objects and keeps history for 1 year, but its views lag by 45 minutes to 3 hours depending on the view. The INFORMATION_SCHEMA views and table functions have no latency but shorter retention, from 7 days to 6 months. By default only ACCOUNTADMIN can read ACCOUNT_USAGE. Other users can use the QUERY_HISTORY table functions of the Information Schema.

The Information Schema has a WAREHOUSE_LOAD_HISTORY table function that covers the last 14 days. It shows load in 5-second intervals when the period is under 8 hours, and 5-minute intervals otherwise. To find waste, compare the credits a warehouse used with the credits attributed to queries. CREDITS_ATTRIBUTED_COMPUTE_QUERIES excludes idle time, so the difference between CREDITS_USED_COMPUTE and that column is the idle cost.

SELECT
  (SUM(credits_used_compute) -
    SUM(credits_attributed_compute_queries)) AS idle_cost,
  warehouse_name
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('days', -10, CURRENT_DATE())
  AND end_time < CURRENT_DATE()
GROUP BY warehouse_name;

Resource monitoring controls spend. A resource monitor has a credit quota and a schedule. It has actions at percentage thresholds: Notify, Notify & Suspend, and Notify & Suspend Immediately, which cancels running statements. A monitor can have up to five Notify actions. It can cover the whole account or specific warehouses, and notifications go to account administrators with notifications enabled. Resource monitors work for warehouses only, not for serverless features.

Alerts turn monitoring into notification. CREATE ALERT defines a condition (IF EXISTS with a SELECT, SHOW or CALL) and an action, which runs when the condition returns at least one row. The alert runs on a schedule, either a cron expression with a time zone or an interval such as 1 MINUTE, and it uses a warehouse unless it is serverless. To send email, the action calls SYSTEM$SEND_EMAIL, which takes the name of a notification integration, the recipients, a subject and a body. If the integration sets ALLOWED_RECIPIENTS, no email is sent to an address that is not on that list. The documentation's example creates the email integration like this:

CREATE NOTIFICATION INTEGRATION mirror_alert_email
  TYPE = EMAIL
  ENABLED = TRUE
  ALLOWED_RECIPIENTS = ('oncall@example.com');

CREATE OR REPLACE ALERT alert_new_rows
  WAREHOUSE = my_warehouse
  SCHEDULE = '1 MINUTE'
  IF (EXISTS (
      SELECT *
      FROM my_table
      WHERE row_timestamp BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME()
       AND SNOWFLAKE.ALERT.SCHEDULED_TIME()
  ))
  THEN CALL SYSTEM$SEND_EMAIL(...);

Alerts are created suspended, so resume the alert with ALTER ALERT ... RESUME before it runs on its schedule. To verify a new alert, or to run it at a chosen point in a pipeline, trigger a single run independent of its schedule with EXECUTE ALERT. You cannot use it on an alert on new data. You can then check what the run did in the INFORMATION_SCHEMA.ALERT_HISTORY table function.

EXECUTE ALERT my_alert;

Event tables cover code-level observability. Snowflake collects log messages, trace events and metrics from function and procedure handlers into the active event table. SNOWFLAKE.TELEMETRY.EVENTS is the default, and you can instead create an event table and set it as active. An event table has predefined columns, including TIMESTAMP, RECORD_TYPE (such as LOG, SPAN or SPAN_EVENT), RESOURCE_ATTRIBUTES, RECORD, VALUE and SCOPE. You control how much is captured by setting levels for each type of data. In the Streamlit example, warehouse-runtime apps set the log level on the database, and the trace level is set the same way with TRACE_LEVEL = ON_EVENT. Analyze the data with SQL, and include a TIMESTAMP filter in event table queries, because these tables can hold a lot of data.

ALTER DATABASE <database_name> SET LOG_LEVEL = INFO;

Sources6789101112131415

Exam traps

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

1. 1.A filter whose value comes from a subquery that returns one constant prunes just like a literal value.Why is that wrong?

   Snowflake does not use predicates containing a subquery for pruning, even when the subquery returns a constant.

   Covered in How micro-partition metadata decides what a query scans

2. 2.Clustering depth is the definitive test of whether a table is well clustered.Why is that wrong?

   Depth is a guide only. The documentation names query performance as the best indicator of clustering quality.

   Covered in Measuring clustering health with system functions

3. 3.Defining a clustering key immediately rewrites the table into clustered order.Why is that wrong?

   Rows are not necessarily updated right away. Snowflake performs automated maintenance only when the table will benefit.

   Covered in When a clustering key is the right fix

4. 4.A resource monitor caps the credit spend of every Snowflake feature, including serverless ones.Why is that wrong?

   Resource monitors work for warehouses only. Serverless features and AI services are tracked with a budget instead.

   Covered in Monitoring and alerting: views, resource monitors, alerts and event tables