Exam SnowPro Core topic 1 question 488 discussion

UNION Without ALL In SQL, it is possible to combine two sets of data with either UNION or UNION ALL constructs. The difference between them is that UNION ALL simply concatenates inputs, while UNION does the same, but also performs duplicate elimination. A common mistake is to use UNION when the UNION ALL semantics are sufficient. These queries show in Query Profile as a UnionAll operator with an extra Aggregate operator on top (which performs duplicate elimination).

C. UNION without ALL

Option C. In a Query Profile, a UnionAll operator with an extra Aggregate operator on top typically signifies a UNION without ALL in the query. In SQL, a UNION operation combines the result sets of two or more SELECT statements. When you use UNION without the ALL keyword, it automatically removes duplicate rows from the result set. This deduplication process requires an aggregation step to identify and eliminate duplicates, which is why the Aggregate operator appears on top of the UnionAll operator in the query profile.

The correct answer is C. Common Query Problems Identified by Query Profile: 1)Joining tables without providing a join condition (Solution: adding join condition) 2)UNION without ALL (elimination duplicates too Large to fit in memory) (solution: Using a larger warehouse or Processing data in smaller batches). 3)Inefficient Pruning : pruning is only effective if the data is stored in a way that is correlated with the query's filter attributes (solution: cluster key, Materialized view) https://docs.snowflake.com/en/user-guide/ui-query-profile#common-query-problems-identified-by-query-profile

correct

https://docs.snowflake.com/en/user-guide/ui-query-profile