DAA-C01 Question 158
Single answerUse cartesian joins, sub-queries, CTEs, and union queriesA retail analytics team in Snowflake needs a daily data quality report showing every combination of active store and supported sales channel, even when no sales occurred. The team also needs to append a second result set containing online-only promotional transactions from a separate table. They want one query that is easy to maintain and avoids double-counting. Which approach best meets these requirements?
- A
Create CTEs for active stores and supported channels, CROSS JOIN them to generate the full store-channel matrix, LEFT JOIN aggregated daily sales to preserve zero-sale combinations, then UNION ALL a second SELECT for online promotional transactions after aligning the column list.
- B
Use an INNER JOIN between stores and channels on STORE_ID, then UNION the result with promotional transactions so duplicate rows are removed automatically.
- C
Use a correlated subquery in the SELECT list to return daily sales for each store, and replace the store-channel matrix with a NATURAL JOIN so Snowflake can infer the correct join keys.
- D
Generate the full matrix with a CROSS JOIN, join raw sales transactions directly without pre-aggregating, and use UNION instead of UNION ALL for the promotional transactions to improve performance and prevent all duplicate counting issues.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to deliberately model the required result grain. In Snowflake, a CROSS JOIN is the correct construct when the requirement is to return every combination of two sets, such as active stores and supported channels. CTEs are commonly used to break complex logic into readable steps, especially when defining filtered dimensions and pre-aggregated fact data. To avoid double-counting, sales should be aggregated to the same grain as the reporting matrix before joining. Then, a LEFT JOIN preserves combinations with no matching sales, which is essential for data quality or completeness reporting. For appending a separate result set, UNION ALL is preferred when you need to retain all rows and avoid the cost and semantic impact of duplicate elimination. Snowflake documentation on joins, CTEs, subqueries, and set operators supports these practices: use explicit join types for clarity, use CTEs for modular query design, and choose UNION versus UNION ALL based on whether duplicate removal is actually required.
- A. Correct.
Correct. This approach matches the business requirement to show every active store combined with every supported channel, including combinations with no sales. A CROSS JOIN is the appropriate way to intentionally generate a Cartesian product when all combinations are required. Using CTEs improves readability and maintainability by isolating active stores, channels, and aggregated daily sales logic. A LEFT JOIN from the generated matrix to aggregated sales ensures zero-sale combinations are retained. Using UNION ALL to append online promotional transactions is appropriate when the second result set should be preserved as provided and you do not want Snowflake to perform duplicate elimination, which can change results and add unnecessary processing.
- B. Incorrect.
Incorrect. An INNER JOIN between stores and channels on STORE_ID does not make sense for generating all store-channel combinations unless the channel table actually contains STORE_ID and represents pre-existing mappings. The scenario requires every active store paired with every supported channel, which is a Cartesian-style requirement, not a matching-key join. Also, UNION removes duplicates across the combined result sets, which may incorrectly eliminate valid rows and is not a substitute for proper aggregation or deduplication logic.
- C. Incorrect.
Incorrect. A correlated subquery can sometimes return per-row aggregates, but it is not the best fit here because the requirement is to build a full store-channel matrix and then attach sales data. NATURAL JOIN is especially risky because it joins on all columns with matching names, which can produce unintended results and is not considered a best-practice choice for maintainable analytical SQL. The misconception is that Snowflake can safely infer the intended join logic from column names; in production reporting, explicit joins are preferred.
- D. Incorrect.
Incorrect. While the CROSS JOIN portion is directionally correct for generating the matrix, joining raw sales transactions directly without pre-aggregation can create duplicate rows and double-counting when multiple transactions exist for the same store, channel, and day. The statement about UNION improving performance is also misleading: UNION requires duplicate elimination, which generally adds overhead compared with UNION ALL. UNION also does not solve underlying grain mismatches or aggregation errors.