[object Object]

The nightly user feed ran fine for 18 months. Then HR introduced a comma in a job title, the staging coalesce key matched the wrong record, and the next morning 47 users had each other’s manager assignments. The two-stage pattern of import sets plus transform maps is exactly the safety net for cases like this — but only if the transform is configured to use it. Most outages from data loads trace to transform maps that bypassed the safety on day one.

The Two-Stage Pattern

Data lands in an import set table (staging) on first ingestion. A transform map moves it from staging to the target table with field mapping, coalesce, and per-row scripts. The two-stage pattern is the safety net — you can inspect what arrived, fix mapping issues, and re-run the transform before touching production tables. Skipping staging by writing directly to the target through scripts loses the inspection step and makes recovery from a bad feed dramatically harder.

Pipeline:
  External source -> Import Set table (staging)
                   -> Transform Map (with coalesce + scripts)
                   -> Target table (sys_user, cmdb_ci_*, etc.)
                   -> Audit log of inserts vs updates

Coalesce Keys

Coalesce fields tell the transform “if this key already exists in the target, update it; otherwise insert a new record.” Without coalesce, every transform inserts duplicates. Pick the right key — a stable external identifier (employee number, asset tag, externalId from SCIM) is best; a composite of 2-3 fields is acceptable when no single stable key exists; email is the worst because it changes. Test the coalesce by deliberately running the same load twice and verifying record count does not double.

Field Mapping

Straight 1:1 mapping for direct field copies. Source scripts for value transformations (concatenation, lookup translation, format normalization). Target scripts for post-insert business logic (notifications, derived field updates). Keep transforms deterministic — the same input must produce the same result on every run, otherwise debugging a bad load is impossible. Avoid scripts that depend on the current state of the target table to compute the new value; that creates ordering-sensitive behavior.

// Transform source script for a field
(function transformEntry(source, target) {
  if (source.u_country == 'United States') {
    target.country = 'US';
  } else if (source.u_country == 'United Kingdom') {
    target.country = 'GB';
  } else {
    target.country = source.u_country.substring(0, 2).toUpperCase();
  }
})(source, target);

Error Handling

Transform errors leave records in the import set with an error state and an error message. Schedule a job that checks for stuck import set rows daily and alerts an admin if any age past one day. Silent import failures are the worst kind — the source system reports success, the target table is missing data, and nobody notices until a downstream consumer does. The platform’s import-set scheduled job log surfaces the error count; surface it to the operations dashboard.

Bulk Load Performance

For large loads, increase the transform batch size to reduce per-row overhead, set “Run business rules” to false where downstream rules are not needed, and schedule for off-hours when the target table is not contended. A data load hitting a hot production table at 2 PM disrupts users; the same load at 2 AM is invisible. The platform’s data archive and bulk operations utilities handle very large loads more efficiently than naive transforms.

Performance settings for large loads:
  batch size: 500-1000 (tune by table)
  run_business_rules: false (when applicable)
  schedule: off-peak (regional time zone)
  parallel: true (for independent record sets)
  copy_empty_fields: false (avoid overwrites with nulls)

Common Failure Modes

Coalesce on a field that is not unique — produces silent overwrites of unrelated records. Verify uniqueness in the source before configuring coalesce. Transform that depends on a related record loaded by a prior transform — order the transforms explicitly via Data Source, do not rely on implicit ordering. Empty source values overwriting target values that have human edits — set “copy empty fields” to false where appropriate; otherwise the import quietly destroys local edits.

What Changed in 2026

Newer releases improved the transform map preview, which lets you simulate a transform against staging data without writing to the target. Use the preview for any non-trivial transform change before scheduling a real run. Older releases require a sub-prod test cycle for the same validation.

Common pre-load checks

Before any production load, sample the source for new field values, format drift, encoding issues, and rows that fail the coalesce uniqueness check. The platform’s import set utilities include a “Test transform” mode; use it. Production-first loading is how single-character changes in the source produce widespread bad data in the target.

What to do this week: list every active transform map and verify each has a coalesce field set; transforms without coalesce are duplicate-insertion incidents waiting to happen.

[object Object]
Share