ADA-C01 Question 380
Single answerUse log trace data to optimize performanceA data engineering team reports that a nightly ETL task has become increasingly slow over the past week. The task executes a complex SQL statement from a Python application using Snowpark. As the Snowflake administrator, you need to use the available log and trace information to identify where time is being spent and determine whether the slowdown is due to SQL execution inside Snowflake or time spent in the client/application layer before and after the SQL runs. Which approach should you take?
- A
Query the event table for trace spans related to the ETL execution, correlate parent and child spans to measure time spent in application code versus the SQL call, and then use the query ID from the SQL-related span to inspect Query Profile and query history for the database execution details.
- B
Use ACCESS_HISTORY to compare object-level reads over time, because it contains end-to-end client timing and is the primary source for tracing application latency outside Snowflake.
- C
Review only WAREHOUSE_LOAD_HISTORY to determine whether the Python client introduced latency, because warehouse load metrics include both client-side and server-side execution phases.
- D
Enable Search Optimization Service on all tables referenced by the ETL query, because trace data is not suitable for separating client latency from server-side execution time.
Show answer and explanation
Correct answer: A
Explanation
The best practice is to use observability data for diagnosis before applying tuning changes. In Snowflake, trace data written to an event table can be used to analyze spans and timing across an instrumented execution flow. This is especially useful when a workload originates from an application, because total elapsed time may include client processing, network delays, and Snowflake SQL execution. By correlating SQL-related spans to the query ID, an administrator can pivot into QUERY_HISTORY and Query Profile for deeper database-side analysis. This layered approach aligns with Snowflake observability guidance: use logs, metrics, and traces to understand execution paths, then use query history and query profile to optimize the actual SQL or warehouse configuration as needed. ACCESS_HISTORY and WAREHOUSE_LOAD_HISTORY are valuable in their own domains, but they do not replace trace data for end-to-end latency attribution.
- A. Correct.
Correct. Snowflake trace data stored in an event table is designed to capture spans that represent units of work across an application flow, including parent-child relationships. In an instrumented application or procedure, these spans can show whether time is spent in the application layer, network calls, or the SQL execution itself. Once the SQL-related span is identified, the associated query ID can be correlated with QUERY_HISTORY and Query Profile to analyze compilation time, execution time, partitions scanned, spilling, joins, and other in-engine performance characteristics. This is the most direct way to use log/trace data to distinguish where latency is occurring and then optimize the right layer.
- B. Incorrect.
Incorrect. ACCESS_HISTORY is useful for governance, auditing, and understanding which objects were accessed by a query, but it is not the primary tool for end-to-end tracing or client-side latency analysis. It does not provide span-based timing that separates application work from SQL execution time. Someone might choose this because it contains detailed object access information, but that does not answer where elapsed time is spent in the request lifecycle.
- C. Incorrect.
Incorrect. WAREHOUSE_LOAD_HISTORY helps identify warehouse utilization, queueing, and load patterns, which can explain some server-side performance issues such as contention or under-sizing. However, it does not capture client-side timing or application-layer latency, and it cannot decompose a request into trace spans. It is helpful supplementary context, but not the right primary method for this scenario.
- D. Incorrect.
Incorrect. Search Optimization Service can improve selective query patterns in some cases, but enabling it broadly is not an investigative approach and may be unnecessary or costly. More importantly, the statement that trace data is not suitable for separating client and server timing is wrong. Trace spans exist specifically to provide observability across execution steps, making them appropriate for diagnosing where time is actually being spent before deciding on an optimization.