Overview
Working with External Data covers how an application brings in information it does not create itself. The first objective is importing CSV or Excel files: a data source loads rows into an import set staging table, and a transform map moves them into the target table, deciding through coalesce whether each row updates an existing record or inserts a new one. The second objective is integrating with an external system over REST, including testing and debugging the calls.
Although the domain has only two objectives, each hides several moving parts: field maps, transform script events, REST message methods, authentication, variable substitution and HTTP status codes. The domain builds on Domain 1's tables, uses the scheduled jobs and script includes from Domain 4, and is governed by Domain 3's access controls and web service settings.
How it's tested
Expect concrete troubleshooting and configuration questions: duplicates after an import, a field that did not map, a transform script that should skip some rows, or a REST call that returns an error. The right answer usually names the specific setting, script event or log that explains the symptom.
-
Import outcome
Predict whether rows insert, update or are skipped given coalesce fields and transform scripts, or fix duplicates and missing values.
-
Transform script placement
Choose among onStart, onBefore, onAfter, onComplete and field map scripts for a stated transformation need.
-
REST call diagnosis
Read an HTTP status code, request log or response body and identify the authentication, endpoint or payload problem.
A weekly Excel import keeps adding duplicate equipment records even though each row has a unique asset tag. Duplicates mean every row is treated as new, which happens when no field map is set to coalesce. Marking the asset tag field map as coalesce makes the transform search for a matching record first, updating it when found and inserting only when no match exists.
Objectives 2
Official objectives from the ServiceNow outline, in outline order.
5.1 Import data in CSV or Excel formatData sources, import sets, transform maps and coalesce.
- What it means
- An import starts with a data source (here a CSV or Excel file attachment), which loads rows into an import set table used for staging. A transform map then maps staging columns to target table fields, and running the transform creates or updates target records and logs the result.
- What to understand
- Coalesce on one or more field maps tells the transform to look for an existing target record with the same value: a match updates it, no match inserts a new record, and no coalesce at all always inserts. Transform scripts run at defined points: onStart once before rows, onBefore before each row, onAfter after each row, onComplete once at the end. Setting ignore = true in onBefore skips a row.
- How it shows up
- A scenario reports duplicates, skipped rows or wrong values after an import, and you identify the coalesce setting, field map or transform script that causes or fixes it.
- Common confusion
- Thinking the import set table is the final destination; it is a staging table, and nothing reaches the target until a transform runs.
5.2 Integrate to, including testing and debugging, an external data source using RESTOutbound REST messages, RESTMessageV2 and debugging.
- What it means
- A REST message record stores an external endpoint and authentication, and its HTTP methods (GET, POST, PUT, PATCH, DELETE) define each call with headers, query parameters and a content template using ${variable} substitution. Scripts call it with sn_ws.RESTMessageV2, set parameters, execute and read the response.
- What to understand
- Each HTTP method has a Test link that sends a real request and shows the status, headers and body. In script, getStatusCode() and getBody() expose the result, and JSON.parse turns a JSON body into an object. Outbound HTTP request logs and the status code (for example 401 for authentication failure, 404 for a wrong endpoint) are the first debugging tools; executeAsync avoids blocking the user.
- How it shows up
- An integration returns an error or empty data, and you choose the check that isolates it: the Test link, the status code, the authentication profile or the variable substitution.
- Common confusion
- Treating any response as success; the script must check the status code because a 401 or 500 still returns a response object and body.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
Import set table
A staging table that holds raw rows loaded from a data source, one import set per load, before any transform touches the target table.
- Remember
- Loading data and transforming data are separate steps; a load alone changes no target records.
- Tested as
- Questions ask why loaded data is not visible in the target table.
- Trap
- Expecting records in the target right after Load Data finishes.
-
Data source
Defines where import data comes from, such as an attached CSV or Excel file, with settings like the header row and sheet number for Excel.
- Remember
- A data source can be reused and scheduled so the same file layout imports repeatedly.
- Tested as
- Appears in setup and recurring-import scenarios.
- Trap
- Pointing at the wrong sheet or header row and getting shifted columns.
-
Transform map
Links an import set table to a target table through field maps, with options such as running business rules during the transform.
- Remember
- Auto Map Matching Fields helps when column and field names line up; other fields need manual field maps or scripts.
- Tested as
- Core of every import question.
- Trap
- Assuming unmatched columns are copied automatically.
-
Coalesce
Marks one or more field maps as the match key so the transform updates an existing target record with the same value instead of inserting a new one.
- Remember
- Match means update, no match means insert, and no coalesce field means every row inserts.
- Tested as
- The most frequently tested import concept.
- Trap
- Coalescing on a non-unique field such as name, which updates the wrong record or merges rows.
-
Transform script events
onStart runs once before processing, onBefore before each row is written, onAfter after each row, and onComplete once after all rows.
- Remember
- Use onBefore to change or skip a row (ignore = true), onAfter for related records needing the target's sys_id, onComplete for summaries.
- Tested as
- Placement questions name a need and ask which event fits.
- Trap
- Trying to skip rows in onAfter, after the record is already written.
-
Field map source script
A field map can use a script that returns the value for one target field, with source available for the staging row.
- Remember
- It shapes a single field; row-level decisions belong in onBefore.
- Tested as
- Appears when one column needs reformatting, such as splitting a name.
- Trap
- Writing a whole-row transform script for a one-field change.
-
Transform history and import log
Each run records how many rows were inserted, updated, ignored, skipped or errored, and messages written during the transform.
- Remember
- Check the transform history and import log first when results look wrong.
- Tested as
- Debugging questions point here.
- Trap
- Inspecting the target table alone and missing row-level errors.
-
REST message and HTTP method
A REST message holds the endpoint and default authentication; each HTTP method record defines one call's verb, endpoint path, headers, parameters and content.
- Remember
- Variables written as ${name} are substituted at run time from setStringParameter or setStringParameterNoEscape.
- Tested as
- Configuration questions ask where a header or parameter belongs.
- Trap
- Hardcoding values that should be substituted per call.
-
RESTMessageV2
The server-side API (sn_ws.RESTMessageV2) that sends a REST message, either from a stored record or built in script, and returns a response object.
- Remember
- execute() waits for the reply; executeAsync() returns immediately and is read later with waitForResponse or a response handler.
- Tested as
- Script snippets in questions use this API.
- Trap
- Calling execute() synchronously from user-facing logic and slowing the form.
-
Response handling
The response exposes getStatusCode(), getBody(), getHeader() and getErrorMessage(); JSON bodies are parsed with JSON.parse.
- Remember
- Check the status code before trusting the body.
- Tested as
- Questions show a code and ask what it means.
- Trap
- Parsing an error body as if it were data.
-
HTTP status codes
200 means success, 201 created, 400 a malformed request, 401 failed authentication, 403 authenticated but not allowed, 404 wrong resource, 500 a server-side failure.
- Remember
- 401 points to credentials, 403 to permissions, 404 to the endpoint or path.
- Tested as
- Common in debugging scenarios.
- Trap
- Treating 401 and 403 as the same problem.
-
Authentication profiles
REST messages and methods can use basic authentication profiles or OAuth 2.0 profiles, set at message level and overridden per method.
- Remember
- Store credentials in profiles, not in scripts.
- Tested as
- Tested in setup and 401 troubleshooting.
- Trap
- Embedding usernames and passwords in script headers.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
Coalesce outcomes
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Coalesce match found | Target record with the same key value exists | Existing record is updated | Expecting a new record |
| Coalesce, no match | No target record has the key value | New record is inserted | Thinking the row is rejected |
| No coalesce field | Transform has no match key | Every row inserts, causing duplicates on reload | Blaming the source file for duplicates |
Transform script events
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| onStart | Once, before any row | Set up shared values or validate the batch | Trying to change a single row here |
| onBefore | Each row, before it is written | Change values or skip with ignore = true | Using onAfter to skip rows |
| onAfter | Each row, after it is written | Target sys_id exists; create related records | Expecting changes here to block the write |
| onComplete | Once, after all rows | Summaries, notifications, cleanup | Using it for per-row logic |
execute() vs. executeAsync()
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| execute() | Sends the request and waits for the response | Simple, but blocks the running transaction | Using it in user-facing synchronous logic for slow endpoints |
| executeAsync() | Queues the request and returns immediately | Does not block; response read later | Reading the response before it has arrived |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Duplicates blamed on the file
- What candidates pick
- Clean the spreadsheet or delete the previous import set before reloading.
- Why it looks right
- The duplicates look like a data-quality problem in the source.
- Why it's wrong
- Without a coalesce field the transform inserts every row; coalescing on a unique key makes reloads update existing records.
Clue to watch for The same rows appear twice after a repeated import and a unique identifier column exists.
-
Coalesce on a friendly field
- What candidates pick
- Coalesce on the display name of the equipment.
- Why it looks right
- Names are visible and easy to recognize.
- Why it's wrong
- Names are not unique and can change, so rows update the wrong record or create new ones; coalesce on a stable unique key.
Clue to watch for The scenario mentions a unique tag, number or ID column alongside a name.
-
Skip in the wrong event
- What candidates pick
- Use an onAfter script to discard rows whose status is Retired.
- Why it looks right
- onAfter sounds like where row-level processing happens.
- Why it's wrong
- By onAfter the target record is already written; set ignore = true in onBefore to skip the row.
Clue to watch for The requirement says certain rows must not be created or updated.
-
Loaded equals imported
- What candidates pick
- Assume the import failed because the target table shows no new records after loading the file.
- Why it looks right
- Load Data reports success, so the data seems lost.
- Why it's wrong
- Loading only fills the import set staging table; the transform map must run to write target records.
Clue to watch for Rows are visible in the import set table but not in the target.
-
401 treated as permissions
- What candidates pick
- Grant the integration user more roles in the external system after a 401 response.
- Why it looks right
- Access errors feel like missing permissions.
- Why it's wrong
- 401 means authentication failed, so credentials, the authentication profile or the token is wrong; 403 is the permissions code.
Clue to watch for The status code is 401, not 403.
-
Response means success
- What candidates pick
- Parse the body immediately after execute() and write the values to the record.
- Why it looks right
- A response object came back without an exception.
- Why it's wrong
- Error responses also return bodies; check getStatusCode() first or the script stores error text as data.
Clue to watch for Records contain error messages or nulls after an integration run.
Answer framework
When a Domain 5 question involves imported or integrated data, work through these checks.
- Identify the stage that failed: the data source load, the import set staging table, the transform map, or an outbound REST call and its response.
- For imports, confirm the coalesce field is a stable, unique key and predict whether each row will insert or update.
- Place logic in the right transform event: onBefore to change or skip rows, onAfter for related records, onComplete for summaries.
- For REST, test the HTTP method from its record first, then confirm endpoint, authentication profile and ${variable} substitution.
- Read the evidence before changing anything: transform history, import log, status code, response body and outbound HTTP logs.
Practice question
Want more like this? The ServiceNow CAD practice test has questions for every domain.
Checklist
0 of 12
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Because this domain has only two objectives, learn both by doing them once end to end. Start with imports. In a personal developer instance, build a small Excel file with a unique asset tag column and a few other fields, load it through Load Data, and create a transform map. Run it once without coalesce, reload and observe duplicates, then set coalesce on the asset tag and reload again. That single experiment covers the most tested concept.
Add an onBefore script that skips retired rows with ignore = true and an onComplete script that logs a count, then read the transform history. Notice which business rules ran.
Next, build an outbound REST message to a public test API. Use the Test link on a GET method, then call it from a background script with RESTMessageV2, log the status code and parse the JSON body. Break the endpoint and the credentials on purpose to see 404 and 401 responses.
Candidates often underestimate how literal these questions are, so practice naming the exact setting or script event. Afterwards, revisit Domain 3's web service access settings and Domain 4's scheduled jobs, which often wrap imports and REST calls.
FAQ
How much of the ServiceNow CAD exam is Working with External Data?
The domain is 10% of the exam, about 6 of 60 questions as a planning estimate. ServiceNow does not publish per-domain question counts.
Does the current blueprint include SOAP?
The current objective names REST only. Older versions of the CAD blueprint also listed SOAP, so older study materials may cover it.
What is coalesce in an import?
Coalesce marks the field map used to find an existing target record. A match updates that record, no match inserts a new one, and without any coalesce field every row inserts.
Which transform script should skip unwanted rows?
An onBefore script, by setting ignore = true for the rows to skip. Later events run after the row has already been written.
How do I test a REST integration without writing script?
Open the HTTP method on the REST message and use its Test link. It sends a real request and shows the status code, headers and response body.
Do UI policies or client scripts apply to imported data?
No. Form-level logic does not run during imports. Business rules can run if the transform map allows it, and data policies can apply to imported records.
How does this domain relate to security?
REST requests into the instance are checked against access controls and the table's web service access setting, which Domain 3 covers.
Other CRM & Business Applications certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 5 knowledge
You have walked through import sets, transform maps, coalesce, transform scripts and REST calls. Check whether you can diagnose import and integration problems quickly under exam timing.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the ServiceNow CAD AI tutor Get any concept explained your way