How can we automatically get the Salesforce newly inserted data set after copy activity in ADF

Question

How can we automatically get the Salesforce newly inserted data set after copy activity in ADF

Umair Ehsan 20

Essentially, I am trying to copy data from an Azure SQL server to Salesforce using Azure Data Factory (ADF). How can we automatically get the Salesforce newly inserted dataset after the copy activity in ADF?

User's image

Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2025-06-13T09:40:00.4566667+00:00
Can you please clarify :

Do you want to copy data from Salesforce into some destination post loading the data into salesforce?

And do you want to use ADF only for #1
Umair Ehsan 20 Reputation points

2025-06-13T09:45:51.1366667+00:00

Hi Nandan, my source is Azure SQL, and the Destination is Salesforce. In ADF pipeline, I created a copy Activity to sink data from Azure SQL to Salesforce. In the next step, I aim to retrieve the newly created data, along with the Salesforce IDs, for the Backfill purpose. How can I achieve this?
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2025-06-13T09:52:35.3866667+00:00

When you say retrieve the data from salesforce, what do you mean?
Do you want to get that data from salesforce and load it somewhere else
Umair Ehsan 20 Reputation points

2025-06-13T09:56:57.12+00:00

In the same pipeline, I want to get the newly inserted data.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2025-06-13T10:00:43.1066667+00:00

You can use the concept of watermark :

https://stackoverflow.com/questions/68301713/how-to-create-an-incremental-load-with-salesforce-as-source-in-azure-data-factor

And then filter out the incremental data based on the watermark

Accepted answer

0 additional answers

Your answer

Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2025-06-13T09:40:00.4566667+00:00

Can you please clarify :

Do you want to copy data from Salesforce into some destination post loading the data into salesforce?

And do you want to use ADF only for #1
Umair Ehsan 20 Reputation points

2025-06-13T09:45:51.1366667+00:00

Hi Nandan, my source is Azure SQL, and the Destination is Salesforce. In ADF pipeline, I created a copy Activity to sink data from Azure SQL to Salesforce. In the next step, I aim to retrieve the newly created data, along with the Salesforce IDs, for the Backfill purpose. How can I achieve this?
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2025-06-13T09:52:35.3866667+00:00

When you say retrieve the data from salesforce, what do you mean?
Do you want to get that data from salesforce and load it somewhere else
Umair Ehsan 20 Reputation points

2025-06-13T09:56:57.12+00:00

In the same pipeline, I want to get the newly inserted data.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2025-06-13T10:00:43.1066667+00:00

You can use the concept of watermark :

https://stackoverflow.com/questions/68301713/how-to-create-an-incremental-load-with-salesforce-as-source-in-azure-data-factor

And then filter out the incremental data based on the watermark

Answer 1

Venkat Reddy Navari 3,125 Microsoft External Staff Moderator

@Umair Ehsan Since you're writing data from Azure SQL into Salesforce using ADF and then want to fetch those newly inserted records (along with their Salesforce-generated IDs) right after the insert, here’s a practical approach you can try within the same pipeline:

Use an External ID – When pushing data into Salesforce, it helps if you're passing a unique value from your SQL source (like a primary key). This can be used later to identify exactly what was inserted.

Add a Follow-up Lookup or Copy Activity – After the insert step, add a step that queries Salesforce (using SOQL) to pull back those same records based on the external IDs. This way, you can get the Salesforce record IDs and any other fields you need. Something like:


Copy
SELECT Id, External_Id__c FROM YourObject WHERE External_Id__c IN ('your-key-values')

Optional: Store or log these records – You can store the results in a SQL or blob sink, especially if you need them for backfill or reconciliation later.

If your load volume is high, instead of directly using external IDs, you can implement a watermark pattern based on CreatedDate, filtering records inserted in the last few minutes (e.g., CreatedDate >= @{pipeline().TriggerTime} minus buffer).

Hope this helps. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Umair Ehsan 20 Reputation points

2025-06-16T09:55:16.05+00:00

Hi Venkat, Thanks for the answer. This approach is correct and applicable. However, in Salesforce default objects where we are not allowed to add an external_id field, what should we do?
Venkat Reddy Navari 3,125 Reputation points Microsoft External Staff Moderator

2025-06-16T10:21:02.3266667+00:00
Umair Ehsan For standard Salesforce objects where you can't define a custom external ID, you still have a couple of options to work around this:
Use a natural key or a combo of fields – Even if there’s no official external ID, often there's a field (or combination) that can act like one—maybe Email, Phone, or something else unique enough for your use case. You can use that to pull back the records you just inserted.

Query by CreatedDate – After the copy activity, you can query records created in, say, the last 5–10 minutes. Salesforce lets you do that using something like:

SELECT Id, Name, CreatedDate FROM Contact WHERE CreatedDate = LAST_N_MINUTES:10

You could even narrow that further by filtering on a field you passed during the insert.

Pass a reference field if allowed – Some standard objects allow you to include optional fields like Description, Custom Notes, etc. Even if it’s not a true ID field, adding a unique string there (e.g., a GUID or your SQL row ID) can help you match things up later.

It's a bit of a balancing act without external IDs, but the CreatedDate + known field values combo usually works well in real-world pipelines.

Share via

How can we automatically get the Salesforce newly inserted data set after copy activity in ADF

0 additional answers

Your answer