ADA-C01 Question 313
Single answerUse effective joining conditionsA Snowflake administrator is investigating a reporting query that joins a 12 TB SALES_FACT table to a 50 MB REGION_DIM table. The query is used frequently by analysts and has become slower after the fact table grew significantly. The current SQL is:
SELECT s.order_id, s.amount, r.region_name FROM sales_fact s JOIN region_dim r ON UPPER(TRIM(s.region_code)) = UPPER(TRIM(r.region_code)) WHERE s.order_date >= '2025-01-01';
The administrator wants to improve join efficiency without changing the business result. Which action is the BEST recommendation?
- A
Store region codes in a normalized format in both tables and join directly on s.region_code = r.region_code, applying any cleanup before data is loaded
- B
Replace the JOIN with a CROSS JOIN and move the matching logic to the WHERE clause so Snowflake can optimize the filter later
- C
Cast both region_code columns to VARIANT during the join to make the comparison more flexible for different formats
- D
Add ORDER BY region_code to both tables before the JOIN so matching rows are colocated during execution
Show answer and explanation
Correct answer: A
Explanation
The best recommendation is to use a direct join on normalized columns rather than applying functions inside the join predicate. In Snowflake, effective join conditions are typically straightforward comparisons between columns with compatible data types and pre-standardized values. When expressions such as TRIM, UPPER, casts, or arithmetic are embedded in the join condition, they add computation and can reduce execution efficiency on large joins. In this scenario, the dimension table is small, but the fact table is very large, so repeatedly transforming join keys during query execution is costly at scale. Snowflake best practices generally favor cleansing and conforming keys during ETL/ELT so joins are performed on raw column equality when possible. This aligns with SQL optimization guidance and practical warehouse design patterns for dimension/fact joins.
- A. Correct.
Correct. Applying functions such as UPPER and TRIM directly to both sides of a join condition can reduce join efficiency because the engine must evaluate expressions for each row before comparing values. A better practice is to normalize the data during ingestion or transformation so the join uses simple equality on like-typed, pre-cleaned columns. This preserves the business logic while making the join predicate more efficient and easier for Snowflake to execute at scale.
- B. Incorrect.
Incorrect. Although an INNER JOIN with its predicate in the WHERE clause can be logically equivalent to some ANSI join forms, replacing it with a CROSS JOIN is not a best practice for performance tuning. A CROSS JOIN creates a Cartesian product conceptually and relies on later filtering, which is less clear and can lead to severe performance issues or mistakes if the filter changes or is omitted.
- C. Incorrect.
Incorrect. Casting both columns to VARIANT does not improve join efficiency and usually makes the predicate less efficient and less precise. Flexible typing is not the goal here; consistent typing and normalized values are. This option reflects a common misconception that semi-structured types help with heterogeneous string comparisons in relational joins.
- D. Incorrect.
Incorrect. ORDER BY is applied to query result sets, not stored table layout in Snowflake. It does not physically colocate rows in permanent storage for future joins. If physical organization is needed, clustering strategies may help some pruning scenarios, but sorting both tables in the query does not make the join itself more efficient in the way described.