Removing duplicates from Google BigQuery tables

The task of removing duplicates from a BigQuery table can be solved in different ways. Let’s look at a few of them.

Using ROW_NUMBER

One option involves using the window function ROW_NUMBER to remove duplicates.

The ROW_NUMBER function assigns each row of the table a unique number, which can be used to select only the unique records. Here is what the code for removing duplicates using ROW_NUMBER looks like:

WITH cte AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY column1, column2, column3 ORDER BY column4) row_num
  FROM
    `mydataset.mytable`
)
DELETE FROM cte WHERE row_num > 1;

This code uses Common Table Expressions (CTE) to first assign each row of the table a unique number via the ROW_NUMBER function. Then all rows whose row_num value is greater than 1 are deleted - that is, all the duplicates.

In this case the original table is overwritten. But if an error has crept into the query, you risk losing data. That is why I recommend writing the script’s result into a new table rather than deleting from the original one. After verifying the new table, you can delete the data in the old table and move the deduplicated data over from the new one.

A query with the DISTINCT operator

Another option is to use the DISTINCT operator, which selects only the unique records from the table. This is simpler than using the ROW_NUMBER window function, but it may be less efficient for large tables.

In addition, this option will not work if your table is split into partitions.

An example of removing duplicates using DISTINCT looks as follows:

CREATE OR REPLACE TABLE `mydataset.mytable_unique` AS
SELECT DISTINCT *
FROM `mydataset.mytable`;

This code creates a new table mytable_unique that contains only the unique records from the original table mytable.

With the ARRAY_AGG function

Yet another way to remove duplicates from a BigQuery table is to use the aggregation function ARRAY_AGG.

Suppose we have a table mydataset.mytable and we need to deduplicate it by the columns column1 and column2. We run the following query:

SELECT a.* FROM (
  SELECT ARRAY_AGG(
    t ORDER BY t.column3 DESC LIMIT 1
  )[OFFSET(0)]  a
  FROM `mydataset.mytable` t 
  GROUP BY column1, column2
);

This query aggregates the data in the mytable table using the ARRAY_AGG function. In doing so, it creates an array of values from the columns of table t.

These arrays are then grouped by the values in the columns column1 and column2, and one element is selected from each group - the one with the largest value in the column3 column. This is achieved with the combination of ORDER BY t.column3 DESC LIMIT 1 and [OFFSET(0)].

Finally, the results of this grouping and selection of the largest value in column3 are combined into the output that is returned as the query result. In this case all the columns from table t are selected and returned in the query result using SELECT a.*.

All that is left is to save the query results into a new table.