Incremental data loads

If a source table contains a column with the datetime data type, you can specify that only data modified since the last import is loaded.

The duration of load queries and scripts includes the entire process from the start of the run, until the last record is written. The duration of a query is shown in these places:

  • On the Load Query tab
  • In the application logs, by displaying the Duration column for the Load Query Execution Stop event.
The Load Queries tab displays the Last Loaded date and time.
Note: Last Loaded is the time at which data was last queried from the source, not the time at which it was successfully loaded into a table.

In a load query, you can use the '<<LAST_IMPORT>>' macro to get the Last Loaded date and time.

For example, in this load query, '<<LAST_IMPORT>>' is used as the value of the lastModified parameter:

SELECT firstName, lastName, age 
FROM Employees
WHERE lastModified>'<<LAST_IMPORT>>'

In relational modeling the '<<LAST_IMPORT>>' macro uses an SQL date and time format and the data lake function infor.lastmodified uses a different time format. This incompatibility between the two date and time formats is handled automatically. If this automatic handling fails, then use the DATEADD function (adding 0 hours) to convert the data lake function to a compatible date and time format:

SELECT *, infor.lastmodified() as lastModified
FROM dEPMTest
WHERE DATEADD(hour, 0, infor.lastmodified()) > ('<<LAST_IMPORT>>')