6.1 Data Migration
Key Takeaways
- An import set is a staging table: source rows land in a temporary import set table, then a transform map moves them into a target table such as Incident or User.
- Coalesce on a field tells ServiceNow to match existing target records and update them instead of always inserting duplicates.
- Transform maps support field mapping plus optional field maps with scripts and onBefore/onAfter transform scripts for derived or conditional values.
- Scheduled data imports automate recurring loads from a data source, and the import set log plus state values let you triage rows that error or are ignored.
- Best practice is to test transform maps in a sub-production instance and migrate them with an update set, not by re-importing data into production by hand.
6.1 Data Migration
Quick Answer: ServiceNow loads external data through a three-stage pipeline. A data source describes where the data lives, an import set table stages the raw rows, and a transform map copies and converts those rows into a real target table such as
incidentorsys_user. Coalesce decides whether a row updates an existing record or inserts a new one.
The Certified System Administrator (CSA) exam tests data migration because almost every real ServiceNow project starts by loading users, groups, configuration items, or legacy tickets from another system. You are expected to know the pipeline stages, what coalesce does, and how to recover when an import goes wrong.
The Import Pipeline
ServiceNow never writes raw external data straight into a production table. Instead it stages the data first, so bad rows can be inspected without corrupting live records.
| Stage | Object | What it does |
|---|---|---|
| 1. Source | Data source | Defines the file or connection: Excel/CSV file, JDBC database, or attachment. |
| 2. Staging | Import set table (u_imp_* / sys_import_set_row) | Holds the raw rows exactly as imported, as text, with no validation. |
| 3. Load | Transform map | Maps staging fields to target fields and inserts or updates the real records. |
Each import run creates an import set record that groups the staged rows and records the overall state (Loaded, Processed, Error).
Data Sources
A data source (sys_data_source) is reusable configuration that points at where data comes from. Common types on the CSA exam:
- File — a one-time uploaded CSV, Excel (XLS/XLSX), XML, or JSON attachment.
- JDBC — a live connection to an external relational database, usually run through a MID Server so ServiceNow can reach an on-premises system.
- Attachment — a file attached to the import set record itself.
The data source also stores the file format, sheet/header row, and delimiter so the rows parse into named columns instead of one blob.
Transform Maps and Field Mapping
A transform map (sys_transform_map) connects one import set table to one target table and lists how each staging column maps to a target field.
- Auto map matching fields quickly links staging columns to target fields with the same name.
- Field maps (
sys_transform_entry) give you one row per mapping where you set source field, target field, and optionally a script for a derived value. - A field map can use source script to compute the target value, for example concatenating first and last name or converting a status code to a choice value.
Coalesce: Insert vs. Update
Coalesce is the single most tested data-migration concept. A field map marked Coalesce = true becomes a match key:
- Before inserting, ServiceNow searches the target table for a record whose coalesce field equals the incoming value.
- Match found → the existing record is updated.
- No match → a new record is inserted.
- No coalesce field at all → every row inserts, which usually creates duplicates.
You can coalesce on multiple fields; then the combination must match. Choosing a stable unique key, such as employee ID or email for users, prevents duplicate records on re-import.
Transform Scripts
Transform maps also support transform scripts that run at defined points in the load:
| Script | When it runs | Typical use |
|---|---|---|
| onStart | Once before any rows | Initialize counters or look-up caches. |
| onBefore | Before each row is saved | Skip a row (ignore = true), clean or default a value. |
| onAfter | After each row is saved | Create related records or set references. |
| onComplete | Once after all rows | Logging, notifications, cleanup. |
Field-map source scripts handle single-value conversions; transform scripts handle row-level or run-level logic such as skipping incomplete rows.
Scheduled Imports and Error Handling
A scheduled data import (scheduled_import_set) runs a data source and its transform map on a recurring schedule, which is how nightly user or CMDB feeds stay current.
Robust error handling on the CSA exam means:
- Inspect the import set state and the staged rows; rows that failed keep
sys_import_state = errorwith asys_import_state_commentexplaining why. - Use transform history and the import set log to see which rows inserted, updated, ignored, or errored.
- Fix the data or mapping, then re-run; correct coalesce keys mean re-running updates the same records instead of duplicating them.
- Build and test transform maps in a sub-production instance and promote the configuration with an update set; never hand-rebuild a transform map directly in production.
A team imports a CSV of 500 employees nightly. On the second night, every user is duplicated instead of updated. Which change fixes this most directly?
Where does external data physically land before a transform map processes it into the target table?
A loaded source row is missing a required field and should be skipped without stopping the whole import. Which mechanism is the standard way to do this?
Which statement about moving a tested transform map from a development instance to production is correct?