DAA-C01 Question 27
Single answerPerform table joins and set operations (e.g., UNION, UNION ALL, INTERSECT, and MINUS)A retail analytics team is consolidating customer records from two regional source tables, EAST_CUSTOMERS and WEST_CUSTOMERS. Both tables have the same structure: CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT. Some customers appear in both tables because they shop in multiple regions. The team needs a result set that includes every row from both tables, including duplicate rows when the same customer record exists in both sources, so they can later analyze how many times records were delivered by upstream systems. Which SQL statement best meets this requirement in Snowflake?
- A
SELECT CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT FROM EAST_CUSTOMERS UNION SELECT CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT FROM WEST_CUSTOMERS;
- B
SELECT CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT FROM EAST_CUSTOMERS UNION ALL SELECT CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT FROM WEST_CUSTOMERS;
- C
SELECT CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT FROM EAST_CUSTOMERS INTERSECT SELECT CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT FROM WEST_CUSTOMERS;
- D
SELECT CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT FROM EAST_CUSTOMERS MINUS SELECT CUSTOMER_ID, EMAIL, FIRST_PURCHASE_DT FROM WEST_CUSTOMERS;
Show answer and explanation
Correct answer: B
Explanation
The correct answer is UNION ALL because the scenario requires full row preservation, including duplicates across the two source tables. In Snowflake, UNION performs a distinct operation on the combined result set, while UNION ALL keeps all rows exactly as returned by each SELECT statement. INTERSECT returns only common rows, and MINUS returns rows in the first result set that are absent from the second. For practical analytics and audit workflows, choosing between UNION and UNION ALL is important because duplicate elimination can materially affect counts, downstream quality checks, and lineage analysis. Snowflake documentation on set operators describes UNION [DISTINCT], UNION ALL, INTERSECT, and MINUS/EXCEPT semantics, including that input queries must return the same number of columns with compatible data types.
- A. Incorrect.
Incorrect. UNION combines the results of both queries but removes duplicate rows from the final result set. In this scenario, the team explicitly wants to preserve duplicate rows so they can measure repeated upstream deliveries. A common misconception is that UNION simply appends data; in reality, it performs duplicate elimination, which changes row counts and can mask duplicate records.
- B. Correct.
Correct. UNION ALL returns all rows from both queries without removing duplicates. Because the requirement is to keep every row from EAST_CUSTOMERS and WEST_CUSTOMERS, including identical rows appearing in both tables, UNION ALL is the appropriate set operator. This is commonly used in data ingestion, reconciliation, and audit scenarios where row preservation matters.
- C. Incorrect.
Incorrect. INTERSECT returns only rows that appear in both result sets. That would exclude rows unique to either region, which does not satisfy the requirement to include every row from both tables. Someone might choose this option if they focus on the phrase 'customers appear in both tables,' but INTERSECT is for finding overlap only, not full consolidation.
- D. Incorrect.
Incorrect. MINUS returns rows from the first query that do not exist in the second query. It is useful for identifying differences between datasets, such as customers present in EAST_CUSTOMERS but not in WEST_CUSTOMERS. It does not append both tables together and therefore does not meet the business requirement.