Clone: Sync source instance user data to target instance user data but preserve target data not found on source

[BabyYoda]

I'm fine-tuning the clone definition for a future clone over to a target instance. Here's what I'm trying to do:

I want to sync over all user related data from the source instance to the target instance and preserve data on the target instance not found on the source instance.

For example:

Let's say I have 20,000 sys_user records on the source instance and 19,000 sys_user records on the target instance. Between the two instances, 180,00 records share the same Sys Id. That means there are 2000 records not on the target instance (20,000 - 18,000) and 1000 records not on the source instance (19,000 - 18,000).

What I want to achieve during the clone over is the target instance 18,000 records are replaced with the 18,000 records from the source instance, as they share the same Sys Ids. 2000 new records are copied over from the source to the target. And 1000 records on the target instance are preserved. Therefore, the total number of records on the target instance after the clone is 18,000 + 2000 + 1000 = 21,000 total sys_user records.

How can this be achieved employing Clone Definition Exclude Tables and Preserve Data? Will I need to write a Post-clone cleanup script to achieve this task?

Thanks for your help in advance!

[Maik Skoddow]

Hi

to the best of my knowledge, there is no way to implement this, and I'm really asking myself whether it is worth the efforts. The main goal of cloning is not merging data of two different instances. If you need that, you should implement a sync approach between your two instances, which runs all the time.

Maik

[BabyYoda]

Yes, I hear what you're saying, in regards to the primary purpose of cloning, as opposed to merging. To dumb it down, the logic is: a) if the Sys Ids are the same, delete/insert or modify target instance data with source instance data. b) if the Sys Ids are not equal then b1) bring over new Sys Ids from Source and b2) preserve Sys Ids on Target.

Or, to put it another way, effectively merge two data sets. Yeah, I didn't think exclude tables and preserve data worked to this capacity.

There is a post-clone scripts (cleanup scripts) but that runs after the clone has completed and after any preserve data datasets are re-loaded to the target instance.

I could write a script which implements, as you say, a sync approach. Remote integration Table API call from target to source and find all sys ids which are on target but not on source (in my case, it would be sys_users and customer_contacts tables). Store the Sys Ids somewhere and I can use that list of sys ids to build an update set comprising user data and user related data (I use the Update Set Utility which is, honestly, a real life saver and time saver and brings in all related data to your update set). Export the Update Set and then import it back on the target instance after the entire clone process has been completed.

It'd be cool if SNOW had a pre-clone script module that fires scripts before the clone begins. That would honestly be perfect, cause I know they talk about very large datasets are best to be handled with update sets and not cloning and, honestly, having a pre-clone set of scripts makes perfect sense.

But the long and short of it merging datasets where the source data wins out over the target when the sys ids are equal and carries over and preservers unequal sys ids cannot be accomplished right now with any of the clone definition features. That's my understanding of the situation right now.

If you have any other ideas, I'm all ears.

Thank you for your help with this!