1

We have a SAS Datawarehouse system in our project that polls records daily from Salesforce to support data archival process. However, they have started complaining that the "updated" Account records are not "actually" updated, meaning that, when they compare the updated Account row with previous day copy of the same Account row, they see no difference at all.

As of now, they are using SystemModStamp field query relevant records from Salesforce daily.

Internally, we know that, we have several batch jobs that run and update Account object daily and we are not surprised if the updated rows are close to 1 Million or so, but we are of course very much surprised to hear that there are no "actual" changes made.

Is there any way to query only those records that are "actually" getting modified ( at least one field getting updated )? We have explored Database.getUpdated method but it seems that this method also checks SystemModStamp behind the scenes (hopefully?), so this would also not work out if a user just "Edits" and "Saves" the record, without making any field changes. Please suggest.

Improve this question
1
  • 1
    Do you know what percentage of records are just touched (updated without any changes)? and why is that a concern?
    – metasync
    Apr 5, 2018 at 14:34

3 Answers 3

Reset to default
3

The only time you can get access to previous and new values is during the trigger. So technically you could compare values and use the result to maintain a changes status. But this can be performance issue for objects with huge number of changes.

After trigger is completed, you cannot know what were the previous values, unless you have tracking enabled.

If you have tracking enabled, you can see the prior values for only those fields in corresponding object history table.

Improve this answer
3
  • Thanks for the response @datasert's. Datawarehouse team complains that they are fetching records based on SystemModStamp but there are no field level changes observed by them. Hence, they want to make sure that they only query the records where at least one field is updated. I am looking for a out of the box solution.
    – the_phantom
    Apr 5, 2018 at 14:55
  • I have already thought about what you explained so I thought I should task some expert advise here
    – the_phantom
    Apr 5, 2018 at 14:56
  • :) then, there is no other out of the box solution for your case.
    – metasync
    Apr 5, 2018 at 15:01
1

Fetching records based on SystemModStamp isn't the best approch while designing a Warehouse.

Salesforce recommends using "getUpdated" SOAP api call instead.

https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_calls_getupdated.htm

What you are trying to do is called as ChangeData capture. https://developer.salesforce.com/docs/atlas.en-us.integration_patterns_and_practices.meta/integration_patterns_and_practices/integ_pat_batch_data_sync.htm

Another reason can be you are updating a different fields which updates your SystemModTimeStamp, while your warehouse isnt storing the field you have updated.

Improve this answer
2
  • Are you sure that getUpdated call returns records that were actually updated and that they do not use SystemModStamp behind the scenes? I mean, does getUpdated call performs a deep check on the sObject rows?
    – the_phantom
    Apr 5, 2018 at 15:24
  • You can read the below blog post to see why we should use getUpdated instead of SystemModTimestamp. salesforce.stackexchange.com/a/18735/19118
    – Pranay Jaiswal
    Apr 5, 2018 at 17:11
0

You can query field history, and filter to those down to the changes needed. An obtuse approach but it should work.

https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_entityhistory.htm

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .