Lessons from a Data Migration

Most large organisations have been embracing change and embarking on modernising their IT platforms in recent years. This typically has been the move from On-Prem to Cloud (PaaS, SaaS, IaaS etc) and more recently moving within the Cloud to Serverless and Pay as you use platforms. Kingfisher is certainly one these organisations which is investing heavily in this modernisation. Regardless of the type of change however, there is one key aspect or entity that has and always will need to be addressed as part of the change and that is the “Data”!

Having led multiple migrations at Kingfisher, I can certainly say that moving or loading data is not a trivial task! It can be complex requiring significant effort involving multiple teams and requires careful planning to avoid any data loss. So, what we do with the data can be broadly categorised into two types: Data Migration and Data Loading/Seeding.

For the purposes of this article, to keep things simple, I will stick to using “data migration” as the terminology when discussing either data migration or data loading except for when required to be differentiated.

There are a lot of articles out there on the steps and best practices of data migration, but I find that typically these tend to be from a Software vendor perspective and specific to using their tool for migrations. Having crossed over from being a Consultant for large software vendors to the other side of being a customer and having been involved in both above types of activities recently and over the years with volumes in the millions, this blog is based on my real world experience with an insight from a customer perspective and what I have learnt from it.

Data Migration

At its simplest data migration is about moving the data from a source system to a target system. This will typically be moving data from an older platform that is being decommissioned to a newer one. The data from the older platform is then decommissioned and no longer used and all the previous consumers of the data move to interacting with the new data source.

Data Loading/Seeding

This typically involves extracting data (from existing data sources) and loading it into a new platform. We only load a subset of the pertinent data into the new platform to enable it to deliver its functionality and the source data stays as is. This will then typically need ongoing data synchronisation between the source and the new platform to be setup via an appropriate pattern depending on the use case e.g. via API calls for real time synchronisation or Pub/Sub pattern for asynchronous.

So, the typical well known and documented data migration process involves the three main steps: Extract, Transform & Load (ETL). Sounds simple, right? Well, I’m afraid that’s not the case! Whilst a migration involves ETL as the broad process there is a lot more involved in and around this which encompasses multiple steps for a successful migration.

I see the process around data migration having the following steps:

• Objective and Scope
• Data Analysis
• Design
• Build
• Test
• Migration Strategy
• Execution
• Reconciliation and Validation

Objective and Scope

Important first step is to establish the objective and scope for the data migration based on the programme objectives.

Data Migration or Loading ?

The answer depends on the overall objective and scope of the programme i.e. moving from an old solution to a new one or simply introducing a new solution into the IT landscape. Regardless of which one, what we do with the data will typically follow a similar process and steps as stated earlier

With scope, it is important to establish the data migration boundaries clearly, such as the type of data that needs to be migrated based on specific criteria, which will in turn define the boundaries for the next step of Data Analysis.

Data Analysis

Once the data migration/loading question is answered and the boundaries defined, we can start the data analysis

Source and Target

We start by looking at the interfaces involved and understand the source and target for the data. The source may involve more than one system. A recent migration programme I was leading included data from 3 sources and one of them was a consolidation of data from 4 other sources!

Understanding the data source and target involves discussing with the relevant teams that own and support the system/data, reviewing documentation and perhaps running some spikes to gain an understanding of the data that is held (we will discuss that a bit later) and the volumes involved, existing feeds in and out of the system, the platform the system is hosted on, potential data extraction capabilities and the formats that the data can be provided in. Gaining this insight starts to provide the basis of the solution design and any potential challenges of what, how and when of the data that is to be migrated.

An example of how gaining this type of insight into the source and target systems helped with a recent data loading activity for a new Customer Identity and Access Programme is where it helped us identify that some of the potential data sources already had a feed out to a system which we could use to consolidate these feeds into a single source for our use. This helped us reduce the potential data sources we would have to consume from 7 to 3!

Analyse the Data — The devil is in the detail

Once we know what our data source(s) and target is we can start to take a closer look at the data which will define what we need to migrate, the data types and volumes involved which will ultimately drive discussions with the business stakeholders, source and target system owners on what and how much of the data we need to migrate. Additionally, this will also provide an insight into any special considerations we need to give to the data. Does the data contain any PII (Personally Identifiable Information) which needs to be handled securely? Is there any additional functionality required in the target system to handle the data being migrated? Is there any data transformation that is required? can the target system handle the expected volumes and are there any licencing implications associated with this?

A useful output from this exercise is a table detailing the source and target data mapping to document the data types, and data transformation rules, any specific logic to be applied to the source or target data (this may be necessary where we have a source or target attribute/field which is a derivative from multiple attributes/fields)

Doing this data analysis ultimately feeds into and drives discussions on the next important decision.

What data do we migrate?

Once we understand the data, an important question to ask early on is do you want or need to migrate all the data that is in the source system? Or can you be selective of what you migrate. A clear set of selection criteria must be defined for each of the data sources. Some pertinent types of questions that you might want to consider could be:

1. Where multiple banners exist within an organisation like we do in Kingfisher, do we need to migrate data for the group or a specific banner ?
2. Where you serve multiple customer bases e.g. Retail and Trade, do we need to migrate data for both types ?
3. Where you have multiple types of customer profiles e.g. Full registered, guest, anonymous, inactive, cancelled, closed — do we need to migrate all of these ? and what volumes would this result in?

Data quality

Another aspect of the data analysis should include an assessment of the quality of the data that is to be migrated. Not all the data that meets the selection criteria necessarily qualifies to be migrated as it may be incomplete or in wrong format and therefore of no use if migrated as is. A consideration should therefore be given to whether a data cleansing activity should be undertaken to make the data useful.

Some examples where analysing the data helped from my experience on our Customer Identity and Access Management and B&Q Club Card loyalty programmes included:

1. Understanding the different types of customer profiles that existed over a period of time going back many years, assisted the business in making the decision to only migrate “Active” customers i.e. customers who had interacted with Kingfisher over the last 3 years. This then drove discussions on the definition of what active meant which ultimately helped establish clear granular requirements for the data we needed to migrate and focus on.
2. Reviewing the data we would need to migrate revealed that we would need to migrate customers’ existing passwords (in their hashed form) to the new platform, however, the current password hashes were generated in a way that the new platform would not be able to cope with. This meant that we were able to identify early on that that a custom solution would need to put in place to cope with existing customer passwords on the new platform.
3. Analysis of the B&Q Club Card loyalty customer data revealed that not all our loyalty customers necessarily had a record on our core platform. This meant that our migration solution needed to consider these types of customers and what should be done with them.

Once the above is established provides a good foundation to go ahead to the next step of design of the migration solution

Design

The design should encompass the information gathered from the steps discussed above, however, in addition will also document the technical details from an end to end perspective. Some important details that should be established should include how we access/receive and transfer the data to be migrated (database access, file transfer mechanisms, security clearance where boundaries are being crossed, access permissions etc), how the actual migration will be done i.e. if we need to build a bespoke solution or use a COTS solution and where we are interfacing with an externally hosted solution, any network configuration that might be required.

From my experience the last point could take some time to resolve so it is something that should be tacked early on. Another important consideration is how we reconcile the data that is being migrated between the source and target systems. This may require generation of reports which may need to be built into the migration process/job. Additionally, the design should consider the ongoing synchronisation of the data where the type of migration is essentially a data loading exercise. Although the synchronisation may not be part of the data loading, the synchronisation is closely tied to it and needs to make sure what is being loaded can be synchronised going forward.

A couple of examples where digging into the design was useful included

1. For the data load for our Customer Identity migration programme, the exported data needed to be loaded into a PaaS solution hosted external to our organisation. The final data load was required to be done by the Kingfisher team. This required specific team members to have access to the target system (an LDAP directory) interface to execute the data loads which required the team members’ machines to be provided with temporary access. This whilst it sounds a simple task, was not easy to accomplish with both Kingfisher and the 3rd Party network teams having to figure out the best way to provide this access keeping security in mind and needing a few attempts of trial and error. In addition, the migration team members also had to learn about the specific interface into the LDAP directory.
2. For the B&Q Club Card data migration of over 8 million customer records involving multiple target systems (Oracle Commerce, SAP CRM, Consent Repository, Data platform), it was important to reconcile the data that is migrated to each system. To enable this the migration jobs has to be adapted to generate accurate statistics of the number of records processed and counts of records to be expected to be loaded into the target systems. This was then used to reconcile with the migration of the data into each of the target systems.

Build

Moving onto what techies like 😊 the building part!

Whether we are implementing a COTS solution or building a bespoke solution, this should incorporate the output from the data analysis taking care of the data mapping that is required and any rules that need to be applied. Additional consideration should be given to the longevity of the solution being implemented e.g. if the solution is to be used for other banners within the organisation later on then the implementation must take this into account by making aspects of the solution configurable and not hardcoding for a specific banner.

Our Customer Identity platform data migration was one such example where this was a consideration as it was rolled out to one banner to start with but would be rolled out to the other banners after.

Test

Apart from the functional testing, a key role of testing is to establish the non-functional aspects of the migration process. This includes

1. Volume — can the solution handle the expected volumes
2. Performance — How long would the solution take to process the expected volume

The above will define the strategy for the migration and the steps for the cutover into production

Volume

Testing the solution for the expected volumes or extrapolating based on a subset will identify any optimisation that may be required from a code and configuration perspective. Prior to doing any volume testing, it is also worth considering whether the test environment has the required data and if not, how that data can be generated.

Performance

The performance testing is about measuring or calculating the time taken to process the expected volume also identifies optimisations that may be required to ensure the migration is completed within the allocated timeframe. This is crucial also for the production cutover planning stage as it will identify if the migration can be completed in the expected timeframe.

Migration Strategy

The result of the testing will shape the migration strategy to be adopted. It will identify any optimisations required for the migration for example any tweaks that may help make the migration run faster and also inform you on the approach for the execution i.e. can you execute the required volume in one go or Where a migration involves a system downtime, it will assist with defining whether the migration needs to be done in stages and started prior to the main cutover day e.g. migration starts cutover day — 2 days before with a delta migration left to do at the main cutover.

Gaining such understanding was hugely beneficial in the 2 migrations I have mentioned in this article, where, for one migration we identified that certain customer profiles (which were the bulk of the volume not requiring a downtime) were migrated much quicker than others. Hence, we were able to make changes to the migration solution to process the quicker customer profiles first outside of the downtime and limit the downtime to only a smaller volume. In another migration, the performance test helped identify that there was no way in the world we could process the expected volume in one night during the main cutover. Hence, we were able to plan for the migration to be done in stages with only a small delta done during the main production cutover. Phew!

Execution

Planning for the execution involves ensuring the required teams (with potential backup resources) are available for the planned time, the required change requests are raised and approved in time and ensuring that all required data source and targets can be accessed by the migration solution and team members as required. With all the above things considered you stand a better chance of a smooth execution!

Reconciliation and Validation

A final piece of the puzzle then is the reconciliation and validation of the migration. The appropriate team must be nominated to reconcile the statistics for the data processed and migrated and to validate against the target a subset of the migrated data to confirm that all has worked as expected. What I have found useful is to gather and keep a record of the statistics for each step of the migration to provide an concise view of the reconciliation in one place which can also be shared with the wider team. This is especially useful when executing the migration over multiple stages over multiple days leading to the production release day.

As with the execution step its only prudent to have a plan (including the possibility of a rollback) in place for when the reconciliation or validation does not go as expected and identifies inconsistencies in the data that has been migrated. Depending on the specific issue, thought should be given to potential remediation or mitigations that could be put in place. In one migration we did find ourselves with an issue which was a potential rollback situation. However, we were able to brainstorm and come up with a simple change in our target system which saved the day and many days of effort which could have gone down the drain!

Summary/Conclusion

The key takeaways for a successful migration I believe are

• Spending time upfront on the detailed analysis to understand the data
• Volume/Performance testing to understand how long it is going to take to migrate your expected data volume
• Panning well ahead of time to establish your migration strategy

As has been famously said “The only constant is change”! So, despite all the planning and testing, expect changes at any stage and be prepared to adapt to the change based on findings from the key activities I have mentioned in this post.

If you are interested in joining us on our journey, please do check out our careers page.

Thanks for reading!