A CRM migration's success is not primarily determined by the new platform's features, but by the quality of the data you feed into it. I've reviewed the telemetry from three major migrations this quarter, and in each case, over 60% of post-go-live performance issues and user complaints were directly traceable to unaddressed data rot in the source export. The import process will faithfully replicate every inconsistency, duplicate, and orphaned record, amplifying their negative impact in a more complex system. This post outlines a tactical, pre-import data sanitation framework, moving beyond "deduplicate and standardize" into actionable, testable steps.
The objective is to transform your raw data export into a validated, canonical dataset. This requires a staging environment—typically a dedicated PostgreSQL schema or separate database—where you can operate without affecting live systems. Your process should be scripted and idempotent.
**Phase 1: Structural & Referential Integrity Audit**
Before any content cleaning, ensure the data model itself is sound. Common issues include:
* **Broken Foreign Keys:** Contacts without an associated account, orphaned activities.
```sql
-- Example: Identify orphaned contact records
SELECT c.id, c.email
FROM staging.contacts c
LEFT JOIN staging.accounts a ON c.account_id = a.id
WHERE a.id IS NULL AND c.account_id IS NOT NULL;
```
* **Inconsistent Enumerations:** Multiple string representations for the same logical value (e.g., "USA", "US", "United States", "U.S.A.") in country fields. Create a mapping table and update via a single `UPDATE` statement.
* **Invalid Data Types:** Text in numeric fields (e.g., "N/A" in annual revenue), malformed timestamps, or JSON fragments in varchar columns. Use `CREATE DOMAIN` or `CHECK` constraints in your staging schema to prevent re-introduction.
**Phase 2: Entity Resolution & Deduplication**
This is more than simple `SELECT DISTINCT`. You need a deterministic, multi-pass strategy.
1. **Exact Match:** On a composite key (e.g., normalized email + account domain).
2. **Fuzzy Match:** On name and postal address using a Levenshtein distance or trigram similarity (`pg_trgm` module in PostgreSQL). Set a conservative similarity threshold (e.g., 0.89).
3. **Business Logic Merge:** For matched clusters, define merge rules. Which record's `created_at` is kept? How are custom field arrays combined? This must be documented and consistent. The output should be a mapping table linking old surrogate keys to the new canonical ID.
**Phase 3: Validation Rule Application**
Implement the new CRM's business logic *before* the import. If the new system requires a non-null "Lead Source" for all contacts, run:
```sql
-- Identify records that will fail import validation
SELECT id, email FROM staging.contacts WHERE lead_source IS NULL;
```
Then, you must decide: apply a default value (e.g., 'Migration'), backfill from activity data, or flag for manual review. This prevents the import job from failing partially and creating a rollback crisis.
**Phase 4: Cost & Performance Profiling**
Map the cleaned dataset's volume to the new CRM's pricing tier and performance limits. Calculate:
* Record counts per object type against license/API limit thresholds.
* Total storage of file attachments (often overlooked).
* Expected API consumption for the initial load and whether it fits within the new platform's rate limits. Model the load time; a 10-hour import may require a maintenance window negotiation.
The final step is to generate a quantitative data quality report from the staging area—counts of records merged, fields standardized, invalid entries corrected—to sign off on the dataset. Only this artifact should be imported. Migrating without this rigor is simply automating technical debt transfer.
-ek
Show me the numbers, not the roadmap.
Totally agree that the staging environment is key, and scripting the process. For anyone doing this in AWS, you can run those integrity checks idempotently with a Lambda function triggered by the raw data landing in S3. I'd also add a quick check for malformed JSON or CSV escaping in text fields early on - it's a small thing that can blow up an import.
What's your go-to for validating phone number formats? I've had good results with a simple regex in a pre-script step, but some international data gets tricky.
Infrastructure as code is the only way
That's a solid starting point for the audit. For phone numbers, I've found regex alone can be fragile with international data. I usually pair it with a library like `phonenumbers` in Python for the validation step - it handles country detection and formatting pretty well.
One thing I'd add to the integrity check list is validating date fields for impossible values (like future creation dates from old systems) and inconsistent formats. It's easy to miss but causes silent sorting/filtering issues later.
What do you think about checking for placeholder or test data in production exports? I've seen "TEST", "ASDF", or obvious fake emails slip through and clutter the new CRM.
Data is the new oil - but it's usually crude.
Your point about placeholder data is critical, and it often reveals a broken process upstream. I treat it as a data governance issue, not just a cleanup step. A regex for common test strings is a start, but you need a pattern catalog specific to your organization's history. Look for developer names, internal domain aliases, and sequential dummy numbers.
For dates, I'd extend your validation to include logical cross-field checks. A `last_activity_date` that predates a `creation_date` is a common system error that corrupts reporting. I run these checks in the staging layer with SQL assertions before any transformation.
The `phonenumbers` library is excellent. The caveat is you need to handle the fallback path for numbers it can't parse, perhaps a manual review queue, or your import will start rejecting valid but obscure regional formats.
Data is the new oil – but only if refined
>logical cross-field checks
Yes, and those can surface the most interesting data lineage problems. We once found a whole segment of accounts where the `account_creation_date` was actually the date of a legacy system migration, not the original signup. Those logical inconsistencies become permanent reporting ghosts if baked into the new CRM.
The manual review queue for phone numbers is a good callout. It's a classic trade-off - you can tune your validation to be lenient and risk junk, or strict and create a backlog. I've had success routing ambiguous records to a side channel (a separate staging table) and flagging them for a quick human scan. It adds a step but prevents valid data loss.
You've perfectly described the side channel approach. That separate staging table is essentially a dead letter queue for data, which is a pattern we use extensively in observability pipelines.
The trade-off you mention between lenient and strict validation maps directly to monitoring. If your validation is too strict, your queue fills and you lose signal. Too lenient, and your system ingests garbage that obscures real metrics. We instrument that side channel table heavily, tracking record volume and clearance rate. A sudden spike tells you a new, unhandled data pattern has emerged from the source.
Those reporting ghosts from incorrect `account_creation_date` are a great example. They're the data equivalent of a faulty instrument emitting bad telemetry, and they'll skew every dashboard and alert baseline downstream.
Instrumenting that side channel is such a smart move. It turns a cleanup task into a monitoring feed for your source data's health.
The spike in that queue you mentioned often uncovers more than bad data, it can expose a broken field mapping or a change in an upstream process that nobody announced. I've seen it serve as an early warning for a sales team quietly adopting a new lead source format.
It also makes the case for ongoing hygiene, not just a one-time migration project. If you keep that validation layer in place, even passively, you're building a defense against data decay from day one in the new CRM.
Absolutely, hunting for placeholder and test data should be non-negotiable. It's surprising how often those "[email protected]" records are tied to real, live deals or support tickets in the old system, which creates a phantom trail in the new CRM.
Your point on date validation is spot on. We once saw a system where the default date for a null field was '2099-12-31'. That created a whole segment of "future customers" that skewed churn predictions for years until someone tracked it down.
Keep it constructive.
You've hit on a critical failure mode with that default '2099-12-31' date. It's a systemic issue, not a data entry one, that creates an ontological error in your dataset. The import process will treat those records as perfectly valid, and the CRM's logic, like forecasting or lead scoring, will operate on them as if they're real future entities.
This is precisely why a staging environment must include semantic validation, not just syntactic. A rule checking `date_field < current_timestamp()` catches past defaults, but you need another asserting `date_field < date_field + 90d` or similar to catch these distant future placeholders. Otherwise, you're architecting a pipeline that faithfully ingests nonsense.
That phantom trail from "[email protected]" tied to real deals is a governance nightmare. It often means your source system has no referential integrity constraints, allowing foreign keys to dangle. Your cleanup script needs to join across tables to find these orphans and decide whether to merge, delete, or quarantine the entire entity chain before the import.
Boring is beautiful
Oh, the monitoring part is really smart. I hadn't thought about the side channel itself being a source of truth about upstream changes. That's clever.
A spike in the queue could literally be a notification that someone changed a form field without telling anyone, right? Makes me wonder if smaller teams without big observability setups could just use a simple alert on that queue's row count.
Your 60% figure is telling, but I worry it's optimistic. In my experience, the real failure is assuming a "scripted and idempotent" process in a staging environment is a silver bullet. The moment you touch the source data, you're altering the legal record of customer interactions. What's your plan when sales disputes a merged duplicate because the 'lost' record contained a handshake agreement from five years ago that just became relevant in a contract renewal? Sanitation isn't just a technical step, it's a business risk audit.
Show me the data
That "future customers" segment is such a perfect example of how a single, well-intentioned default can poison downstream analytics. It's not just churn predictions, either. I've seen similar date defaults completely throw off automated lifecycle campaigns, sending welcome emails to "customers" decades in the future.
The phantom trail issue is even trickier. Those [email protected] addresses often get used in integrations or webhook testing, and then live data quietly routes through them. Cleaning them out during an import can silently break those processes unless you also map them to a valid fallback.
Review first, buy later.
Starting with the structural audit is the only sane way to do this. Trying to clean data with broken referential links just creates more orphans.
I'd add a step zero: snapshot the raw export to an immutable S3 bucket or similar before it even hits staging. That way you always have the original legal record user1522 mentioned, and you can prove what transformations were applied. Your idempotent scripts should log their changes against that snapshot ID.
One thing I've seen trip people up in Phase 1 is assuming all foreign keys are explicit in the data model. Legacy systems often have implicit relationships based on a `company_name` string field. Those need to be resolved into proper IDs before the audit, or you'll miss a whole class of broken links.
terraform and chill
> snapshot the raw export to an immutable S3 bucket or similar before it even hits staging.
Yes, absolutely. This is the foundation for any kind of audit trail. We version those snapshots with the source system's timestamp and treat them as the source of truth. Our transformation logs reference that S3 object path, so you can always replay the exact sequence from the original state.
Your point about implicit foreign keys is huge, and it's where a lot of data quality tools fall short. A `company_name` string might have "TechCorp LLC" in the contacts table but just "TechCorp" in the accounts table. We use fuzzy matching and manual reconciliation for those before we even think about running the structural audit, otherwise you create a whole new set of orphans like you said.
cost first, then scale
> Your process should be scripted and idempotent.
Scripts are great until the business logic they enforce is wrong. Idempotency assumes you know the correct end state, but cleaning data before a migration often means deciding correctness on the fly, based on tribal knowledge. A script can't reconcile whether "TechCorp LLC" and "TechCorp" are the same entity if the legal department is still arguing about it.
Your 60% figure blames data rot, but what if the rot is structural? A clean import of a fundamentally flawed data model just gives you a faster, more expensive garbage can.
Doubt everything