DAA-C01 Question 97
Single answerSystem functionsA data analyst is troubleshooting why a dashboard query against a large fact table suddenly became slower after a new set of daily loads. The analyst wants to determine whether Snowflake's automatic clustering depth has increased enough to justify reclustering or redesigning the table's clustering strategy. Which system function should the analyst use to evaluate the current clustering quality of the table?
- A
SYSTEM$CLUSTERING_INFORMATION('SALES_DB.PUBLIC.FACT_ORDERS')
- B
SYSTEM$QUERY_HISTORY('SALES_DB.PUBLIC.FACT_ORDERS')
- C
SYSTEM$MICRO_PARTITION_INFORMATION('SALES_DB.PUBLIC.FACT_ORDERS')
- D
SYSTEM$TABLE_STORAGE_METRICS('SALES_DB.PUBLIC.FACT_ORDERS')
Show answer and explanation
Correct answer: A
Explanation
When query performance degrades after incremental loads, one common cause is reduced micro-partition pruning due to worsening clustering. Snowflake provides SYSTEM$CLUSTERING_INFORMATION specifically to inspect a table's clustering characteristics and determine whether clustering depth has increased. This helps analysts decide whether the current clustering key remains effective or whether maintenance or redesign is needed. In contrast, query history functions are used to inspect past query executions, and storage-oriented functions do not measure clustering quality. Snowflake documentation on clustering and system functions identifies SYSTEM$CLUSTERING_INFORMATION as the appropriate function for evaluating clustering state.
- A. Correct.
Correct. SYSTEM$CLUSTERING_INFORMATION returns clustering metadata for a table, including details that help evaluate clustering quality and depth. This is the appropriate system function when investigating whether data layout degradation may be affecting pruning efficiency and query performance after new data loads.
- B. Incorrect.
Incorrect. Query history is useful for investigating execution behavior, runtime, and query plans, but SYSTEM$QUERY_HISTORY is not the system function used to evaluate a table's clustering state. Query history answers how queries behaved, not whether the table's micro-partitions are well clustered.
- C. Incorrect.
Incorrect. This is a plausible distractor because micro-partition details are related to pruning and clustering, but SYSTEM$MICRO_PARTITION_INFORMATION is not the standard system function analysts use for assessing clustering quality in this scenario. The exam-relevant function for clustering evaluation is SYSTEM$CLUSTERING_INFORMATION.
- D. Incorrect.
Incorrect. Storage metrics focus on table storage usage and related statistics, not clustering depth or clustering effectiveness. A candidate might choose this option if they confuse storage growth from daily loads with degraded clustering, but storage metrics do not directly measure clustering quality.