Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago
Solved

Eliminating duplicated records based on the Last Updated Date

Continuing the discussion from Fetch data whose date is less than 10hrs from the existing date!: Hi Team, The discussion was going on a different thread but it would have made more sense to open ...
expression_language
pipeline
transform
  • koryknick's avatar
    koryknick
    4 years ago

    @darshthakkar - Just FYI - the Salesforce Upsert snap returns a “created” field in the response which you can use to determine if the record was inserted vs. updated.

    In regards to pulling modified records since the last execution, I mentioned the use of a control table that you can store the last modification date pulled from the source that was successfully upserted to Salesforce. This can be done with a simple Aggregate snap to get the max date, then write back to the config table only after all updates have been completed in Salesforce.

    Note that if you are using the Bulk API option on the Salesforce Upsert snap, you may need to follow-up with a Salesforce Poller to get the final result of the updates. See the snap documentation for more details.

koryknick's avatar
koryknick
Employee
4 years ago

@darshthakkar - you are correct, the Salesforce Bulk snaps will only use the Mutual Auth or Oauth2 account types.

Regarding selection of records from Snowflake based on timestamp, you could change the method you are using for that date range selection so that modified records are only selected once. A typical pattern I’ve seen used successfully is to store the maximum value of the last modification timestamp that was updated into Salesforce so it can be used as the filtering criteria for the Snowflake query during the next pipeline execution. Obviously, you will need to store that value somewhere - perhaps back into a configuration table in Snowflake?

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago

I hear you, thanks @koryknick.

We can follow either of the 2 steps below:

  1. Have a staging table for comparison (compare all the fields to be double sure)
  2. Updating the query which would consider records updated in last 6hrs as the batch is running at an interval of 6hrs (4 times a day)

Well, I wouldn’t ask you for a generic logic as the use cases differ with each client, there might be a requirement in the future to run batch every hour and there in, I’ll have to update the query which considers records updated last hour or last hour and half (putting half an hour more as a buffer).

The only reason to consider records updated in last 48 hours was to assess risks in advance, let’s assume snapLogic is down (which is quite unusual as per my experience thus far!) or there are some issues in any of the upstream or downstream systems, 48 hrs was a segway to consider all those records in the downtime.

I will brainstorm the logic again and put something which considers exactly the modified records and modified records + some modified records in the last batch run (if batch run interval is 6hrs and query is pulling records updated in last 6.5hr or 7hr).

Let me know your thoughts on this one and thank you again for your time and assistance.
Enjoy the long weekend.

Regards,
Darsh