COF-C03 Question 306
Single answerApplying SQL for query optimizationA data engineering team runs a daily report that joins a 12 TB SALES_FACT table to a 50 MB PRODUCT_DIM table and filters on SALES_DATE for the last 7 days. The query is taking longer than expected even though only a small date range is needed. The team wants to optimize the SQL before considering a larger warehouse. Which change is most likely to improve query performance in Snowflake?
- A
Rewrite the query so the SALES_DATE filter is applied directly to SALES_FACT before or during the join, rather than wrapping the date column in a function in the WHERE clause
- B
Replace the join with a CROSS JOIN and filter the rows afterward so Snowflake can better parallelize the query
- C
Cast PRODUCT_ID to VARCHAR in both tables during the join to ensure Snowflake compares the values consistently
- D
Move the SALES_DATE filter from the WHERE clause into a QUALIFY clause so it is evaluated later in query processing
Show answer and explanation
Correct answer: A
Explanation
The best optimization is to write the predicate so Snowflake can prune micro-partitions on the large SALES_FACT table. Snowflake stores metadata about micro-partitions, including min/max values for columns, and uses that metadata to avoid scanning irrelevant data when predicates are written effectively. A common anti-pattern is wrapping a filter column in a function, which can reduce pruning effectiveness. For a large fact table filtered to the last 7 days, a direct predicate on SALES_DATE is the most impactful SQL-level optimization. This aligns with Snowflake best practices for query tuning: reduce data scanned, filter early on selective columns, and avoid unnecessary transformations in predicates and join conditions. Relevant Snowflake documentation includes guidance on micro-partitions, partition pruning, and query profiling/query performance optimization.
- A. Correct.
Correct. In Snowflake, query performance often depends on effective micro-partition pruning. If the SQL applies a function to the filtered column, such as DATE_TRUNC(SALES_DATE) or TO_DATE(SALES_DATE), Snowflake may be less able to prune micro-partitions efficiently. Filtering directly on the base column with a sargable predicate such as SALES_DATE >= CURRENT_DATE - 7 allows Snowflake to eliminate unnecessary partitions earlier and scan less data. Applying the selective filter on the large fact table as early as possible is a common SQL optimization best practice.
- B. Incorrect.
Incorrect. A CROSS JOIN creates a Cartesian product before filtering, which usually increases the amount of data processed and makes performance worse, not better. This is the opposite of an optimization for a large fact table joined to a small dimension table.
- C. Incorrect.
Incorrect. Casting join keys during the join can add unnecessary computation and may reduce efficiency. If the columns are already compatible types, introducing casts does not improve performance. It may also prevent optimal execution compared with joining on native, properly typed columns.
- D. Incorrect.
Incorrect. QUALIFY is used to filter the results of window functions after those functions are computed. It is not the appropriate clause for a normal date predicate on a base table column. Moving a standard filter from WHERE to QUALIFY would not improve pruning and could make the query semantically invalid unless window functions are involved.