I have about 400K records in opportunity object which needs a cleanup of duplicate records using a batch class. I don't want to delete the duplicate records, I want to check them as 'prospective duplicate' maintained in a custom field. Could anyone please suggest me how should I do this. I see one solution here - How do i Find and Delete Duplicates in Custom Object using SOQL or APEX however I am not sure how to do it on such a large number of records. Can someone please suggest me a solution around this?
Many duplicate management solutions work by calculating a unique key for each record, which incorporates the critical data points that are to be compared between records to find duplicates. They are then able to group the records by this key to find duplicate sets.
This works for many types of criteria, like "email address and last name and first initial", for Contacts. A Fred Jones (email@example.com) might have a unique key of "firstname.lastname@example.org/jones/f", for example - aggregating those data points into a key. That would then match on a Frederick Jones (email@example.com), who would also have "firstname.lastname@example.org/jones/f" as their unique key.
One way to approach this, and there are many, would be to write a batch class to generate and populate such a key across all Opportunities in your org into a new External Id custom field. (It's important for it to be External Id so that the field is indexed). Your Opportunities might, for example, have a unique key made up of the Account Id, the Amount, and the Close Month - "001000000000000/100.45/12", for example.
Then, you can export this data into a spreadsheet, where you'll have an easier time manipulating it to find the duplicates. Or you can run a query like this to find duplicate sets:
SELECT count(Id), Duplicate_Key__c FROM Opportunity GROUP BY Duplicate_Key__c HAVING count(Id) > 1
and then drill down on the individual duplicate groups to handle them in the way that your organization deems most appropriate.
This is one option. There's a lot of complexity in duplicate management, and the details are going to be fairly dependent upon your organization's specific requirements and duplicate criteria. You may also wish to evaluate a 3rd-party tool like DemandTools.