DAA-C01 Question 28
Single answerPerform table joins and set operations (e.g., UNION, UNION ALL, INTERSECT, and MINUS)A retail analytics team receives two daily feeds of customer IDs eligible for a loyalty campaign: one from in-store purchases (STORE_ELIGIBLE) and one from e-commerce purchases (WEB_ELIGIBLE). Each table contains a single column, CUSTOMER_ID, and both feeds can contain duplicate rows because of upstream processing issues. The team needs a query that returns each eligible customer exactly once if the customer appears in either source. Which SQL statement should the analyst use in Snowflake?
- A
SELECT CUSTOMER_ID FROM STORE_ELIGIBLE UNION SELECT CUSTOMER_ID FROM WEB_ELIGIBLE
- B
SELECT CUSTOMER_ID FROM STORE_ELIGIBLE UNION ALL SELECT CUSTOMER_ID FROM WEB_ELIGIBLE
- C
SELECT CUSTOMER_ID FROM STORE_ELIGIBLE INTERSECT SELECT CUSTOMER_ID FROM WEB_ELIGIBLE
- D
SELECT CUSTOMER_ID FROM STORE_ELIGIBLE MINUS SELECT CUSTOMER_ID FROM WEB_ELIGIBLE
Show answer and explanation
Correct answer: A
Explanation
This question tests practical understanding of Snowflake set operators. When the requirement is to return rows appearing in either input and to eliminate duplicates, UNION is the correct choice. UNION ALL preserves duplicates, making it suitable only when duplicate rows are intentionally retained or when deduplication is handled separately for performance or business reasons. INTERSECT returns only common rows, while MINUS (synonym of EXCEPT in Snowflake) returns set difference. Snowflake follows standard set operation semantics: the participating queries must return the same number of columns with compatible data types, and the set operator determines whether duplicates are removed. For this scenario, best practice is to choose the operator that directly matches the business requirement rather than combining UNION ALL with additional DISTINCT logic unless there is a specific optimization reason. See Snowflake SQL documentation on set operators such as UNION, UNION ALL, INTERSECT, and MINUS/EXCEPT.
- A. Correct.
Correct. UNION combines the results from both queries and removes duplicate rows from the final result set. In this scenario, the business requirement is to include customers found in either source and return each customer only once, even if duplicates exist within one source or across both sources. UNION matches that requirement directly.
- B. Incorrect.
Incorrect. UNION ALL combines all rows from both queries without removing duplicates. Because the source feeds can already contain duplicate CUSTOMER_ID values, and the same customer can appear in both tables, UNION ALL would return duplicate rows and violate the requirement to return each eligible customer exactly once. This is a common mistake when users focus only on combining datasets and overlook deduplication behavior.
- C. Incorrect.
Incorrect. INTERSECT returns only rows present in both result sets. That would produce the overlap between store and web eligible customers, not all customers eligible from either source. Someone might choose this option if they confuse 'eligible from both channels' with 'eligible from either channel.'
- D. Incorrect.
Incorrect. MINUS returns rows from the first query that do not appear in the second query. In Snowflake, MINUS is a supported synonym for EXCEPT, but it is used for set difference, not for combining all eligible customers. This would exclude any customer also found in WEB_ELIGIBLE and would miss customers found only in the web feed.