Question 630
A Snowflake acceptance test follows this incident: A performance report shows high compilation time but almost zero actual execution time for repeated short statements. What evidence most directly proves the correction?
Explanation
Correct Answer
An optimized workload reduces avoidable compilation overhead without changing query results. This validates the relevant action: Inspect query profile compilation versus execution and limit excessive dynamic SQL statement variation where feasible. The underlying Snowflake behavior is: Statement compilation and data processing are different components of total query latency.
Incorrect Answers
The investigation links performance change to a documented scan or join behavior difference. This evidence tests a DIFFERENT situation: A query suddenly slows after a large table changed, yet the team looks only at warehouse size changes. The correction required in this case is: Inspect query profile compilation versus execution and limit excessive dynamic SQL statement variation where feasible.
The pipeline produces identical required outputs with fewer repeated full scans. This evidence tests a DIFFERENT situation: A nightly transformation consumes excessive time because the same large staging table is scanned multiple times in separate jobs. The correction required in this case is: Inspect query profile compilation versus execution and limit excessive dynamic SQL statement variation where feasible.
Alerting detects a lagging consumer early and a replay drill recovers from an approved source when needed. This evidence tests a DIFFERENT situation: An ingest pipeline uses a stream and task but falls behind for days beyond the underlying change-tracking retention period. The correction required in this case is: Inspect query profile compilation versus execution and limit excessive dynamic SQL statement variation where feasible.