Incremental Data Updates and Synchronization

  • Last update: June 20, 2025
  • Overview

    Application Scenario

    This document applies to scenarios where new data is generated. For details about modified or deleted data, see Data Comparison Example.

    For more details about data synchronization schemes, see Overview of Data Synchronization Schemes.

    Scheme Introduction

    During data synchronization, sometimes you need to perform incremental data updates periodically to ensure data timeliness. For example, update new data (in the source table) to the target table every day.

    Incremental data updates can be divided into three types based on the actual data details:

    Scenario
    Update Method

    The source table has a timestamp field.

    Timed synchronization (see the "The Source Table Having a Timestamp Field" section)

    The source table does not have a timestamp field but has a unique identifier.

    Timed synchronization (see the "The Source Table Having No Timestamp Field but Having a Unique Identifier" section)

    The source table has neither a timestamp field nor a unique identifier.

    Real-time synchronization (see the  "The Source Table Having Neither Timestamp Field nor Unique Identifier" section)

    The Source Table Having a Timestamp Field

    If the source table has a timestamp field, you can add a Parameter Assignment node to obtain the maximum timestamp value in the target table, and then add a Data Synchronization node to obtain incremental data for updates.

    Example

    The source table is DEMO_ODS_Contract, and the target table is DW_Contract, both containing the timestamp field Order_Creation_Time, as shown in the following figure.

    2.1.png

    If the latest time of the Order_Creation_Time field in the target table is earlier than that in the source table, it indicates that new data is generated in the source table and needs to be synchronized to the target table.

    Obtaining the Latest Time in the Target Table

    Add a Parameter Assignment node to extract the maximum value of the Order_Creation_Time field in the target table and set it as the Time parameter, as shown in the following figure.

    select max(Order_Creation_Time) from [DW_Contract]

    2.2.png

    Updating the New Data to the Target Table

    1. Drag in a Data Synchronization node and connect it to the Parameter Assignment node.

    2. In the Data Synchronization node, extract data from the source table where the value of the Order_Creation_Time field is greater than that of the Time parameter and synchronize it to the target table, as shown in the following figure.

    SELECT * FROM [DEMO_ODS_Contract] where Order_Creation_Time > ${Time}

    2.3-1.png

    3. Since the data to be synchronized is new, it can be appended to the target table directly. To implement it, you can leave the logical primary key unset and set Write Method to Write Data into Target Table Directly, as shown in the following figure.

    iconNote:
    For more details about write methods, see Data Synchronization - Write Method.

    2.3-2.png

    4. You can set the execution frequency of scheduled tasks. For example, if you want to synchronize new data to the target table on a daily basis, you can set it to execute once a day.

    Click the QQ浏览器截图20250402161725.png icon in the upper right corner to set the execution frequency, as shown in the following figure.

    2.3-3.png

    The Source Table Having No Timestamp Field but Having a Unique Identifier

    Applicable scenario: The source table has a primary key or logical primary key, but does not have a timestamp field, based on which incremental updates cannot be performed.

    iconNote:
    You need to modify the SQL statement according to actual scenario.

    Example

    The source table is JDY_Order, and the target table is Order_Target.

    The source table lacks a timestamp field or the timestamp field cannot be used as an update identifier. However, the source table has a logical primary key field Order_ID, as shown in the following figure.

    3.1.png

    Description of the incremental synchronization scheme:

    • Add a Parameter Assignment node to obtain the unique identifier Order_ID in the target table.

    • Add a Data Synchronization node to obtain the corresponding data of the Order_ID field that exists in the source table but not in the target table (that is, the new data to be synchronized), and use the Data Synchronization node to synchronize the data to the target table, thus completing the incremental updates.

    Obtaining the Unique Identifier Field in the Target Table

    Add a Parameter Assignment node to retrieve the unique identifier Order_ID in the target table Order_Target and set Order_ID as a parameter, as shown in the following figure.

    3.2.png

    Updating New Data to the Target Table

    1. Drag in a Data Synchronization node and connect it to the Parameter Assignment node.

    2. In the Data Synchronization node, obtain the corresponding data of the Order_ID field that exists in the source table but not in the target table (that is, the new data to be synchronized), and use the Data Synchronization node to synchronize the data to the target table, thus completing the incremental updates, as shown in the following figure.

    3.3-1.png

    .3. Since the data to be synchronized is new, it can be appended to the target table directly. To implement it, you can leave the logical primary key unset and set Write Method to Write Data into Target Table Directly, as shown in the following figure.

    iconNote:
    For more details about write methods, see Data Synchronization - Write Method.

    3.3-2.png

    4. You can set the execution frequency of scheduled tasks. For example, if you want to synchronize new data to the target table on a daily basis, you can set it to execute once a day.

    Click the QQ浏览器截图20250402161725.png icon in the upper right corner to set the execution frequency, as shown in the following figure.

    4.png

    The Source Table Having Neither a Timestamp Field nor a Unique Identifier

    You are advised to use the Data Pipeline function for real-time incremental updates. For details, see Overview of Data Pipeline.

    附件列表


    主题: Best Practices
    Previous
    Next
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy