Leaving a Third-Party System? Plan the Data Migration First

Here’s the scenario. You run part of your business on a third-party system. Now you want to own your data: query it directly and build your own reports on top of it.

Yes, you could periodically download a bunch of CSV files, import them into a spreadsheet, and get your result.

So what’s the problem?

First, that approach is fragile. Someone has to download the files and paste them into the spreadsheet by hand, every time.

Note: this assumes there’s no API or plug-in connecting your system to anything else, for example HubSpot → Zapier → Google Sheets.

Second, whoever owns that task becomes the only person who knows how it works. If they leave the company, you find out that only “John” knew how to keep that report updated.

So you decide: let’s build our own system. But we need to bring all the historical data with us.

Building the internal system is the easy part

• Everything works
• Your test data looks right
• The system runs smoothly

The real test is getting the historical data into it.

(Oh, here we go.)

Getting the data

You ask your provider for a full data export, because you’re migrating.

• They send you their data catalogue
• You select the data types you want
• They send you the export

So far so good.

Then you notice something. You designed the database so the clients table requires a first name and a last name, but the export only has a full name.

That’s the kind of issue you hit when you migrate from a system you never owned: you don’t know the full data definition, so you make assumptions while designing the new database, and some of them turn out to be wrong.

From here, you need to build small transformation processes to deal with each of those mismatches.

Ready to migrate

Once your database design finally lines up with the data you were given, you’re ready to go:

• Migration scripts
• A rollback plan
• Expected downtime
• Communication to everyone affected
• And so on

Time to import into the production database.

MIGRATION COMPLETED!

Job done?

Nope.

The critical phase

Now you’re in the critical phase. You need to monitor production closely and check that every feature you built shows the data you expect from what you imported.

Maybe you need to fix more data. Maybe it’s backend bug fixes to handle quirks in the historical records.

A database migration needs careful thought at every step. It’s never an easy job, and it takes real effort from the whole team.

Plan before you migrate

If you’re doing a migration, or about to, my advice is simple: have a good migration plan before you start. Mapping the possible scenarios up front makes it 10x easier to act once the migration is under way. I’ve put the questions I work through into a data migration checklist.

And not every migration has to happen all at once. Assess your use case, plan, and then act on it.

Moving off a third-party platform is also a chance to rethink the data model instead of rebuilding the old system’s limitations. That’s the kind of work I describe in this migration case study, and it’s a big part of what solution architecture is for. If you’re planning a move like this, a migration and integration assessment is the usual way in.