This is a critical and often underestimated challenge in any non-trivial CRM migration. The core issue is maintaining data integrity when the source system remains mutable. A simple "dump and load" is insufficient; you need a strategy to capture deltas.
The most robust approach I've implemented involves a multi-phase process with a final cutover synchronization. The key is to treat the initial data extract as a baseline, then switch to capturing incremental changes until the exact moment you decommission the old system. This typically requires enabling change data capture (CDC) mechanisms or, at minimum, leveraging high-fidelity audit logs or `updated_at` timestamps.
For example, on a recent Salesforce to HubSpot migration, we used the following pattern:
1. **Initial Extract & Load:** Pull a full dataset, using a watermark timestamp (`T1`).
2. **Delta Capture Phase:** During the UAT and pre-cutover period, a separate process continuously queries for records where `SystemModstamp > T1`, transforms them, and stages them in a queue or a staging table in the new system's format.
3. **Final Cutover Sync:** At the designated downtime window, you stop writes to the old system (or enforce a brief freeze), process the remaining queue, and perform a final validation sync.
The technical complexity lies in ensuring idempotency and handling hard deletes. Your delta processor must be able to re-apply the same update without causing duplicates or errors. Here's a conceptual idempotent upsert for a staging table:
```sql
MERGE INTO staging_table AS target
USING (SELECT :id, :new_field_data, :updated_at FROM delta_queue) AS source
ON target.legacy_id = source.id
WHEN MATCHED AND target.last_updated < source.updated_at THEN
UPDATE SET target.field_data = source.new_field_data, target.last_updated = source.updated_at
WHEN NOT MATCHED THEN
INSERT (legacy_id, field_data, last_updated) VALUES (source.id, source.new_field_data, source.updated_at);
```
What often breaks is not the technology, but the business process. Users creating critical records or updating opportunities in the final hours before cutover, without understanding those changes are in flight, can lead to post-migration confusion. Clear communication, strict change freezes, and a well-defined rollback plan are as important as the technical solution.
I'm interested to hear how others have managed the trade-offs between a long delta-capture phase (complex, but less downtime) and a shorter, more aggressive freeze period (simpler, but business-impacting). What was your synchronization strategy?
—J
Agreed on the CDC pattern, but relying solely on timestamps like `SystemModstamp` can be a risk if the source system's clock skews or the granularity is insufficient. We learned this the hard way during a NetSuite migration where `last_modified` was only precise to the second, causing collisions and lost updates in a high-volume environment. We had to augment it with a logical sequence ID from the application layer.
Your multi-phase approach is sound. One additional step we now bake in is a pre-cutover reconciliation report, run after the final delta capture but before flipping writes to the new system. It's a sanity check comparing record counts and checksums of key fields from the source and target, using the same watermark logic. It doesn't catch everything, but it adds a layer of confidence and can highlight systemic transformation errors.
The real challenge, in my experience, is handling hard deletes in the source during the delta phase. CDC logs often capture them, but if you're only using `updated_at`, those records vanish without a trace. You need a mechanism to propagate the delete operation, which usually means a separate, parallel process scanning a dedicated trash table or audit trail.
Oh man, the hard delete problem is the absolute worst. It's the ghost in the machine. Even with CDC, if your target schema isn't designed to receive and interpret a delete event, you just end up with orphaned data. I once saw a marketing automation platform still sending emails to "customers" who'd been deleted from the source CRM six months prior because the sync only handled upserts.
Your point about granularity is the killer. We did a Dynamics migration where the modified date field rounded to the *minute*. A whole minute of transactions could be lost. The sequence ID trick is crucial. We ended up creating a dedicated "migration version" column in the source, a simple integer we bumped via a trigger on any change, to have a monotonic, order-preserving key to follow.
That pre-cutover report is a life saver. We run it, but we also run a sample of "high-risk" records through a full diff, not just counts. It's saved us from at least two catastrophic transformation bugs where the data moved but the meaning didn't.
APIs are not magic.
The dedicated version column is the right idea, but adding triggers to the source is a risk I won't take on a critical system during migration. It's another point of failure.
Better to consume the CDC log directly and maintain your own immutable sequence. It's more work but doesn't touch the source schema.
Your sample diff on high-risk records is good. We extend that to a full "touch test": pick a sample of records, modify them in the old system after the delta pass, and verify the change appears in the new system. Catches dead syncs.
Least privilege is not a suggestion.
That "ghost in the machine" problem with deletions is something I hadn't even considered. It makes sense, but it's a bit scary.
So if I'm understanding correctly, you not only need to map where the data goes, but also what every *type* of change *means* to the new system. A delete needs to be an archive, or a status change, or an actual delete over there, depending on the rules.
Your "migration version" column idea seems smart for ordering, but user64's point about not touching the source system gives me pause. Is the trade-off always between complexity in the sync logic versus risk to the source?