, , , ,

Duplicates in Data and SQL

You know, after literally multiple decades in the data space, writing code and SQL, at some point along that arduous journey, one might think this problem would be solved by me, or the tooling … yet alas, not to be.

Regardless of the industry or tools used, such as Pandas, Spark, or Postgres, duplicates are a common issue in pipelines, and SQL remains the most classic and iconic problem. Things just never change, and humans never learn their lessons, at least I don’t.

Solving the duplicate record problem.

Honestly, there is a never ending list of vendors who would love to sell you some fancy Data Quality and Monitoring tool that will solve every problem, including duplicates, except it wont. Duplicates have been a problem since the Data Warehouse days of SQL Server and SSIS. Duplicates are still a problem with a Modern Data Lake House architecture using Delta Lake / Apache Iceberg and Spark.

Technology can rarely solve our data problems like we want. Duplicates will be with us forever.

Best get used to it.

Instead of making it complicated, which we can, I think there are some straight forward ways to deduplicate data, any data set, in any system, that are accessible to everyone. Yes, each tool we might use probably has built in deduplication methods which can be helpful, but it’s also help to take our duplicate problems back all the way into the dataset, instead of making the data processing tools do all the hard work.

Here is how you should solve your data duplicated.

  • every dataset should have a primary key
  • every dataset when processed should drop duplicates based on the primary key

Yes, there are some nuances hidden in here, but this applies to 95% of datasets and tools, regardless if you are on Snowflake, Databricks, Pandas, Polars, or DuckDB.

Primary Keys

Ok, the age old solution that no one uses, especially in the Lake House world. Pretty much everyone who works in data is familiar with primary keys, at least in concept, yet they are not widely used outside of RDBMS databases, for whatever reason. There are two types of primary keys you will run into.

  • enforced primary keys in things like Postgres
  • simple unenforced logical primary keys in things like Delta Lake

It honestly doesn’t matter if a primary key is enforced or not, it’s the process of creating primary keys that will, help with the deduplication and processing of duplicates in data. Every single dataset you come into contact with has a primary key, and you should know it, and calculate it.

You will have one of the two following primary keys.

  • composite primary keys
  • user generated primary keys

A composite key is basically the combination of column(s) that make a record unique. For example …

The above composite primary key is made up of two columns. This SQL above is what you would run into for a RDBMS database like Postgres or MySQL.

So, on the opposite end of the spectrum in a Lake House we might have a logical primary key that is not enforced AND user generated in Delta Lake.

Honestly, the primary key kinda just depends on the context and what tooling, but it’s all the same in the end.

You need to define what makes each record unique, create a primary key for that record, and use that primary key for all downstream data de-duplication.

Post processing duplicates and deduplication.

So, now that you have your primary key, the uniqueness of your dataset defined, the last step is to simply use whatever tool is doing the data processing and ETL of your data, DataFrame, SQL, whatever … to deduplicate your data in your pipeline. You can’t just assume a dataset will never have duplicates, most likely it will at some point.

If you don’t design for it, it will break.

Examples of deduplication using a primary key column with PySpark, Polars, Pandas, and DuckDB.

In PySpark, dropDuplicates() is the go-to method:

Polars provides unique():

Pandas use drop_duplicates():

In DuckDB, a SELECT DISTINCT ON (Postgres-style) or ROW_NUMBER() works:

That’s pretty much what you need to deal with duplicates in your data. I know, nothing fancy, sorry. It’s best just to go straight to the data and deal with by adding some sort of primary key, logical or enforced, doesn’t really matter. Putting the work in up front to know what is a unique record takes an extra five minutes, and will pay dividends later.

Once you have that primary key, it’s a one-liner insert into most of your data pipelines to ensure know duplicates are making their way downstream to blow up a Dashboard or MERGE statement.

That’s always a good thing.

Some folk might complain that their 15 PB’s of data is too large to deduplicate. Fair enough, but if you are processing your data in smallish chunks, it’s a good idea to give it a try … see if you can get away with the extra compute and processing. Same for streaming datasets, at least get a primary key defined, and maybe in downstream pipelines and analytics you can deduplicate when looking at slices of the data.