Snowflake Integration with ServiceNow Using the REST API OAuth 2.0 and Scheduled Job!!

[nikhilsoni5]

I am working on a Snowflake integration that imports data from a Snowflake table into a ServiceNow target table. The integration is built using IntegrationHub and uses REST Messages, Data Stream Actions, a Data Source, and a Transform Map to map Snowflake columns to ServiceNow fields. The integration itself is functioning correctly, and the data import process has been working as expected.

However, there was a design mistake made when the integration was originally developed. In the Transform Map, the coalesce field, which is used to identify unique records during imports, was configured incorrectly. The coalesce field should have been set to Internal ID, as this is the true unique identifier in Snowflake. Instead, the developer configured it to use IEP Full Name.

This has caused problems because IEP Full Name is not a unique value. The Snowflake data owners can modify the full name whenever needed, while the Internal ID remains constant and never changes. As a result, when names were changed in Snowflake, the import process was unable to correctly identify existing records and created duplicate entries in ServiceNow instead.

Several users have now reported seeing duplicate records in the application. I attempted to correct the issue by changing the coalesce field from IEP Full Name to Internal ID, which is the correct unique column. While this change will prevent future duplicates from being created, it does not resolve the duplicate records that already exist in the ServiceNow table.

The main challenge is that I cannot simply delete the duplicate records from the ServiceNow table. Although some of these records are duplicates, they may already be referenced by other activity records or related records in the system. If a duplicate record is deleted and another record is pointing to it through a reference field, that reference could become empty or invalid, causing data integrity issues.

Because of this, I am considering whether disabling the duplicate records might be a safer approach than deleting them. However, I am currently unsure of the best remediation strategy and am confused about the approach I should follow. I need to determine how to clean up the duplicate data while ensuring that existing references and dependent records remain intact and that the issue does not impact users or system functionality.