Concepts → Incremental Load

Overview

Incremental load is one of several data load strategies in Incorta. Unlike a full load, which re-fetches all data, an incremental load retrieves only new or updated records from the source, preserving unchanged existing data and significantly reducing load times.

Incremental load jobs can run on demand or as a scheduled job. They can target an entire physical schema, a single object, or multiple objects. However, incremental load jobs can be scheduled for one or more physical schemas or objects.

Prerequisites

Incremental loading can be enabled for physical schema tables and materialized views (MVs). Requirements vary by object type and connector.

Query-based objects

For MVs and tables using query-based connectors, such as MySQL, SQL Server, Oracle, and IBM DB2, you must define an Update Query (or Incremental Script for MVs) that filters records using a timestamp, date, or numeric (LONG or INTEGER) column. Both the full load and incremental queries must return the same set of columns.

File system and data file connectors

For file-based connectors, such as local files, Google Drive, Box, and OneDrive, you provide a separate Update File for incremental jobs when loading data from a single file. Both the full load file and the update file must contain identical column names (including letter case). Otherwise, the Loader Server will extract data from the update file if the column name matches a table column and will ignore data in other columns.

Data lake connectors

For data lake connectors, such as AWS S3, FTP, and Google Cloud Storage (GCS), you provide a separate Update File/Update Worksheet for incremental jobs loading data from a single file. Both the full load file and the update file must contain identical column names (including letter case).

However, incremental loads of directories do not require an update file. You can use either Last Successful Extract Time or Timestamp in File Name as the incremental reference. Accordingly, for the latter strategy (Timestamp in File Name), file names in the selected directory must contain a specific timestamp format.

Key columns (Recommended)

Defining one or more key columns on a table or MV is strongly recommended. Without key columns, the Loader Service appends all matched records on each incremental run, which can result in duplicates. With key columns defined, the Loader Service performs deduplication, appending new records and updating existing ones.

Incremental load strategies

The strategy you can use depends on the object type and connector. The table below summarizes availability:

Update file strategy

This strategy is available for local files, file system, and data lake connectors when extracting data from a single file. In this strategy, you specify an update file or worksheet that the Loader Service extracts data from while loading data incrementally. The Loader Service extracts all data from the update file or worksheet, and then a deduplication process removes duplicate rows.

Last successful extract time strategy

This is the default strategy for query-based physical schema tables. Additionally, you can use this strategy when loading data from a directory using a file-based connector.

Query-based tables

The Loader Service replaces the ? placeholder in the Update Query with the timestamp of starting the last successful extraction, then retrieves records where the source timestamp column matches the condition.

Example – Update Query:

Copy
SELECT * FROM Sales.invoices WHERE Sales.invoices.LAST_UPDATE_DATE > ?

Directory-based tables

The Loader Service uses the timestamp of starting the last successful extraction as the starting point for the next incremental load. For example, given a /path/to/sales directory, an initial full load extracts all files in this directory. When new files are added, their last modified timestamps are compared against the previous extraction time, and only newer files are extracted in the next incremental load.

Timestamp in File Name strategy

The Loader Service determines which files to extract based on a timestamp embedded in the file name. For example, given files named sales_2026-04-01.parquet through sales_2026-04-03.parquet, an initial full load extracts all files. When sales_2026-04-04.parquet is added, its name timestamp is greater than those already extracted, so only that file is extracted in the next incremental load.

Maximum value of a column strategy

The Loader Service compares the maximum value of the designated Incremental Column in the Incorta object to the corresponding column in the source, retrieving only records that satisfy the condition, usually exceeding the maximum value. This column can be a timestamp, date, or numeric (LONG or INTEGER) type, and must be included in both the full load and update queries.

Example — Physical Schema Table Update Query:

Copy
SELECT CustomerID, TerritoryID, AccountNumber, CustomerType, ModifiedDate
FROM OnlineStore.customer
WHERE CustomerID > ?

Example — MV Incremental Script (Spark Python):

Copy
df = read("Sales.invoices")
df = df.filter("ModifiedDate > ?")
save(df)

Last successful transform time strategy

This is the default strategy for MVs. The ? placeholder in the Incremental Script is resolved to the MV's last successful transformation start time.

Example — MV Incremental Script (Spark Python):

Copy
df = read("Sales.invoices")
df = df.filter("ModifiedDate > ?")
save(df)

Log-based strategy

The log-based incremental load strategy uses Debezium to capture data changes (CDC) from the source transaction logs and streams inserts, updates, and deletes to Incorta via Apache Kafka, enabling efficient, near-real-time synchronization without querying source tables.

For more details, refer to Concepts → Log-Based Incremental Load.

Incremental load job life cycle

An incremental load job processes only the changes since the last successful load, moving through three stages: Extraction > Transformation/Enrichment > Load and Post-Load.

Stage 1: Extraction

After extracting new and updated records, new or updated source Parquet files are written to a subdirectory within the object's latest Parquet version directory (established by the most recent full load).

Deduplication & Compaction:

• If key columns exist, a deduplication process flags duplicate rows.
• If Always Compact is enabled (in CMC > Tenant Configurations > Data Loading), a compaction job runs to remove duplicates and writes compacted files to the object's _rewritten directory.
• The compaction process then generates Delta Lake metadata files that point consumers (MVs, Spark, Notebook services, Preview data) to the correct Parquet file versions.

Stage 2: Transformation / Enrichment (MVs only)

Once all physical schema tables are extracted, MV transformation begins:

• Spark reads from the underlying objects' compacted Parquet files and writes new MV Parquet files into a subdirectory under the MV's latest version directory.
• If compaction is enabled, a compacted version is also written to the MV's _rewritten directory.

Stage 3: Load & Post-Load

For objects with performance optimization enabled:

• The Loader Service loads data into the Engine memory.
• The Engine calculates formula columns, key columns, and load filters, then generates snapshot DDM files saved to the schema's ddm directory.
• For objects involved in join relationships as a child table, the Engine creates updated join DDM files in the ddm/joins directory.