Closing a deal with a large customer typically means having to migrate their data across Uptick. Here’s how we did it with our largest customer (at the time of writing).

Background

One of the not so interesting tasks when working as a developer is performing a customer data migration. Many times, the schema of each customer is different from any prior migration we’ve done. As such, migrations we perform are typically bespoke. Migrating this customer was a huge challenge because we had never migrated such large amounts of data before. This was a national company with branches across Australia. To complicate matters, the customer wanted to roll out Uptick to their company across a period of almost a year, branch by branch. Also, Uptick runs PostgreSQL (PSQL), while our customer was on MSSQL; I will be using the type of database to distinguish the source and destination database.

How we did it

This was a huge undertaking that needed planning and collaboration with our customer. We also needed to be careful, since the data migrated across for a branch that had gone live mustn’t be affected during the migration of another branch. Additionally, I did not want the script fail at runtime, so a lot of effort went into preparing for as many outcomes as possible.

Step 1: Mapping the data models and fields of our customer’s existing system to Uptick

To begin migrating, I first needed to identify the data models we were migrating from and what they would represent in Uptick. I also had to map the attributes on each data model. This involved a lot of collaboration with our customer Point of Contact (POC) in charge of the migration. Some fields were straightforward and easy to tell where they fit within Uptick. Others were not as obvious, and some fields even had to be placed on a different data model on Uptick.

While it would have been ideal to have all models and fields mapped out first before starting the migration, it was unlikely for us to get it right on our first go. Rather than trying to perfect it at the start, I began trying to migrate clear-cut data models over. The others would fall into place as we went along.

Step 2: Writing the script to migrate the data

Simply writing the script to import the data naively was easy. Once the data models had been mapped out, it was just reading in the information, and dumping it into Uptick following the mapping. However, the challenging part in this step was implementing the script to be as performant as possible. With over 3 million data records to migrate across, it was not possible to process them one at a time.

Step 2a: Updating the script to migrate carefully and efficiently

Because this was a rolling migration over a time period, I had to make sure that already migrated data wasn’t affected when migrating a new branch. One idea was to import each branch’s data as they went live. However, this was ruled out because branches wanted to know what Uptick was going to look like with their real data, hence an initial import needed to be done.

This left me with a couple of options. When migrating a new branch, after the initial import, I could either:

1. update the already migrated records, and create any new ones created since the initial import.
2. delete any records for that branch, and re-import everything for that branch again.

Updating the records was the safer options, since I would not accidentally delete any data, but this proved to be too difficult and slow, since each data record in Uptick would have to be individually compared to the version on our customer’s existing system one at a time. As a result, I opted for the latter. This had its own set of challenges, for example, identifying which records to delete. Since only one of our models were linked to branch, I had to link every other data model to that model to determine which branch it belonged to. Only after that could I determine what to delete.

To begin deleting and re-importing records for a new branch, I needed to find out the relevant pSQL records, and their corresponding representation in the MSSQL database. By creating a map in memory, any references to the objects could look it up rather than performing a database query, improving run times drastically. The only difficulty in this part was that, due to the sheer volume of data we had to migrate, the machines running the migration ran out of memory. This was addressed by temporarily bumping up the amount of memory. In hindsight, this could have been avoided by writing the script more cleverly and not caching querysets.

Step 2b: Executing the script

The script had been written, and all that needed to be done now was to run the script. I scheduled this to run as a background task using cron, and quadruple checked to make sure my crontab was set up right. I wouldn’t want the script running on the 1st of the month, when I meant I wanted it to run at 1am. Once that was done, all I could was hope that everything went well until I woke the next morning.

Step 3: Cleaning the data

The migration should have ended here, but some data was incorrect in our customer’s existing system. They had duplicates of some records, and only only was correct. When migrating to Uptick, they wanted to make sure that the data brought over into Uptick ignored all those duplicates. This was the hardest part of the entire migration, because it introduced additional logic to make sure we selected the corrected one, and ignored all the other duplicates.

Other difficulties

In the early stages of the migration script, I stored the MSSQL object primary key in our PSQL object in a field temporarily. At the end of the migration, I would then “clean” the field by deleting its contents, thinking it would not be needed again since it had been migrated. This made it extremely difficult to trace the corresponding object in MSSQL. What I eventually did was to store the key permanently on the object. This would also help our customers’ users identify which object it corresponded to in their old system.

As we neared the cutoff date of a branch going live, the pressure continually increased. I had to make sure that once problems were identified, they were addressed by the end of the day, so that I could re-run the script on a sandbox server that night to determine if it was fixed the next day. Once the customer POC was happy with the results on sandbox, I could run the script on the live servers.

The customer POC was still in touch with me after the entire migration, because of issues they hadn’t noticed during the migration. This meant more back and forth to fix the problems.

Conclusion

TL;DR:

1. Create a data model mapping
2. Be in contact with the POC often in the initial stages to sort out ambiguous issues in mapping
3. Store a reference to the customer’s data record on the successfully migrated record (and don’t delete it)
4. Use that reference when creating a map in memory to cut down on database access
5. Write your script in a defensive manner and never assume things will be fine; be explicit to ensure the script does what you want it to do
6. Write your script in a way that it can be re-run
7. Don’t give your personal mobile number to customers