I need to de-dupe the data, the data fields are as follows
id, name,interestedProduct, _timeStamp.
The data is stored in a delta lake.
I have learned that there is a way to increase the efficiency of de-duplication.
Here is my code:
df = spark.sql('select * from dwd.tb_customer')
cols = ['id','name','interestedProduct','_timeStamp']
df = df.sortWithinParition(“id”).dropDuplicates(cols)
After testing, this method does improve the efficiency of de-duplication.
But my question is:
sortWithinParitions is sorting by partition, while dropDuplicates is de-duplicating globally.
So why is it more efficient to sort the data and then do the de-duplication?
And I found out that there is an argument that although dropDuplicates is global de-duplication, sortWithinParition('id').dropDuplicates(cols) is optimized by the executor to do the de-duplication inside each partition.
If this is correct, then de-duplicating and finally merging is done in the partitions, then if we don't use repartition('id') in partitioning the data
then the end result may still be duplicate records.
So how exactly does dropDuplicates work.
Why sortWithinParition + dropDuplicates can be more efficient?
If sortWithinParition + dropDuplicates is used for de-duplication, is it true that the executor will optimize dropDuplicates to de-duplicate within a partition?