COF-C03 Question 307
Single answerApplying SQL for query optimizationA retail analytics team runs the following query many times per day to support a dashboard. The SALES table contains several years of data and is clustered naturally by load date. Users usually look at the last 7 days of orders for a small set of regions.
SELECT * FROM SALES WHERE TO_DATE(ORDER_TS) >= CURRENT_DATE - 7 AND UPPER(REGION) IN ('WEST','EAST');
The query is slower than expected and scans far more micro-partitions than the team anticipated. Which change is the BEST way to optimize this query using SQL best practices while preserving the same business logic?
- A
Rewrite the filter to avoid applying functions to the columns, for example: WHERE ORDER_TS >= DATEADD(day, -7, CURRENT_DATE) AND REGION IN ('WEST','EAST')
- B
Replace SELECT * with SELECT DISTINCT * so Snowflake can eliminate duplicate rows before scanning micro-partitions
- C
Add ORDER BY ORDER_TS to help Snowflake prune older micro-partitions during query execution
- D
Cast ORDER_TS to VARCHAR and compare it to a string literal for better predicate evaluation
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, query optimization often starts with writing predicates that allow efficient micro-partition pruning. Snowflake stores metadata about values in micro-partitions, and filters that compare base columns directly are more likely to benefit from that metadata. Wrapping columns in expressions such as TO_DATE(column) or UPPER(column) can make pruning less effective and increases per-row processing. Best practice is to avoid unnecessary functions on filter and join columns when possible, and to select only the needed columns rather than using broad patterns that add work. In this scenario, the best SQL-level optimization is to rewrite the WHERE clause to compare ORDER_TS and REGION directly. This aligns with Snowflake documentation and best practices around selective filters, partition pruning, and efficient SQL design.
- A. Correct.
Correct. Applying functions to filtered columns, such as TO_DATE(ORDER_TS) and UPPER(REGION), can reduce Snowflake's ability to take full advantage of micro-partition metadata for pruning. Rewriting the predicate so the base columns are compared directly is a common SQL optimization technique. If REGION values are already stored consistently in uppercase, then REGION IN ('WEST','EAST') avoids the extra function call. Likewise, comparing ORDER_TS directly to a date boundary such as DATEADD(day, -7, CURRENT_DATE) is more pruning-friendly than wrapping the column in TO_DATE().
- B. Incorrect.
Incorrect. SELECT DISTINCT * generally adds extra work because Snowflake must perform deduplication. It does not help micro-partition pruning, which depends on filtering predicates and metadata. This option reflects a common misconception that removing duplicates early reduces table scanning, but DISTINCT is evaluated after reading the necessary data.
- C. Incorrect.
Incorrect. ORDER BY affects the final result ordering, not how Snowflake decides which micro-partitions to scan for a filter predicate. Adding ORDER BY can increase cost and latency due to sorting. It does not improve partition pruning for the WHERE clause.
- D. Incorrect.
Incorrect. Casting ORDER_TS to VARCHAR usually makes predicate evaluation less efficient and can further reduce pruning opportunities because the comparison is no longer based on the native timestamp/date semantics. String comparisons on timestamp values are not a best practice for query optimization.