We have a process that downloads full data from a couple Microsoft Dynamics CRM entities and stores in an AWS Aurora Postgres database. Since this data is typically quite static from day to day, we don't want to do full truncate/replace actions every time we run. Instead, since every entity we are downloading has a "modified_on" timestamp field, we are trying to key off of that. We store the greatest value of modified on in a separate object, and only pull rows from source where "modified_on > saved_value." Then, we can run a quick, incrementally updating process we can run as often as is needed.
Our process works by
- Establish connection to our Dynamics Source
- use tDBInput followed by tContextLoad and tLogRow to store saved_value as context variables
- use tMicrisoftCrmInput to extract data, on the condition that modified_on > saved_value
- Upsert result rows in Postgres database
- Find the new maximum value of modified_on, and store in the same database
This works incredibly well, for some entities. Other, there are clear missing updates -- rows would be modified between iterations of saved_value, but our process does not pick them up to update. Our flat file testing has yet to reproduce this issue, and it is quite intermittent. We had thought this was an issue regarding entity-lock, users updating while we were pulling data, but we have seen more instances than not where the update date was not even close to our download times.
A couple other things
- We have rare instances where we do lookups during the downloads, or where we have to break apart custom JSON. Some of these cause the same issue, some don't.
Has anyone else seen this issue, or come to a resolution with having consistently accurate incremental data pulls from Dynamics CRM?
