ADA-C01 Question 290
Single answerDomain 4.0: Performance Monitoring and Tuning (20%)A Snowflake administrator is investigating slow dashboard queries from a BI tool. The queries run every few minutes against a 12 TB fact table joined to several small dimensions. Query Profile shows repeated full table scans on the fact table, and the administrator notices that the same dashboard filters are consistently applied on ORDER_DATE and REGION. The warehouse size is already appropriate for concurrency, and increasing warehouse size has not materially improved latency. Which action should the administrator take to most directly improve query performance for this workload?
- A
Create a clustering key on the fact table using ORDER_DATE and REGION, then monitor clustering depth and reclustering cost
- B
Convert the small dimension tables to temporary tables so Snowflake can avoid scanning the fact table repeatedly
- C
Enable Search Optimization Service on the virtual warehouse handling the BI workload
- D
Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter so longer-running queries can complete without interruption
Show answer and explanation
Correct answer: A
Explanation
This question tests practical performance tuning in Snowflake by requiring the candidate to identify the optimization that best matches the observed workload pattern. When Query Profile shows repeated full table scans on a very large table and the same filter columns are used frequently, the key issue is often poor micro-partition pruning. A clustering key can improve pruning when queries repeatedly filter or sort on specific columns and the table is large enough for clustering benefits to matter. Snowflake documentation and best practices emphasize using Query Profile, pruning behavior, and clustering information to determine whether clustering is appropriate, while also balancing maintenance cost. Search Optimization Service is a valid Snowflake feature for some selective access patterns, but it is not enabled at the warehouse level, making that option incorrect as written. Warehouse scaling helps with compute capacity and concurrency, but if the problem is unnecessary data scanned due to poor pruning, resizing alone may not materially reduce latency.
- A. Correct.
Correct. The scenario indicates repeated selective filtering on the same columns of a very large table, with Query Profile showing full table scans. Defining a clustering key on ORDER_DATE and REGION can improve micro-partition pruning so Snowflake reads less data for those dashboard queries. This is especially relevant when natural clustering has degraded over time due to DML or data load patterns. Monitoring clustering information and reclustering cost is also a best practice because clustering improves performance only when the pruning benefit outweighs maintenance cost.
- B. Incorrect.
Incorrect. Temporary tables affect object lifecycle and session scope, not pruning behavior on the large fact table. Converting small dimensions to temporary tables would not address the root cause shown in Query Profile: repeated full scans of the 12 TB fact table. Someone might choose this if they confuse table type with physical optimization, but Snowflake still needs to scan the large table unless pruning improves.
- C. Incorrect.
Incorrect. Search Optimization Service is configured on tables, not on virtual warehouses. In addition, it is generally intended for highly selective point-lookups and certain predicate patterns, not as a warehouse-level setting. The option is factually incorrect because it references enabling the service on a warehouse. Even if Search Optimization were considered on a table, the most direct optimization indicated by repeated filtering on large-table columns and full scans is improved clustering for pruning.
- D. Incorrect.
Incorrect. STATEMENT_TIMEOUT_IN_SECONDS controls how long statements are allowed to run before timing out. It does not improve execution speed or reduce scanned data. This is a common operational parameter, but changing it would only allow slow queries to keep running longer, not make the dashboard more responsive.