ServiceNow Transform Maps and Data Pills: The Definitive Guide to Clean Data Migrations

Every ServiceNow implementation eventually needs to move data in from another system — whether it's a legacy CMDB, an HRIS platform, a product catalog, or a customer list. Import Sets and Transform Maps are how you do that cleanly. But if you've ever run an import only to find thousands of duplicate records, mangled field values, or records that silently failed to transform, you know the gap between "it ran" and "it ran correctly" can be enormous.

This guide covers the patterns that actually work in production: Transform Map configuration, Coalesce strategy, Scripted Transforms for complex logic, and how Data Pills can make your import stack maintainable as it grows.

How Import Sets and Transform Maps Work Together

Before diving into patterns, let's clarify the architecture, because mixing these concepts up causes most of the problems I see in the wild.

Import Sets are staging tables. When data comes in — via Web Service Import, CSV Upload, or any other method — it lands in an Import Set table (u_import_set_table_name) first. The data sits there in raw form, untransformed. This is the buffer. It lets you inspect exactly what came in before anything is written to production tables.

Transform Maps are the rules that govern how raw Import Set records become target table records. Each Transform Map:

1. Links a source Import Set table to a target table
2. Defines field-level mappings (source field → target field)
3. Can apply transformations, Coalesce settings, and scripted logic
4. Runs when you execute the Transform action — either manually, via scheduled job, or via script

The flow is always: Raw Data → Import Set Table → Transform → Target Table

Setting Up a Transform Map: The Foundation

Start by creating an Import Set and a Transform Map from System Import Sets → Administration:

1. Create a new Import Set and note the source table name (e.g., u_hris_employee_import)
2. Set up your source (CSV upload, Web Service, MID Server, etc.)
3. Run the import to populate the staging table with raw data
4. Create a Transform Map pointing to your target table (e.g., sys_user)
5. Auto-map fields first (ServiceNow can guess many mappings), then refine

Field Mapping Types

Direct Mapping — simplest case, source field value goes directly to target field:

Format Override — the source data needs formatting before it lands in the target:

The Critical Role of Coalesce

This is the single most important setting in any Transform Map, and it's also the most overlooked.

When you mark a field as Coalesce in your Transform Map, you're telling ServiceNow: "Use this field as the unique identifier for matching incoming records to existing records."

How it works in practice:

Say you're importing employee records from your HRIS every night. You have employee_id as the Coalesce field. The first import creates new sys_user records. The second import comes in — ServiceNow sees employee_id = 'EMP-042' and checks: does a record with that sys_id already exist? It does, so instead of inserting a new record, it updates the existing one.

Without Coalesce, every nightly import creates duplicate records. I've seen instances with 50,000+ duplicate user records because a Transform Map had Coalesce set on the wrong field or not set at all.

Coalesce rules:

• Pick a field (or combination of fields) that uniquely identifies the entity
• Coalesce on sys_id directly only when the source provides genuine sys_id values
• You can set multiple Coalesce fields — ServiceNow uses them in order until a match is found
• If no Coalesce match is found, a new record is inserted

// This is how you set Coalesce via script when creating a Transform Map
var tm = new GlideRecord('sys_import_map');
tm.initialize();
tm.name = 'HRIS Employee Import';
tm.source_table = 'u_hris_employee_import';
tm.target_table = 'sys_user';

// The Coalesce field is set at the field mapping level, not the map level
// In the UI: check the Coalesce checkbox on the employee_id field mapping

Scripted Transforms: When Simple Mappings Aren't Enough

Most real-world migrations involve data that doesn't map cleanly. Dates in different formats. External codes that need to be resolved to internal references. Conditional logic. That's where Scripted Transforms come in.

Field-Level Scripted Transforms

Add a transform script to an individual field mapping to manipulate the value during transform:

// Transform script on the 'department' field mapping
// source field: dept_code (e.g., "FIN-001")
// target field: department (reference to cmn_department)

(function transformValue(sourceValue, target, sourceRow) {
    // Map external department codes to internal sys_id
    var deptMap = {
        'FIN-001': '1234567890abcdef1234567890abcdef',  // Finance
        'HR-002':  'abcdef1234567890abcdef1234567890', // Human Resources
        'ENG-003': '99abcdef1234567890abcdef123456789', // Engineering
        'OPS-004': 'aabbccdd1234567890abcdef123456789'  // Operations
    };

    var mappedSysId = deptMap[sourceValue];
    if (mappedSysId) {
        // Return the sys_id string for the reference field
        return mappedSysId;
    }

    // If no mapping found, return null — or log a warning
    gs.warn('TransformMap: Unmapped department code: ' + sourceValue);
    return null;
})(sourceValue, target, sourceRow);

Coalesce + Scripted Transform: The Complete Pattern

Here's a real-world pattern for a nightly employee sync that handles inserts, updates, and graceful handling of missing references:

// Full field mapping configuration for employee_number field
// Field: employee_number → user_name
// Coalesce: YES
// Scripted: YES

(function transformValue(sourceValue, target, sourceRow) {
    // Normalize: strip whitespace, force uppercase
    var normalized = (sourceValue || '').trim().toUpperCase();

    // Validate format (e.g., EMP-XXXX)
    var validFormat = /^EMP-\d{4,6}$/.test(normalized);
    if (!validFormat) {
        gs.warn('TransformMap: Invalid employee number format: ' + sourceValue);
        return null;
    }

    return normalized;
})(sourceValue, target, sourceRow);

Row-Level Scripted Transform (onComplete)

For complex logic that needs to run after all field mappings are complete — like setting related records, triggering workflows, or cross-table validation — use the onComplete script on the Transform Map:

// Transform Map: onComplete script
// Runs after all field mappings for a single row are processed

(function onComplete(sourceRow, target) {
    // target is the newly created/updated record (GlideRecord)

    // Set manager reference based on imported manager employee number
    if (sourceRow.manager_emp_num) {
        var mgr = new GlideRecord('sys_user');
        mgr.addQuery('user_name', sourceRow.manager_emp_num.trim().toUpperCase());
        mgr.query();

        if (mgr.next()) {
            target.manager = mgr.sys_id;
        }
    }

    // Set user roles based on department
    if (sourceRow.department) {
        var roleMap = {
            'FIN-001': ['fin_analyst', 'itil_user'],
            'HR-002':  ['hr_specialist', 'itil_user'],
            'ENG-003': ['developer', 'itil_user'],
            'OPS-004': ['operations', 'itil_user']
        };

        var roles = roleMap[sourceRow.department];
        if (roles) {
            for (var i = 0; i < roles.length; i++) {
                var ur = new GlideRecord('sys_user_has_role');
                ur.initialize();
                ur.user = target.sys_id;
                ur.role = roles[i];
                ur.insert();
            }
        }
    }

    // Update the target record
    target.setWorkflow(false); // Skip business rules if needed
    target.update();

})(sourceRow, target);

Data Pills: Reusable Transformation Logic

Data Pills are one of ServiceNow's most underrated features for anyone doing repeated integrations. A Data Pill encapsulates a transformation function — say, normalizing a date format or mapping a country code — and makes it reusable across any Transform Map that uses the same source field.

When to use Data Pills:

• The same transformation logic is needed across multiple import sets
• A source system sends data in a format that changes over time (you update the pill, all maps using it update)
• You want to version-control your transformation logic separately from individual Transform Maps

Creating a Data Pill:

Navigate to System Import Sets → Transform → Data Pills:

// Data Pill: NormalizePhoneNumber
// Purpose: Strips non-numeric characters, ensures country code prefix

(function normalizePhone(phoneValue) {
    if (!phoneValue) return null;

    // Strip all non-numeric characters
    var cleaned = phoneValue.replace(/[^0-9]/g, '');

    // If it's a local Australian number without country code, add +61
    if (cleaned.length === 9) {
        cleaned = '61' + cleaned;
    }

    // Add + prefix if not present
    if (cleaned.charAt(0) !== '+') {
        cleaned = '+' + cleaned;
    }

    return cleaned.substring(0, 16); // Limit to reasonable phone length
});

Attaching a Data Pill to a Transform Map field:

1. Open the Transform Map
2. Click the field mapping row
3. In the field properties panel, find "Data Pill"
4. Select the Data Pill you created
5. The pill's function runs before the field is written to the target table

This means if your phone number format changes in the source system in six months, you update one Data Pill instead of hunting through dozens of Transform Map scripts.

Handling Lookup Failures Gracefully

One of the most common transform failures is the Reference field lookup failure — the transform tries to set a reference field (like department) with a value that doesn't match any existing record, and the transform errors out or writes null.

Here's the defensive pattern:

// Transform script for a reference field with safe fallback
// Source: dept_name (string) → Target: department (cmn_department reference)

(function transformValue(sourceValue, target, sourceRow) {
    if (!sourceValue) return null;

    // Try to find matching department
    var dept = new GlideRecord('cmn_department');
    dept.addQuery('name', sourceValue.trim());
    dept.query();

    if (dept.next()) {
        return dept.sys_id.toString();
    }

    // Fallback: create a placeholder department
    var newDept = new GlideRecord('cmn_department');
    newDept.initialize();
    newDept.name = sourceValue.trim() + ' (Imported)';
    newDept.company = sourceRow.company || null; // Carry over company context
    var newDeptId = newDept.insert();

    gs.info('TransformMap: Created placeholder department: ' + sourceValue);
    return newDeptId;
})(sourceValue, target, sourceRow);

Migrating Historical Data: The Staged Approach

For large historical migrations (say, importing 5 years of incident history from a legacy system), don't run a single massive transform. Instead:

Phase 1: Load raw data into Import Sets Run the import in batches of 10,000–50,000 records. Monitor the import job completion and check for errors at each stage.

Phase 2: Run Transform in batches Use sourceRow.getRowCount() to limit, and run transforms via scheduled script:

// Scheduled Script: Batch Transform Runner
// Run via scheduled job — process 500 records per execution

var importSetTable = 'u_historical_incidents_import';
var transformMapSysId = 'abc123def456789abc123def456789'; // Your transform map sys_id

var transformer = new sn_impex.TransformMap();
transformer.setImportSetTable(importSetTable);
transformer.setTransformMapSysId(transformMapSysId);
transformer.setBatchSize(500);
transformer.setOffset(0);

// Run transform — processes next 500 untransformed records
var result = transformer.runTransform();

gs.info('Transform batch result: ' + JSON.stringify({
    processed: result.processed,
    errors: result.errors,
    skipped: result.skipped
}));

Phase 3: Post-transform validation After the transform completes, run a validation script to audit the results:

// Post-transform validation script
// Checks for common data quality issues after transform

var auditResults = {
    missingDepartment: [],
    invalidDates: [],
    duplicateCoalesce: []
};

var gr = new GlideRecord('incident');
gr.addQuery('sys_import_state', 'finished');
gr.addQuery('sys_import_row_state', 'complete');
gr.addQuery('opened_at', '<', '2020-01-01'); // Historical records
gr.setLimit(500);
gr.query();

while (gr.next()) {
    // Check for missing department reference
    if (!gr.department.nil()) {
        var check = new GlideRecord('cmn_department');
        if (!check.get(gr.department)) {
            auditResults.missingDepartment.push(gr.number.toString());
        }
    }

    // Check for invalid dates
    var openedAt = gr.getValue('opened_at');
    if (openedAt && openedAt.indexOf('1970') !== -1) {
        auditResults.invalidDates.push(gr.number.toString());
    }
}

gs.info('Transform audit: ' + JSON.stringify(auditResults));

Best Practices Summary

Before the migration:

• ✅ Identify the correct Coalesce field(s) before building the Transform Map
• ✅ Test with a small sample set first (100-500 records)
• ✅ Validate source data format and encoding before importing
• ✅ Document field mapping rationale — you'll forget why you mapped cost_center to u_cost_centre in six months

During the migration:

• ✅ Run transforms in batches for large datasets
• ✅ Monitor Import Set transform logs for errors (sys_import_set_transform_log)
• ✅ Log Coalesce match rates — if you're getting more inserts than expected, check the Coalesce field values

After the migration:

• ✅ Run validation scripts to check data quality post-transform
• ✅ Spot-check records against source system
• ✅ Set up scheduled Transform Map runs with error alerting
• ✅ Consider using Data Pills for transformations you'll repeat

Common Gotchas

Import runs but nothing appears in the target table Check: Did you actually run the Transform? The Web Service Import or CSV upload populates the Import Set — the Transform is a separate step.

Transform errors on a reference field The source value doesn't match any existing target record. Fix with a Scripted Transform that creates a fallback record or logs the failure for manual resolution.

Coalesce field mismatch Source data has emp_id = 'emp-042' but the existing ServiceNow record has user_name = 'EMP-042'. Normalize the coalesce value in a Scripted Transform — don't leave it to chance.

Duplicate records despite Coalesce Multiple fields marked Coalesce can conflict. Make sure you understand the Coalesce priority order. Also check: is the source field being cleaned/normalised before the Coalesce check?

Wrapping Up

Import Sets and Transform Maps are the foundation of every data migration in ServiceNow. The concepts are simple — raw data in, clean records out — but the details matter enormously. A single misplaced Coalesce setting can create thousands of duplicates. A missing Scripted Transform can cause silent reference failures that only surface weeks later in a report.

Start every migration with a clear Coalesce strategy, test your transforms on sample data before running production loads, and invest in Data Pills for any transformation logic you'll reuse across integrations. Your future self will thank you when the next migration rolls around and you can just attach the existing pill instead of re-writing the script from scratch.