ADA-C01 Question 307
Single answerCompare compile versus runtime optimizationsA Snowflake administrator is troubleshooting a dashboard query that joins a very large SALES_FACT table to smaller DIM tables and filters on a date range and region. The query text is unchanged between runs, but execution time varies significantly depending on the filter values selected by users. Query Profile shows that some runs scan far fewer micro-partitions than others. The administrator wants to explain this behavior to the BI team and identify which optimizer behavior is occurring before execution versus during execution.
Which statement BEST compares compile-time optimization with runtime optimization in this scenario?
- A
Compile-time optimization determines an execution plan using available metadata and statistics before the query runs, while runtime optimization can further reduce work during execution, such as pruning micro-partitions based on actual filter predicates encountered.
- B
Compile-time optimization occurs only after the warehouse starts processing data, while runtime optimization happens before a virtual warehouse is assigned to the query.
- C
Compile-time optimization is responsible for resizing the virtual warehouse during the query, while runtime optimization is limited to result cache reuse.
- D
Compile-time optimization and runtime optimization are equivalent in Snowflake; both terms refer only to automatic clustering decisions made after query completion.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1. In Snowflake, compile-time optimization refers to the planning stage before the query executes. At this stage, the optimizer evaluates SQL structure, join order possibilities, access paths, and metadata/statistics to create an execution plan. Runtime optimization refers to optimizations that occur as the query is being executed, where actual predicate values and storage metadata can further reduce the amount of work performed. In practice, this is often visible as differences in micro-partition pruning between runs of the same parameterized query.
This distinction is important for administrators analyzing Query Profile and troubleshooting inconsistent performance. If query text is unchanged but selectivity varies by user input, compile-time planning may be similar while runtime behavior differs because different predicates eliminate different sets of micro-partitions. Snowflake documentation on query processing, micro-partitions, and partition pruning supports this model: Snowflake stores metadata about micro-partitions and uses it to avoid scanning irrelevant data whenever possible. Best practice is to explain to stakeholders that identical SQL text does not guarantee identical runtime cost when predicate selectivity differs, especially on large fact tables.
- A. Correct.
Correct. In Snowflake, the optimizer builds a plan before execution using object metadata, statistics, and query structure. During execution, Snowflake can still apply runtime behaviors that reduce work, including pruning based on filter values and storage metadata, which explains why the same query shape can perform differently for different parameter values. This aligns with how Snowflake separates planning from execution while still using metadata-rich storage to avoid unnecessary scans.
- B. Incorrect.
Incorrect. This reverses the concepts. Compile-time optimization happens before execution begins, not after the warehouse starts processing data. Runtime optimization happens during query execution, after planning. A warehouse must be available for execution, but that does not redefine compile-time versus runtime behavior.
- C. Incorrect.
Incorrect. Snowflake does not treat compile-time optimization as warehouse resizing logic. Warehouse sizing, scaling, and multi-cluster behavior are compute-management features, not the definition of compile-time optimization. Runtime optimization is also not limited to result cache reuse; result cache reuse can bypass execution entirely when applicable, but it is not the sole runtime behavior.
- D. Incorrect.
Incorrect. Compile-time and runtime optimization are not equivalent terms, and neither is defined as automatic clustering. Automatic clustering is a table maintenance feature that improves clustering depth over time; it is separate from how an individual query is optimized before and during execution.