Photo Hunt in BigQuery tables

Recently I have to run the data models in Google BigQuery and verify if there are any differences between before and after the changes. I came across this stackoverflow forum¹ and found it’s pretty useful for my case so I would like to share it here as well.

---

Quick answer

Let’s say we have two tables in Google BigQuery and want to compare and find any differences in all columns. We can use this query.

SELECT "exist in A" as result, *
FROM (
  SELECT * FROM table_a
  EXCEPT DISTINCT
  SELECT * FROM table_b
)
UNION ALL
SELECT "exist in B" as result, *
FROM (
  SELECT * FROM table_b
  EXCEPT DISTINCT
  SELECT * FROM table_a
)

Before we go to talk how the query returns data row differences, we shall understand set operators first.

---

Set operators

Set operators² are syntax to perform interactions between two or more tables. Other than JOIN (which I have published in How to befriend your queries | 2. When do we JOIN?)

There are 4 useful operators we can consider to use when it’s needed.

UNION ALL

This includes everything from both left and right tables and preserve duplicate records.

There are data from both tables, including “Bria” and “Carleton” appear twice in the diagram below.

UNION DISTINCT

This includes everything from both tables but keeps only unique records.

“Bria” and “Carleton” now appear once along with the rest from both tables.

INTERSECT DISTINCT

This finds duplicate records from both tables, and concludes only unique ones from them.

Only “Bria” and “Carleton” appear here.

EXCEPT DISTINCT

This includes everything from both tables, excluding duplicate records.

Only data in left-side table except “Bria” and “Carleton” that also appear in another table.

---

Explanation

Now we go back to the first query above, it can be described as below:

• a EXCEPT DISTINCT b will return unique records in a that does not exist in b.
  And vice versa, b EXCEPT DISTINCT a will return records in b that does not exist in a.
• On top of each subquery EXECEPT DISTINCT, we SELECT "exist in A" and "exist in B" to identify which table the different records come from.
• Finally, we UNION ALL to combine two results together to see different records in both tables.
• UNION DISTINCT returns the same result as UNION ALL but it additionally computes unique rows. Therefore, we use UNION ALL which is a bit faster.

---

Limitations

• Both tables must have same schemas. Number of columns and each data types need to be exactly the same.
• array and struct data types are not supported in UNION DISTINCT, INTERSECT DISTINCT, and EXCEPT DISTINCT. We need to unnest an array or SELECT in field level under the struct for examples, SELECT struct.field1, struct.field2, ....
• In other database engines, EXCEPT DISTINCT may not work and we have to use other solutions such as MINUS in Oracle³.

---

My application

I find the set operators are useful for data stitching and data investigation as I did in the past.

My scenario was to find and expect no differences between before and after updating the models, so these are my steps:

1. I ran the model before making changes and stored in table A.
2. I made changes, ran again and stored in table B.
3. I queried using EXCEPT DISTINCT comparing both and stored in a temporary table, says table_compare.
4. I queried on table_compare and sort rows based on keys, timestamps, and the result column to see if it “exist in a” or “exist in b”.

5. As the example below, I made it adjacently for before-and-after comparison and scan through them to spot differences in every columns.

    SELECT result, key, timestamp, col_a, col_b, col_c
    FROM table_compare
    ORDER BY key, timestamp, result  
   
    /*
    sample result:
   
    | result     | key | timestamp  | col_a | col_b | col_c |
    | ---------- | --- | ---------- | ----- | ----- | ----- |
    | exist in A | 001 | 2026-01-01 | A     | B     | C     |
    | exist in B | 001 | 2026-01-01 | A     | B     | K     |
    | exist in A | 002 | 2026-02-02 | Q     | W     | E     |
    | exist in B | 002 | 2026-02-03 | Q     | W     | R     |
    */
   

6. When found issued columns, I ran the queries again, scoping those columns for a couple of times to make sure if they’re actually something.
7. Investigated into the model logics specially on those columns and fixed them.

Now the models were fixed successfully.

---

References

1. sql - efficient way to compare two tables in bigquery - Stack Overflow 

2. Set operators | Query syntax  |  BigQuery  |  Google Cloud Documentation 

3. How to compare two tables to get the different rows with SQL