For Example how to generate the Order_History data through Order_Current table.
ETL Rule:
1. Incremental data load
2. Loading: Never truncate the history table, follow the following rules.
3. A new field: action_flag is added in history table. with default value as 'I' (Insert)
4. When doing the data loading, we need to compare the records from source with those records whose action_flag is 'I' to decide if then need to be inserted or skipped:
a. If they are different, then do insert with action flag 'I'
b. If they are the same, then do nothing.
5. It needs to be changed to 'U'(Updated) when the records are updated in the source and then insert a new one into the history table with action_flag 'I'.
6. It need to be changed to 'R'(Removed) when the records as somehow deleted in the source. We should not insert a new one with action_flag 'I'.
7. Another new field, 'new_status_date' is added, with the default value as sysdate.
8. Whenever the action_flag is change from 'I' to 'U' or 'R', the new_status_date is udpated with the sysdate.
ETL Rule:
1. Incremental data load
2. Loading: Never truncate the history table, follow the following rules.
3. A new field: action_flag is added in history table. with default value as 'I' (Insert)
4. When doing the data loading, we need to compare the records from source with those records whose action_flag is 'I' to decide if then need to be inserted or skipped:
a. If they are different, then do insert with action flag 'I'
b. If they are the same, then do nothing.
5. It needs to be changed to 'U'(Updated) when the records are updated in the source and then insert a new one into the history table with action_flag 'I'.
6. It need to be changed to 'R'(Removed) when the records as somehow deleted in the source. We should not insert a new one with action_flag 'I'.
7. Another new field, 'new_status_date' is added, with the default value as sysdate.
8. Whenever the action_flag is change from 'I' to 'U' or 'R', the new_status_date is udpated with the sysdate.
