ADA-C01 Question 312
Single answerUse effective joining conditionsA Snowflake administrator is reviewing a nightly reporting query that joins a 12 TB SALES_FACT table to a 50 MB REGION_DIM table. The query has become slower over time. Query Profile shows a large amount of data scanned from SALES_FACT before the join, and the SQL currently looks like this:
SELECT s.order_id, s.order_date, r.region_name FROM SALES_FACT s JOIN REGION_DIM r ON UPPER(TRIM(s.region_code)) = r.region_code WHERE s.order_date >= '2025-01-01' AND r.is_active = 'Y';
The administrator wants to improve join efficiency without changing the business result. Which change is the BEST choice?
- A
Normalize region_code values during data load and change the join to s.region_code = r.region_code
- B
Replace the INNER JOIN with a CROSS JOIN and move the join predicate to the WHERE clause
- C
Cast both join columns to VARCHAR(16777216) in the query to ensure type consistency
- D
Move the filter r.is_active = 'Y' from the WHERE clause into a QUALIFY clause after the join
Show answer and explanation
Correct answer: A
Explanation
The best answer is to eliminate runtime functions from the join condition, especially on the large table. In Snowflake, effective join conditions are typically simple, direct comparisons between compatible columns, ideally using cleaned and standardized keys. When functions are applied to join columns, the optimizer has less opportunity to use metadata and pruning-related efficiencies effectively, and the engine must compute the expression for many rows before joining. In this scenario, standardizing region_code during loading or transformation and then joining on s.region_code = r.region_code is the most practical improvement. Snowflake documentation and performance best practices emphasize simplifying predicates, reducing unnecessary transformations in critical query paths, and filtering data as early as possible.
- A. Correct.
Correct. Applying functions such as UPPER(TRIM()) to a join column at query time can reduce optimizer effectiveness and increase processing overhead, especially when used on the large fact table side of the join. Standardizing and cleansing join keys during ingestion allows the query to use a simple equality predicate, which is the most effective join condition for performance and optimizer planning. This also preserves the business logic while avoiding repeated per-row transformations on the 12 TB table.
- B. Incorrect.
Incorrect. Although an INNER JOIN with a predicate in the WHERE clause can be logically equivalent in some SQL patterns, rewriting this as a CROSS JOIN is not a best practice for readability or optimization. It does not solve the main performance issue, which is the function being applied to the large-table join key. In addition, CROSS JOIN syntax can encourage accidental Cartesian products if the predicate is modified or omitted.
- C. Incorrect.
Incorrect. Explicitly casting both sides to a very large VARCHAR does not improve join efficiency and may add unnecessary computation. The problem is not primarily datatype width but the runtime transformation of the fact-table key. If datatypes truly differ, aligning them in the table design or ETL process is preferable to casting large join inputs during query execution.
- D. Incorrect.
Incorrect. QUALIFY is used to filter the results of window functions after those functions are evaluated. It is not an appropriate replacement for a standard row filter such as r.is_active = 'Y'. Moving this condition to QUALIFY would either be invalid or change semantics, and it would not address the inefficient join predicate.