What you will be able to do
- Explain what clustering metadata Snowflake keeps for a table's micro-partitions
- Interpret clustering depth, including the value for an empty table and the meaning of a constant state
- Call SYSTEM$CLUSTERING_DEPTH with or without a column list, and predict when it errors
- Read the JSON returned by SYSTEM$CLUSTERING_INFORMATION and tell which clustering version a table uses
Key concept
Clustering depth — The average number of micro-partitions whose value ranges overlap for a chosen set of columns. A lower depth means less overlap, so queries filtering on those columns can skip more micro-partitions.
1.What Snowflake records about each micro-partition
Snowflake automatically divides the data in every table into micro-partitions. These are contiguous units of storage that each hold between 50 MB and 500 MB of uncompressed data, stored by column. You never define them yourself. Snowflake partitions each table in the order the data is inserted or loaded. What makes micro-partitions worth analysing is the metadata Snowflake keeps for each one: the range of values in every column, the number of distinct values, and other properties used for optimization and query processing.
The value ranges are what make pruning possible. If a query filters on a range of values, Snowflake can skip every micro-partition whose range cannot contain a match. It then prunes by column inside the micro-partitions that remain. Ideally, a filter that covers 10% of a range scans only 10% of the micro-partitions. Pruning has limits, though. Snowflake does not prune on a predicate that contains a subquery, even if the subquery returns a constant.
The complication is that micro-partitions can overlap in their value ranges. Overlap helps prevent skew, but every micro-partition whose range covers a filtered value still has to be scanned. So the useful question is: for a given set of columns, how much do this table's micro-partitions overlap? To answer it, Snowflake keeps clustering metadata in addition to the per-partition statistics. That metadata covers the total number of micro-partitions, how many of them have overlapping values in a given subset of columns, and how deep that overlap is.
Checkpoint 1 of 6· Check yourself
Which of these is NOT part of the clustering metadata Snowflake maintains for a table's micro-partitions?
The clustering metadata covers the partition count, the number of overlapping partitions, and the overlap depth. Query history is not part of it.
“The number of micro-partitions containing values that overlap with each other (in a specified subset of table columns).”Source: docs.snowflake.com
Sources1
2.Clustering depth: putting a number on overlap
For a populated table, clustering depth is the average depth of the overlapping micro-partitions for the specified columns. It is always 1 or higher, and a smaller value means the table is better clustered on those columns. The bounds are easy to mix up. The "1 or more" rule applies only to tables that contain data. An empty table has no micro-partitions, so its depth is 0, and that holds whether or not a clustering key is defined.
At the other extreme, if the value ranges of the micro-partitions don't overlap at all, the micro-partitions are in a constant state, which means clustering cannot improve them. In a real table with a large number of micro-partitions, reaching a constant state everywhere is neither likely nor required for good query performance.
Snowflake gives two main uses for depth. The first is monitoring a large table's clustering health over time as DML runs against it. The second is deciding whether a large table would benefit from an explicit clustering key. Depth is a signal, not a verdict. Snowflake states that depth is not an absolute or precise measure of whether a table is well clustered. If queries on the table perform as needed, the table is likely well clustered. If query performance degrades over time, the table may benefit from clustering.
Checkpoint 2 of 6· Check yourself
A monitoring job shows a fact table's clustering depth rising steadily over several months. Its dashboard queries still meet their performance targets. Based on Snowflake's guidance, what is the best reading?
Depth is a health signal, not an absolute measure. Snowflake names query performance as the best indicator of how well clustered a table is.
“Ultimately, query performance is the best indicator of how well-clustered a table is:”Source: docs.snowflake.com
Sources1
3.SYSTEM$CLUSTERING_DEPTH: depth on demand
Snowflake exposes clustering metadata through two system functions: SYSTEM$CLUSTERING_DEPTH and SYSTEM$CLUSTERING_INFORMATION, which also reports clustering depth. SYSTEM$CLUSTERING_DEPTH is the narrower of the two. It computes the average depth of a table for the columns you pass in, or for the table's clustering key if you pass none.
SYSTEM$CLUSTERING_DEPTH( '<table_name>' , '( <col1> [ , <col2> ... ] )' [ , '<predicate>' ] )Every argument is a string, so each must be enclosed in single quotes. Whether the column list is required depends on the table. If the table has no clustering key, the column list is required, and leaving it out returns an error. If the table has a clustering key, the list is optional, and when you omit it Snowflake calculates depth using the defined key. You can also pass any columns at all to measure depth on columns other than the key. This lets you check whether a candidate key would help before you define it. The optional predicate narrows the calculation to a range of values in those columns. You write it without the WHERE keyword.
Checkpoint 3 of 6· Check yourself
Table SALES has no clustering key. An engineer runs SYSTEM$CLUSTERING_DEPTH('sales') with no column list. What happens?
Without a clustering key, Snowflake has no columns to use by default, so the column list is mandatory.
“For a table with no clustering key, this argument is required. If this argument is omitted, an error is returned.”Source: docs.snowflake.com
Checkpoint 4 of 6· Exam question
A data engineer runs `SYSTEM$CLUSTERING_INFORMATION('sales_fact', '(sale_date)')` on a 40 TB table and sees `average_depth` of 850 with `average_overlaps` of 700, while a similarly sized table with well-tuned clustering reports a depth near 4. What does the high `average_depth` value most directly indicate?
Correct answer: A — The table's micro-partitions overlap heavily on the sale_date key, so filtering queries must scan far more partitions than a well-clustered table would.
- A. A high average_depth means micro-partitions have wide, overlapping value ranges for the clustering key, so a filter on sale_date can no longer prune most partitions and must scan many more of them. This directly explains why the table performs worse than one reporting a depth near 4.
- B. Snowflake does cap the partitions it evaluates above two million and notes this in the result, but it does not fabricate or inflate the depth figure; the returned average_depth still reflects real overlap in the sampled partitions.
- C. An invalid column expression causes the function call to fail with an error rather than silently substituting insertion order for the requested columns, so this would not produce a misleadingly high but valid-looking depth value.
- D. SYSTEM$CLUSTERING_INFORMATION reads persisted micro-partition metadata rather than re-executing a query plan, so warehouse sizing does not influence the computed depth or overlap figures it returns.
Sources2
4.SYSTEM$CLUSTERING_INFORMATION: the full diagnostic
SYSTEM$CLUSTERING_INFORMATION reports clustering depth together with other details, and it works on any columns of any table. It follows the same argument rule as SYSTEM$CLUSTERING_DEPTH. For a table with an explicit clustering key, the table name is enough. For a table without one, or to evaluate columns other than the key, you pass the columns as an additional argument. The function returns a VARCHAR that contains a JSON document of name/value pairs. These are the fields you are most likely to read:
| Field | What it tells you |
|---|---|
| cluster_by_keys | The columns used to compute the clustering information; they can be any columns in the table |
| version | Which Automatic Clustering version the table uses: CLASSIC (Clustering Classic) or OPTIMA (Optima Clustering) |
| notes | Suggestions for clustering more efficiently, such as a warning when the clustering column's cardinality is extremely high; it can be empty |
| total_partition_count | The total number of micro-partitions in the table |
| total_constant_partition_count | The number of micro-partitions that have reached a constant state for the specified columns; not available under Optima Clustering |
Two fields depend on the clustering version. For tables on Optima Clustering, total_constant_partition_count and partition_depth_histogram are not available. Where it is reported, a higher constant-partition count means more micro-partitions can be pruned from queries. The version field is also the documented way to find out whether a table runs on Optima Clustering or Clustering Classic.
Checkpoint 5 of 6· Check yourself
An engineer needs to know whether a clustered table uses Optima Clustering or Clustering Classic. What should they do?
The version field returned by SYSTEM$CLUSTERING_INFORMATION contains CLASSIC or OPTIMA, and Snowflake directs you to this function for exactly this purpose.
“To determine whether a table uses Optima Clustering or Clustering Classic, call SYSTEM$CLUSTERING_INFORMATION.”Source: docs.snowflake.com
Checkpoint 6 of 6· Exam question
A team defines a clustering key directly on an `event_time` column stored with nanosecond precision on a 5 TB events table, expecting better pruning, but query latency and clustering depth do not improve after reclustering completes. What is the most likely cause and correct fix?
Correct answer: A — Nanosecond precision gives almost every row a distinct value, so pruning barely improves; casting with `TO_DATE(event_time)` cuts cardinality while keeping order.
- A. A nanosecond-precision key is close to unique per row, so each micro-partition still spans nearly the full value range and pruning stays weak; truncating to a coarser grain like a date raises the number of rows sharing a value while still preserving sort order. This is the documented fix for over-high-cardinality clustering keys.
- B. Snowflake fully supports TIMESTAMP columns as clustering keys; the problem here is the column's cardinality at nanosecond grain, not an outright restriction on the data type.
- C. The search optimization service targets point lookups on unclustered columns and does not fix or replace a poorly chosen clustering key; clustering keys also benefit range predicates, not just equality ones.
- D. Reclustering is an ongoing background operation Snowflake performs on the base table itself, and materialized views are a separate object type that does not substitute for defining an effective clustering key.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Clustering depth is always at least 1, so an empty table reports a depth of 1.Why is that wrong?
The 1-or-more floor applies only to populated tables. A table with no micro-partitions has a depth of 0, even if a clustering key is defined.
2.A high clustering depth on its own proves a table needs reclustering.Why is that wrong?
Depth is not an absolute or precise measure. Query performance is the best indicator of whether a table is well clustered.
3.SYSTEM$CLUSTERING_DEPTH can be called with only a table name on any table.Why is that wrong?
The table name alone works only when the table has a clustering key. Without a key, the column list is required, and omitting it returns an error.
Covered in SYSTEM$CLUSTERING_DEPTH: depth on demand
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Snowflake stores metadata about all rows stored in a micro-partition, including:”
↩︎ What Snowflake records about each micro-partition“Micro-partitions can overlap in their range of values, which, combined with their uniformly small size, helps prevent skew.”
↩︎ What Snowflake records about each micro-partition“Snowflake does not prune micro-partitions based on a predicate with a subquery, even if the subquery results in a constant.”
↩︎ What Snowflake records about each micro-partition“the micro-partitions are considered to be in a constant state (i.e. they cannot be improved by clustering).”
↩︎ Clustering depth: putting a number on overlap“Determining whether a large table would benefit from explicitly defining a clustering key.”
↩︎ Clustering depth: putting a number on overlap“The smaller the average depth, the better clustered the table is with regards to the specified columns.”
↩︎ Key concept“A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 0.”
↩︎ Exam trap 1“The clustering depth for a table is not an absolute or precise measure of whether the table is well-clustered.”
↩︎ Exam trap 2“The number of micro-partitions containing values that overlap with each other (in a specified subset of table columns).”
↩︎ Checkpoint“A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 0.”
↩︎ Prediction“Ultimately, query performance is the best indicator of how well-clustered a table is:”
↩︎ Checkpoint - 2.
“Computes the average depth of the table according to the specified columns (or the clustering key defined for the table).”
↩︎ SYSTEM$CLUSTERING_DEPTH: depth on demand“The average depth of a populated table (i.e. a table containing data) is always 1 or more.”
↩︎ SYSTEM$CLUSTERING_DEPTH: depth on demand“Note that predicate does not utilize a WHERE keyword at the beginning of the clause.”
↩︎ SYSTEM$CLUSTERING_DEPTH: depth on demand“For a table with no clustering key, this argument is required. If this argument is omitted, an error is returned.”
↩︎ Exam trap 3“For a table with no clustering key, this argument is required. If this argument is omitted, an error is returned.”
↩︎ Checkpoint - 3.
“This function can be run on any columns on any table, regardless of whether the table has an explicit clustering key”
↩︎ SYSTEM$CLUSTERING_INFORMATION: the full diagnostic - 4.
“The returned string is in JSON format and contains the following name/value pairs:”
↩︎ SYSTEM$CLUSTERING_INFORMATION: the full diagnostic“For tables that use Optima Clustering, the following fields aren’t available: total_constant_partition_count partition_depth_histogram”
↩︎ SYSTEM$CLUSTERING_INFORMATION: the full diagnostic
Also cited
“To determine whether a table uses Optima Clustering or Clustering Classic, call SYSTEM$CLUSTERING_INFORMATION.”
↩︎ Checkpoint