ServiceNow Import Sets and Transform Maps

Import sets are ServiceNow’s general-purpose way to bring outside data in: an HR feed of employees, a spreadsheet of assets, a nightly extract from a finance system. Data lands in a staging table first, then a transform map copies it into the real target table. The mechanics are approachable. The decisions that matter, mostly about how a row finds its existing record, determine whether the hundredth run of the import leaves you with clean data or with three copies of everyone who ever changed their email address.

Data source, staging table, transform

A load has three stages:

1. A data source describes where the data comes from (an attached file, JDBC, LDAP, a REST call) and creates an import set table, a staging table whose columns mirror the source with u_ prefixed names.
2. Each load creates an import set and one import set row per source record. Nothing has touched your real tables yet.
3. A transform map links the staging table to a target table. Its field maps copy source columns to target fields, and transform scripts handle anything a straight copy can’t. After transformation, each row records what happened: inserted, updated, ignored, skipped or error.

Inbound integrations can use the same path through the Import Set REST API, which writes a row to the staging table and runs the transform straight away, returning the outcome for that row. Two transform map settings deserve a deliberate choice: Run business rules (whether target-table business rules fire during the transform) and whether data policies apply to import sets, which they do unless the policy opts out.

Coalesce decides insert or update

Coalescing is how a staging row finds its existing target record. ServiceNow’s documented rules:

• No coalesce field: every row is inserted. Nothing is ever updated.
• One coalesce field: if a target record has the same value, it’s updated; otherwise a new record is inserted.
• Several coalesce fields: all of them must match (an AND, not an OR).
• Conditional coalesce: a source script on the sys_id field map returns the sys_id of the record to update, or -1 to insert.

And the details that bite: matching is case-insensitive unless you tick Coalesce case sensitive; an empty value doesn’t match anything unless you tick Coalesce empty fields; if several target records match, only the first is updated; and if none of the coalesce fields is indexed on the target, you need to add an index, or every row turns into a table scan.

Replaying one HR feed three ways

This Node.js model (plain JavaScript reproducing the documented coalesce rules, not the transform engine) loads the same four-row HR feed into sys_user with three different configurations. The instance already holds Ana, Raj, Mei under her old email mei.chen@acme.com, and a service account with no email address.

// Replaying one HR feed through different transform-map settings.
const feed = [
  { u_employee_id: 'E1001', u_email: 'Ana.Silva@Acme.com',  u_name: 'Ana Silva',   u_department: 'Finance' },
  { u_employee_id: 'E1002', u_email: 'raj.k@acme.com',      u_name: 'Raj Kumar',   u_department: 'Fianance' },
  { u_employee_id: 'E1003', u_email: 'mei.brandt@acme.com', u_name: 'Mei Brandt',  u_department: 'IT' },  // was mei.chen@
  { u_employee_id: 'E1004', u_email: '',                    u_name: 'Tom Okafor',  u_department: 'IT' },
];

function freshInstance() {
  return {
    sys_user: [
      { sys_id: 'u1', employee_number: 'E1001', email: 'ana.silva@acme.com',  name: 'Ana Silva', department: 'd1' },
      { sys_id: 'u2', employee_number: 'E1002', email: 'raj.k@acme.com',      name: 'Raj K',     department: 'd1' },
      { sys_id: 'u3', employee_number: 'E1003', email: 'mei.chen@acme.com',   name: 'Mei Chen',  department: 'd2' },
      { sys_id: 'u4', employee_number: 'C0042', email: '',                    name: 'Svc Backup', department: 'd2' },
    ],
    cmn_department: [{ sys_id: 'd1', name: 'Finance' }, { sys_id: 'd2', name: 'IT' }],
  };
}

function transform(opts) {
  const db = freshInstance();
  const map = { u_employee_id: 'employee_number', u_email: 'email', u_name: 'name' };
  const same = (a, b) => (opts.caseSensitive ? a === b : String(a).toLowerCase() === String(b).toLowerCase());
  const results = [];
  for (const row of feed) {
    const values = Object.fromEntries(Object.entries(map).map(([s, t]) => [t, row[s]]));
    // Reference field: find the department by name; the choice action decides what happens on a miss.
    let dept = db.cmn_department.find((d) => same(d.name, row.u_department));
    if (!dept && opts.refAction === 'create') db.cmn_department.push((dept = { sys_id: `d${db.cmn_department.length + 1}`, name: row.u_department }));
    if (!dept && opts.refAction === 'reject') { results.push(`${row.u_employee_id} Skipped (unknown department ${row.u_department})`); continue; }
    values.department = dept ? dept.sys_id : '';
    const keys = opts.coalesce.map((s) => map[s]);
    const blankKey = keys.some((k) => values[k] === '');
    const target = keys.length && (!blankKey || opts.coalesceEmpty)
      ? db.sys_user.find((u) => keys.every((k) => same(u[k], values[k]))) : undefined;
    if (target) { Object.assign(target, values); results.push(`${row.u_employee_id} Updated ${target.sys_id}`); }
    else { db.sys_user.push({ sys_id: `u${db.sys_user.length + 1}`, ...values }); results.push(`${row.u_employee_id} Inserted u${db.sys_user.length}`); }
  }
  console.log(`${opts.label}\n  ${results.join(' | ')}\n  users=${db.sys_user.length} departments=${db.cmn_department.map((d) => d.name).join(',')}`);
}

transform({ label: 'A) coalesce email, case-insensitive, reference action create', coalesce: ['u_email'], refAction: 'create' });
transform({ label: 'B) same, with Coalesce empty fields ticked', coalesce: ['u_email'], coalesceEmpty: true, refAction: 'create' });
transform({ label: 'C) coalesce employee id, reference action reject', coalesce: ['u_employee_id'], refAction: 'reject' });

A) coalesce email, case-insensitive, reference action create
  E1001 Updated u1 | E1002 Updated u2 | E1003 Inserted u5 | E1004 Inserted u6
  users=6 departments=Finance,IT,Fianance
B) same, with Coalesce empty fields ticked
  E1001 Updated u1 | E1002 Updated u2 | E1003 Inserted u5 | E1004 Updated u4
  users=5 departments=Finance,IT,Fianance
C) coalesce employee id, reference action reject
  E1001 Updated u1 | E1002 Skipped (unknown department Fianance) | E1003 Updated u3 | E1004 Inserted u5
  users=5 departments=Finance,IT

Configuration A coalesces on email. Ana’s row matches despite the capital letters, because matching is case-insensitive by default. Mei changed her surname and email, so her row matches nobody and creates a second Mei, now with two accounts, two sets of roles to reconcile, and incidents split between them. Tom has no email, and empty values don’t coalesce, so he’s inserted, which is correct here only by luck. Raj’s misspelt department “Fianance” didn’t match, and with the reference choice action set to create, a new department called Fianance now exists.

Configuration B ticks Coalesce empty fields. Tom’s empty email now matches the service account’s empty email, and the transform overwrites the service account’s name and department with Tom’s. No error, no warning, and an account that jobs depend on now belongs to a new hire.

Configuration C coalesces on the employee ID. Mei’s record is updated with her new email, Tom is inserted as a genuinely new user, and the bad department is rejected instead of created: Raj’s row is skipped and visible in the import results, where someone can fix the source.

Choosing a stable key

The lesson generalises: coalesce on an identifier that the source system owns and never reuses. Employee numbers, asset tags, a vendor’s ticket ID stored in correlation_id, or an external ID column you add for the purpose. Names change, email addresses change with names, and display values aren’t unique. When no single stable key exists, a conditional coalesce script can try a strong key first and fall back to a weaker one, but log when it falls back, because those rows are the ones that create duplicates.

Reference fields and choice actions

A field map that targets a reference or choice field has a Choice action for values the target doesn’t recognise: create a new choice or referenced record, ignore the value, or reject, which skips the entire row. For reference fields you can also set the Referenced value field name so the lookup matches on a code (a department ID) rather than the display name.

create is convenient during an initial migration and dangerous in a recurring feed, where every typo becomes a permanent record that people start selecting. For recurring loads, prefer matching on a code with reject, and treat skipped rows as work for the source system’s owners.

Scripts for what field maps can’t express

Transform scripts run at defined points: onStart and onComplete once per run, onBefore and onAfter around each row, plus source scripts on individual field maps. Inside a row script, action tells you whether the row is about to insert or update, and setting ignore = true skips it. An illustrative onBefore script (not run on an instance) that keeps a feed from creating accounts it shouldn’t:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
    // Leavers arrive with status T; deactivation is handled by a separate, reviewed job.
    if (source.u_status == 'T') {
        ignore = true;
        return;
    }
    // Never create a user without the key we coalesce on.
    if (action == 'insert' && !source.u_employee_id.toString()) {
        log.warn('Skipped row without employee id: ' + source.u_name);
        ignore = true;
    }
})(source, map, log, target);

ServiceNow’s documentation shows the same action == 'insert' check as the way to make a transform “update only”: coalesce on the key and ignore anything that would insert.

Making repeat loads safe

A recurring import should be safe to run twice in a row and produce the same result. Coalescing on a stable key gives you that for inserts and updates. Deletions need their own design, because a transform only sees rows that are present: if a person disappears from the feed, nothing happens to their user record. The common approach is to stamp each record the import touches with the run’s timestamp and have a separate, reviewed step deactivate (not delete) records whose stamp is older than the last successful run, with a ceiling that aborts if the number is implausibly large, such as half the company because the source sent an empty file.

Watch the row states after each run. A load that normally reports 30 updates and 2 inserts and suddenly reports 4,000 inserts is telling you the coalesce key changed format at the source. Import set tables are staging, not history, and old import sets are cleaned up on a schedule, so keep anything you need for audit elsewhere.

Interview practice

After an HR system migration, the nightly user import doubled the number of active users. The transform map coalesces on email, and HR’s new system sends email addresses in a different format (asilva@acme.com instead of ana.silva@acme.com). How do you recover, and how do you prevent it from happening again?

Answer

The coalesce key changed format at the source, so no row matched and every employee was inserted as a new user. First, stop the scheduled import so the next run doesn’t add more. Then identify the duplicates: users created by that import set (the row records show which target records were inserted) whose employee number matches an existing user. For each pair, keep the original record, which has the roles, group memberships and task history, update its email to the new format, and deactivate the duplicate after checking nothing was assigned to it in the meantime.

To prevent a repeat, change the coalesce field to a key HR owns and won’t reformat, the employee number, and make sure that field is indexed on sys_user. Add an onBefore script that ignores rows without the key. Add a guard: if a run would insert more than a small percentage of the user base, fail the run before transforming, not after. And agree with HR that a change to identifier formats is a change requiring a test load into a sub-production instance first.

Related reading

• ServiceNow interview questions
• ServiceNow multiple-choice questions
• ServiceNow CMDB Identification and Data Quality, which applies the same identity questions to configuration items
• ServiceNow Scheduled Jobs, for scheduling loads and their clean-up steps
• Updating records using coalesce (ServiceNow documentation)