A constant design challenge when building integration is answering the question, “What has changed in the Source System since the last time I checked?“, we refer to this as “Net New or Net Change”. It is a topic in which we address in detail in our Level 2 Training. One can download the section of the training here => CLICK Many applications provide us with a great way of checking this, typically it is a field on the record called Modified On Time Date, or Created On Time Date.
Some records even contain both fields. These field establish half of the equation needed to determine the set of changed records. These values are most often maintained by the application layer within a system.
As Scribe has two different platforms, Scribe Insight and Scribe Online, we have 2 different strategies to solve this question. In this article I will discuss how Scribe Insight uses this time based query to detect the Net New data by using a System Variable in the Source Query that keeps track of the last time the integration was run as the second half of the puzzle.
To use this function one will select Variables from the Configure Source screen by using the :LastRunDateTime variable in the “Where” portion of the SQL Query. For Example:
Where :LastRunDateTime <= Source.ModifiedOnTime
This will return all the newest records since the last time the integration ran and returned any data. These functions are all pared with a UTC version of the function. This converts the date stored internally into a UTC (Coordinated Universal Time) equivalent. This is used when comparing to systems that store the Modified Dates in UTC which is increasingly common. The following time based Systems Variables are available for these queries:
- : LastRunDateTime — The most recent date and time (local) that this query was executed and returned any results.
- : LastRunDateTimeDTS — The most recent date and time (local) that the specified DTS was executed.
- : LastRunDateTimeUTC — The most recent date and time (in GMT/UTC) that this query was executed and returned any results.
- : LastRunDateTimeUTCDTS — The most recent date and time (in GMT/UTC) that the specified DTS was executed.
- : PrevRunDateTimeDTS — The second most recent date and time (local) that the specified DTS was executed.
- : PrevRunDateTimeUTCDTS — The second most recent date and time (in GMT/UTC) that the specified DTS was executed.
- : ThisRunDateTime — The date and time (local) that this query began execution.
- : ThisRunDateTimeUTC — The date and time (in GMT/UTC) that this query began execution.
This pattern is subject to a few different reliability problems. This pattern requires that you build additional functionality to support the retry of rejected records. That is because the window for each record only comes around 1 time. So if that record does not make it to the target successfully it will be eternally orphaned, until it is modified again. So one will need to use the scribe rejected row function with another integration that helps manage the retry of theses rows. Additionally the source queries can get very complex if you are tracking changes to parent and child records. As modified stamped on both the parent and the child need to be checked. Finally, this pattern does not support deleted. As the record which is deleted is just not available the next time the query is run. So typically an additional integration needs to be built to check the target on a periodic basis to find orphaned records.
Given these limits this pattern is still a very popular integration pattern for our users.