Data Extension Caching when doing Lookups

Question

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.

Answer 1

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