Building a Delta Migration Project

This Article was updated for Precision Bridge Version 8.3

In many cases, once the initial migration of records is completed there is a need to re-run the projects again to pick up changes made since the last migration. Of course it would be possible to re-run the original project again, but in many cases there will be a large volume of records and this would take a considerable time to complete. What is needed, is to only migrate changes made since the last migration, so that the projects can be run regularly and complete quickly.

This will require changes only to the source filtering for each mapping and in some cases, you might need to change mappings to ensure records are skipped in certain circumstances.

This article focusses on the ServiceNow target use case, but much of the general advice is applicable to other use cases as well.

Things to Consider

You may have existing source filters on the mappings. If the source data has changed so that it no longer matches the filter, any changes made to this record will not be migrated. For example in the initial run, you only migrated active incidents. Now you want to run a delta migration to bring over any changes to the incidents already on target or new incidents. If you still only migrate active incidents, then any that were migrated in the first run, but are now inactive on source will not be migrated. These incidents will remain out of sync.
In some mappings, you may have used inclusion rules to migrate only 'child' records related to a set of migrated 'parent' records. However in a delta migration, you want to migrate all changed/new child records, regardless of whether the parent was changed. For example, you migrate all incidents (the parent) and all associated incident tasks (the child) using an inclusion filter. Lets say you add a source filter to both parent and child to only migrate records updated since a given date, but keep the inclusion filter in place for incident_task. If an incident task is updated, but the parent incident is not, the inclusion filter will prevent the incident task record from being migrated and it will remain out of sync.

Best Practice Approach to Building Delta Migration Projects

The approach described below is for a Service Now instance migration project (Service Now->Service Now), but can be adapted in most cases for any project.

Building the Delta Projects

Make a copy of the original project you used to migrate the data. Prefix the name with DELTA to make it clear this is a delta migration project.

In the copied project, enable the LAST_EXECUTION_TIMESTAMP variable. More information on this can be found here.

Changes to Source Filters

For each form mapping look at the existing source filter (if there is one). Consider if any part of it needs to remain. For example, if you only want to migrate records for a given domain or company, presumably the delta migration project will need to keep this filter. However if you originally migrated 'active' incidents and now want to migrate changes made to them (plus any new incidents) you will need to remove the 'Active' filter to ensure that records that were migrated initially, but are now inactive are included.

In every form mapping add a source filter using the LAST_EXECUTION_TIMESTAMP variable to the remaining source filter (if any)

See the example below for clarification of how to adjust source filters

Original Filter

Delta Migration Filter

Changing Inclusion Filters to Support Delta Migrations

If the form mapping has an inclusion filter it will usually need to be removed to prevent the possibility that a child record was updated, but its parent was not. The exception to this is where the child is a Journal Entry (i.e. using the #!JOURNAL ENTRY MIGRATOR!# target) In this case, a new journal entry is considered to be a change to the parent record, so the inclusion filter will still pick up changes correctly.

Removing the inclusion filter will open up the possibility of child records being migrated for parents that do not exist on the target, which is not usually desirable. To prevent this, a lookup mapping can be used in the project to check that the parent record exists before migrating the record. See the example below for incident tasks, where we only want to migrate incident tasks for migrated incidents.

First, we change the source filtering and inclusion filter:

Original Filter

Delta Migration Filter

Next, we need to ensure that we do not import tasks for incidents that do not exist on the target, regardless of when they were updated.

Using a Lookup to Check a Referenced Record Exists

To create a lookup mapping to identify the records, define a target lookup (as below) This assumes that sys_ids are being maintained on both the source and target (which is best practice)

Using the Query builder to build the query string

Once this lookup is added, it can be used in place of a simple mapping for the incident field.

The result of this will be to perform a check to make sure the incident record exists already before the incident task is migrated. If it does not, it will be skipped. The reported message will make it clear why it was skipped.

Executing Delta Migrations

Now that the Delta migration has been built, it can be executed. There are a few things to consider here too:

It is generally preferable to migrate out of hours when the source records are unlikely to be changed.
The first time you run your delta migration project, you will need to set the value for the variable LAST_EXECUTION_TIMESTAMP in the execution settings (see below). This should be set to the date or time that the initial migration execution started so as to be sure not to miss any changes made since that point. LAST_EXECUTION_TIMESTAMP must be given a date/time value formatted yyyy-MM-dd HH:mm:ss and will be assumed to be in the UTC timezone.
Each time the delta migration project runs, the LAST_EXECUTION_TIMESTAMP will be updated. This will mean that it will be automatically set ready for the next execution. However you are still free to edit the value if you wish - if, for example, you want to re-run to correct any errors.