Staging with Change Tracking

**** NOT COMPLETE ****

There are 2 use cases for using the Change Tracking pattern:

Change Tracking existed on the table before any data was inserted into the table:

• In this scenario just create the extract with the change tracking pattern and leave the DmlVers Change Identifier initial value as 0. The initial run will pick up all data in the source table.

Change Tracking was added to the table after there was some data added to the table.

• Change tracking only captures changes after change tracking is set on for the table, so all data inserted into the table prior to change tracking set on will not be extracted using this pattern.
• In this case, it necessary to first define the extract as a full extract. Generate, Deploy and run it as a Full extract, and then later change the pattern to ‘Change Tracking’.
• Prior to changing the pattern to ‘change Tracking’, but after you have run at least one full extract, execute the following SQL in the source database:

o    SELECT CHANGE_TRACKING_CURRENT_VERSION(). Note down this value, you will use it as the initial value of the DmlVers change identifier when you change to the ‘Change Tracking’ pattern.

• Run a Full Extract. This will ensure the persistent staging database is up to date.
• Change the pattern to ‘change tracking’ and set the value of the DmlVers change identifier to the value return by the SQL above. Note, you should do this as close deployment time as possible. This will reduce the number of ‘duplicate’ row versions in the persistent staging table. Note, only one is marked as latest. The duplicate effectively never existed due to how effective and end dates are set.
• Generate, Deploy and Execute the Extract with the ‘Change Tracking’ pattern.
• This will ensure you get all rows and their changes as required.