Optimize BigQuery: Use UNION ALL Over UNION DISTINCT for Distinct Sources

In BigQuery, UNION ALL combines multiple query results into a single set, including all duplicate rows. UNION DISTINCT (or simply UNION in some other #sql dialects) also combines query results, but it eliminates duplicate rows, performing a de-duplication process. When you know the sources you're unioning have distinct values and don't have duplicates, it's more efficient to use UNION ALL to avoid the unnecessary de-duplication step that UNION DISTINCT performs.

To optimize BigQuery queries when unioning two sources that are known to have distinct values, you should use UNION ALL instead of UNION DISTINCT. This is because UNION ALL does not perform the redundant de-duplication step that UNION DISTINCT does, making the query more efficient, especially for capacity pricing users where it can save up to 1/2 of slot usage.

You should use UNION DISTINCT in BigQuery when you need to combine results from multiple queries and also need to eliminate any duplicate rows that may exist across the combined dataset. It's important to use UNION DISTINCT sparingly and only when necessary, as it involves additional work to de-duplicate the results.

Yes, using UNION ALL over UNION DISTINCT can affect BigQuery pricing. While there's no difference in on-demand pricing (since the same amount of data is scanned), there is a significant difference for capacity pricing users. Using UNION ALL can result in using only half the slot usage compared to UNION DISTINCT, which can lead to cost savings.

It is important to avoid unnecessary use of UNION DISTINCT in BigQuery because it can lead to inefficient use of resources. When you know that the datasets being unioned are already distinct, using UNION DISTINCT will cause BigQuery to perform an unnecessary de-duplication step, which can waste processing time and resources, particularly impacting capacity pricing users.