Queries for incremental data loads

The Data Lake contains every version, or variation, of a record. Each record in Compass data storage is associated with the Data Lake object ID and the Data Lake object datetime. The object datetime is the timestamp in which the data object “payload” was added to the Data Lake. The timestamp is a property of the payload object itself; it is not a property in the object or defined the data object metadata. The timestamp is referred to as the lastmodified property.

The lastmodified value is commonly used for incremental loads, so a full result set is not retrieved each time a query is run. For example, you can use a lastmodified value in a WHERE clause to retrieve data from data objects loaded on or after a specific datetime, such as 2019-09-01T09:30:47.323Z. Note that all lastmodified timestamps are in UTC in ISO8601 RFC3339 format with three fractional seconds. The lastmodified value is available through two methods:

You can use the infor.lastmodified() function to select the lastmodified value. The parameter for the function is the data object or alias that is referenced in the query.
You may add the lastmodified value to a query as a synthetic property. The property is synthetic because it is not defined in a data object’s metadata.

Note: The infor.lastmodified() function is recommended over the lastmodified synthetic property. We recommend the function because it avoids contention with any data object that may have an actual lastmodified property. The lastModified value, selected as a property, may be deprecated in a future release.

You can also use the lastmodified synthetic property or the infor.lastmodified() function in the SELECT clause of the query to select the lastmodified value for each record.

You may also reference the value in the WHERE clause, GROUP BY, HAVING, and ORDER BY clauses, and in datetime functions.