Bulk delete data extension rows

Question

I am using the C# Fuel SDK for the deletion of rows in a data extension, am I am trying to delete large(ish) volumes of records at once.

The sample code only provides details of how to delete records one-by-one. Deleting large volumes (hundreds to thousands) takes a very long time using this approach.

I have begun migrating my code to use the REST APIs, but according to the documentation, there are no supported methods for delete data extension records. (see link here: https://code.exacttarget.com/apis-sdks/rest-api/v1/hub/data-events/postDataExtensionRowsetByKey.html)

Is there anyway to bulk delete a large volume of records at once through the APIs or SOAP clients?

Answer 1

You have to loop and capture the RequestID from the Response to churn through the rows in blocks of 2500.

Here's the important part from a PHP implementation:

var_dump($results);

while ($results->OverallStatus=="MoreDataAvailable") {

    $rr = new ExactTarget_RetrieveRequest();
    $rr->ContinueRequest = $results->RequestID;
    $rrm = new ExactTarget_RetrieveRequestMsg();
    $rrm->RetrieveRequest = $rr;
    $results = null;
    $results = $client->Retrieve($rrm);
    $tempRequestID = $results->RequestID;
    print_r($results->OverallStatus.' : '.$results->RequestID.' : '.count($results->Results));                    

}

If you'd like to keep all of your code in SFMC, I've deleted 300 million rows from a DE using this SSJS in SFMC Landing Page that posts to itself. I utilized a DE of keys and deleted all of the rows from the target DE one key at a time. It took a while but it worked.

<script runat="server">

Platform.Load("core", "1.1.1");

function pruneRows () {

  var DERowKeys = DataExtension.Init("DEofRowKeys");
  var DERowKeyRows = DERowKeys.Rows.Lookup(["ProcessedFlag"], [0], 50, "RowDate");

  var returnString = "";
  var totalDelCount = 0;

  for (var i in DERowKeyRows) {

     var RowKey = DERowKeyRows[i].RowKey;
     var RowDate = DERowKeyRows[i].RowDate;

     returnString += "<br>" + RowKey;
     returnString += ", " + RowDate;

     var delCount = 0;
     var action = "";
     var begin = (new Date()).getTime();

     var sl = DataExtension.Init("DataExtensionToPrune");

     try {

       delCount += sl.Rows.Remove(["RowKey"], [RowKey]);
       totalDelCount += delCount;
       action = "delete";           
       returnString += ", " + delCount;

     } catch (e) {

       action = "error";

     }

     var end = (new Date()).getTime();
     var duration = (end-begin).toString() + 'ms ';

     // update row as processed
     if (action != "error") {

        var updateCount = DERowKeys.Rows.Update({"ProcessedFlag":1,"RowsDeleted":delCount, "Duration":duration, "Action":action}, ["RowKey"], [RowKey]);

     }

  }

  returnString += "<br>total deleted in batch: " + totalDelCount;

  return returnString;
}

</script>