ServiceNow Data Migration and Import Sets Integration Patterns

Integration patterns for Data Migration and Import Sets are about how data moves and is checked, not just where it lands: staging raw data before transformation, cleansing and deduplicating it, sequencing loads so lookups resolve, reconciling against the source, and phasing the migration so it can be rolled back. This page takes those five patterns in turn, then the pitfalls and the post-migration checks.

Pattern 1: Staged Table Approach

Extract data from the legacy system into a temporary staging table in ServiceNow. The data is raw and unmodified. Transform scripts read from the staging table, cleanse and enrich the data, and write to a second staging table. Load scripts read from the transformed staging table and insert into production tables.

Benefits: staging tables are read-only backups (if something goes wrong, you can restart from the last successful staging load); transformation and load are separate (you can fix transformation logic without re-extracting from the legacy system); and you can run multiple test cycles before going live.

Example: extract 100,000 incidents from the legacy system into stg_incident_raw. Run transformation: convert date formats, populate missing fields with defaults, deduplicate on incident number. Load into incident table after a full reconciliation run.

Pattern 2: Cleansing and Deduplication

Migrated data is often dirty: duplicates exist (the legacy system had poor controls and customers got created twice), fields are incomplete (a date field is null in half the records), and references are broken (a customer lookup points to a customer ID that doesn’t exist).

Build cleansing logic into Import Sets: standardize phone number format (remove dashes and parentheses), convert null values to defaults (set status to “draft” if not provided), and validate lookups (if a customer ID is provided, verify it exists in the customer table). Flag records that fail validation. These become a manual review list: someone decides whether to drop the record, fix it, or escalate.

For duplicates, run deduplication rules: if two records have the same name and email, merge them. Keep the record with more complete data and discard the other. Test deduplication rules on a sample (1,000 records) before running on the full dataset.

Pattern 3: Sequencing and Dependencies

Records have dependencies: an incident references a customer, the customer must exist before the incident can be created. If you try to create incidents before customers, foreign key constraints will fail.

Sequence imports: customers first, then locations, then configurations, then incidents. If import sequence matters, document it and test it. A common mistake: importing incidents before all referenced customers exist; imports fail silently or with cryptic errors.

Identify circular dependencies: an organization table references a parent organization (reporting line), which requires organizations to be imported in a specific order (parents before children). ServiceNow’s hierarchical imports can handle this, but it requires careful setup.

Pattern 4: Reconciliation and Validation

After loading data into production, reconcile against the source system. Count records: the legacy system has 100,000 incidents; ServiceNow should have 100,000 incidents (allowing for records marked for exclusion). Sample records: pick 100 random incidents from ServiceNow and verify the data matches the source.

Run data quality checks: no incidents without a customer (null foreign key), no incidents with future close dates, no blank descriptions. Spot-check: are critical fields populated correctly? Are lookups working (incidents reference valid customers)? Are calculated fields correct (priority derived from impact and urgency)?

If reconciliation fails: stop the load, investigate, fix the data or the transformation logic, and reload. Do not proceed to production with data integrity issues; they compound over time and become very expensive to fix later.

Pattern 5: Phased Migration and Rollback

Migrating 5 million records at once is risky. A better approach: migrate a pilot group first (one department’s incidents), validate, and expand. If the pilot is successful, migrate another group. If there are issues, they are caught at a smaller scale.

Pilot usually runs for one week in production. If issues are discovered, roll back: restore from backup and fix the transformation logic. Rollback should be planned and practiced beforehand; surprises during rollback are painful.

After pilot validation, full migration runs during a maintenance window (after hours, with no users in the system). Migrate, run reconciliation, validate, and notify users the system is ready.

Common Migration Pitfalls

Assuming data quality in the source system. Legacy systems often have dirty data; cleansing is essential, not optional. Budget a significant proportion of migration effort for cleansing and validation.

Not testing with production-like data volume. A transformation script that works fine on 1,000 records may run for hours on 5 million. Test with realistic volume and measure performance; optimize before going live.

Migrating too much data. Not all legacy data is valuable. Incident records from 10 years ago may not be needed; they consume storage and clutter reports. Decide on a cutoff date and only migrate recent data. Your reports and searches will be faster, and users won’t be confused by ancient history.

Failing to document the mapping. Document what fields in the legacy system map to what fields in ServiceNow, what transformations were applied, and what records were excluded and why. A year later, when someone asks why a customer is missing from the migration, the documentation will answer the question.

Post-Migration Validation and Support

After migration goes live, stay alert. Users will report anomalies: “This customer’s contact info is wrong,” “This incident’s history is missing,” “This linked change is gone.” Some anomalies are data quality issues in the legacy system, not migration bugs. Others are true migration problems.

Establish a “migration support window” where the migration team is available to investigate and fix issues, for as long as the issue backlog after go-live requires. Issue severity determines fix approach: critical (data is corrupted, queries return wrong results) get immediate attention; medium (a field is missing, a lookup is wrong) get fixed in batches; low (cosmetic inconsistencies) may be acknowledged but not fixed.

Automate repeatable checks: run reconciliation queries weekly for the first month, monthly thereafter. Flag any divergence (legacy system changed, but ServiceNow data didn’t, suggesting records should have been migrated but weren’t).

Choosing the feed before choosing the pattern

Integration patterns for data migration are often discussed as if the question were how to move rows. The more important question is what kind of feed the source can provide. A source that exports nightly files, a source that exposes a stable query interface, and a source that can only produce a one-off dump each need a different design, even when the target import mechanism is identical. Describe the feed first, then pick the pattern that fits it.

What the feed decision changes downstream

Each pattern changes where errors are found. A one-off file surfaces problems in the first load, which is clear but late if the file is wrong. A paged pull can fail halfway through and leave the staging table partly filled, so the load must know how to resume without duplicating rows. A delta feed moves the hardest problem to the source system’s change tracking, because a record deleted there but never sent as a deletion can sit in the platform indefinitely. Choosing the pattern is therefore a decision about where the team will look when something is wrong.

A checklist for any feed that will run more than once

1. Record how the source identifies each row, and confirm that identifier never changes after creation.
2. Decide how deletions and retirements are represented, and test one of each.
3. Make the load resumable, so that a failure partway through does not require a full reload.
4. Stamp each staging batch with its source timestamp so the reconciliation can compare like with like.
5. Give the feed an owner on each side, so that a silent change in the source has someone to notify.

Questions about integration patterns

Is a file always safer than an API for migration?

Not necessarily. A file is easy to inspect and rerun, which makes it a good choice for a one-time load. An API can be better for an ongoing feed because it can return only changed rows and report errors per request. The safer choice is the one whose failure modes the team can detect and recover from without help.

Where should the transform logic live if middleware is involved?

Keep business-meaning transforms on the platform side, where the platform owner can read and test them, and keep only format normalization in the middleware. Logic split across both layers is hard to test, and changes to one layer tend to break the other without anyone noticing.

Can a delta feed replace the full reload entirely?

Rarely. A full reload is the most reliable way to correct drift and to reconcile against the source’s current state. Plan to run a full reconciliation periodically even when the delta feed is working well.

Next step

The feed decision depends on how the legacy data is built and how it will be reconciled after each run. Staged rows are shaped before they reach the target, a step covered in the import set and transform map design post. Automating the delta feed itself is covered in the automation opportunities guide, and ownership of each source in the data source governance post.