Salesforce Marketing Cloud Automation Studio

This one is for Salesforce Marketing Cloud Solution Architects.

Have you come across a situation where your Journey picks data based on the Automation run? The SQL inside Automation has overwritten statement. Only data is coming in based on a field say DateRecordCreated (when the record got created/updated to qualify for SQL that run through Automation)

This case is very much a situation in industries like banking, where millions of transactions may happen globally at a time.

Well, this is not a big concern, but what if the system itself goes down for the moment.

Even 5 minutes of downtime will miss millions of record update through Automation.

So what’s the solution? How to fetch details from the last run to current run automation?

Just follow me with this as below.

Create a Data Extention (DE: Batch_sync) with the following fields

  • BatchID (Primary Key and have ‘1’ as the default value)
  • Last Run Date
  • Current Run date

And Create a record in this Data Extention (DE) manually.

Now Create SQL Query to populate this DE.


BatchID = '1',

Case when b.Last_Executed_Date is null then getdate() Else b.Current_Executed_Date End as Last_Executed_Date,

Current_Executed_Date = getdate()

from Batch_sync b

Remember, we are keeping the same DE as Destination DE with the Update action.

Always keep this query in the same Automation that is used for running the Journey.

Now Update the Query to fetch the records for Journey with the following.

WHERE M.DateRecordCreated > (Select Last_Executed_Date from batch_sync where Current_Executed_Date between Last_Executed_Date and Current_Executed_Date )

With this in place, any interruption will not impact the purpose of Automation running at the backing in the Salesforce Marketing Cloud.

Click to join our Telegram channel and keep getting a notification when we post a new article.