Author: David Masri Founder & CEO

When coding your migrations or integrations you’ll come across bad data. It’s better to alter your transformation code to fix that data rather than fix the data itself.
This ensures that when new data is created that has the same issue its already dealt with. A few examples:

1. The source system has a single field for Name and it needs to be split into First and Last. You should either alter the source system to use two fields or write transformation code to split it.
2. The source system has email addresses with spaces or other bad characters in them. The source system stores address lines in separate fields (Salesforce wants them in one, with line breaks between the lines)
3. The dev transforms the data for the first object (at the top of the hierarchy)
4. Names are all capital, but users want them converted to camel case.
5. A comments field is stored as plain text in the source system but is feeding a rich text field in Salesforce. (You need to convert line breaks to HTML
   tags)

For each of these cases, it's a mistake to try and fix the data manually. You will get updated data sets and will have to redo this work, and then you will have to re-QA it. Even if your users fix the data directly in the source, there is no guarantee that that new data won't be created badly (unless new validation code is put into the source system). Not to mention the high probability of human error during the production migration!

For integrations, fixing the source data and adding validation code to the source system is the best way to go, because if that system owns the data, it should also own the validation. If this can’t be done, then fix it in your transformation code. For migrations, it's probably not worth the effort to modify code that's being retired, so doing this in your transformation code is best.

There are some things that just can't be done in code, and a data clean-up project may be warranted. This can be done either in the source system before go-live or in Salesforce after.*

Where Possible, Fix code not data.

*Based on my past experiences, any plan to clean up data after go live, rarely comes to fruition

This article is adapted from my book: Developing Data Migrations and Integrations with Salesforce.