Migrating ADF to Salesforce v2 Dataset, how to get MAX() value of Object's LastModifiedDate

@Maarten

Thanks for reaching out to Microsoft Q&A

Migrating to the new Salesforce v2 linked service and dataset in Azure Data Factory (ADF) can indeed present some challenges, especially with the limitations on aggregate functions like MAX(). Here are a few alternative approaches you can consider:

Use Data Flows in ADF You can leverage Data Flows in ADF to perform the aggregation. Data Flows allow you to perform transformations on your data, including aggregations, without needing to write SQL queries directly. Example of Using Data Flows Here’s a brief example of how you might set up a Data Flow to get the MAX(LastModifiedDate):

1. Create a Data Flow in ADF.
2. Add a Source Transformation to read data from Salesforce.
3. Add an Aggregate Transformation to calculate the MAX(LastModifiedDate).
4. Add a Sink Transformation to write the results to a staging table or directly use them in your pipeline.

Resources

For more detailed guidance, you can refer to the official documentation on using Salesforce with ADF.

Utilize Salesforce Reports

Although you mentioned you’d prefer not to create multiple reports, using Salesforce Reports can be a viable solution. You can create a report for each object that includes the MAX(LastModifiedDate) and then use the ADF Salesforce connector to fetch these reports.

Incremental Data Load with SystemModStamp

Instead of using LastModifiedDate, consider using SystemModStamp. This field is updated automatically whenever a record is created or updated and can be used for incremental data loads. You can query records where SystemModStamp is greater than the last fetched value.

Custom API Endpoint

If you have development resources, you could create a custom API endpoint in Salesforce that returns the MAX(LastModifiedDate) for each object. This endpoint can then be called from ADF to get the required values.

Staging Table in SQL Server

Another approach is to create a staging table in your SQL Server DWH where you store the LastModifiedDate values for each object. You can then query this table to get the MAX(LastModifiedDate) for each object before performing the data load.

Hope this helps. Do let us know if you any further queries.

Hi @phemanth thank you for the suggestions. The data flow seems the most elegant solution.

I'm not sure how using SystemModStamp would work. We would still need to query it beforehand, to make sure that the delta that we load is controlled or fixed with a lower and upper boundary timestamp.

We actually thought of another one; a Salesforce automation or apex that calculates and saves the LastModifiedDate of our synched objects in a custom object, before the nightly run in the ADF. We would then need to query that single object at the start of our pipeline.

But in the end, I found this also to work, which for now seems the easiest fix:

SELECT LastModifiedDate FROM Contact ORDER BY LastModifiedDate DESC

The documentation said that ORDER BY is not supported.

Would this mean that the results of the above query are not to be trusted? Or did I interpret the documentation incorrectly? Thank you.

@Maarten

Using SystemModStamp

You’re correct that you would still need to query SystemModStamp to set the boundaries for your delta load. The idea is similar to using LastModifiedDate, but SystemModStamp is generally more reliable for incremental loads because it captures all changes, including system updates.

Salesforce Automation or Apex

Creating a custom object to store the LastModifiedDate values using Salesforce automation or Apex is a solid approach. This way, you can ensure that the values are pre-calculated and readily available for your ADF pipeline.

Using ORDER BY LastModifiedDate DESC

Regarding your query:

SELECT LastModifiedDate FROM Contact ORDER BY LastModifiedDate DESC

If the documentation states that ORDER BY is not supported, it generally means that the query might not be optimized or guaranteed to work as expected. However, if it works for you, it might be due to specific conditions or configurations in your environment.

The warning about ORDER BY not being supported typically means that Salesforce does not guarantee the order of the results, especially for large datasets or complex queries. This could lead to inconsistent results. If your current solution works reliably, you might continue using it but be cautious and monitor for any inconsistencies.

Given your scenario, the Data Flow approach in ADF seems the most robust and scalable solution. However, if your current ORDER BY query is working and you are comfortable with it, you can continue using it while keeping an eye on the results.

Thank you once again @phemanth

I'm trying to set up the Data Flow, but my Salesforce dataset is not in the list. I'm not sure if Salesforce is supported as Data Flow source

Hi @phemanth thanks again

I've been trying to set up the Data Flow, but Salesforce doesn't seem to be supported. My dataset is not in the list. So I think I'll try another solution, from the options above.