0

In our org, we have 60 million Account records many of which are duplicates. We are trying to find all those duplicate records based on their golden Id(MDM). We are trying the below query in Batch and other tools but it times out anyway.

SELECT COUNT(ID) ID, R1_ACC_TXT_Id_Golden_record__c FROM ACCOUNT WHERE CreatedDate=Last_YEAR GROUP BY R1_ACC_TXT_Id_Golden_record__c HAVING COUNT(ID)>1 limit 1000

Is there a way to find out at least the count of duplicate records with their salesforce ID?We also have informatica ETL tool that we can make use of.

Regards, Chetan

Improve this question
2
  • 1
    Can you batch iterate the "golden record" values and count Account duplicates for each of those in a more targeted query? You will run separate queries per golden record, or number of golden records, so instead of 1 query you will have n queries, each of which deals with just a "small" set of data rather than everything.
    – Phil W
    Commented May 22, 2020 at 11:04
  • Can you extend the where clause to include additional indexed filters? You may also want to review an old but accepted post around this area too.
    – TSmith
    Commented May 22, 2020 at 11:07

1 Answer 1

Reset to default
3

Run a Duplicate Job, then you can easily export the DuplicateRecordItem and/or DuplicateRecordSet values to get all the duplicates (each set defines a group of duplicate items). Aside from exporting all the data and massaging offline, this is probably the most convenient way to do this.

Improve this answer
8
  • Thanks @sfdcfox, I have run the duplicate job by creating a matching rule on golden ID of records.I am assuming it will scan all the 60+ million records.
    – Chetan
    Commented May 25, 2020 at 14:51
  • Is it possible to accomplish through any ETL tool to find the same say Mulesoft?
    – Chetan
    Commented May 25, 2020 at 14:52
  • @Chetan The GROUP BY query is limited to 2,000 groups (so, 2,000 groups of records). This probably won't help you if you have a large number of duplicates. A System Administrator can run this query, but it will take a long time and will only return at most 2,000 groups. The Matching Rule will match all the records, though it will also take some time to scan the records, but it will successfully complete.
    – sfdcfox ♦
    Commented May 25, 2020 at 15:12
  • Thanks @sfdcfox. So the result produced by this job is downloadable?
    – Chetan
    Commented May 25, 2020 at 17:40
  • 1
    @Chetan In the query editor, you can use select R1_ACC_TXT_Id_Golden_record__c from account where id in (select recordid from DuplicateRecordItem) (or any other fields you'd like). You can query DuplicateRecordSet if you want the total number of duplicate groups (as in your original query).
    – sfdcfox ♦
    Commented May 25, 2020 at 19:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .