ARA-C01 Question 419
Single answerUse of system clustering informationA data architect is reviewing query performance for a 12 TB SALES_FACT table in Snowflake. The table was defined with a clustering key on (ORDER_DATE, REGION_ID). Most dashboard queries filter on ORDER_DATE ranges and sometimes REGION_ID. Automatic Clustering is enabled, but performance has become inconsistent after several weeks of heavy incremental loads. The architect wants to use Snowflake system clustering information to determine whether poor pruning is contributing to the problem before changing the design. Which action should the architect take first?
- A
Query SYSTEM$CLUSTERING_INFORMATION('SALES_FACT') to review clustering depth and overlap for the current table state
- B
Query INFORMATION_SCHEMA.TABLE_STORAGE_METRICS to verify whether Time Travel retention is causing clustering degradation
- C
Use SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS on SALES_FACT to confirm whether search optimization is needed instead of clustering
- D
Run SYSTEM$CLUSTERING_RATIO('SALES_FACT') and, if the ratio is below 100, conclude that micro-partition pruning is not a factor
Show answer and explanation
Correct answer: A
Explanation
When diagnosing whether a clustered table is losing pruning efficiency, Snowflake best practice is to examine system clustering metadata directly before changing keys or introducing another optimization feature. SYSTEM$CLUSTERING_INFORMATION is designed for this purpose and provides actionable details about how well the current clustering key aligns with the table's micro-partition layout. In real workloads with frequent inserts and merges, clustering quality can degrade over time even when Automatic Clustering is enabled, especially if data arrives out of order or query predicates only partially align with the key. By reviewing clustering depth and overlap, an architect can determine whether micro-partition pruning is likely weaker than expected. This aligns with Snowflake documentation on clustered tables and system functions for evaluating clustering, where SYSTEM$CLUSTERING_INFORMATION is the primary diagnostic function and is more useful than relying only on summary ratios or unrelated storage/search optimization metrics.
- A. Correct.
Correct. SYSTEM$CLUSTERING_INFORMATION returns detailed clustering metadata for a table, including metrics such as average depth and information about partition overlap relative to the clustering key. In this scenario, the architect specifically wants to assess whether degraded clustering is reducing pruning effectiveness after incremental loads. This function is the most appropriate first step because it provides direct system-generated evidence about clustering quality on the table as it exists now.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLE_STORAGE_METRICS is useful for understanding storage consumption and retention-related storage effects, but it does not provide the clustering quality indicators needed to diagnose pruning effectiveness. Time Travel retention may affect storage cost, but it is not the primary system view for determining whether the table's clustering key is no longer organizing micro-partitions effectively.
- C. Incorrect.
Incorrect. SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS helps estimate the cost of enabling search optimization service, which is a different feature intended for certain selective lookup patterns. The scenario asks how to use system clustering information first to determine whether poor pruning is a clustering problem. Estimating search optimization costs skips the required diagnosis and does not directly assess existing clustering health.
- D. Incorrect.
Incorrect. SYSTEM$CLUSTERING_RATIO is a legacy-style metric that is less informative than SYSTEM$CLUSTERING_INFORMATION for architectural diagnosis, and interpreting any value below 100 as proof that pruning is the issue is overly simplistic. Architects should not make a design conclusion from a single ratio without examining richer clustering details such as depth and overlap. The misconception is treating one summary number as a complete diagnosis.