Moving data is the easy part. A production integration must also match existing records, resolve references, standardise equivalent values, reject unsafe input and remain supportable when the source changes.
01Source data
02Stage and validate
03Trusted record
| Raw value | Rule | Target value |
|---|---|---|
| Microsoft Corp | Vendor mapping | Microsoft |
| MSFT | Vendor mapping | Microsoft |
| Unknown | Validation | Review queue |
01
Use case
Three sources send manufacturer names as Microsoft Corporation, Microsoft Corp and MSFT. Storing all three values fragments reports and downstream matching. The integration should preserve the raw value for audit while producing one approved target value.
| Raw source | Normalised target | Action |
|---|---|---|
| Microsoft Corporation | Microsoft | Map |
| Microsoft Corp | Microsoft | Map |
| MSFT | Microsoft | Map |
| Unknown vendor | No automatic match | Reject or review |
02
Build the pipeline
- 01Document required fields, stable source key, data types, value lists, expected volume and retry behaviour.
- 02Load raw data into an Import Set or staging layer so source evidence is retained.
- 03Create a Transform Map to map fields and resolve references.
- 04Choose a genuinely unique coalesce key. Do not coalesce on a display name that can change.
- 05Use field maps or reusable lookup logic for known value normalisation.
- 06Reject unsafe rows with a clear error instead of silently creating incomplete records.
- 07Record run counts for inserted, updated, ignored and failed rows.
03
Example transform script
Practical exampleValidate in a non-production instance
(function transformRow(source, target, map, log, isUpdate) {
var vendorMap = {
'microsoft corporation': 'Microsoft',
'microsoft corp': 'Microsoft',
'msft': 'Microsoft'
};
var rawVendor = (source.u_vendor + '').trim();
var normalized = vendorMap[rawVendor.toLowerCase()];
if (!source.u_external_id || !normalized) {
ignore = true;
log.error('Rejected row. Missing external id or unmapped vendor: ' + rawVendor);
return;
}
target.setValue('u_external_id', source.u_external_id);
target.setValue('u_vendor_normalized', normalized);
target.setValue('u_vendor_raw', rawVendor);
})(source, target, map, log, action === 'update');Practical note
Use a controlled lookup table instead of a script object when business owners need to maintain many mappings. Restrict write access and audit mapping changes.
04
Testing matrix
| Test | Expected result |
|---|---|
| Known external ID | Existing record updates once |
| New external ID | One new record inserts |
| Same payload retried | No duplicate |
| Unknown vendor | Rejected with reason |
| Missing required field | Rejected without partial target |
| Reference not found | Quarantined or handled by approved fallback |
| Large batch | Completes within agreed window with counts |
05
Production controls
- Alert on failure percentage, not only complete job failure.
- Retain correlation IDs between source, staging and target.
- Protect integration credentials and apply least privilege.
- Define who fixes source errors and who fixes platform mapping errors.
- Version the source contract and test format changes before deployment.
Continue practical learning.
Explore more articlesExplore more implementation-focused ServiceNow and architecture guides.