Queries for incremental data loads

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 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 these methods:

• Using 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.
• Adding 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.

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 can also reference the value in the WHERE clause, GROUP BY, HAVING, and ORDER BY clauses, and in datetime functions.