BigQuery - DELETE statement to remove duplicates

2 min read 06-10-2024

BigQuery - DELETE statement to remove duplicates

Deleting Duplicates in BigQuery: A Comprehensive Guide

BigQuery is a powerful cloud data warehouse that offers efficient data management, including the ability to handle duplicate records. Sometimes, duplicate data can create issues with data integrity and analysis. This article provides a comprehensive guide on using BigQuery's DELETE statement to effectively remove duplicate records from your tables.

Understanding the Problem: Duplicate Data in BigQuery

Imagine you have a table containing customer data. You might notice that some customers have multiple entries with slightly different information or even identical data. These duplicates can lead to inaccurate reporting, skewed analysis, and inefficient resource usage.

Example Scenario: Identifying and Removing Duplicates

Let's say you have a table named customer_data with the following schema:

CREATE TABLE customer_data (
    customer_id INT64,
    customer_name STRING,
    email STRING,
    city STRING
);

This table contains several duplicate records with the same customer_id but different values in other columns. Here's a simplified representation:

customer_id	customer_name	email	city
1	John Doe	[email protected]	New York
1	John Doe	[email protected]	New York
2	Jane Smith	[email protected]	London
2	Jane Smith	[email protected]	London

We need to identify and remove duplicate entries, keeping only the first instance for each unique customer_id.

Implementing the DELETE Statement

BigQuery provides the DELETE statement to remove specific rows from a table. Here's how to use it to remove duplicates:

DELETE FROM `your_project_id.your_dataset.customer_data`
WHERE customer_id IN (
    SELECT customer_id
    FROM `your_project_id.your_dataset.customer_data`
    GROUP BY customer_id
    HAVING count(*) > 1
)
AND NOT customer_id IN (
    SELECT customer_id
    FROM `your_project_id.your_dataset.customer_data`
    GROUP BY customer_id
    ORDER BY customer_id
    LIMIT 1
);

Explanation:

DELETE FROM ...: This specifies the table from which to delete rows.
WHERE ...: This clause filters the rows to be deleted.
customer_id IN (SELECT ...): This subquery selects all customer_id values that appear more than once in the table (i.e., duplicates).
AND NOT customer_id IN (SELECT ...): This subquery selects the first occurrence of each customer_id based on the ORDER BY clause. This ensures that the first occurrence of each customer_id is not deleted.

Important Considerations:

Unique Identifier: Ensure you have a reliable unique identifier column (like customer_id) for your table.
Data Consistency: Before executing the DELETE statement, make sure you have a backup or a clear understanding of the data you're deleting.

Additional Tips:

Identifying Duplicates: Use SELECT statements to identify and examine duplicate records before deleting. This allows you to understand the extent of the duplication and confirm the intended outcome.
PARTITION BY: For large datasets, use the PARTITION BY clause in your DELETE statement for more efficient processing.
ROW_NUMBER() Function: The ROW_NUMBER() function can be used to assign a unique number to each row within a group defined by the PARTITION BY clause. This can be helpful for identifying and deleting duplicates.

Conclusion

Deleting duplicates in BigQuery using the DELETE statement is an effective way to maintain data integrity and improve data analysis results. Remember to carefully consider your data and use the appropriate techniques to identify and delete duplicates accurately.