Skip to main content

How ETL Implements Incremental Updates

Background

Currently, BI Smart ETL cannot directly implement incremental dataset updates. Each ETL run overwrites the full dataset. As the data volume grows, ETL performance deteriorates, and the total runtime of ETL jobs across the system becomes longer. If BI Smart ETL can implement incremental data updates, this issue can be resolved.

Solution

In this solution, historical data uses a monthly cycle. The stock data ETL updates all data before the current month at 18:00 on the 2nd day of each month. The incremental data ETL updates current-month data every early morning. On the 1st and 2nd day, it updates data for the current month and the previous month. From the 3rd day onward, it updates only current-month data.

Overall ETL Layered Design

  1. The DW-layer ETL is divided into DW-sample table (before current month) and DW-sample table (current month). Because the DW layer is not used by cards, no merged dataset is needed.

  2. The DM-layer ETL is divided into DM-sample table (before current month) and DM-sample table. The latter merges incremental DM data with DM data before the current month.

Detailed ETL Design

DW-sample table (before current month)

-- Ensure that the retrieved data is before the current month.
SELECT * FROM  input1 WHERE  `创建时间`<  DATE_TRUNC('month',CURRENT_DATE())

DW-sample table (current month)

-- "-2" ensures that previous-month data remains in the incremental dataset in the early morning of the 1st and 2nd, because the historical dataset is updated on the evening of the 2nd.
SELECT * FROM input1 WHERE `创建时间`>= DATE_TRUNC('month',CURRENT_DATE())

DM-sample table (before current month)

Directly use DW-sample table (before current month) as the DW-layer dataset and generate the corresponding DM.

image.png

DM-sample table

Append rows to merge the incrementally updated DM for the current month and the DM before the current month into a complete DM dataset that can be used directly by the business.

image.png

Business Value

With incremental updates, ETL performance improves by 3-5 times compared with full updates. The larger the stock data volume and the more complex the ETL, the greater the performance improvement. If BI optimizes row append performance in the future, performance can improve further.