ARA-C01 Question 418
Single answerUse of system clustering informationA retail company stores 8 TB of order history in a Snowflake table named FACT_ORDERS. The table is defined with a clustering key on (ORDER_DATE, REGION_ID). Recently, query performance has become inconsistent for dashboards that filter on the last 30 days and a small set of regions. The architect wants to determine whether poor micro-partition pruning is contributing to the issue before deciding whether to change the clustering strategy or enable Automatic Clustering. Which action is the MOST appropriate to use Snowflake system clustering information for this analysis?
- A
Query SYSTEM$CLUSTERING_INFORMATION('FACT_ORDERS') to review metrics such as clustering depth and partition overlap for the current clustering key.
- B
Run SHOW TABLES LIKE 'FACT_ORDERS' and compare the BYTES column over time to determine whether the table is sufficiently clustered.
- C
Query ACCOUNT_USAGE.QUERY_HISTORY to find long-running dashboard queries; if execution time is high, that confirms the table needs a new clustering key.
- D
Use SYSTEM$CLUSTERING_DEPTH('FACT_ORDERS', '(ORDER_DATE, REGION_ID)') because it returns all clustering diagnostics, including overlap details and recommendations.
Show answer and explanation
Correct answer: A
Explanation
When diagnosing whether a clustering strategy is helping selective queries, Snowflake system clustering functions are the correct tools. SYSTEM$CLUSTERING_INFORMATION is especially useful because it provides a richer picture of clustering state than simply looking at query runtimes or table size. In practice, architects use it to evaluate whether the existing clustering key aligns with common filter predicates and whether micro-partition overlap is limiting pruning. If the table shows poor clustering metrics for the relevant columns, the architect can then consider refining the clustering key or using Automatic Clustering. Snowflake documentation on clustering keys and system functions describes SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH as diagnostic functions, with the former generally providing more complete clustering details for analysis.
- A. Correct.
Correct. SYSTEM$CLUSTERING_INFORMATION is the primary built-in function for evaluating how well a table is clustered. It returns clustering diagnostics for the table, including information that helps assess micro-partition overlap and clustering quality relative to the defined or specified key. This is the most appropriate first step when the goal is to determine whether poor pruning due to clustering is likely contributing to inconsistent query performance.
- B. Incorrect.
Incorrect. SHOW TABLES returns metadata such as size and other table attributes, but table size or growth does not indicate whether data is well clustered. Clustering quality is about how values are organized across micro-partitions and how much overlap exists for the filter columns, not simply how many bytes the table contains.
- C. Incorrect.
Incorrect. QUERY_HISTORY can help identify slow queries and their frequency, but it does not by itself confirm that clustering is the root cause. Slow performance could come from many factors, including warehouse sizing, join strategy, cache state, or poorly selective predicates. System clustering information should be used to validate whether partition pruning is likely impaired.
- D. Incorrect.
Incorrect. SYSTEM$CLUSTERING_DEPTH is a valid function for examining clustering depth, but this option overstates its output. It does not return all clustering diagnostics, overlap details, and recommendations in the same way SYSTEM$CLUSTERING_INFORMATION does. Relying on it alone would provide a narrower view of clustering quality.