COF-C03 Question 173
Single answerLogging and tracingA Snowflake administrator needs to investigate why several SQL statements issued by a BI tool are intermittently slow. The administrator wants to review execution history, identify query timings, and correlate statements back to the BI application and the specific user sessions that submitted them. Which Snowflake feature provides the most appropriate built-in source of this information?
- A
Query history in ACCOUNT_USAGE or INFORMATION_SCHEMA, including fields such as QUERY_TEXT, USER_NAME, SESSION_ID, CLIENT_APPLICATION_ID, and timing metrics
- B
The load history views, because they capture all SQL operations and include the client tool that submitted each statement
- C
The access history views, because they are intended to troubleshoot statement performance and store execution duration for every query
- D
The warehouse meter history views, because they list every SQL statement executed on a warehouse along with the application name and session identifier
Show answer and explanation
Correct answer: A
Explanation
For logging and tracing SQL activity in Snowflake, QUERY_HISTORY is the most appropriate built-in source. It is specifically intended for reviewing statement execution history, durations, users, sessions, and client metadata. In practice, administrators use SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for account-level historical analysis or INFORMATION_SCHEMA query history functions for more immediate operational troubleshooting. By contrast, LOAD_HISTORY is limited to data load operations, ACCESS_HISTORY is aimed at governance and auditing of object access, and WAREHOUSE_METER_HISTORY is for warehouse consumption metrics rather than statement-level tracing. This aligns with Snowflake documentation on Account Usage views and Information Schema table functions for monitoring query activity and performance.
- A. Correct.
Correct. QUERY_HISTORY is the primary built-in source for tracing SQL execution in Snowflake. It includes statement text, execution status, user, session, client/application metadata, and timing details such as compilation and execution time. Administrators commonly use ACCOUNT_USAGE.QUERY_HISTORY or the corresponding INFORMATION_SCHEMA table functions to investigate slow or intermittent query behavior and tie statements back to client tools and sessions.
- B. Incorrect.
Incorrect. Load history is focused on data loading operations such as COPY INTO and related file-load activity. It does not serve as the general tracing source for all SQL statements executed by users or BI tools. Someone might choose this option because ETL and BI platforms often perform loads, but load history is too narrow for broad SQL performance investigation.
- C. Incorrect.
Incorrect. ACCESS_HISTORY is primarily for governance, auditing, and object access lineage, such as identifying which objects were accessed by queries. It is not the main source for detailed query timing analysis or end-to-end tracing of SQL execution performance. This is a common misconception because access history contains query-related audit data, but it is not designed as the first stop for troubleshooting latency.
- D. Incorrect.
Incorrect. WAREHOUSE_METER_HISTORY provides warehouse credit consumption and metering information over time. It helps analyze warehouse usage and cost trends, not detailed per-statement tracing. It does not list each SQL statement with session and client application identifiers in the way query history does.