I have done my due diligence on this and simply cannot find an answer that does not involve Apex, which is what I'm trying to avoid for various reasons.
Does anyone know how to adequately without a ton of labor delete tens of millions of records in a (managed, custom) object in Salesforce? (specifically, it's an object from Exact Target / Marketing Cloud)
The closest I can think of based on my investigations are:
-
SQLForce – seems highly dangerous (though perhaps no more so than any other solution)
-
Excel Connector (I use it religiously) – I could write VBA to pull a group, delete a group, etc, but given that the table has over 100 million rows already, querying it is a problem (maybe SystemModStamp)
I haven't found anything adequate on AppExchange or via Google. I'm betting Apex is the only answer beyond SQLForce or an insanely time consuming routine like Excel Connector or Data Loading a list of the IDs and upload deletions that way. Am I missing something obvious??
Thanks.
UPDATE 2015-05-22 5pm EST – For the sake of continued due diligence, I've started a Data Loader deletion (Bulk API) of 1 million records to see the result. Fingers crossed! I will update again after the US Holiday.
UPDATE 2015-05-22 530pm – I am rather pleasantly surprised that it WORKED. Now for a real test (next week) of perhaps 10 million, and if that works, may as well go for the 150 million I need to expunge.
We have a specific Trigger that is overbearing, involving our Accounts and Opportunities, and such Bulk actions would be a joke to even consider usually, but either (a) since this is not directly related, or (b) perhaps due to the latest round of developers we've had who have touched up code here and there, maybe we're beyond that.
I will update again after the next test.
Best Answer
A previous organisation I worked had had a couple of child objects with over 250 million rows of data in.
When the time came to delete some of it we populated a custom indexed field on the relevent parent object records (eg all with a created date in 2013) with a sequential range of values, and then used Informatica to run a query to extract record ids from the child object for a range of values in the parent object custom field that equated to 100,000 child records and then hard delete them.
We then had a batch script to increase the range of values for the parent object and reran the extract/delete process. Informatica allowed us to schedule the job to run every 10 minutes, using this method we were able to delete 70 million records over a weekend.