How to Prevent Duplicate Records in ServiceNow Import Sets Using Coalesce

Import Sets are ServiceNow's mechanism for bulk data ingestion. Data from a CSV file, a JDBC database query, an LDAP directory, or an HTTP endpoint lands in a staging table first, then a Transform Map processes each row and writes it to the target ServiceNow table. The process is powerful and flexible — but it has one critical configuration step that many administrators skip or configure incorrectly: Coalesce.

Without Coalesce, every import run inserts every row as a new record. Run the same CSV file three times and you have three copies of every record. For a user import with 5,000 employees, that means 15,000 sys_user records after three runs. Cleaning up that kind of duplication is a multi-hour project. Coalesce prevents it entirely with a single checkbox.

What Coalesce Actually Does

Coalesce designates one or more fields in a Transform Map as match keys. When the transform processes each row from the staging table, it checks whether a record already exists in the target table where the coalesce field value matches the incoming row's value.

If a match is found: the existing record is updated with the incoming data. No new record is created.

If no match is found: a new record is inserted.

This is the insert-or-update pattern, sometimes called upsert. It is the correct behaviour for almost every recurring import scenario — user sync, asset import, CI population, CMDB updates. The only time you deliberately want insert-only behaviour (no coalesce) is when you are importing genuinely new records that should never match existing data, such as historical transaction logs.

The coalesce check happens during the transform, not during the staging load. Data loads into the staging table first regardless — coalesce only affects what happens when that staged data is mapped to the target table.

How to Configure Coalesce — Step by Step

Coalesce is configured on individual field maps within a Transform Map, not on the Transform Map itself. Here is the complete setup process:

Step 1: Open your Transform Map. Navigate to System Import Sets > Transform Maps and open the Transform Map for your import. If you have not created a Transform Map yet, create one that points from your staging table to the target table.

Step 2: Open the Field Maps list. In the Transform Map record, scroll to the Field Maps related list. This lists every staging column mapped to a target field.

Step 3: Find the field that uniquely identifies a record. This is the most important decision in the entire setup. Choose a field whose value uniquely identifies a record in the target table and is consistently present in your source data. Common choices:

• User imports: email or employee_number
• Asset/hardware imports: serial_number
• CMDB server imports: serial_number combined with ip_address
• Location imports: name or a location code
• Department imports: name or a department code

Step 4: Open the Field Map for your chosen field. Click into the Field Map record for that field.

Step 5: Check the Coalesce checkbox. The Coalesce field is a boolean on the Field Map record. Check it and save. That is the entire configuration.

Now when the transform runs, it will search the target table for a record where your coalesced field matches the incoming value before deciding whether to insert or update.

Choosing the Right Coalesce Field

The coalesce field must be a value that is stable across import runs, unique within the target table, and always present in the source data. If any of these three conditions are not met, coalesce will not work as expected.

Stable: The value should not change over time. A user's email address is a reasonable coalesce field because it rarely changes. A user's name is a poor coalesce field because names change (marriage, legal name change) and are not unique. For assets, serial number is stable. IP address alone is not — it can be reassigned.

Unique: The value must identify one and only one record. If two users have the same employee number due to a source system error, coalesce will match the first one it finds and update it — creating incorrect data. Verify uniqueness in your source data before configuring coalesce.

Present: If the coalesce field is empty on a row, ServiceNow has nothing to match against and will fall back to inserting a new record. A row with a blank email address will insert a new user even if that user already exists. Always validate that your coalesce field is populated for every row before running a production import.

Multi-Field Coalesce

For some imports, no single field is sufficient to uniquely identify a record. In these cases, you can configure coalesce on multiple fields. ServiceNow will look for a record where all coalesced fields match simultaneously.

The most common use case is CMDB server imports, where neither serial number nor IP address alone is fully reliable, but the combination of both is unique. Configure coalesce on both the serial_number field map and the ip_address field map, and ServiceNow will only match a record when both values align.

Multi-field coalesce is more precise but more demanding on data quality. If either coalesce field is blank on a row, the match may fail and a duplicate may be inserted. For CMDB imports specifically, also consider the CMDB Identification and Reconciliation engine which has more sophisticated deduplication logic built in.

Testing Coalesce Before Running in Production

Never run a new Import Set configuration directly in production. Test the coalesce behaviour in a development or test instance first using this process:

Test 1 — First run (insert). Import a small batch of records that do not yet exist in the target table. Verify that new records are created with the correct field values.

Test 2 — Second run (update). Modify one field value in your source data for the same records (for example, change a user's department) and run the import again. Verify that the existing records are updated and no duplicates are created. The record count in the target table should be identical after the second run.

Test 3 — Missing coalesce field. Remove the coalesce field value from one row and run the import. Verify the behaviour — by default ServiceNow inserts a new record. Decide whether this is acceptable or whether you need a Transform Script to handle the case.

If all three tests pass in development, your coalesce configuration is correct and ready for production.

Transform Scripts for Advanced Coalesce Logic

For scenarios where the coalesce logic needs to be more dynamic — for example, matching on a field that requires transformation before comparison, or skipping rows where the coalesce value is absent — you can use an onBefore Transform Script to implement custom logic.

// onBefore Transform Script
// Skip rows where employee_number is empty to prevent false inserts
if (!source.u_employee_number || source.u_employee_number == '') {
    ignore = true;  // skip this row entirely
    gs.log('Skipping row — empty employee_number: ' + source.u_full_name,
           'UserImportTransform');
}

The ignore = true flag tells the transform engine to skip the current row without inserting or updating anything. This is cleaner than allowing a failed coalesce match to create a duplicate. Log the skipped row so you can investigate the source data quality issue.

Coalesce and the Sys ID Field

A special case worth knowing: if you map the sys_id field in your Transform Map and coalesce on it, ServiceNow will look up the existing record by sys_id directly. This is the most reliable coalesce method possible since sys_ids are guaranteed unique and never change.

However, it requires that your source data contains the ServiceNow sys_id for each record. This is typically only practical when the source system has previously exported data from ServiceNow and is now reimporting it. For imports from external systems, sys_id coalescing is not usually an option.

Common Coalesce Mistakes and How to Avoid Them

Coalescing on a display value instead of a raw value. If your staging table stores the display value of a reference field (for example, the user's full name in the caller_id field rather than their sys_id), coalesce will attempt to match on the display value. This is unreliable. Always coalesce on raw values — sys_ids, unique codes, or system identifiers.

Forgetting to configure coalesce on a new Transform Map. When you create a new Transform Map, the Field Maps are generated automatically but coalesce is not set by default on any of them. It is easy to run the first import, see records created, and assume everything is correct — only to discover duplicates after the second run.

Coalescing on a field that is not indexed. ServiceNow uses a database query to find matching records during the coalesce check. If the coalesce field is not indexed, this query runs a full table scan, which is extremely slow on large tables. For user imports on email or asset imports on serial_number, verify that the target field has a database index. Work with your admin to add one if not.

Using coalesce without validating source data quality. Coalesce is only as reliable as the data in your coalesce field. Run a pre-import data quality check on every batch to verify that the coalesce field is present, unique, and correctly formatted before triggering the transform.

Production Checklist for Import Set Coalesce

Before running any recurring Import Set in production, verify:

• At least one coalesce field is configured on the Transform Map
• The coalesce field is stable, unique, and always populated in the source data
• The coalesce field is indexed on the target table
• Insert-then-update test has been run in a non-production instance
• An onBefore Transform Script handles rows with blank coalesce values
• Transform errors are logged with enough detail to trace the source row

Coalesce is one checkbox in a field map record. It is the single most important configuration in any Import Set. Getting it right from the start prevents hours of duplicate cleanup, data quality investigations, and emergency data fixes. Configure it on every recurring import — without exception.