ADA-C01 Question 560
Single answerQuery historical dataA Snowflake administrator needs to investigate a performance incident that occurred 10 days ago. The team wants to identify which SQL statements were executed by a specific service user during a 2-hour window and review execution characteristics such as start time, end time, and bytes scanned. The administrator also wants an approach that works even if the original warehouse has been dropped since the incident. Which action should the administrator take?
- A
Query the ACCOUNT_USAGE.QUERY_HISTORY view in the SNOWFLAKE shared database and filter by USER_NAME and START_TIME
- B
Run SHOW QUERIES HISTORY IN WAREHOUSE <warehouse_name> for the 2-hour window because SHOW commands retain query history independently of warehouse existence
- C
Use INFORMATION_SCHEMA.QUERY_HISTORY on the affected database because Information Schema retains all account query history for 365 days
- D
Query TABLE_STORAGE_METRICS in ACCOUNT_USAGE to identify the SQL statements and bytes scanned for the service user
Show answer and explanation
Correct answer: A
Explanation
For investigating historical SQL activity, Snowflake provides query metadata through both Information Schema table functions and the SNOWFLAKE.ACCOUNT_USAGE schema. For incidents from days ago, the best practice is to use SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY, which supports account-level historical analysis and includes useful fields such as USER_NAME, START_TIME, END_TIME, EXECUTION_STATUS, and BYTES_SCANNED. Administrators should also remember that ACCOUNT_USAGE views can have latency before records appear, so they are excellent for retrospective investigations but not always for near-real-time monitoring. By contrast, Information Schema query history functions are intended for shorter historical windows and are not the long-term retained source for broad account history. This aligns with Snowflake documentation for QUERY_HISTORY access patterns and the distinction between Information Schema and Account Usage views.
- A. Correct.
Correct. SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY is designed for querying historical query metadata across the account, including columns such as USER_NAME, START_TIME, END_TIME, and BYTES_SCANNED. Because this is account-level historical metadata, it does not depend on the warehouse still existing at the time of investigation. This is the appropriate source for an incident that occurred 10 days ago.
- B. Incorrect.
Incorrect. SHOW commands are not the correct mechanism for deep historical query analysis over this kind of time range, and they are not the standard source for account-level historical query metadata needed here. In addition, tying the investigation to a warehouse object is unnecessary and brittle, especially if the warehouse has been dropped.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA query history functions are useful for more recent history, but they do not provide all account history for 365 days. A common misconception is to treat Information Schema as the long-term source for historical query retention. For older incidents such as 10 days ago, ACCOUNT_USAGE is the intended source, subject to its documented latency.
- D. Incorrect.
Incorrect. TABLE_STORAGE_METRICS reports storage-related metadata, not executed SQL statements or their execution characteristics. Someone might choose this because bytes scanned sounds like a storage metric, but query-level execution details come from query history views/functions, not table storage views.