ADA-C01 Question 306
Single answerCompare compile versus runtime optimizationsA Snowflake administrator is troubleshooting a dashboard query that joins a very large SALES fact table to several dimension tables. The team notices two patterns: (1) the same SQL text usually finishes quickly, but on some runs it takes much longer, and (2) adding a highly selective date filter generally reduces the amount of data scanned. The administrator wants to explain which behaviors are due to compile-time optimization versus runtime optimization. Which statement best distinguishes these two in Snowflake?
- A
Compile-time optimization determines an execution plan before the query runs, while runtime optimization can adapt behavior during execution based on actual conditions such as data distribution or filtering effectiveness.
- B
Compile-time optimization in Snowflake is limited to parsing SQL syntax, while all join ordering and pruning decisions are deferred to runtime optimization.
- C
Runtime optimization only applies when the warehouse is resized during query execution; otherwise Snowflake relies entirely on compile-time decisions.
- D
Compile-time optimization controls warehouse cluster startup and auto-suspend behavior, while runtime optimization controls result cache reuse.
Show answer and explanation
Correct answer: A
Explanation
The key distinction is that compile-time optimization happens before query execution, when Snowflake analyzes the SQL statement and produces an execution plan using metadata and optimizer rules. Examples include plan generation, join planning, and pruning opportunities identified from metadata. Runtime optimization refers to what happens while the query is actually executing, where real execution conditions affect performance, including the amount of data scanned and how operators perform in practice.
In real environments, administrators often see a query with identical SQL text perform differently across runs. That does not mean Snowflake skipped compile-time optimization; instead, runtime factors such as cache state, actual predicate selectivity, and data access patterns can change observed duration. Snowflake documentation and query profiling guidance emphasize examining both planning and execution characteristics when troubleshooting performance. Best practice is to use Query Profile, review bytes scanned, partitions scanned, and operator-level timing to determine whether performance differences stem from planning assumptions or runtime conditions.
- A. Correct.
Correct. In Snowflake, the optimizer produces a plan before execution using available metadata, statistics, and query structure. That is the compile phase. During execution, Snowflake can still realize performance differences based on actual runtime conditions, such as how selective predicates are in practice, how much data is scanned, and how operators behave while processing data. This distinction matches the observed scenario: the date filter affects scanned data and execution behavior, while the plan itself is created before execution.
- B. Incorrect.
Incorrect. This reflects a common misconception that compile time is only syntax checking. In reality, compile-time optimization in Snowflake includes logical and physical planning activities such as join strategy considerations, predicate evaluation planning, and partition pruning decisions where metadata supports them. Runtime behavior may still influence final performance, but compile time is far more than parsing.
- C. Incorrect.
Incorrect. Runtime optimization is not limited to warehouse resizing events. Warehouse size affects available compute resources, but runtime execution behavior exists regardless of resize operations. Snowflake can experience different execution characteristics from one run to another due to factors such as cache state, actual data processed, and execution conditions, not just dynamic resizing.
- D. Incorrect.
Incorrect. Warehouse startup and auto-suspend are warehouse management features, not examples of compile-time query optimization. Result cache reuse is also not best described as runtime optimization in the compile-vs-runtime sense tested here; it is a query reuse/caching feature that can bypass execution altogether when conditions are met. This option mixes unrelated platform behaviors with optimizer concepts.