Loading Source Data Incrementally

Incremental data loading is considered as a best practice in data modelling. If a database contains large amount of data from various sources that are continuously updated, reloading the entire data set can be time consuming.

The Load source data incrementally option in the Inputs tab helps in loading only the new and updated records for the selected source with incremental load.

• If the incoming data set includes no updates to the existing data, append incremental pipelines can be built. The pipeline is designed such that the append target obtains only the data to be added to an existing target.
• If the incoming data set includes updates to the existing data, merge incremental pipelines can be built. The pipeline is designed such that the merge target obtains only the data to be inserted or updated to an existing target.
• If the incoming data set includes updates to the existing data which may include updates in one or more of the natural keys or the table is being updated in SCD2 manner or there are no unique row identifiers, insert-overwrite incremental pipelines can be built. The pipeline is designed such that the target obtains only the data to be inserted or updated to an existing target.

A sample pipeline is explained below:

For example, ORDERS is a source where only new data is added and ORDER_DETAILS is a dimension table where the records do not get updated. A target with sync mode set to Append can be added to this pipeline to speed up the pipeline build.

If there are updates on ORDERS, a target with the sync mode set to Merge can be added to speed up the build.