A Pattern for Salesforce Data Migration

Good day. Welcome to another X Force Data Summit, presentation. This one's by Martin Gardner. His title is solution principal.

He works for an international consultancy called Slalom. He works in the Salesforce part of that part of that consultancy, though they have other abilities that he'll tell you about. And his presentation today is about a pattern for data migration. It's more about all the things that go into a data migration.

I've looked through it and I think it's interesting and really important and it's something that people miss when they concentrate on just the technical aspects of data migration. So without further ado, here's Martin and we're gonna learn from him today. Thank you.

Hello. Thank you very much. Yes. My name is Martin and, I'm here today to talk about, data migration pattern, that I've developed, over a number of years.

I am a solution principal at Slalom. We're a large consultancy and work in the London office in the Salesforce practice. We also work with AWS, Tableau, Snowflake and have a large operation that builds things from the ground up ourselves.

So, as you may be aware if you've done data migration previously, they are quite difficult.

They're more than just technical exercise, are lots of things you need to think about when you're going to be doing a data migration and this session is really going to go through all the things that you need to remember or consider when you're building out a data migration project.

So how can we possibly remember all these things? Well, if we think of them as a pyramid that works quite nicely. So we have four areas that we're going to look at. We're going have data, methods, technology and people.

And I'm going to address each one of these in turn and break them down and show you what goes into each of them and what things you need to think about, remember or take action on in order to make your data migration successful.

So let's look at the first thing which is data.

How can we ensure firstly that the data we're using in our process is of good quality?

We're not going to be able to say that we've done successfully migrated our data if it arrives in the target system completely, scrambled or messed up in any way. So we need to decide how to measure that quality.

What rules or KPIs are we going to put in place at the beginning at the entry point to our migration that will drive out an understanding of the quality of that data as it came in and which we can then use at the other end to verify that we've not made it any worse.

It's quite tempting in a data migration to cleanse the data, to clean it up as you bring it across and that sometimes involves merging records. So if you're looking for duplicates and merging them, that's quite tricky to then test on. So if you're going to do that, I recommend you separate that out into a separate project or a separate phase so you do your deduplication and merging before you attempt to migrate because one of the really easy ways to check you've migrated the right thing is to count the number of records in the source and count them in the target. If you've merged them in the middle of your process, you can't then rely on that as a metric for telling whether or not you're off by a few records or a few thousand records.

So really you need to be careful about doing that kind of data quality stuff during a data migration.

So I would suggest place some gates at the beginning and the end of your process that ensure a certain level of quality for your data and then use that as a means to measuring how the process is going.

Some of the things you might find in your source data that you might need to deal with are email addresses, field links, data types, mandatory fields of null values and broken referential integrity.

With Salesforce to Salesforce migrations you'll probably see fewer of the email address problems because Salesforce email fields have some validation already and you're unlikely to find referential integrity problems as long as you're building relationships using lookups and master details and not storing IDs in text fields and things like that. However, if you're pulling from any other data source SQL Server or a less managed system, then you may well find these things and so you want to be looking out for those in your source data as part of input KPIs.

So when we've got an idea of what the quality is, we want to make sure that we don't let into the process poor quality data. So we've defined our rules for entry and then we screen out any records that don't meet those rules and pass them back to the client to resolve before they go through the process. That will ensure that when we get into the target system, the data there is of a known level of quality, and can be accepted at UAT.

Next up in the data areas location this one is a more kind of practical aspect it's like where is the data? Is it in the cloud? Are we going to store it on premise? Where is it going to be stored when it's being migrated? Is it going to be stored in memory? Is it going to be cached to a disk somewhere?

And also, is it going to move across international borders? So are we migrating our data from China to the US or from the US to Europe through China? What are those kind of implications? Are there any legal implications to that that we need to consider?

Next up is data volume. We really do need to have a clear understanding of how much data we're moving and how quickly we need to move it.

That means we can then take that information and work out whether the tools we're going to choose will be able to support that volume of data. Now if we do that up front at the beginning of the project, we can tailor our tool choice to the volumes of data we're moving.

We can also check that the target system can support that volume of data.

In the Salesforce world there are several different ways of managing large amounts of data so you may well want to consider whether it's appropriate to migrate that data into Salesforce or whether it's to put it into a more cost effective data store and then access the data using external objects something like that.

Finally and most importantly here on the data area is security. So for that we need to know where is the data going to be, who's looking after it and who's got access to that information, How are we going to keep control of it? Do we have any requirements on us under GDPR or other legislation to make ensure it's secure. I recommend with the handling of the data to ensure that the client retains data, retains the data on their own systems. So it's usually best to set up the client to set up a virtual machine and then we do the work on the virtual machine that's in their domain of control so that they're not passing that data to us to process that keeps things nice and simple.

So here we are, this is the tips for this area.

We want to define those quality rules for the data entering the migration pipeline. Don't cleanse the data during the migration do it either before or after.

Estimate how long it's going to take you to do the migration based on data volumes and the limitations of the platform you're using.

And then wherever possible keep that data in the client's domain of control and responsibility so that you can keep the legal aspects simple.

So moving on now to methods. The methods area this is going to cover all of the things that you might use to do the moving of the data. So things like what processes you're going to use, how are you going to set things up, what methodology you might use for the project and how might you test that sort of thing.

So first of all, the contract now I'm approaching this from the point of view of a consultancy, but you could take this information, and use it in an internal project because really you are going to be setting up a contract between you and your users or whoever's the stakeholder for this project and you want to be very clear about what's going to happen in each phase.

I recommend then that you break the project down into two distinct phases the discovery and design first followed by the build and test and execution after that.

The reason for that is that and I've seen this on a lot of projects it's very difficult when you first come into an environment looking at a spec from a customer to really understand quite how many fields or how many objects or to really understand the complexity of what's needed to achieve the migration.

So rather than having to over commit or under commit before you go in to the project, start off with a phase where you go and look at all the objects that you're going to be migrating, all of the data you can get your hands on to get to a point where you've got a much more accurate estimate for how long it's going to take to implement.

So what should you put into that discovery and design phase?

So you're going to be wanting to look at your data migration strategy which is the who, how, when, where and what if of the migration.

You're going to want to look at the data mapping so data mapping at an object and field level what high level transformations are you expecting to make, how many objects and fields you're going to be working with and then if you take all of that the build effort is going to be proportional to that object and field count.

So this slide here just shows a little bit more detail about what's in that data strategy document.

So we're looking to cover how are we going to do the migration. Who's going be responsible to what, what environments are they going to use, do we need any servers set up or any cloud services set up and initialised for us?

What are going to do if things go wrong? So we'll need a rollback strategy How do we delete the data from the target environment? What processes are we going to use? Who's going to sign off on things? A release schedule so when are we going to do what? Who's going to do those activities?

A definition of what the data quality rules will be for the project and then finally things about how we'll test it, how we will log the processing of the data migration and who's going to sign off on it and how that will be reported.

The data migration mapping document takes it a step further this is well into the design part. So we're looking here at trying to describe everything that's relevant about the source and systems all of the objects, fields that we're going to be mapping, their data types. What we're really trying to do is make things as simple as possible for us to implement. So we're looking for identifying potential problems around data formats, sizes of fields, data type mismatches, complex transformations that might need a staging database to implement or some really custom coding.

And then we want to work out our execution order. With Salesforce and many systems where you have relationships between objects, you have to be careful about the order in which you load them because for example a contact can't be loaded before the account that it is a child of, because it needs the account ID to load against and be linked to. Potentially you could load everything without any parent IDs on them and then update it subsequently to add the IDs in. However, there are a lot of objects in Salesforce for which that won't work, especially where there are master detail, lookups on those objects.

So you will need to plan the order in which you're going to load each object.

And you may have to plan for revisiting objects to update them once when you find loops in the dependency tree.

So how would we go about building this? I would recommend a classic ETL pattern for a data migration so we're going to extract the data from the source system, stage it somewhere, transform the data and then load it into target system.

And the way I would break that down would be into five separate jobs. So in order to make things easy to test, it's nice to be able to pull everything out of the source system into the staging database, stop and then check that we've pulled out the number of records we were expecting, that we haven't got any error messages, that everything seems to load correctly and then we can move on to mapping. So by breaking everything down into different steps, we isolate each step, we can test each step in isolation and we can repeat each step without having to go all the way back to the beginning. So if we get towards the end of our process, we can undo a certain number of steps and redo them without having to clear down everything and start again which with a very complex migration can save a lot of time and headaches.

So I break it down into these five jobs or processes: a GET, a MAP, a PUT, a DELETE and a RUN.

The GET pulls everything out the source database into the staging database.

The map part does all of the transformations to change the input schema into something that looks a lot like the target schema. The only thing that map won't be able to do is resolve foreign key references.

So for example, if we're loading an account into Salesforce, it will be given a Salesforce account ID that will be unique to that record in that org.

And when we then try and load the contacts, we need them to be loaded with an account ID matches the right record for them.

We have the information then in the staging database that tells us what the ID was for the account we're looking for. We'll use an external ID to find the right account and then we use that account's ID to, update that. That can only happen in the put phase of the process where we've already put the accounts in and we're then putting the contacts in.

You can sometimes do that stuff in the map but it creates a dependency that means that say the account has to be got mapped and put before you can do the mapping of the contacts which may not be what you want to do. So if you want to keep things vertically isolated you ensure that you can run all of the get jobs, then all the maps, then all the puts without having interdependencies between, a map and a put separately.

So moving on to what does the put do? That just loads it into the target system. So that's going to take record from our staging database that looks a lot like the target that we're loading to and just push it in. It might do this foreign key resolution to look up based on external IDs, parent records or look up values that we're after.

But that's pretty much it and then it's going to report back on the process and the success of that.

The final two, processes here are helpers really so you have a delete one which is your rollback for each particular object and a run which allows you to orchestrate, the process for an entity or the whole thing and you can be as granular as you like here. On the right here, we've got an example of a run job where it's chaining together the get, the map, the put and then the delete. So this was what you would use it in this way. When you're debugging or testing, you've got to step through and execute the process, run it through, check that the data's there and then remove the data to make it leave it clean from another run.

So here is an example of a GET process.

So we have a query for the Salesforce data and then we're going to stage that directly into a database table.

At this point, we're not going to change the schema, we're to create a replica table in our SQL database that exactly reflects what we pulled out the source system. We can then use that to verify that we got the right data and we can also go back afterwards. So if we get to the end of the process and the client checks the data and says ah there's a problem in this field the value is wrong we can trace that back all the way through our staging tables back to the source record that we pulled in and verify that either the data is correct, know has been related correctly or we can identify where in the process the data the field was transformed incorrectly.

This would be a typical map process so we are now loading from that staging table to pull the records out and we're performing some transformations. So recoding account references or the industry fields. So if we've got pick lists that need to be modified or transformed, this is the point in the process where we do that.

And the output is a mapped table in our staging database that represents as close as possible the target table structure.

Here we have a PUT process which again is quite simple it's just reading from that database table and pushing that into our target system.

So as a result of running that put process we'll have a table with some IDs in it for the newly created records so we can take that and push that into Salesforce through the delete operation to remove them out.

And here we have the orchestration again of all those four processes pulled into one.

So you may ask why would I suggest using a database for staging the data and in that which case what kind of things should I do with it? Well I find the database quite useful especially with complex data migrations because it gives me somewhere to stash things and come back to them. So I can cache the data I pulled out so I don't have to go back to the source system and pull it out again.

In the past I've had some where it's quite slow to pull the data out so you want to minimise the number of times you're pulling that data out from the source system in order to speed up your end to end process.

So how would you set it up? Well keep the logging separate from the actual migration data so in SQL Server that would be two different schemas and use a prefix for the tables so that you can follow the entities through the system. You've got source account that'd be the source data that you load that you pulled in from the source tables target or TGT account would be the transformed data that so the map process will build the TGT table and then the put process will then generate the out account data which is a record of what data was actually loaded into the system.

Any reference data should be prefixed with 'ref' so for example if you're mapping users from one system to another you will need some reference user tables. Using that prefix it keeps it clear it's clearly understandable the purpose of each table and you don't end up accidentally loading the wrong thing.

So what do we do when we're testing the data? This is the final area for methods. We want to make sure that it matches the production data quality so ideally test with production data that can be troublesome so you may well have to implement procedures that control access to that system when you're testing to ensure that you abide by the appropriate regulations.

Use the database as an audit log and proof for your testing so build in those KPIs and brokeouts into the audit tracking in your process so that it's easy to just run a SQL report or run a report over the execution and say this is the result, is what happened and you're not having to trawl through log files trying to work it out.

This is a typical process for, testing, data migration you're going to have a unit testing phase where you're looking at each particular job in isolation making sure that it works on its own then you go into some end to end testing where you're building the whole thing together, and starting to practice and get an idea of how long it's going to take with representative data set volumes.

Your UAT phase is where you get the users back in and get them to check that it looks right you want to be minimising the number of dev changes and fixes you're doing in that part of the process and you really do want to be testing on representative if not live data, if possible.

And then you want to do some dry runs three or four dry runs probably best, so that you can iron out all the problems before you hit them.

Otherwise you'll be up until midnight on the go live weekend trying to fix some data problem that you could have found several weeks earlier.

So that's it for methods, so separate out the discovery from the delivery that gives you an opportunity to re estimate everything and agree the validation methods and testing responsibilities early.

Be really clear about who's taking the risk of the migration, especially if you're contracting, using time and materials or fixed price.

So technology then for that this area we need to understand the source and target of our database migration.

So are we coming from Salesforce to Salesforce, SQL Server to Salesforce, CRM Dynamics? Yeah. What systems are we doing? And we need to know a lot of information about that to understand the limitations that we'll be working under.

For example, in Salesforce, will we be able to turn off triggers and automation when we load in in order to improve the performance? If we do that we'll have to may have to ensure that we can either, trigger the behaviour that those triggers would have run after the migration or we need to incorporate whatever mappings or coded changes that that would have done into our process itself.

So there's a plethora of tools that we can use and we probably want to consider the data volumes when choosing those tools alongside that.

So you can use Excel and Excel plugins to load small volumes in.

The Salesforce import wizard can do up to about fifty thousand but I imagine that would be pretty simple records. You know a single object with no other references perhaps. The Salesforce data loader you can get a bit more complicated but again it gets a bit painful with large volumes of data because it's very difficult to validate huge text files.

And then you can use more commercial products which can make it a lot easier to load, very large data volumes and do a lot more complex transformations.

Final area on technology limits. So with Salesforce in particular, there are some limits that you need to look out for. If you're using the bulk API for example, you can only do ten thousand batches enrolling twenty four hour period. And I have had it on some projects where we've had to reduce the batch size down to between one or ten records and for many thousands of records that can cause you to go over that limit.

In that case, if you're hitting that sort of thing where you can only load one record at a time because of various triggers that you can't turn off in Salesforce, you're going to have to switch from using the bulk API to using the SOAP API, because that doesn't have the same limit. It will not run as fast but then given you're loading one or two records at a time you're not really gaining anything from using bulk asynchronous API anyway.

One of the other ones that will catch people out are the two hundred thousand number of content versions that can be published in the twenty four hour period.

So if you have more than two hundred thousand files that you want to load into your target sales or system, you aren't going to be able to those in within you're not going to be able to load them and get them published within twenty four hours anyway, so you will probably need to plan out, for that taking longer than twenty four hours.

So in summary then for the technology area, find all your source systems, select tools you already know how to use and then check the limits that you're going to be operating under.

And now people so there are many people involved in a data migration and the first one I'm going to talk about here is the data owner. So they're the person who understands, the significance of the data migration.

They're really the owner of the system that's being migrated from or to and they'll be accepting the output of UAT and the dry run testing. So quite an important person, and they will need to take ownership for either everything or you'll have to have multiple data owners but ideally a single person taking responsibility for every, object you're migrating. This would be someone in the client that, would be perhaps sponsor for the project.

The data owner then probably would need to nominate a subject matter expert who would be someone in their team who really understands where all the skeletons are hidden in data.

They know the quality and they can help us to answer the questions that will come up around what should be mapped, why is the data like it is, where should this data go, how's it being used. So they'll be critical to the project.

The dev team then is going to need either one person in each of these roles or you know you could share the roles somewhere but you need these skills covered by the people on your team.

You'll need a project manager to manage the interaction with the client, track milestones and report progress and keep things aligned and help with prioritisation.

You'll need a data migration architect who will be able to draft the migration strategy and lead the ETL developers a few one or more ETL developers to design and build the ETL processes according to the migration strategy, optionally a DBA or access to one for your staging database to ensure that it's properly tuned and works correctly and then a really good tester who will be able to validate the migrated data and process follows the design and is acceptable.

Final part of the pyramid here for people is the skills area so we need to have a team of people who really understand ETL technology, especially in the dev team. And it's good if the data owner and the data SME can understand that as well. So we'll need to tell them about how ETL processes work, what unique keys are and what's going to happen if we de duplicate that sort of stuff.

The data SME is going to need a little bit more knowledge of data and how it's moved around because they are going to be able we're going to need to talk to them about it at that level.

They'll need to know about foreign keys and unique keys and entity relationship diagrams and field metadata.

Our dev team, they really need to be expert in ETL processes, and ideally expert in Salesforce APIs as well so that they can avoid to be Salesforce sculptures that will otherwise trip people up. And it's best if you pick a team well and pick a set of tools that matches the team or a team that matches the tools that you want to use. It's really not a good idea to start a new migration with a completely unfamiliar toolset because that will double the amount of time it's going to take.

Summing up then in the people area keep clear ownership of the data.

It's clearly it's got to be accepted by the business or by the data owner at the end of the project and access to the SME the data SME is critical. It can really slow a project up if it's difficult to get hold of someone who can answer the questions about the data that's being migrated.

And then as I mentioned earlier, pick a team that knows the tools you're going to use or pick tools that knows the tools that you already know and that should ensure success.

So in summary then, data migrations are difficult, but there are four things four key areas we can think of that, help us to be successful and they are data, methods, technology and people.

Thank you very much.

Any questions?

Thank you, Martin. Yeah, I'm going to ask a couple questions that I hope maybe the audience would have asked or not.

The first one is understanding how important documentation is and agreeing with you one hundred percent on that. Do you have any tools that you like to use to document the Salesforce data before you migrate it? Or is it mostly homegrown type, you know, Excel sheets or Word documents or whatever?

So mostly, it ends up being Excel spreadsheets because they're portable. You can hand them over to someone and they can use them. I do have a few tools I've built in Python to extract the Salesforce metadata easily into tables which can then be used.

And one project I did write some stuff in Google spreadsheet as well-to-do some checking across tabs. So you'd have a tab for objects, fields, and then the mappings, then try and validate that the data types you are mapping were correct.

But it's quite difficult to communicate complicated mappings whichever way you do it. So it's, yeah, it's tricky sometimes.

Yeah.

So no silver bullet yet for that, No, not yet.

On the UAT, when I've been involved in projects like this, it seems like UAT is a place where things can fall down, where things can slip through the cracks. You know, the best efforts of the development team, even if they're still going to miss it because they're not subject matter experts. And sometimes the subject matter experts aren't as engaged as they should be. Do you try to put UAT criteria or a testing plan into this contract or have it written down somewhere? How much formalising do you think is the right amount?

I think it's kind of difficult to build a lot into the contract before you know the scope of the migration.

Think one of the key things is to be clear about where the responsibility lies for doing the UAT testing. I think the UAT should be done by the client, and led by the clients because they've got to accept the data when it comes out of the process.

And I think one of the things to do to make it easier is at the beginning of the project to identify what the KPIs are around this data. What does really matter? What matters about it. So if it's a financial institution that's usually money. So we might for example sum up the amount of assets under management and make sure that we're within a certain tolerance of that when we've migrated it and to try wherever possible to automate the testing the migration down to a few simple reports or queries to get a good feel for where the data is correct or wrong and follow that up with some targeted record by record eyeballing of particular potentially problematic records. So that's why doing some data quality profiling in the beginning is useful because that can throw up the curveballs in the source data that might cause problems during the transformation migration.

So that allows you to then go ah okay if we've got a particular scenario we know is going to be problematic we'll create a test case for that we'll ensure we have test data that exercises it and we'll look at in detail at that. So you might do that for you know areas where you think there's going to be a difficult transformation but also important customers. For example, you might have some very high profile customers you want to make sure their data is correct, less important customers you're not so worried about in your data set. So you would prioritize testing with their data and making sure it looks right and operates correctly.

Sounds smart. Since you're using a relational database as sort of your middle user cache kind of for want of a better term is there one that you like better or you think is better suited for this task or is that more of a customer requirements?

It's more down to the customer. I built it in MySQL on a laptop because it was free, but for a client who had Microsoft who had done the same thing on SQL Server.

There are some kind of slight differences to the way in the way that SQL Server and MySQL work which change how what you can do but yes generally I don't have much of a preference it's really only about having somewhere to store the data and run some reports on it and whatever's Well, I think it's a fascinating and often overlooked part of the whole integration process.

I really appreciate your time today and your expertise. Thank you, Martin.

Thank you very much. You're welcome.

‍