Skip to main content

Migration Strategy

James Herring
Updated

Choosing the right migration strategy before you start building procedures saves significant rework later. This article covers how to plan migration order, handle reference fields, and decide between full and incremental approaches.

Planning Migration Order

The core rule for data migration is: always migrate parent records before child records. Child records reference parents by ID. If the parent does not exist in the target when the child is inserted, the reference field cannot resolve and is either left empty or rejected by the target.

Migration order is expressed as the order of Migrate Records steps within a procedure. A typical step ordering for a ServiceNow-to-ServiceNow migration:

  1. Users — referenced by assigned_to, caller, created_by, and similar fields on almost every other record.
  2. Groups — referenced by assignment rules and escalation records.
  3. Categories / Configuration Items — lookup data used by incident and change records.
  4. Incidents / Requests / Changes — transactional records that reference all of the above.
  5. Journal Entries / Work Notes — child records attached to incidents and other tables.

This ordering applies broadly across systems. When migrating from Jira, the equivalent step order is: Users, Projects, Components, Issues, Comments.

Attachments aren't a separate step in this order — enable the Attachment Migration toggle on the Migrate Records step that owns the parent record, and PB migrates attachments alongside the record itself. See Advanced Options.

Why Order Matters

When Precision Bridge migrates a child record, it maps the source parent ID to the corresponding target parent ID using a Table Correlation or a formula-based lookup. If the parent was never migrated, no correlation entry exists and the mapping fails.

For example, if the Migrate Incidents step runs before the Migrate Users step, the assigned_to field on each incident cannot be resolved because the user correlation table is empty. The result is either blank assignment fields or outright errors, depending on your validation configuration.

Handling Reference Fields

Precision Bridge offers two approaches for mapping source IDs to target IDs:

A Table Correlation is a configured matching rule between a source table and a target table — a query that identifies which target record corresponds to a given source record. During a procedure run, PB builds an in-memory cache of source-to-target ID pairs for the correlation: pairs are written as the Migrate Records step inserts or updates records, and supplemented on demand by running the correlation's query against the target system whenever a downstream step looks up an ID that isn't already cached.

Advantages:

  • Reusable on re-run — On a re-run the cache starts empty, but the correlation's query continues to find the same target records (assuming they still exist and the matching fields haven't changed), so re-runs update existing records rather than creating duplicates.
  • Reusable across steps and procedures — Any step or procedure in the project can reference the same correlation by name.
  • Clean field mappings — Reference fields use a simple correlation lookup rather than complex formulas.
  • Supports many reference fields — Scales well when dozens of fields reference the same parent table.

The in-memory cache itself does not survive across runs or application restarts. What makes correlations re-runnable is the configured query, not stored state.

Use Table Correlations whenever you expect to re-run a migration, or whenever multiple steps need to translate IDs against the same parent table.

Formula-Based ID Maps (Simpler for One-Off Migrations)

For smaller or one-off migrations, you can use an Extract Records step to build a dictionary in an output variable, then reference that dictionary in field mapping formulas.

Advantages:

  • Simpler setup — No correlation configuration required.
  • Self-contained — Everything lives within a single procedure.
  • Good for one-off migrations — When you will not re-run or reuse the mapping.

The tradeoff is that formula-based maps are rebuilt on every run and cannot be shared across procedures without passing variables explicitly.

Incremental vs. Full Migration

Full Migration

A full migration extracts all records from the source system on every run. Key mappings determine whether each record is inserted (new) or updated (existing) in the target.

Use full migration when:

  • This is a one-time migration with no ongoing synchronization.
  • The source dataset is small enough to extract completely in a reasonable time.
  • You need to ensure the target is a complete mirror of the source.

Incremental Migration (Delta Migration)

An incremental or delta migration uses date-based filters to extract only records created or modified since the last run. This is significantly faster for ongoing synchronization.

Every procedure has two automatically-maintained system variables that exist precisely to support this pattern. They are visible at the top of the procedure's Variables panel and require no manual setup:

  • LAST_EXECUTION_TIMESTAMP — the start time of the previous completed run of this procedure, as a timezone-aware UTC datetime. Updated automatically at the end of each successful run. Empty on the first run.
  • CURRENT_EXECUTION_TIMESTAMP — the start time of the current run, set when execution begins. Carried for the duration of the run so steps later in the procedure can reference the same instant.

For the common case — "pick up everything that changed since the previous run" — reference LAST_EXECUTION_TIMESTAMP directly in your extraction filter. No procedure input variable, no external persistence, no Assign Variables step.

  1. On the Migrate Records (or Extract Records) step, add a filter on the source's "last modified" field.
  2. Reference LAST_EXECUTION_TIMESTAMP as the comparison value: [sys_updated_on] > LAST_EXECUTION_TIMESTAMP.
  3. Run the procedure once with the filter omitted (or against a full extract) to seed the target, then enable the filter for subsequent runs. The first scheduled run will use the previous run's start time as the cutoff.

For ServiceNow, JIRA, and other systems where the "last updated" field is timezone-aware, no conversion is needed — both sides are UTC. For systems with naive datetimes, see Available Built-in Functions — Working with the current date and time for the timezone-handling pattern.

First-Run Handling

LAST_EXECUTION_TIMESTAMP is empty (None) on the very first run. If you want the first run to still execute (rather than fall through with an empty filter), use one of:

  • Filter expression with a fallback: [sys_updated_on] > (LAST_EXECUTION_TIMESTAMP or datetime(2000, 1, 1, tzinfo=timezone.utc)) — treats a missing prior run as "everything since the year 2000".
  • Override on the first run: open the procedure, toggle the Override switch on LAST_EXECUTION_TIMESTAMP, and pick a concrete starting cutoff. After the first run, disable the override so subsequent runs pick up the automatically-maintained value.

Override for Re-runs

The Override switch on LAST_EXECUTION_TIMESTAMP is also the right mechanism for one-off re-runs from a specific point in time — for example, after a target-side issue where you need to replay the last 48 hours. Toggle override on, pick a past execution from the combobox (or Custom Date Time Value for an arbitrary cutoff), run the procedure, then toggle override off so the next scheduled run resumes from CURRENT_EXECUTION_TIMESTAMP.

When to Use a Custom Variable Instead

A procedure input variable (e.g. last_sync_date) is still the right tool when:

  • The cutoff lives outside the procedure — for example, you read it from a target record, a correlation table, or a CSV file produced by an upstream system.
  • You want the same procedure to support both delta and full re-runs by passing different cutoffs externally.
  • Multiple procedures share a single cutoff that you want to centralise.

Procedure variables reset to their defaults at the start of every run, so updating last_sync_date mid-procedure via an Assign Variables step does not carry over. For that pattern, persist the cutoff explicitly (write to a target record or CSV file at the end of the procedure, load it back via an Extract Records step at the start of the next run).

Use incremental migration when:

  • You are performing ongoing synchronization between systems.
  • The source dataset is large and full extraction is too slow for regular runs.
  • You only need to capture changes, not rebuild the entire dataset.

Handling Deletes

Precision Bridge focuses on inserting and updating records. It does not automatically delete records from the target that no longer exist in the source.

If you need to handle deletes:

  1. Use an Extract Records step to pull current source IDs.
  2. Compare against existing target records to identify records present in the target but absent from the source.
  3. Use a separate procedure or manual process to handle the deletions, with appropriate review and approval.

Rollback Considerations

Data migrations carry risk. Protect yourself with these practices:

  • Never modify the source system. Precision Bridge reads from the source and writes to the target. Keep the source as your system of record.
  • Test in dev or staging first. Run the full migration against a non-production target before touching production.
  • Document the migration plan. Record the expected order, record counts, and validation criteria before executing.
  • Take target backups. If the target system supports snapshots or backups, take one before each production migration run.
  • Use key mappings. With key mappings configured, re-running a migration updates existing records rather than creating duplicates, making recovery from partial failures straightforward.

Summary

  • Migrate parent tables before child tables to ensure reference fields resolve correctly.
  • Use Table Correlations for complex, multi-procedure projects; use formula-based maps for simple, one-off migrations.
  • Choose full migration for one-time moves and incremental migration for ongoing synchronization.
  • Plan for rollback by testing in non-production environments and keeping the source system unchanged.

Related articles

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk