Salesforce Org Merges and ETL

Hi. My name is Zach Berman. I'm the partner engagement manager at Integrate.io. In this session, I'm going to talk about Salesforce org merges and how using an ETL platform for the data migration during an org merge can streamline the project and reduce the downtime for users. It also allows the opportunity for optimization of the new combined org. These are large scale projects that require significant planning to successfully complete the merge with minimal downtime for the users during that cutover period.

There's careful orchestration needed for configuration, transfer of of metadata, and, the data migration that all need to be addressed. From a deployment standpoint, the data migration piece is typically going to be your longest running process during an org merge, and I'm going to focus here on on that piece of the merge puzzle and how an ETL tool like Integrate.io helps streamline that process. We recently worked with a customer who had separate orgs for its international and domestic US business units, and they were working towards a migration to Lightning under a new combined global org to move closer to that single source of truth and a unified operating model between both of these business units. You know, the results of these two independent business units were too complex org structures and schemas and with a great need of, transformation to bring those schemas into alignment and successfully complete that merge.

So when we talk about the aspect of orchestration that needs to be done around, an org merge, From the data migration standpoint, it comes from the aspect of dependencies between objects and also the reality that for certain objects to be migrated, there's potentially going to be workflows, triggers, and validation rules that are going to need to be suspended for a period of time.

So here's a quick look at the entity relationship diagram for standard sales objects on a Salesforce org. The interrelationship of these objects defines a need for a specific order of migration of these objects to successfully rebuild the same relationships from the retiring org to the newly created records and IDs and the surviving org. A typical order for importing core objects would look something like this, accounts followed by campaigns, followed by contacts, etcetera. So you can link these altogether, as they were in the previous org.

So where that leaves us is after successfully migrating the records for these objects, we then need the new IDs that they generate in the surviving org to match them up with IDs from the retiring org to allow successful migration of records from the subsequent objects and keep those same relationships. This is something that can be done on a manual basis with something like Excel and using VLOOKUP to successfully map those necessary IDs prior to each, you know, batch you're loading through.

But an ETL tool like Integrate.io can make that a dynamic process with much less manual effort, especially when it comes time for, you know, production deployment and going live.

So I'm going to jump into Integrate.io here and show you what a basic orchestration of that looks like for migrating the opportunities from the retiring org. But I'm also gonna touch on some of the other aspects of the Integrate.io platform that assisted in the project. So as opposed to utilizing CSV files with something like data loader for this effort, with Integrate.io, you open up the option to using relational databases, data lakes or even going directly org to org as Integrate.io has native bidirectional support for Salesforce.

In the planning stages of an org merge, you're likely to be looking at object by object going through the data with a scalpel rather than a shovel to determine on a per object basis. If you need to migrate all of the fields from that given object, just some of them, or even if there is an opportunity to archive some older unneeded records and reduce your overall storage costs on the new combined org.

And that's not just restricted to the retiring org in this process, the same effort can be made towards existing records in that surviving org before you actually do the merge together to optimize your fully combined org at the end of this.

So XPlanny allows for not only the control of the specific records you'll be bringing across, but it can also be utilized for that archiving effort to move subsets of records from objects out to external storage for archiving and save on those storage costs.

The transformation layer in xPlani even offers the capability for field level encryption as you move data from source to target.

So as you're archiving those records to an external system, you can maintain the necessary security and compliance standards for those records even if there were say PII that you're pulling out of them on record level.

So when we're looking at the packages here for the purposes here in this example, in place of the flat files that you'd use with Data Loader, I'm going to utilize a Postgres database to create a dynamic workflow to migrate the opportunity records. So from the retiring org into a Postgres table and then up into Salesforce and then back. So we have those record IDs available to continue down this orchestrated flow and build those relationships without a lot of manual lift at the time we're actually going to put this under production.

So you'll see several packages here around opportunities such as the load, the pull down, validation.

We'll look at those but I'm gonna start in this workflow package here as this is actually the orchestration of these other packages for loading in the opportunities. So in this example, I've already run a previous process to bring the desired subset of opportunities out of the retiring org into a Postgres table. And the workflow you see here is going to load those in to the surviving Salesforce org, retrieve those new IDs that are created to build relationships for subsequent objects. And then we have an optional step here that can actually go and do a quick comparison and validation to see if those records are actually showing up in the surviving org.

You can build in an error tolerance for these jobs of how many errors are acceptable in Salesforce before we would define it as a failed job that would notify you and then you can quickly go look in this table and see what records you may wanna reattempt with and address why they failed.

So first we'll look at this opportunity load. So we're going to go into the package here. All right, so what we have going on in this opportunity load package is the first at the top is this postgres table we have that are opportunities from the retiring org. We go through, we just have a retiring schema and an opportunity table. You do have a where clause here. So these packages can be converted when you're typically looking at a snapshot of data that you're bringing through whether that is in flat files or whether it is in something like relational database here, a snapshot that you're going to load in during that cutover period and then there likely is some new records and changes in a small gap in between there potentially. And you can go through and utilize the same package but build out those where clauses to load those new records after the cutover process is complete.

So as we go through here, look, Integrate.io goes against this table, gives you all the available fields in the schema. These are the same fields available in the object on the Salesforce side.

Whether you do this when you are loading into a Postgres table or going direct Salesforce to Salesforce or anything like that, or here when we're going from the Postgres table into Salesforce, we can go through and actually get granular about what fields we're bringing through. So back to that aspect of, if you're identifying specific fields that are not of value or in use, you don't have to carry those across into this process and you certainly don't have to carry them across to the surviving org if you're not going to utilize them. But for the purposes here, we're looking at the majority of these fields being brought across.

So you get a little data preview down here as well so you can see what these fields are and we've got some example data that's populated into these Salesforce orgs. So I'm gonna click save here. So now we have our opportunity flow of our existing opportunity records from the retiring org. And now what we need to do is map in those related IDs.

So to this opportunity in the org that is being retired, there's relationships to what user is tied to the opportunity, what campaign, what account.

Those users campaigns and accounts have the equivalent on the surviving org side, but they have new record IDs that we need to be associating with. So we'll look here, we're going to do this through a series of joins and joins are very declarative in the XPlanning interface.

So we have a user map table here that we've gone through and you'll see we have surviving org schema here and a user map. And then when we jump over to the schema, you'll see we have just a couple of selected fields here.

So when we are running these processes, we have a custom object that is actually populating the record ID from the retiring org into each of these records so they can go back and be used for reference. And then this waterfall package is actually going to pull these back down. So you have these tables generated in the process that you can go and do these joins on subsequently in each of these packages and take away that manual aspect of using Excel on a flat file and VLOOKUP and correcting these through each pass and then loading them up and doing the same process over and over.

So we have our new ID here and then we have our retiring org ID custom field that is on the surviving org side. We know what the ID is for a given user that would be reflected in that retiring orgs opportunity record. So click save there. And now we quickly go and add a join between these two datasets.

So we're looking, we've got a left join, we want all the opportunities coming through. And then we want to take a look at who was the owner of that opportunity. And so we have our owner ID and that's going to relate to the retiring org ID custom field of the users, and then we relate those together.

And then we move on to the next step here. So the next step, we we need to associate campaigns and we have the same thing of a previous dataset that has been pulled into this surviving org schema in Postgres and a campaign table. And we're doing the same aspect here. We have a retiring org ID and we have the new ID that's been generated in the surviving org. We're going to go through, do the same very fast join and subsequently throughout this, we keep going out. Now this is a very limited look at the transformation capabilities of Explanny and this use case. Certainly in the overall use case I was presenting where these two large business units with a lot of customization on their platforms, there's other transformation that needs to occur as you go through and make the schema from each of these objects on their fields match up to what is desired in the surviving combined org.

And all of that can be done through the Xplanning platform and our standard transformations that we have that are declarative. There's also a select component that you can go through and there's a lot of different functions you can utilize as well to cleanse and shape that data to exactly what you need to load on the other side and it can do that all on the fly. So it's a repeatable dynamic package that you can carry through these multiple aspects of UAT testing in a sandbox and then dealing with any kind of idiosyncrasies that you're going to have when you move to production, you can modify it, you can handle those through the platform.

So after we do all these joins, we do have this mapping alias select component here. And basically what's been done in here is we have all of our existing fields that are pulling through and they're aliased. And then right at the end here, you'll see we have the user map ID to user ID, campaign map ID to campaign ID and account map to account ID. And what's been done in the select component is I've eliminated the retiring orgs IDs from here.

So when we go to actually map the schema onto the Salesforce object side, we're not looking at multiple user IDs in this data set or anything like that. So they're dropped out of the schema entirely and we only have the specific ones that we're going to use. And then when we're going to complete the load itself, our target connection is our surviving Salesforce org. We're going to our opportunity object and doing an insert.

We do use the bulk API here so you can do batch sizing up to ten thousand, turn it through and this is the error tolerance I was touching on as well, where you can determine how many errors are acceptable before Integrate.io notifies you that this is a failed job and we have a variety of hooks and notifications that can send out to you. So likely you're gonna be very hands on in this process and watching it as it goes. But depending on the size of the org that's being migrated and the number of records, some of these can be longer running jobs and you may be doing other things for the project while that migration is happening. You wanna be notified as soon as there's an issue past your own error tolerance that you have.

And then you have the aspect, we're going insert only here but again, when we're talking about any changes that may have happened between snapshot out through the cutover window, you can go through and do upserts and you could convert this whole package to an upsert process that's looking either at newly created Salesforce IDs or in this instance, if you're upserting those records, you can go on that retiring org ID custom field and key off of that and do any upserts and then bring any newly generated records through or changes to the records that were post snapshot that you had.

And then from there, you can quickly click this autofill, it'll match up input fields and destination fields if they have the same naming conventions. Certainly there is the likelihood that, especially with custom fields, have different naming conventions between the two orgs that you're trying to bring into alignment. So you can go and define these as needed.

And then when we go and look at the final page here, we're taking, in my sample dataset here, it's the same user that is the owner that created by last modified by, but you can certainly pass through joints for all of these different aspects and identify each of them. And then we are going and here's the step that populates the data in that eventually will populate the data into a table for subsequent loads. We're taking our retiring opportunity ID, which is the original ID from the retiring org and we're populating that into a custom field, retiring org IDC, you saw used in these joins throughout.

And then we also have the account ID that we've pulled into map two from the new surviving org and what account is going to align to it. So when we're done there, we click Save, we have a Save and Validate, this will go through and check any of the transformation operations that are being done syntax, test the connections, all of that. And then from there you can do a Save and Run job or you can wait and add this into an orchestrated package like we're talking about.

So that's the load end of things. Now I'm going to go back to this workflow and show you kind of how that populates into this dynamic package that is going to give you all of these tables that you're looking at here where you can pull and map the campaign IDs, the account IDs, any dependencies that they have or relationships that they have to other objects in the new org.

So the next step in the workflow is this opportunity retrieve. We'll go in here and title this opportunity pull down. So we're going to open this up and we'll take a quick look here. So here our source rather than a Postgres table is Salesforce org itself.

And so this is the surviving Salesforce org. This is when the load has occurred. So one thing I'll jump back to here and show you is that you can set this on failure, on completion, on success. So you can determine how this will happens, but basically when this package completes and loads, it will trigger up this opportunity pull down package.

So we know that those records have been pushed through into Salesforce and are going to generate those new IDs.

So here we're going through, now we're going to look at the opportunity object in the surviving Salesforce org and we're going to use this where clause to only specifically look for records that the retiring org ID custom field is not null. So we know it is a record that came from our retiring org and that's the only things we're going to need IDs from and cut down on the amount of records we're actually bringing into this table.

Then from there, I'm going through in this process and I'm bringing through a full load of all of these records that have gone through. So if there's specific comparisons that wanna be done, can compare on every field that's there. But certainly you could do something as simple as only using those two fields that we're using in those joins where you just need the ID and then you need the retiring org ID custom field to match up on and join on.

So we go through and save that and then going into the destination is very simple. We have our org merge database, we have a surviving schema, and then we have an opportunity table there.

And for this, we're just going and doing appends. And then we are going through and similar thing just auto filled, we're going through and populating all of these fields into a brand new table. You do not have to create this table in your database ahead of time, the XPlanny platform can do this. So when you go through through and kinda build out these packages and go to run them, this is something you can queue up this workflow and it will build this table and have it available there. Certainly though, if you were going to take that route, you do need to have your intended naming conventions available and know what your field names are going to be if you're looking to keep building future packages that are then going to join back against this to get those new IDs as we did in that original package.

So we have that, that will populate those tables we were using. And then if we look back in this workflow, we also have kind of an optional step of a validation rule here. So we can, after both of those complete, it will trigger validation and this utilizes both of the tables that were created. So you have the opportunities from the retiring Oregon Postgres table, you have the opportunities from the surviving Oregon Postgres table that have that custom retiring org ID. And then we quickly join them together on the original ID and the retiring org ID. And then from there, we can go through and do things like make a case statement to identify whether it's been processed or not just matching off whether the ID exists. But certainly you can do deeper levels of validation between the records and do spot checks.

And then we'll load that back into the existing retiring opportunity table. So then if we wanted to go and say, rerun this package again, we can go through and use that where clause in the initial package and say, only pull records where the has been processed field is showing as false. So where we had those error records, we can try that rerun and we don't have to kind of rebuild the wheel as far as the whole package goes here. If there was some small issue that we were able to navigate on the database side or whatever it might be.

Then we go back and look at the packages. So back to my initial point, beyond just this aspect of you need these record IDs to successfully create relationships, as you go to load certain objects, there are likely existing workflows, triggers, validation rules that are going to need to be suppressed, paused during the time that you're loading. When you have these specific set packages doing this process, you can start to establish your cutover window and what it's going to look like for when you need to have those things shut off and how soon can you get them back on in your process to get users back into a fully working state on the new combined org.

And a key component of that is actually getting an understanding of how long each of these jobs take to run, which you can do in the Explanny platform because we have a job history and you can see upon completion, you can get an idea of the run times that it actually took to run individual processes. So you can go and iterate through dry runs of the whole process from end to end, loading the objects in the order that they need to and end up with kind of a map out of the timing, aligning specific packages to when specific workflows triggers validations need to be shut off and having clear alignment there.

And it'll come with the same alignment that you'd see when you're looking at timing from doing the configuration of the platform, metadata deployment, the packages that you're using from third party providers, things along those lines.

So thank you for taking the time for watching. I hope this has been informative. Obviously, I've just scraped the surface of what's involved in a successful org merge here, but certainly related to this tooling, it's something that can bring you a lot of flexibility, save you a lot of time, open up opportunities to spend time on optimization and refactoring that you may have not had if you're going the more manual route and just have to focus on that lift and shift effort.

But certainly if you have any questions about this or you have any needs around moving data in and out of Salesforce for any reason, please reach out, happy to talk about it and see how we might have some shared success in your efforts.

‍