Four database ETL Modes

Based on the model design and source data, there are four types of data ETL modes: full refresh, image increment, event increment, and image comparison.
 
Full Refresh: only the latest data is included in the data warehouse data table. The original data is deleted each time the data is loaded, and the latest source data is fully loaded. In this mode, Data ExtractionProgramExtract all records from the source data. Before loading, clear the target data table and load 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 the source data are updated on a regular basis, but the record contains the record time field. Records of historical 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. A dimension table that stores historical information in most data sources.

 

 

 

Techtarget Chinese original content, original article link: http://www.searchdatabase.com.cn/showcontent_9280.htm