I'm creating an automation that may need to have upwards of 20 script activities, in order to process 40,000+ records as there's a governor limit of 2500 in Rows.Retrieve. What's the best practice for the number of script activities in an automation? Please advise.
Don't take this the wrong way but the word best practice is very, very loosely used here.
Using SSJS for a bulk retrieve is a bit like using a screwdriver to drive a nail into the wall. Sure works, if you do it the "right way", except for the fact that the screwdriver manufacturer wrote in the manual that you shouldn't do it.
People have circumvented the governor limits: you can make your script use a while loop. Which means you need exactly one script. But the screwdriver suffers a bit more than proper usage would make it. (i.e. Salesforce's servers get to do a lot of inefficient work).
And if you are unlucky, your while loop workaround just leads to the next problem, timeouts, it depends on the operations you perform. If it times out, you'll have to go back to the SQL answer. So maybe, just consider calling an SQL query from your script.