Just finished week two of our migration from a legacy, homegrown CRM to Salesforce, and I think I've looked at more bad data than actual code this week. 😅 The actual data transfer scripts? Maybe 200 lines. The data validation, cleaning, and mapping logic? Over a thousand.
It feels like 80% of the effort is untangling years of "temporary" workarounds. Our old system let users enter almost anything. For example, our "Industry" field had values like "Tech," "Technology," "IT Services," "IT," and my favorite, "Tech?? (please confirm)." Mapping these to Salesforce's clean picklist is a nightmare.
I built a small Python script using `pandas` and `fuzzywuzzy` to help cluster and suggest standardizations. Maybe it'll help someone else here:
```python
import pandas as pd
from fuzzywuzzy import process
def clean_categorical_column(series, canonical_list, threshold=80):
"""Suggest canonical values for a messy column."""
suggestions = {}
for unique_val in series.unique():
match, score = process.extractOne(unique_val, canonical_list)
if score >= threshold:
suggestions[unique_val] = match
else:
suggestions[unique_val] = "REVIEW MANUAL: " + unique_val
return suggestions
# Example usage
old_industries = df['old_industry'].dropna().unique()
salesforce_industries = ['Technology', 'Healthcare', 'Finance']
mapping = clean_categorical_column(pd.Series(old_industries), salesforce_industries)
print(mapping)
```
**What I've learned so far:**
* **Profile First, Map Later:** Run exhaustive reports on field uniqueness, null rates, and value distributions *before* you even think about mapping logic.
* **Build a "Dirty Data" Log:** Every record that fails a validation rule gets logged with its ID and reason. This becomes your cleaning to-do list.
* **Expect the "Other" Category:** No matter how good your picklist is, you'll need a catch-all for un-mappable legacy values. Plan for it.
Is this normal? Did anyone else feel like an archaeologist of bad decisions instead of a developer? What were your biggest data horror stories, and how did you automate the cleanup?
Happy coding
Clean code, happy life
Oh you are absolutely not alone. That 80/20 split feels painfully accurate - sometimes it's more like 90/10. The "temporary" workaround data is the absolute worst, because it often *worked* in the old, forgiving system, which means there's a ton of it.
I love the fuzzy matching approach for picklists, it's a lifesaver. Where I've seen this get really gnarly is with things like address data or person names. You can have "St.", "Street", "St", and "Str." all in the same column, and then you need to reconcile that with external systems for enrichment. The logic for parsing and standardizing that can dwarf the actual import.
One piece of unsolicited advice from going through this a few times: build your validation rules *now* in the new system before you let anyone touch it. If you don't, you're just kicking the can down the road and will be cleaning this same data again in three years. Salesforce validation is your best friend to stop "Tech??" from ever happening again 😄
Has your script helped you find any truly bizarre legacy entries that made you laugh (or cry)?
Pipeline is king.
That fuzzy matching script is a great start. I've found these one-off cleaning scripts can become technical debt themselves if you're not careful.
Consider wrapping that logic in a Jenkins pipeline stage or a GitHub Actions job. This forces you to version the cleaning rules alongside your migration code and gives you a clear audit trail of what transformations ran. Something like:
```groovy
stage('Standardize Industry Data') {
steps {
script {
def cleaned = sh(script: 'python scripts/clean_industry.py legacy_export.csv', returnStdout: true)
writeFile file: 'standardized_output.csv', text: cleaned
}
}
}
```
It also makes rerunning specific cleaning steps trivial when you discover another edge case next week.
Commit early, deploy often, but always rollback-ready.
"Build your validation rules now" is good advice, but only if the new system's validation doesn't become the next bottleneck. I've seen teams get so strict that workflows break and new, worse workarounds are born in shadow systems. Sometimes you need a phased approach, not just a hard gate.
That address standardization problem is a classic. Fuzzy matching helps, but it can also introduce silent errors. You trade manual cleaning time for manual review time of its false positives, especially with international data.
The truly bizarre entries usually come from people who knew the old system was junk and tried to flag it. "Tech??" is a cry for help. The ones that make me cry are the nulls disguised as valid strings, like "N/A", "NULL", or a single space. Those just propagate invisibly.
Don't panic, have a rollback plan.
That's a really good point about one-off scripts becoming debt. In my limited experience with dashboard projects, I've seen similar issues with transformation logic in Power Query or Tableau Prep. It starts as a few steps to fix a column, then grows into an unmanageable, undocumented chain.
The idea of versioning the cleaning rules alongside the migration code is something I hadn't considered. Does placing the logic in a CI/CD pipeline also help when you have to document the data lineage for your reports later? I imagine having that audit trail you mentioned would be essential for explaining how a metric was derived from the raw source.
Your 80/20 estimate is actually quite conservative in my experience. That thousand-line validation layer is the real migration - the transfer script is just the final copy step. I've seen the same pattern when consolidating data from multiple legacy systems.
The fuzzy matching approach is good for a first pass, but it has a subtle scaling problem. The `fuzzywuzzy` process.extractOne function does a pairwise comparison with every canonical value for every input string. With a large canonical list and a large dataset, this can become a significant bottleneck, O(n*m). For one migration, we had to replace it with a two-stage approach: an initial exact match lookup using a dictionary of known mappings we built manually, then a much smaller fuzzy pass only on the remaining unresolved items. This cut the runtime from hours to minutes.
Also, be careful with that `threshold=80` default. It's often too permissive for short strings. "Tech" and "Teach" can score in the 80s, but would map to entirely different industries. Consider lowering it for longer strings and raising it for fields with short, ambiguous values.
brianh
Yep, that script looks familiar, and it's a solid starting point! Just a small heads-up on a dependency snag: `fuzzywuzzy` can be tricky to install in some CI environments because it requires `python-Levenshtein` for speed. You might consider the `thefuzz` package (the renamed, actively maintained fork) or `rapidfuzz` if performance becomes an issue.
Also, the `"REV"` placeholder is smart. That's exactly how you keep manual review in the loop. I'd suggest logging those "REV" items out to a separate CSV with the original value and the top fuzzy match, so you're not starting from scratch when you review them. It makes that manual step way faster.
Clean code is not an option, it's a sanity measure.
I can definitely see how that fuzzy matching script would be a huge help with the "Industry" field problem. The way it flags items for review with "REV" is a smart safety net.
I'm curious about something, maybe because I'm less technical. When you set your threshold to 80, how did you decide on that number? Did you run some tests to see if that caught enough of the messy variations without creating too many false matches? I'm always worried about making those judgment calls without enough data.
You've hit on a key benefit I've observed: treating cleaning rules as code within a CI/CD pipeline absolutely helps with data lineage. The audit trail from the pipeline runs provides a de facto, time-stamped record of each transformation applied. This is critical for report certification.
However, a caveat to your point about Power Query chains is that moving logic to a pipeline can simply shift the debt elsewhere if the underlying cleaning scripts aren't modular and well-tested. I've seen teams create a "pipeline monster" that's just as opaque as the Tableau Prep flow it replaced.
For your specific question, yes, it helps for deriving metrics. When an analyst questions a number, you can point to the specific Git commit and pipeline run that produced the dataset, showing the exact cleaning rules that were active at that time. This moves the conversation from "is the data right?" to "should our rule for handling 'N/A' be changed?"
Data > opinions
Absolutely, that audit trail for metrics is the holy grail. When a CRO asks why the pipeline says 2.1 million and the dashboard says 2.3 million, being able to pull up the exact Git commit where we standardized "M" vs "Million" is priceless. It turns a blame game into a process discussion.
But your caveat about creating a "pipeline monster" is so real. I've seen teams just lift their 200-line Python script, with all its hard-coded paths and magic numbers, and drop it into a Jenkins job. It's versioned, yes, but it's still spaghetti. You still can't test the logic for handling "N/A" in isolation.
The trick, I think, is to force the same discipline you'd have for application code. That means breaking the cleaning logic into small, single-purpose functions with unit tests, and having the pipeline job call a versioned module. Otherwise, you're right, you've just made your debt more official.
Pipeline is king.
That fuzzy matching script is a lifesaver for the initial pass. Been there many times. Your 80/20 effort split is dead on; I actually think the planning and scoping for this phase is where a lot of migrations fall down. Clients just don't anticipate the sheer volume of entropy in their own data.
One thing I'd add from hard experience: be prepared for that "REV" list to be your most important deliverable. It's not a failure bucket - it's a change management tool. That list of ambiguous entries ("Tech??") is often a direct map to internal disagreements or process gaps that were papered over in the old system. Presenting it to stakeholders forces the conversation about what "Technology" actually means to the sales team vs. finance, and you can bake that decision right into the new validation rule. So in a way, you're not just cleaning data, you're fixing the business logic that made it messy in the first place.
Implementation is 80% process, 20% tool.
That initial 80/20 estimate you have is spot-on for the discovery phase, but in my structured tests, the validation and cleaning portion consistently expands to consume 90-95% of the total migration timeline. The "Industry" field example is a perfect microcosm of the entire problem.
Your Python script is a logical first-pass tool. However, my method requires a step before fuzzy matching: building a comprehensive mapping dictionary from historical data. This involves extracting every unique value for that field over the last, say, three years and manually resolving them to the canonical picklist once. This dictionary becomes your first lookup layer. You then run fuzzy matching only on values not found in the dictionary, which drastically reduces computational load and false positives. The "REV" bucket should be for genuinely new, unseen entries, not for re-evaluating the same "Tech??" entry for the thousandth time.
This approach also surfaces systemic issues. If you see "Technology" mapped to "Tech" for Q1 data but to "IT Services" for Q2, it points to a training gap or a process change that wasn't documented, which is valuable intelligence for the post-migration ops plan.
That pre-fuzzy matching dictionary approach is the only way I've found to make these projects sustainable. It's exactly what I meant by "cleaning logic as code" in an earlier post - that manually resolved mapping table is a versionable artifact.
There's a second-order benefit you didn't mention. Once you have that canonical mapping dictionary, you can treat it as a reference for real-time data entry validation in the new system. You can build a simple API that takes an input string and returns the matched canonical value, or flags it for immediate review. It turns a migration cleanup task into a foundational data governance tool.
My one caveat is that the dictionary's shelf life depends on the domain. For a stable field like "Industry", it works for years. For something like "Product Category" in a fast-moving company, it can become outdated in a quarter, requiring a strategy for continuous dictionary maintenance.
That shelf life caveat is critical and directly impacts the ROI of building the dictionary in the first place. If it decays in a quarter, you've just created a maintenance liability.
The strategy for continuous maintenance often gets glossed over. You need a clear owner and process, usually in the operational team that uses the field, or it'll rot. Budgeting for that ongoing ownership is a non-negotiable part of the initial project scope.
—hd
Ugh, the "Tech?? (please confirm)" hit me right in the feels. That's the perfect example of data debt, where a process question just got stored in a field forever.
Your 80/20 estimate is generous, honestly. I've found that initial script is just the first 10%. The real time-sink is the social part, getting everyone to agree on what "Technology" officially means for the new system. That fuzzy matching output is great, but it often becomes a negotiation document between teams who all used the field differently. Good luck with the rest of it
Happy customers, happy life.