‹ All interview questions

Integration

Your production environment has a nightly scheduled import set job pulling 50,000 asset records from a vendor…

Senior · ServiceNow · Jan 2026

The question

Your production environment has a nightly scheduled import set job pulling 50,000 asset records from a vendor CSV feed into the staging table `u_asset_import`. Over the last 3 weeks, you've observed the target `alm_asset` table growing by 1.2 million records instead of the expected ~50K updates/inserts. Duplicate detection is enabled with `serial_number` as the coalesce field, yet sys_audit shows 85% of inserts are duplicates with identical serial numbers. Walk us through your systematic diagnosis approach (first 3 diagnostic steps with specific queries/logs to check). Then provide 3 solution options with trade-offs. Finally, show code for the PROBLEM (what's likely happening) and SOLUTION (your recommended fix). Production context: This runs at 2AM daily on a clustered production instance with Identification Reconciliation active. Business impact: CMDB health score dropped 40%, license reconciliation failing. No recent changes to transform map or data source.

Reading Between the Lines

A senior candidate first diagnoses systematically rather than jumping to code. Step 1: Check Import Set Run history (sys_import_set_run) for load counts vs transform counts - expect 50K loads but 1.2M transforms indicates multiple transform map execution. Step 2: Validate staging table duplicates by querying `u_asset_import` GROUP BY serial_number HAVING count > 1, checking created timestamps for batch patterns. Step 3: Review transform map script for coalesce logic and data source schedule for overlapping loads. Good answers stop at symptoms; great answers correlate node logs (scheduler worker saturation) with syslog for 'TransformMapWorker' duplicates.

The Problem (Likely Happening Now)

// PROBLEM: Multiple transform maps OR ignore=false causing row duplication
// In onStart script or bad transformRow - runs each row multiple times

function onStart() {
  // BAD: No unique handling, multiple maps process same import set
  log.info('Processing all rows without dedup logic');
}

function transformRow(source, target, map, log, action) {
  // PROBLEM: No source.unique_id check, processes duplicates
  if (source.serial_number) {
    target.serial_number = source.serial_number;
    // Missing: if existing record exists, skip or update
  }
  // Runs MULTIPLE times per row if multiple maps configured [2][5]
}

The Solution (Recommended Fix)

// SOLUTION: Robust onBefore transform script with deduplication + upsert
// Add to Transform Map > Transform Scripts > onBefore
(function transformRow(source, target, map, log, action) {
  // 1. Create unique key combining business identifiers
  var uniqueKey = source.serial_number + '|' + source.asset_tag;
  
  // 2. Check if already processed in THIS import set run
  var gr = new GlideRecord('u_asset_import');
  gr.addQuery('sys_import_set', source.sys_import_set.toString())
    .addQuery('u_unique_key', uniqueKey)
    .addQuery('sys_id', '!=', source.sys_id)
    .query();
  if (gr.next()) {
    log.warn('Duplicate detected: ' + uniqueKey);
    ignore = true;  // Skip this row
    return;
  }
  
  // 3. Set tracking field
  source.u_unique_key = uniqueKey;
  
  // 4. Coalesce with trim for data quality
  target.serial_number = source.serial_number + '';
})(source, target, map, log, action);

Senior Considerations

  • Trade-off 1: Staging cleanup (increase sys_auto_cleanup frequency) vs Transform dedup script - cleanup scales better for 50K+ but loses audit trail.
  • Trade-off 2: Single transform map with conditional targets vs multiple maps - single map prevents engine-level dupes but harder maintenance.
  • Trade-off 3: Background script for historical cleanup vs IR rules - IR fixes ongoing but won't touch existing dupes; use Reconciliation Groups post-fix.
  • Great answer trait: Mentions scheduler concurrency limits (sys_properties: glide.scheduler.worker_count) causing race conditions in clusters + monitors post-fix via Import Statistics dashboard.
  • Production hardening: Add error handler in onComplete to alert on >5% ignore rate; schedule cleanup job for staging tables weekly; test with load simulator before 2AM window.
  • Edge case mastery: Handles source data with leading/trailing spaces (trim in coalesce); multi-node failover (use sys_import_set as scope); XML/JSON sources needing xpath dedup.