Configuring Incremental Sync Orchestration Pipeline
Set up a Matillion orchestration pipeline to load only new or changed records since the last sync from Salesforce.
An incremental sync pipeline loads only new or changed records since the last sync. It adds two components before the Salesforce Load step.
Pipeline steps
1. Define a pipeline variable named LastModifiedDate. Use a Query Result To Scalar component in advanced mode to query the maximum LastModifiedDate value from the raw table and store it in this variable.
2. Configure the Salesforce Load component with Incremental Load as the load type. Select the LastModifiedDate field as the High-Water Mark Selection.
3. Run the incremental transformation pipeline. Pass the LastModifiedDate variable to the pipeline. The transformation pipeline uses a Filter component to retain only records whose LastModifiedDate exceeds the stored value.
4. Run the same stored procedures as the full sync pipeline.
Create a schedule for the incremental sync pipeline to run periodically. Navigate to the pipeline schedules configuration in Matillion and define the desired frequency.