Persistent Staging

Overview

Persistent Staging is used in a data migration so that any changes to data is persisted between migrations, and you will be provided with a complete audit log of your persisted data. This means that with each persistent staging migration, when data is changed or modified, Loome Integrate will keep both a record of the time that the data was in a particular state and insert a new data record that will show the current state of the data.

This is facilitated by two views:

View Description
History This view is a history of your data that has been recorded in your database by persistent staging.
Current This view is the current state of your data in your target and this should be the same as the data in the source.

This allows for immutable extract and load. 

You can also use both incremental and persistent staging in a data migration task, but please note that it will take away part of the functionality of persistent staging. Incremental will only load recently modified records. The records that persistent staging will keep a history of will not be persisted unless they are included in the selected incremental staging time period. So if you decide to use persistent staging with incremental staging, you will only persist the data in the time period you have defined.

How to Create a Persistent Staging Task

Create a new task and select Persistent Staging as your task type.

Data migration task type

Select your source and target connectors. For the example in the following image, I have selected SQL Server for the source and target.

Source and Target as SQL Server

Choose the tables that you would like to extract and load into your target using persistent staging. In the image below, I have chosen one table and it has moved into the target section.

To configure your persistent staging task and select your key column, you will have to click on the Persistent Staging button beside your selected table.

Persistent Staging button

Select your key column, which Loome Integrate will use as the unique identifier for persisted records, and then select any ignore columns.

Key column and Ignore columns

Submit the task and you can either run it now or create a schedule that will cause it to run at a time you have chosen.

If we were targeting a schema of TargetSchema and a table of TableName the following objects would be created.
[TargetSchema].[TableNameSource]
  • This is a table that contains all imported records for this task’s persistent staging history
  • If records are modified and then persisted, there will be duplicates of the record with a different PersistSourcesKey value
[TargetSchema].[TableNameCurrent]
  • This is a view that exposes the current state of the source table
  • The results included are all the records in the source table that do not have an end date
[TargetSchema].[TableNameHistory]
  • This is a view that exposes the history of all records in the source table
  • Each record’s history is grouped by its key columns and ordered from oldest to current