ADA-C01 Question 378
Single answerUse log trace data to perform root cause analysesA Snowflake administrator is investigating an intermittent failure in a Java UDF used by several ETL jobs. The jobs succeed most of the time, but during peak processing windows some executions fail with a generic Java exception in the query output. The development team has enabled unhandled exception logging and asks the administrator to identify the root cause using Snowflake log trace data. Which action should the administrator take first to most directly correlate the failing SQL statement with the Java stack trace generated by the UDF?
- A
Query the event table for log entries and filter by the failing query ID so the administrator can review the emitted exception details and stack trace for that execution
- B
Query QUERY_HISTORY and review the BYTES_SCANNED and COMPILATION_TIME columns to determine whether the warehouse was under-provisioned when the UDF failed
- C
Use RESULT_SCAN on the failed statement and inspect the returned rows to extract the full Java stack trace from the prior result set
- D
Review LOGIN_HISTORY for the user who ran the ETL job to identify whether a failed authentication event caused the Java exception in the UDF
Show answer and explanation
Correct answer: A
Explanation
For root cause analysis of Snowflake handler code such as Java UDFs, the key diagnostic source is the event table that stores telemetry data, including logs and traces, when logging is configured. In this scenario, the administrator already knows the failure is associated with a specific SQL execution and needs to connect that execution to the exception details. The best first step is therefore to use the query ID from the failed statement and query the event table for the corresponding records. This is the most direct way to retrieve the emitted stack trace and related log context.
A practical workflow is often: identify the failing query in QUERY_HISTORY, capture its QUERY_ID, then query the event table for log/trace records associated with that execution. QUERY_HISTORY helps locate the failed statement, but the event table provides the deep diagnostic payload required for root cause analysis. This aligns with Snowflake guidance on event tables and logging/tracing for handler code, where telemetry data is centralized for troubleshooting rather than returned in normal SQL result sets.
- A. Correct.
Correct. When unhandled exception logging is enabled for supported handler code, Snowflake writes log and trace data to an event table. Filtering the event table by the failing query ID is the most direct way to correlate the SQL statement execution with the associated exception details and stack trace, which is exactly what is needed for root cause analysis.
- B. Incorrect.
Incorrect. QUERY_HISTORY is useful for execution metadata such as duration, warehouse usage, errors, and query identifiers, but it does not contain the Java stack trace emitted by handler logging. While resource pressure may be relevant in some investigations, it does not directly provide the logged exception details needed to diagnose the UDF failure.
- C. Incorrect.
Incorrect. RESULT_SCAN can access the result set of a previous query, but it does not expose handler log or trace records stored in an event table. A failed statement may not even produce a usable result set containing diagnostic stack trace content. This reflects a common misconception that all runtime diagnostics are returned as query results.
- D. Incorrect.
Incorrect. LOGIN_HISTORY is relevant for authentication and sign-in troubleshooting, not for application-level Java exceptions thrown inside a UDF during query execution. A failed login event would prevent the query from running, whereas this scenario involves intermittent failures during active execution.