Partial importing from Data Lake

Partial Import reduces run time and the amount of data transferred by processing only records that were added or modified since the last successful import.

M3 stores data in Data Lake by using the variationNumber value as the epoch (LMTS) timestamp. When you run Import Data Lake, you update the ImportData table (Year column) with the epoch time to record the last import time. TemplateType = 11 is the configuration for Import Data Lake.

You can then create this partial import feature by adding WHERE clause but the user can still change the clause. During Import Data Lake, the application service replaces [LASTIMPORTTIME] with the current value in the ImportData table (Year column). This behavior keeps the WHERE clause continuously updated with each import from Data Lake.

Enabling Partial Import

  1. From the menu, select Version > Import > Import Data Lake or Actions > Import Data Lake.
    The Import Data Lake dialog box is displayed.
    Note: You can access Import Data Lake from the Actions menu in Version Control.
  2. In Input, select the Compass SQL query.
  3. Select Partial Import.
    If no WHERE clause exists, DMP inserts: WHERE timestamp > [LASTIMPORTTIME].

    If a WHERE clause already exists, DMP appends: AND timestamp > [LASTIMPORTTIME].

  4. DMP inserts the filter before ORDER BY, GROUP BY, or HAVING clauses to keep the SQL valid.
  5. You can edit the generated filter for specific table structures.
    Note: You can use [LASTIMPORTTIME] for other columns, not only for variationNumber. When extracting the WHERE clause, ensure that other keywords do not follow the clause, such as ORDER BY and HAVING.
  6. In Output, specify this information:
    Type
    Select an output type. Select Database Table or CSV file output.
    Name
    If Database Table is selected, specify the name of the table.
    Folder/File Name
    If CSV file output is selected, specify the folder or file name for the output.
    Reset Table
    For Database Table output, clear Reset Table in most cases when you use Partial Import. This setting appends incremental results instead of replacing the entire dataset. For CSV file output, DMP always overwrites the target file, so the file contains only the records returned by the most recent import.

Timestamp placeholders

DMP stores the last successful import timestamp in the ImportData table, where TemplateType = 11. The value is stored as a Unix epoch in seconds in the Year column. DMP resolves the value at run time before the Compass query runs.

This table shows the available placeholders:

Placeholder Resolved value Example
[LASTIMPORTTIME] ISO 8601 timestamp 2026-02-12T10:52:04.000Z
[LASTIMPORTEPOC] Unix epoch in seconds 1739357524
[LASTIMPORTDATE] Date value 2026-02-12

Recommended usage:

  • Use [LASTIMPORTTIME] to filter standard timestamp columns.
  • Use [LASTIMPORTEPOC] to filter numeric epoch-based columns.
  • Use [LASTIMPORTDATE] when date-only precision is sufficient.

During the first import, no previous import timestamp exists. In this case, the placeholders resolve to epoch zero, 1970-01-01, so DMP imports all matching records.

Examples

Example 1: Importing item descriptions from MITMAS

The objective is to synchronize item descriptions (ITDS) into a DMP attribute table using ITNO as the unique identifier.

Full Import Query

SELECT ITNO, ITDS, STAT, ITGR
FROM MITMAS
WHERE CONO = 780

Partial Import Query

SELECT ITNO, ITDS, STAT, ITGR
FROM MITMAS
WHERE CONO = 780
AND timestamp > [LASTIMPORTTIME]

We recommend to clear the Reset Table check box, and use IMP_METHOD_UPDATE to update existing records by ITNO.

Example 2: Importing SUNO or MABC from MITBAL

The objective is to import Supplier Number (SUNO) or ABC Classification (MABC) into DMP text keys or extra keys by using WHLO and ITNO as the composite key.

Full Import Query

SELECT WHLO, ITNO, SUNO, MABC
FROM MITBAL
WHERE CONO = 780

Partial Import Query

SELECT WHLO, ITNO, SUNO, MABC
FROM MITBAL
WHERE CONO = 780
AND timestamp > [LASTIMPORTTIME]

For the Extra Keys or Text Keys configuration, we recommend to map WHLO and ITNO as K1/K2 match fields, and use Update mode to avoid duplicate keys.

Server workflow and scheduled tasks

Import Data Lake supports interactive workflow runs and server-based scheduled processing. When you use Partial Import in a server workflow, the application service automatically resolves timestamp placeholders from the stored ImportDataId. No additional configuration is required.