Update types
Registers will be updated using one of the following update processes:
Full refresh
This truncates all existing records in the register and then loads the entire contents of the source file into the register. This generally means that no history of records is maintained (unless included in the source data).
Type 1
This update method overwrites existing records where there is an update to that record (matching on the key), and therefore does not keep historical data. A type 1 change (where the key of the incoming record already exists in the table) would update the existing record. A new record would be inserted if the key doesn’t already exist in the table.
Type 2
This update method keeps a history of changes to the data. A new row is inserted into the register each time a record is updated or there is a new record. Multiple records can exist for a particular Business Key (this is generally the code column) with the history of records for a particular Business Key being managed using Business/Record Start and End Dates to reflect when the record was active. The current record can be identified as it will have a NULL Business/Record End Date. When a record is being updated, the incoming record matches (on the Business Key) with the most up to date (current) record in the table. The Business/Record End Date of the current record is populated with the Business/Record Start Date of the incoming record minus one day and then the incoming record is inserted into the table. If the Business Key of the incoming record doesn’t match an existing record in the register then it will be inserted as a new record. The majority of our registers are updated using the type 2 update method.
Last edited: 11 October 2022 1:48 pm