I have a data source that builds a table of company sites using a string field called site_code as the coalesce value. It runs once a day.

Every day, the same record was being inserted into the database with the same 32-character string in site_code.

The purpose of the coalesce field in a transform map is to prevent this exact situation. When the site_code matches an existing record's site_code, that record is updated instead of a new record being created. I couldn't understand why coalesce wasn't doing its job.

After a lot of time spent fruitlessly researching and testing, I realized what was happening.

The site_code for that one record from the data source is 38 characters long.

The site_code field in my ServiceNow table is 32 characters long.

The coalesce code in data source was comparing a 38-character string to a 32-character string. They didn't match, so the 38-character string was stored in site_code, which chopped it down to 32 characters.

When I expanded the size of the site_code field in SN to 50 characters, it solved the problem. The record is no longer being inserted each time the data source import is run.