You've handled dozens of data migrations using EF Migrations, SSIS, or custom ETL jobs.

Setting the Stage

Import Sets are ServiceNow's ETL pipeline. Data flows: External Source → Staging Table → Transform Map → Target Table. If you've built SSIS packages or written EF seed/migration scripts, the concept is identical — but the terminology and failure modes are ServiceNow-specific.

The ETL Pipeline Comparison

// .NET / SSIS ETL Flow (what you know)
// CSV/API → DataReader → Transform (C# mapping) → SqlBulkCopy → Target DB
//
// ServiceNow Import Set Flow
// CSV/API/Excel → Import Set (staging table) →
//   → Transform Map (field mapping + logic) →
//   → Target Table (e.g., cmdb_ci_server)

// Transform Map = Your AutoMapper profile + validation logic
// Coalesce Field = Your upsert key (like MERGE...ON in SQL)

Transform Map — Your Field Mapper

• Field mapping: Source column 'server_name' → Target field 'name'. Direct mapping, like AutoMapper's CreateMap<Source, Target>().
• Coalesce field: The match key for upsert logic. If a target record with this value exists, UPDATE. If not, INSERT. Like SQL Server's MERGE...ON target.serial_number = source.serial_number.
• Transform scripts: onBefore, onAfter, onStart, onComplete — hooks at every stage of the transform. Like EF's SaveChanges interceptors or SSIS package event handlers.

The Coalesce Disaster Scenario

// THE SETUP:
// Nightly import of 50,000 server records from a vendor CSV
// Coalesce field: 'name' (hostname)
//
// THE PROBLEM:
// 200 servers have the same hostname 'WEBSERVER' across
// different data centers. The vendor differentiates by serial_number,
// but the coalesce is only on 'name'.
//
// WHAT HAPPENS:
// Import finds 'WEBSERVER' in target → Updates the SAME record
// 200 times, overwriting with the last CSV row's data.
// 199 servers vanish from the CMDB.
//
// THE FIX: Composite coalesce
// Set BOTH 'name' AND 'serial_number' as coalesce fields
// Now the match key is: WHERE name='WEBSERVER' AND serial='SN-12345'
// Each server gets its own record

// Transform Script to validate before insert
(function transformRow(source, target, map, log, action) {
  // Prevent duplicates — check if already imported in this batch
  if (!source.u_serial_number || source.u_serial_number == '') {
    log.warn('Skipping row: missing serial number');
    ignore = true; // Skip this row
    return;
  }
  
  // Data quality — trim whitespace (classic ETL issue)
  target.serial_number = source.u_serial_number.toString().trim();
  target.name = source.u_hostname.toString().trim();
})(source, target, map, log, action);

Senior Considerations

• Staging table cleanup: Import Set staging tables grow forever unless you configure auto-cleanup. Like temp tables in your ETL that never get truncated.
• Run order matters: If multiple Transform Maps exist for one Import Set, they run sequentially. A second map can reference records created by the first — useful for parent-child relationships.
• Error handling: Transform errors create records in the Import Set Log. Monitor these like you'd monitor SSIS package execution logs.