Coalesce

What It Is

Coalesce is a Transform Map field mapping configuration that designates specific fields as unique identifiers during data transformation. When ServiceNow processes import set records through a transform map, it checks if a record with the same coalesce field value already exists in the target table. If found, the transformation updates the existing record instead of creating a duplicate. This mechanism solves the fundamental integration problem of data deduplication during automated imports from external systems.

Architecturally, coalesce lives within the System Import Sets application, specifically as a configuration option on Transform Map field mappings stored in the sys_transform_entry table. Each field mapping record contains a coalesce boolean field that marks whether that particular field should be used for duplicate detection. The coalesce mechanism operates at the data layer during the transform engine's execution, sitting between the staging import set table and the final target table.

The coalesce function integrates directly with ServiceNow's GlideRecord query mechanism during transformation. When the transform engine encounters a field marked for coalesce, it constructs a query against the target table using all coalesced fields as query conditions. If multiple fields are marked for coalesce within a single transform map, ServiceNow treats them as AND conditions—all coalesced field values must match for a record to be considered a duplicate. This query execution happens before any record insertion, making coalesce a fundamental part of the transform pipeline's data integrity layer.

You cannot function without coalesce in any scenario involving recurring data imports from external systems where records might be sent multiple times with updates. Employee imports from LDAP, incident updates from monitoring systems, asset imports from discovery tools, and vendor master data synchronization all require coalesce to prevent table bloat and maintain referential integrity. Without coalesce, every import run creates new records regardless of whether they represent the same logical entity, leading to duplicate incidents, multiple employee records, and broken relationships between tables.

Platform administrators typically configure coalesce settings during initial integration setup, while system administrators manage ongoing adjustments when business logic changes. Developers become involved when coalesce logic requires custom scripting in transform map field mappings or when building automated import processes through IntegrationHub or scheduled jobs. The responsibility often shifts between roles—admins handle standard field-based coalescing like employee ID matching, while developers handle complex scenarios requiring transform scripts or multiple-condition coalescing logic.

Recent ServiceNow releases haven't fundamentally changed coalesce behavior, but Vancouver introduced performance improvements to the transform engine that reduced coalesce query execution time for large datasets. The Washington release enhanced coalesce debugging through improved transform logs that now show which coalesced fields triggered record updates versus insertions. More significantly, Quebec added support for coalescing on reference fields with proper dot-walking, allowing coalesce conditions like assigned_to.user_name rather than requiring sys_id values.

Where to Find and Configure It

Navigate to System Import Sets > Administration > Transform Maps to access the primary configuration interface. Open any transform map record and scroll to the Field Maps related list where each field mapping shows a Coalesce checkbox—check this box to mark the field for duplicate detection. From System Import Sets > Administration > Import Sets, you can test coalesce behavior by running transformations and reviewing the results. Access System Logs > Import Sets to see detailed logs showing which records were updated versus inserted based on coalesce matches.

In Studio, navigate to the application containing your transform maps and expand Import Sets > Transform Maps to modify coalesce settings within scoped applications. App Engine Studio users can access transform maps through Logic and Automation > Data Import where coalesce appears as a toggle switch in the field mapping interface. For direct database access, query the sys_transform_entry table where the coalesce boolean field controls this behavior. Global transform maps remain accessible from any scope, while scoped transform maps only appear within their originating application context.

How It Works Step by Step

The coalesce mechanism executes during transform map processing as part of ServiceNow's import set transformation pipeline. When the transform engine processes each import set row, it first evaluates all field mappings to extract values from the source record. For fields marked with coalesce, the engine builds a query object containing the target field names and their corresponding values from the import set record. This query construction happens before any database writes occur, allowing the system to determine whether to insert or update.

The transform engine maintains internal state during processing to track which fields require coalesce evaluation and caches query results within the same transform session. If multiple import set rows contain identical coalesce field values, ServiceNow reuses the previous query result to avoid redundant database hits. When no matching record exists, the engine creates a new GlideRecord for insertion. When matches are found, the engine loads the existing record and applies all field mappings as updates, preserving any field values not included in the transform map configuration.

The Execution Order

1. Transform engine reads the import set row and evaluates all field mappings, including any transform scripts
2. Engine identifies all fields marked for coalesce and extracts their values from the current import set record
3. System constructs a GlideRecord query against the target table using coalesced field values as AND conditions
4. Query executes against the target table to check for existing records matching all coalesce criteria
5. If no match found, engine creates new GlideRecord and sets all mapped field values for insertion
6. If match found, engine loads existing record and updates only the fields defined in the transform map
7. Record saves to database and transform logs record the operation as either 'inserted' or 'updated'

// Transform script showing coalesce logic for employee import
// This runs during field mapping evaluation
var employeeId = source.u_employee_id;
var department = source.u_department;

// Check if employee exists using coalesce on employee_number
var emp = new GlideRecord('sys_user');
emp.addQuery('employee_number', employeeId);
emp.query();

if (emp.next()) {
    // Update existing employee
    target.sys_id = emp.sys_id;
    target.department = department;
    target.last_updated = new GlideDateTime();
} else {
    // New employee - set required fields
    target.employee_number = employeeId;
    target.department = department;
    target.active = true;
}

Real-World Scenarios

Employee Data Synchronization from LDAP

Your organization runs daily LDAP imports to keep employee information current in ServiceNow. Without coalesce, each import creates duplicate user records, breaking assignment relationships and inflating your user count artificially.

Configure your transform map with employee_number marked for coalesce. Map the LDAP employeeID field to employee_number and check the coalesce box. Add field mappings for first_name, last_name, email, and department without coalesce settings. Set the target table to sys_user and run your transformation.

Watch for employees whose employee numbers change in LDAP—these will create new records instead of updating existing ones. Ensure your LDAP query filters out disabled accounts if you don't want to update inactive ServiceNow users. Consider adding a transform script to set the user_name field only for new records to avoid overwriting customized usernames.

Incident Updates from Monitoring Tools

Your monitoring system sends incident updates throughout an outage lifecycle, from initial alert through resolution. You need each monitoring alert to update the same ServiceNow incident rather than creating multiple incident records for the same underlying issue.

Set up coalesce on the correlation_id field in your transform map targeting the incident table. Map the monitoring tool's unique alert ID to correlation_id with coalesce enabled. Configure field mappings for short_description, description, priority, and state to allow updates as the alert evolves.

Be careful with state transitions—your monitoring tool might try to reopen resolved incidents if it sends stale data. Consider adding a condition in your transform map to ignore updates when the incident state is Resolved or Closed. Monitor your import set logs to identify correlation IDs that appear multiple times to verify coalesce is working correctly.

Asset Import with Multiple Unique Identifiers

Your asset management system exports hardware inventory with both serial numbers and asset tags as identifying information. You need to prevent duplicate asset records when either identifier matches an existing configuration item in ServiceNow.

Create two separate transform maps—one coalescing on serial_number and another on asset_tag, both targeting the cmdb_ci_computer table. Alternatively, use a single transform map with a transform script that queries for existing records using either identifier before setting the target sys_id. Map standard fields like name, model_id, and location in both approaches.

Watch for assets where serial numbers get reassigned or asset tags change—these scenarios can create orphaned records. Implement data quality checks in your source system to ensure serial numbers remain unique across your inventory. Consider adding a last_discovered field to track when assets were last seen during discovery runs.

The Classic Mistake

// Transform Map: Import Users
// Coalesce field: name (User table)
// Source table: u_import_users

// Field mapping:
source.employee_name -> target.name (Coalesce: true)
source.email -> target.email
source.department -> target.department
source.manager -> target.manager

// What happens:
// Run 1: Creates "John Smith" user
// User changes name to "John A. Smith" in system
// Run 2: Creates DUPLICATE "John Smith" user
// Run 3: Creates ANOTHER "John Smith" user

// Result: Multiple records with same source data
// because display name changed after import

This fails because display fields like name, number, or short_description change over time through normal business processes. Users see duplicate records appearing in their system seemingly at random. ServiceNow is correctly doing the coalesce lookup, but when the display field was modified after the initial import, the next transformation run cannot find the existing record using the original source value. The coalesce mechanism is working perfectly—it's just looking for a value that no longer exists in that field.

// Transform Map: Import Users  
// Coalesce field: u_external_id (User table)
// Source table: u_import_users

// Field mapping:
source.employee_id -> target.u_external_id (Coalesce: true)
source.employee_name -> target.name
source.email -> target.email
source.department -> target.department
source.manager -> target.manager

// What happens:
// Run 1: Creates user with u_external_id = "EMP001"
// User changes name from "John Smith" to "John A. Smith"
// Run 2: Finds existing user by u_external_id = "EMP001"
// Updates name back to source system value or skips name field

// Result: Single record maintained across all imports

When to Use This vs Alternatives

Coalesce is the correct choice when you have a reliable external identifier and need to maintain record synchronization across multiple import runs. This is your primary tool for preventing duplicates during regular data synchronization from LDAP, HR systems, CMDB tools, or any external system where the same logical record appears in multiple data loads.

Use Coalesce When You Need Synchronization

Choose coalesce for scheduled imports where source records represent the same entities over time—users from LDAP, assets from discovery, or incidents from monitoring tools. Data Import Sets and manual uploads won't give you this level of control. Business Rules with GlideRecord.get() lookups are too slow and error-prone for bulk operations.

Use Duplicate Prevention When You Need Fuzzy Matching

Switch to Duplicate Prevention Rules when you need to match on multiple fields or handle variations in data quality—like matching users by both email domain and last name, or finding assets by serial number OR asset tag. Coalesce only works with exact matches on a single field. Use Import Sets without transforms when you're doing one-time data migration and don't need ongoing synchronization.

Use Both When You Need Complex Processing

Combine coalesce with transform scripts when you need custom logic during the matching process—like updating only specific fields on existing records, or creating child records when the parent is matched. Use coalesce with Business Rules when you need post-processing after the match, like sending notifications or updating related records. The coalesce handles the core matching logic while other mechanisms handle the complex business requirements.

Platform Interactions & Side Effects

• Business Rules fire on update when coalesce finds existing records—before and after update rules execute with current.operation() returning 'update'
• Audit records in sys_audit show field changes for coalesced updates, with reason field showing the import set sys_id
• Notifications configured for target table trigger on coalesced updates—use conditions checking event.parm1 to detect import-triggered notifications
• ACLs on target table apply during coalesce operations—import service account needs write access to all fields being mapped, not just create rights
• Update Sets capture Transform Map changes but not the coalesce field configuration changes made through Field Maps—requires manual intervention during deployments
• Database indexes on coalesce fields dramatically impact performance—queries use SELECT * FROM table WHERE coalesce_field = 'value' for every transform row
• Transform errors write to sys_import_set_run with state = 'error' when coalesce field lookups fail due to multiple matches
• Workflow and Flow Designer activities break when triggered by coalesced records because sys_created_on remains original date while sys_updated_on shows current timestamp
• Reference field lookups during coalesce operations bypass cache and hit database directly, causing performance issues with large reference data sets
• Choice list values validate against target table dictionary during transform—invalid choices cause entire import set row to error with no partial record creation

Debugging and Troubleshooting

The most common failure symptom is duplicate records appearing despite coalesce configuration. Users report seeing "identical" records in their tables after import runs, and the sys_import_set_run shows successful completion with no error messages. Another telltale sign is transform performance degrading over time as the target table grows, indicating missing indexes on coalesce fields. Error messages like "Multiple records found for coalesce field" appear when your supposedly unique identifier field contains duplicates.

Start debugging at System Logs > All filtering by Source = Transform to see coalesce lookup details. Enable transform debugging by setting glide.import.log.level system property to debug. Check the sys_transform_entry table for field-level mapping results and error details. The sys_import_set_row records show sys_target_table populated when coalesce succeeds and sys_transform_map empty when it fails.

Look for specific error messages: "Field value is empty for coalesce field" indicates your source data has blank values in the identifier column. "Access denied" errors mean the transform user lacks write access to target table fields. "Invalid table" appears when coalesce field references don't match between source and target table structures. Performance problems show up as "Database query timeout" errors in transform logs when coalesce fields lack proper indexing.

Diagnostic Checklist:

• Query target table directly: SELECT coalesce_field, COUNT(*) GROUP BY coalesce_field HAVING COUNT(*) > 1 to find duplicates
• Verify field mapping coalesce checkbox is enabled and points to correct target table field
• Check source data quality—null, empty, or whitespace values in coalesce field prevent matching
• Confirm database index exists on target table coalesce field using Stats > Table and reviewing index list
• Test transform with single record using Transform single record button to isolate field mapping issues
• Review Transform Map scripts for custom logic that might override coalesce behavior
• Validate user permissions by impersonating the transform service account and manually updating target table records

Quick Reference

• Coalesce queries are case-sensitive—"SMITH" won't match "Smith" even if database collation is case-insensitive
• Transform Maps support only one coalesce field per map—multiple coalesce fields require custom scripting in onBefore script
• Maximum field length for coalesce matching is 4000 characters—longer values truncate silently causing match failures
• Reference field coalesce uses sys_id values, not display values—source data must contain target record sys_ids
• Coalesced updates preserve sys_created_on and sys_created_by from original record creation, only sys_updated_* fields change
• Transform error recovery skips remaining Import Set rows after coalesce failures—fix data quality before retrying entire batch
• Scheduled Import Jobs bypass workflow and approval processes for coalesced updates—use Business Rules for complex validation logic
• Choice field validation occurs after coalesce lookup—invalid choice values cause successful coalesce operations to fail during field assignment
• Coalesce field changes in target table after import require running Transform again on existing Import Set to apply new matching logic
• Audit trail shows coalesced record changes with Document key pointing to Import Set Row sys_id for tracking data source