1

Script description:
There is a script that aims to retrieve up to 200 records from the Data Extension, perform some manipulation with retrieved values and lastly delete processed records from the Source Data Extension.
If there are only 200 records - it works fine but then an additional requirement appears that Data Extension might have more than 200 records thus we decided to iterate the flow in 200 records batches and here is when the problem occurs.

Main problem:
Let's say, the Data Extension has 300 records, the first batch of 200 records is processed and then deleted but when it comes to the second batch the script views the Data Extension records as they were initially presented, which means as there are 300 records still rather than actual 100 left after deletion in the first iteration. So that, it looks like that when the script is executed it snapshots the Data Extension and you can only work with that version of the Data Extension within that particular script execution.

How can we dynamically retrieve and delete retrieved records from the same Data Extension?

Script:

%%[
FOR @a = 1 TO 2 DO

    SET @LookupOrderedRows = LookupOrderedRows("Data_Extension", 200, "Field1 asc", "All", 1)

    IF RowCount(@LookupOrderedRows) > 0 THEN

        FOR @i = 1 TO RowCount(@LookupOrderedRows) DO

            SET @Field1 = Field(Row(@LookupOrderedRows, @i), "Field1")
            /* Skipped retrieval of more fields */

            DeleteData("Data_Extension", "Field1", @Field1)

        NEXT @i

    ENDIF

NEXT @a
]%%

Notes:

  1. Cannot retrieve more than 200 records per batch
  2. Tried to simply have 2 LookupOrderedRows one after another (not within the loop) and the result is the same
  3. Checked amount of records of the Data Extension with DataExtensionRowCount AMPscript function and it always shows 300 records at any given position within the script
  4. Tried doing the same with SSJS Platform functions, which resulted in the same outcome

UPD:
Adam's suggestion about Data Extension caching sounds exactly what we are experiencing but there is no relation to Email Sends. We are running the script in the CloudPage and will run it later in the Script Activity. Also, tried running it with 2400 records, which is what should have solved based on the Knowledge Article.
The main problem is still there - when we are doing a Lookup, Data Extension records got cached and because of that you cannot "mark" with UpdateData or DeleteData functions which records have been processed in a batch, so that the next batch will pick other records. This leads to the same outcome - only the first 200 records are processed.

1 Answer 1

2

SFMC has some caching settings/business rules for small Data Extensions.

I'd suggest opening a support case to see if it can be adjusted.

There's a hint about it in this knowledge base article.

Regardless of the caching, I'd recommend separate steps for your lookup and your delete. Feels convoluted, but some derivative of this may work for you:

DataExtension1

SubscriberKey LookupKey DeleteFlag
1111 2 false
2222 1 false
3333 1 false
4444 2 false
5555 1 false
6666 2 false
7777 1 false
8888 1 false
9999 2 false
0000 1 false
%%[

for @a = 1 to 2 do

    set @rows = LookupOrderedRows("DataExtension1", 200, "SubscriberKey asc", "LookupKey", 1, "DeleteFlag", 0)
    set @rowcount = RowCount(@rows)
    output(concat("<br><br>LOOKUP pass #", @a ," rowCount: ", @rowCount))

    if @rowcount > 0 then

        FOR @i = 1 TO @rowcount DO

            set @row = row(@rows, @i)
            set @SubscriberKey = field(@row, "SubscriberKey")
            output(concat("<hr>SubscriberKey: ",@SubscriberKey))

            set @updateResults = UpdateData("DataExtension1",1,"SubscriberKey", @SubscriberKey,"DeleteFlag", 1)
            output(concat("<br>updateResults: ",@updateResults))

        next @i

    endif

next @a


output(concat("<hr>DELETE PASS"))
set @rowsDeleted = DeleteData("DataExtension1", "DeleteFlag", 1)
output(concat("<br>rowsDeleted: ",@rowsDeleted))

]%%

Output:

PASS #1 rowCount: 6
SubscriberKey: 0000
updateResults: 1
SubscriberKey: 2222
updateResults: 1
SubscriberKey: 3333
updateResults: 1
SubscriberKey: 5555
updateResults: 1
SubscriberKey: 7777
updateResults: 1
SubscriberKey: 8888
updateResults: 1

PASS # rowCount: 6
SubscriberKey: 0000
updateResults: 1
SubscriberKey: 2222
updateResults: 1
SubscriberKey: 3333
updateResults: 1
SubscriberKey: 5555
updateResults: 1
SubscriberKey: 7777
updateResults: 1
SubscriberKey: 8888
updateResults: 1

DELETE PASS
rowsDeleted: 6

EDIT:

I think you can bypass the caching using SSJS core functions -- since you have to explicitly init the DE before interacting:


<script runat="server" language="javascript">

Platform.Load('Core','1')

var DEKey = "DataExtension1";

for (p = 0; p < 3; p++) {

  var de = DataExtension.Init(DEKey);
  var sfp1 = {Property:"LookupKey",SimpleOperator:"equals",Value:1}
  var sfp2 = {Property:"DeleteFlag",SimpleOperator:"equals",Value:false}
  var cfp = {LeftOperand: sfp1, LogicalOperator: "AND", RightOperand: sfp2};
  var rows = de.Rows.Retrieve(cfp);
  var rowCount = rows.length;

  Write("<hr>LOOKUP PASS #" + p);
  Write("<br>rowsCount: " + rowCount);

  for (i = 0; i < rowCount; i++) {

      var SubscriberKey = rows[i].SubscriberKey;

      var updateResults = Platform.Function.UpdateData(DEKey,['SubscriberKey'],[SubscriberKey],['DeleteFlag'],[true]);
      Write("<br>updateResults: " + updateResults)

  }

  var rows = de.Rows.Retrieve(cfp);
  Write("<br>(post update lookup) rows: " + rows.length);

}

var rowsDeleted = Platform.Function.DeleteData(DEKey,['DeleteFlag'],[true]);
Write("<br>rowsDeleted: " + rowsDeleted);

</script>

Output

LOOKUP PASS #0
rowsCount: 6
updateResults: 1
updateResults: 1
updateResults: 1
updateResults: 1
updateResults: 1
updateResults: 1
(post update lookup) rows: 0
LOOKUP PASS #1
rowsCount: 0
(post update lookup) rows: 0
LOOKUP PASS #2
rowsCount: 0
(post update lookup) rows: 0
rowsDeleted: 6
7
  • A pretty fresh article, thanks for it. First of all, I am running that script in the CloudPages and will later in the Script Activity, so not related to Email Sends. Yet, have tried creating a new DE with 2400 records and still face the same problem - only the first 200 records are processed and deleted.
    – Niko
    Commented May 17, 2022 at 12:53
  • Updated my answer above with an alternative for you to try. Commented May 17, 2022 at 14:18
  • Checked all 3 versions of your answer :) So far only tried the last one - where you have the UpdateData function - this one does not work because the LookupOrderedRows still works on the initial snapshot of the DE. I do have hope for the approach when concatenating SubKeys and then parsing them to delete them out of the loop. Will update you once tried.
    – Niko
    Commented May 17, 2022 at 15:01
  • Update the description of the question. Thanks for the suggestions though!
    – Niko
    Commented May 18, 2022 at 8:01
  • Added a SSJS option that appears to bypass the caching. Commented May 18, 2022 at 13:10

You must log in to answer this question.

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