A CRM migration rarely goes wrong at the import. It goes wrong in what the import didn’t tell you.

In August 2026 I moved the Claims Bible data out of four Airtable bases and into one. I co-founded Claims Bible, so it was my own data and I can show you the detail rather than the version a client would sign off. There were 305,713 records in the backup, and 2,512 claims and 1,108 people went into the new structure with zero rejections. I’d point at that zero with some caution, because a loader that rejects nothing is only telling you it accepted what it was given, and most of this note is about what it was given.

The column headings aren’t the data

One of the legacy tables had 161 columns. When I went through them, 118 were empty. Every one of those columns had been added because somebody needed to record something at some point, and the heading still said what it was for, but nothing had been written into it in years, or ever.

So the first scripts I wrote didn’t move anything. They reported what each column actually contained, how many rows had a value, what the values looked like and how many different ways the same thing had been written. That’s slower than mapping headings to fields, and it’s the only way I know of finding out what you’ve really got.

It also turns up things nobody knew were there. When the old app that fed the bases was being retired, I exported everything it held, and reading through it I found a reference generator that gave each lender on a claim a letter, A for the first, B for the second and so on. On a claim with thirty lenders it ran past Z and into punctuation. Nothing had ever complained, because nothing downstream checked.

Duplicates are a design problem before they’re a data problem

There were 87,126 customer rows across the bases, and when I matched them on email they came down to 53,559 people. Nearly four rows in ten were somebody who was already there.

The tempting fix is a deduplication pass, and I did run one, with a preflight that showed me every merge before it happened. But the duplicates weren’t really a data problem. The old structure kept a customer record inside every claim, so a person who made two claims became two customers, and no amount of cleaning would have stopped that happening again the following week.

The new structure has a table of people and a table of claims, and a person exists once with as many claims as they need. The relays that bring new enquiries in were rebuilt to look the person up first and only create them if they’re genuinely new. That combination took the duplicate rate from 39% to 18%, and it’s the second half, the plumbing, that keeps it there.

If you take one thing from this note, it’s that a migration which cleans the data and leaves the inbound route as it was will be dirty again within months, and you’ll have paid for the clean-up twice.

A field that’s always true is telling you about the system, not the person

The consent checkbox on one of the relays was arriving as ticked on every single submission, whether or not anybody had ticked it. The automation tool was turning the value into a piece of text on the way through, and any text at all reads as true.

The summary screen said consent had been given, and I only found the problem by reading the raw payloads that were actually arriving. In a regulated business that would have made every consent record we held worthless, and it looked perfectly healthy from the outside.

The lesson I took from it is to be suspicious of any field with no variation in it. A real yes/no question gets some of each. If a column says yes ten thousand times out of ten thousand, the thing it’s recording is how the system was set up, and you need to find that out before you carry it into a new one.

The failures you can’t see are upstream

The bases were fed by an app with its own database, sending each new record across by webhook. If the base didn’t answer, it retried for about seven seconds and then gave up, with no queue and nothing written down. So when something went missing you found out weeks later, if at all.

When I came to retire that app, it turned out to be the only place that held 447 of the older claim references, and its record of what had happened only ran forwards. There was no way to replay anything. The field that recorded when a claim had been sent to the law firm had never been read without a filter on it, and when I read it properly 851 had a date in it and 666 were empty. That one count is what separated the claims that had never been sent from the ones that had been sent and lost, which are two very different conversations to have with a partner.

None of that shows up if you only look at the system you’re migrating to. You have to follow the data back to wherever it starts.

Let the loader flag things, not fix them

Every migration script I write has a dry run, which does everything except write, and prints what it would have done. On this job the dry run reported 83 phone numbers it couldn’t put into a standard format, 203 dates of birth it couldn’t be sure about, one that was in the future, three test addresses it would leave out and five more it thought were tests but wasn’t sure.

It would have been easy to write it so that it corrected all of those and produced a clean report. I wrote it the other way round, so that it left them exactly as supplied and told me about them, because a loader that guesses produces a tidier report and a worse database. The 203 dates are the obvious example. Whether 03/04 is March or April is a question about the person, and a script can’t answer it.

When it looks like you’ve lost data

Part way through, 506 rows that should have been in the new base weren’t there. That was 64% of what I expected across 195 claims, and for an afternoon it looked like the migration had lost a large part of the data.

It hadn’t. I’d written a reconciliation method before I started, which said how to compare the old bases with the new one and what counted as a match, and working through it the cause came down to a power cut during a load, made worse by the base limiting how fast the retries could go. The genuine gap was 58 rows, and every one of them could be identified and reloaded.

I’m including it because it’s the moment where a migration either has a method or has a panic. Writing the method down first costs an afternoon, and it’s the reason that afternoon was spent working through a list rather than wondering what else had gone.

What I do now, in order

• Back everything up and count it before changing anything. On this job that was the 305,713 records, with every attachment, reconciled against the live bases.
• Inspect what the data actually contains, column by column, rather than trusting the headings.
• Design the new structure so a person exists once, and decide what counts as the same person before merging anybody.
• Run every load as a dry run first, and have it report what it won’t do rather than deciding for you.
• Reconcile against a method that’s written down, so a missing number has a procedure attached to it.
• Rebuild whatever feeds the new system, so it stays clean after the people who cleaned it have gone home.

None of it’s clever. Most of it’s the work people skip because they’re keen to get on, and it’s where the money is lost when it goes wrong.

Questions I get asked

Who this is for

Businesses whose customer data has grown up across a form tool, a CRM, a spreadsheet and somebody’s inbox, and where nobody’s quite sure which one is right when they disagree. It doesn’t need to be 300,000 records to cost you money. Two systems that both think they own the customer is enough.

Tell me where your data lives now and where you want it, and I’ll tell you what I’d want to look at first.

The service this note is about is data, CRM and automation, and the full write-up of the job is the Claims Bible data migration case study.