Coalesce Rules in ServiceNow – Complete Guide for CAD Exam
Introduction to Coalesce Rules
When working with external data in ServiceNow, one of the most critical concepts you must understand is coalesce rules. Whether you are importing data from spreadsheets, external databases, or other systems, coalesce rules determine how incoming records are matched against existing records in the target table. Mastering this concept is essential for the ServiceNow Certified Application Developer (CAD) exam.
Why Are Coalesce Rules Important?
Without coalesce rules, every import operation would simply insert new records into the target table, potentially creating massive numbers of duplicate entries. Coalesce rules solve this problem by defining how the system identifies whether an incoming record already exists. This is critical for:
• Data integrity – Preventing duplicate records from being created in your tables
• Data updates – Ensuring that existing records are updated rather than duplicated when re-importing data
• Efficient data management – Keeping your ServiceNow instance clean and reliable
• Integration accuracy – Ensuring external data sources synchronize correctly with ServiceNow tables
What Are Coalesce Rules?
A coalesce rule is a setting applied to one or more field mappings in a Transform Map that tells ServiceNow to use those fields as the unique identifier(s) when matching incoming records to existing records in the target table.
When a field mapping is marked as Coalesce = true, ServiceNow uses the value in that field to search for an existing record in the target table:
• If a match is found, the existing record is updated with the incoming data.
• If no match is found, a new record is inserted into the target table.
You can set coalesce on a single field or on multiple fields. When multiple fields are set to coalesce, all coalesced fields must match (they work together as a compound key using AND logic) for the system to consider it an existing record.
How Coalesce Rules Work – Step by Step
1. Data is loaded into an Import Set table – External data (CSV, JDBC, LDAP, etc.) is first loaded into a staging table called an Import Set table.
2. A Transform Map is created or used – The Transform Map defines how fields from the Import Set table map to fields in the target table (e.g., the Incident table, User table, CMDB, etc.).
3. Coalesce is set on field mappings – Within the Transform Map, you mark one or more field mappings with Coalesce = true. This tells the system which fields to use for matching.
4. The transform runs – For each row in the Import Set:
a. ServiceNow looks at the coalesced field(s) and searches the target table for a record with matching values.
b. If a match is found → the record is updated.
c. If no match is found → a new record is inserted.
5. Results are logged – The Import Set log shows which records were inserted, updated, or had errors.
Example Scenario
Suppose you are importing user data from a CSV file into the User [sys_user] table. Your Transform Map has the following field mappings:
• CSV Column email → Target Field Email (Coalesce = true)
• CSV Column first_name → Target Field First name
• CSV Column last_name → Target Field Last name
• CSV Column phone → Target Field Phone
When the transform runs, for each row, the system checks: Does a user already exist with this email address?
• If yes → update that user's first name, last name, and phone.
• If no → create a new user record.
Multiple Coalesce Fields
If you set coalesce on both First name and Last name, the system will look for a record where both the first name AND last name match. This is useful when no single field is unique enough on its own, but a combination of fields creates a unique identifier.
Key Rules and Behaviors
• If no coalesce field is set, the transform will always insert new records – it will never update existing ones.
• Coalesce fields use AND logic – all coalesced fields must match for a record to be considered a match.
• Coalesce fields should ideally be unique – if the coalesced field matches multiple records, ServiceNow will update the first record found, which can lead to unpredictable results.
• Coalesce is set at the field map level, not at the Transform Map level.
• You can use sys_id as a coalesce field if you have the sys_id values from the target table available in your import data.
Where to Configure Coalesce
1. Navigate to System Import Sets → Transform Maps.
2. Open the relevant Transform Map.
3. In the Field Maps related list, open the field mapping you want to coalesce on.
4. Check the Coalesce checkbox (set it to true).
5. Save the field map.
Common Use Cases
• Importing users and coalescing on email or user_name to prevent duplicate user accounts
• Importing CMDB data and coalescing on serial number or asset tag to update existing CIs
• Importing incident data and coalescing on a correlation ID from an external ticketing system
• Periodic data synchronization where records should be updated, not duplicated
Coalesce and the Choice of Insert/Update
It is worth noting that the Transform Map also has a setting for the action to take when running the transform. By default, it can insert and update. However, you can restrict the action to:
• Insert only – only create new records (even with coalesce, matched records are skipped, not updated)
• Update only – only update existing matched records (new records are not created)
• Insert and Update – the default behavior described above
========================================
Exam Tips: Answering Questions on Coalesce Rules
========================================
1. Know the default behavior: If no coalesce field is set on any field mapping in a Transform Map, all records will be inserted as new. This is one of the most commonly tested facts.
2. Understand AND logic: When multiple fields are coalesced, they are evaluated with AND logic. The exam may present a scenario with multiple coalesced fields and ask whether a record will be updated or inserted. Remember: ALL coalesced fields must match for an update to occur.
3. Know where coalesce is configured: Coalesce is set on individual field mappings within a Transform Map, not on the Transform Map itself. If the exam asks where to set it, look for answers mentioning field map records.
4. Watch for trick questions about duplicates: If an exam question describes a scenario where duplicate records are being created during an import, the most likely cause is that no coalesce field has been set, or the coalesce field is set on a non-unique field.
5. Remember the matching behavior: Match found = update. No match found = insert. This is fundamental and will appear in various forms on the exam.
6. Differentiate from other import concepts: Do not confuse coalesce rules with Transform Map scripts, onBefore/onAfter scripts, or data policies. Coalesce is strictly about matching incoming records to existing records.
7. Unique fields matter: If a coalesced field matches multiple records in the target table, the system updates the first match found. The exam may test your understanding of why it is important to coalesce on truly unique fields.
8. Scenario-based questions: The CAD exam often presents scenario-based questions. For example: "An administrator imports a CSV of users weekly. Each week, duplicate user records are created. What should the administrator do?" The answer involves setting coalesce on a unique field like email or user ID in the Transform Map's field mappings.
9. Remember the import process order: Data Source → Import Set Table → Transform Map (with coalesce on field maps) → Target Table. Understanding this flow helps you answer questions about where coalesce fits in the process.
10. Practice identifying the correct coalesce field: The exam may ask you to choose the best field to coalesce on. Always pick the field that uniquely identifies a record (e.g., email for users, serial number for hardware, employee ID for HR records).
Summary
Coalesce rules are a fundamental part of working with external data in ServiceNow. They prevent duplicate records by matching incoming data against existing records based on specified fields. For the CAD exam, remember that coalesce is configured at the field map level within a Transform Map, uses AND logic for multiple coalesced fields, and determines whether a record is inserted or updated during a data import. Without coalesce, every import creates new records, making it one of the most important settings to configure correctly.