Why you should use UNION DISTINCT sparingly

Let’s help BigQuery do less unneeded work!

If you’re UNIONING two sources known to have distinct values (and they don’t have duplicates), go for UNION ALL instead of UNION DISTINCT (UNION for some other SQL dialects) to avoid redundant de-duplication.

In the example below, I’ve unioned two Google Trends tables — one that is only for US terms and another one for the rest of the world. Since one table only contains US and the other everything except the US, we know the union of the two tables to be distinct from the start, thus not needing the UNION DISTINCT.

There’s no difference indeed for on-demand pricing (same amount of data scanned), but quite a difference for capacity pricing users ( 1/2 of slot usage).

So use UNION DISTINCT (and any other DISTINCT) sparingly and when you actually need it.

Originally published at https://datawise.dev on April 2, 2024.