COF-C03 Question 172
Single answerLogging and tracingA data engineering team runs a business-critical transformation every night by calling a Python stored procedure in Snowflake. The procedure intermittently fails, but the SQL statements around the call complete successfully, making it difficult to determine where the failure occurred. The team wants a Snowflake-native way to capture detailed execution information from inside the stored procedure so they can troubleshoot the issue after the run completes. Which approach should they use?
- A
Add logging and tracing instrumentation inside the Python stored procedure and review the emitted telemetry data in Snowflake after execution
- B
Query QUERY_HISTORY only, because it captures line-by-line execution details from inside stored procedures
- C
Enable Time Travel on the database so failed procedure execution steps can be replayed and inspected
- D
Use RESULT_SCAN on the CALL statement to retrieve the internal exception stack trace for the stored procedure
Show answer and explanation
Correct answer: A
Explanation
The best answer is to add logging and tracing instrumentation inside the Python stored procedure. For Snowflake-native observability of handler code, Snowflake provides telemetry capabilities such as logging and tracing for supported languages and objects, including stored procedures. This is the appropriate solution when failures occur inside the procedure body and are not fully explained by surrounding SQL statement history. By contrast, QUERY_HISTORY helps with SQL-level monitoring, not detailed internal handler execution. Time Travel is for historical data access and recovery, not debugging runtime logic. RESULT_SCAN only works with prior query results and does not expose internal telemetry. This aligns with Snowflake guidance on logging, tracing, and metrics for handlers and on using query history for SQL statement monitoring rather than application-level tracing.
- A. Correct.
Correct. Snowflake supports logging, tracing, and metrics for handler code such as Python stored procedures. Instrumenting the procedure allows the team to emit telemetry from inside the handler, which is the right Snowflake-native approach when SQL history alone does not provide enough visibility into internal procedure logic. This is especially useful for intermittent failures because the telemetry can be reviewed after execution for troubleshooting.
- B. Incorrect.
Incorrect. QUERY_HISTORY is useful for reviewing executed SQL statements, timings, and status, but it does not provide line-by-line insight into the internal logic of Python handler code inside a stored procedure. A common misconception is that query history acts like an application debugger; it does not expose detailed in-procedure execution traces.
- C. Incorrect.
Incorrect. Time Travel is designed to access historical data in tables, schemas, and databases, not to replay procedural execution or inspect internal runtime behavior of stored procedures. Someone might choose this option because it sounds like a historical troubleshooting feature, but it applies to data state recovery rather than execution diagnostics.
- D. Incorrect.
Incorrect. RESULT_SCAN can return the result set of a previous query if one exists, but it is not a mechanism for retrieving internal logging or a full exception trace from within stored procedure handler code. This option reflects a misunderstanding of RESULT_SCAN as a diagnostic tool rather than a result-set reuse feature.