Four data ETL Modes

There are four data ETL modes based on the model design and source data: Completely refresh, image increment, event increment, Image Comparison

There are four data ETL modes based on the model design and source data:

Completely refresh: Only the latest data is included in the data warehouse data table,The original data is deleted for each load, and the latest source data is fully loaded.. In this mode, the data extraction program extracts all records from the source data, clears the target data table Before loading, and then loads all records. To speed up data deletion, Truncate is generally used to clear data tables. This mode is used for the current information table in this system.

Image Increment:Records in source data are regularly updatedBut the record contains the record time field, and the historical records of the data are saved in the source data, ETL can extract incremental data from the source data and load it to the data warehouse in an additional way. The historical data records are also stored in the data warehouse.

Event Increment: Each record is a new event and there is no inevitable relationship between each other. The new record is not a change to the original record value. The record includes the time field, the new data can be extracted and loaded to the database using the time field.

Image Comparison: The data warehouse data has an effective date field to save the historical information of the data. The source data is not retained and may be updated every day. Therefore, you can only compare the new image data with the image of the last loaded data to find the change part, update the historical data to the effective end date of the updated record, and add the changed data. Dimension tables for storing historical information in most data sources