Mass Delete Tens Of Millions Of Records

Question

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:

1. SQLForce - seems highly dangerous (though perhaps no more so than any other solution)

2. 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.

Answer 1

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.

Answer 2

Write a batch which will delete 10000 records per execution and logic should count the total no. of records , divide by 10000 and execute the batch that no. of times.

Answer 3

Deleting that much of data from Salesforce is no joke and require a great deal of planning. Writing batch delete has its own demerits and it can easily surpass the 24 hr rolling limit of your Org and moreover it will stall the batches that are required to complete business processes and use case. So In my experience, deleting 250m+ records needs to be well thought through. Following are key principles to consider

1. Always keep a tab on number of batches that are being executed for deletion
2. Consider running delete operations from reliable computer and having good internet connection (Data loader hangs multiple times even when all things are okay)
3. Consider non batch approach also (Multi threaded, Multi instance programs each thread having 199 records deletion at one go) (I wrote a JSforce/nodejs based utility that spawn 10 loops and 10 such instances were launched)