Migrating Business-Critical Data Without Losing Context or Relationships

#The problem, as it actually presented

The brief in this situation is almost always "we need to move the data across". It arrives as a spreadsheet, one tab per object, source field in the left column and target field in the right, with a date by which the legacy systems are meant to be switched off.

The spreadsheet is not wrong. It answers a much smaller question than the one that decides whether the migration succeeds.

A provider consolidating two legacy systems across twenty years of service records does not have twenty years of rows. It has twenty years of decisions. A status value meant one thing before a reorganisation and something different afterwards. A free-text note has been carrying the referral source since the day somebody discovered there was no field for it. A person exists twice because the two systems came from two organisations that merged, and nobody has ever had to reconcile them, because the systems never spoke to each other.

Moving the rows is the mechanical part. Moving the meaning is the engagement.

#Why a field-to-field mapping is not a migration plan

A mapping says where a value lands. It does not say what the value means on arrival, and the gap between those two things is where migrations quietly fail.

Three failures recur.

The same idea, modelled differently. Both systems hold something called a case. In one it opens when the referral is received and closes when the episode ends. In the other it opens when the first appointment is booked, and a separate one is created for each service line. Mapped field to field, both become the same object, and every duration, count and trend computed afterwards mixes two definitions. The report runs. The number is meaningless.

Precision invented on the way in. A source system stores a date with no time, because the process never captured one. The target field is a date-time. Something has to supply the missing part, and if nobody decides, the load supplies midnight in whatever timezone the running user happens to have. Two decades of records then carry a precision that was never collected and a timezone nobody chose.

Values that lost their vocabulary. Picklists accumulate. A status list edited for twenty years holds live values, retired values, values differing only in punctuation, and values that were once a workaround for a missing field. A mapping that transfers them all preserves the mess and hard-codes it into the new automation. A mapping that collapses them silently destroys a distinction somebody will ask about.

What closes this gap is profiling before mapping: reading the actual distribution of values in the source rather than the documented one. It is unglamorous, it takes longer than anyone budgets, and it is the only way to discover that a field described as mandatory is empty across most of the historic records.

#Relationships, and the reference nobody notices is broken

Rows have order. This is the genuinely mechanical part of a migration, and it still goes wrong more often than anything else.

A child loaded before its parent has nowhere to point. Depending on how the load is written, one of two things happens. The load fails loudly, which is fine. Or the reference is left empty and the record is created anyway, which is not. An empty lookup on a service record raises no alarm. It surfaces months later, when somebody notices a person with no history, or a report whose totals are smaller than everyone remembers them being.

So load order is designed rather than discovered. Accounts before contacts, contacts before cases, cases before the activities and files that hang from them, and every junction record after both of the things it joins. Where a relationship is circular, one side is loaded with the reference empty and a second pass populates it, and that second pass is a named step with its own reconciliation rather than a clean-up somebody is expected to remember.

The harder version of the problem is the relationship that exists in the source only as a convention. A note saying to see the record for the next of kin is a relationship. A reference number typed by hand into a text field is a relationship. These are real, they are not modelled, and the choice is either to reconstruct them, which is a judgement exercise with a review queue attached, or to declare them out of scope in writing. What is not available is discovering them after cutover.

#External IDs, and the cost of not having them

Every object receiving migrated data carries a unique external ID holding the source system key, populated on the first load rather than added after the first incident.

That is one field, and it changes the character of the whole exercise.

It makes the migration re-runnable. With an external ID, the load is an upsert. Running it twice produces the same result as running it once. A batch that fails part way can be restarted. A defect found in a transformation can be fixed and the affected records reloaded in place. The rehearsal can be run repeatedly against the same target without accumulating duplicates. Without it, every load is an insert, every re-run is a second copy, and recovery from a bad batch is a deletion exercise on records a user may already have touched.

It makes reconciliation possible. Proving a source record arrived means finding it. Matching on a name and a date works until two people share both, which across twenty years of service records is not a hypothetical. Matching on the source key is exact.

It survives the project. After cutover, the external ID is how a question about a legacy record is answered, how the archive joins back to the live org, and how a later integration recognises what it is looking at. This is the third class of decision described in the data model decisions you cannot cheaply undo: the field is cheap to add at any time, but the mapping it would have recorded cannot be reconstructed afterwards, except by redoing the unreliable matching that caused the problem.

Where both source systems hold a version of the same entity, each gets its own external ID field. Collapsing the two keys into one loses the ability to say which system a record came from, and after a merge that is the first question asked whenever the two disagree.

#History, audit trails, and what cannot be recreated

Created date and last modified date can be set on load where the org is configured to permit it. Field-level history cannot. The platform generates that from changes it observes, so a trail of changes made in a system being retired cannot be replayed into it, and neither can the identity of a user who left years ago.

This is not a technical obstacle to route around. It is a decision to make in the open, because where auditability obligations apply, quietly dropping the trail is the worst of the available options and the one that happens by default.

There are three honest choices.

Migrate the current state and keep the trail in the archive. The live org holds the record as it stands. The full change history stays in a read-only store, retained for as long as the obligation requires, joined back by external ID. We recommend this most often, because it keeps the org fast and the obligation met at the same time.

Reconstruct a summarised history as records. Where a change trail has real operational value, it is loaded as data on a purpose-built object rather than dressed up as platform history. It reads as data, it is reportable, and it is labelled as migrated rather than observed.

Establish that it is not required, and write that down. Sometimes the correct answer. It only counts when the person who owns the retention obligation has recorded the decision.

#What to archive rather than migrate

Retention obligations pull against a clean org, and both pressures are legitimate. The obligation says keep it. Good design says do not put two decades of closed, untouched records in front of every user and every query that runs, for the reasons set out in why an org feels slow.

The reconciling principle is that retention is about being able to produce the record, not about the record living in the CRM.

So the boundary is drawn deliberately. Records that are operationally live, or that a user will realistically open beside a live record, are migrated. Records that exist to satisfy retention are archived to a store that is cheaper, immutable and access-controlled, indexed by the same external ID, with a documented path to retrieve one on request.

Two constraints shape where that store sits and who may read it, and both are questions about the class of obligation rather than about technology. Where the data may physically reside is one. Who may see which parts of it is the other, and access to historic clinical detail is usually narrower than access to the operational record, which means the archive inherits its own access design rather than borrowing the one built for the org. Segregation of duties applies to the migration team as much as to the users: the people who can move the data are not automatically the people entitled to read it. The same reasoning appears wherever an org sits inside a supervised organisation, which is the subject of what changes when the org belongs to a bank.

Data 360 earns its place here when the same person has to be recognised across the operational org and the retained history without copying the sensitive detail into the org to do it. Unifying an identity is not the same as loading a record, and keeping those two things separate is what lets the org stay minimal while the history stays complete.

#Reconciliation that proves the migration

A migration is not finished when the load completes. It is finished when somebody who did not run it can verify it.

Counts alone do not do that. Equal row counts on both sides are entirely consistent with every value being wrong. So reconciliation is designed as a set of assertions written before the load, each one checkable by a person:

• Every source record of each type has exactly one target record carrying its key, and no target record carries a key absent from the source.
• For each migrated relationship, the number of children per parent matches the source, and no migrated child has an empty parent reference where the source had one.
• For every transformed field, the distribution of resulting values matches the mapping decision. A status collapse designed to produce four values that produces five means a value was missed.
• Amounts and dates are compared value by value across a sampled set, chosen to include the oldest records and the known-awkward ones rather than drawn at random.
• A named set of real records, picked by the people who use them, is opened in the new system and read end to end by somebody who knows what it should say.

The output is a reconciliation report produced by the load process itself and kept as evidence that the migration was correct. Where auditability obligations apply, that artefact is not project overhead. It is part of what makes the new system defensible.

#The cutover rehearsal

A migration you have not practised is a plan, not a procedure.

The rehearsal runs the whole sequence against a full-volume copy: extract, transform, load in order, reconcile, then the fallback. It is run more than once, and readiness is not that it worked. Readiness is that two consecutive runs produced the same result and the elapsed time is known rather than estimated.

The rehearsal is where the unpredicted things surface. A transformation that is fine on a sample and slow at full volume. A validation rule or an automation that fires during the load and rewrites values behind the migration, which is why automation is deliberately suspended for the load and its reinstatement is an explicit checklist item rather than an assumption. An integration user permission that is correct in the sandbox and absent in production.

The fallback is rehearsed too, and it has to be a real one. Saying you would restore from backup is not a fallback if nobody has timed the restore. The plan names the point of no return, the person who makes the call, the criteria they make it against, and what the organisation does for the people it serves during the window while the old system is brought back.

#The decisions that were contested

Not migrating everything. The instinct where obligations apply is to bring it all, because keeping feels safer than deciding. The counter-argument is that an org holding two decades of untouched records is slower for everyone, harder to secure, and no better at meeting a retention obligation than an archive designed for exactly that. What settled it was writing down where each class of record would live and having the retention owner sign the list.

Rebuilding history as data rather than pretending. There was pressure to load a change trail into something that would look like platform history. We argued against it, because a trail that appears to have been generated by the system but was actually assembled by a migration script is a misleading artefact in precisely the situation where the trail matters most. It goes in as data, labelled as data.

Keeping the two source keys separate. The tidier design collapses both legacy identifiers into a single external ID. We kept both, because for years after a merge the most common data question is which system said what, and one field cannot answer it.

Manual review for ambiguous matches. Deduplicating people across two systems produces a band of near-matches that no rule resolves confidently. Automating that band would eventually merge two different people who share a surname, a suburb and a year of birth. On a service record that is not a data quality issue. The queue stays manual, and it stays manual on purpose.

#What changes

The outcomes worth claiming here are operational, and they follow from the design rather than from effort.

The second-order effect is the one leaders notice on the weekend itself. When the reconciliation report exists and the rehearsal has been run twice, the go or no-go conversation stops being a discussion about how everyone feels and becomes a reading of a document. That is a different meeting.

#What we would tell you before starting

Spend the time at the front. Profiling and the archive boundary are the two activities that look like delay and are actually the whole schedule, because everything downstream is priced by how much has to be migrated and how many value decisions remain open when the build starts.

Get the external IDs in before the first load, on every object, one field per source system. It is the cheapest insurance in this kind of work, and there is no version of it you can add later at the same price.

And be honest early about history. Ask the person who owns the retention obligation what has to be producible and for how long, get the answer in writing, and design to it. Migrations that go badly in this setting rarely go badly at the load. They go badly at the moment somebody asks a question about a record from years ago, and the answer is that the system no longer knows.