In this eighth post in my Data Migration series, I want to discuss the importance of building or licensing technology to facilitate movement of data to your new system. “Use technology” would seem to be an obvious bit of advice, given the data is or will be stored digitally, it will be moved from one computer system to another, and technology teams will mostly likely do the migration. However, I have seen time and again the instinct among organizations to treat Data Migration as an ad hoc task or process and to miss business and nonfunctional requirements that may be present in a migration.
Data Migration is very amenable to use of supporting systems and tools. Migration work is very amenable to automation. Tools and automation could make the difference between a successful migration and one that throws your projects dates into chaos and creates ongoing process work post-migration. I will use some of my previous Data Migration posts as a guide and point out where teams I have worked with built technology solutions.
Knowing Your Data, Controlling Scope, Validating Process
In my series of posts about Knowing Your Data, we discussed a general process that you should understand and adapt to lead a detailed and deliberate walkthrough of the data models involved. You need to understand your target system – data object by data object, table by table, field by field. You’ll need to understand what fields are required, where you’ll get the data to push into the new system, and what cleansing or transformation is required. Much of this is ad hoc and manual work. But in turning analysis into the plan, identifying scope and creating a Migration Validation plan, you will find a variety of automatable activities.
These processes offer opportunities, large and small, expensive and at no additional cost, to apply technology:
- Use widely available desktop apps such as Microsoft Excel to build and rigorously maintain a Data Dictionary model. See some words of wisdom from an ArgonDigital expert.
- Excel also provides a wide variety of tools to assess the ‘cleanliness’ of your existing data, model transformations, analyze potential work to migrate a set of data, or analyze results of a migration story.
- Some ad hoc work is necessary, but a migration of any scope will require iteration and application of similar repeated actions across many data sets. Storing data in files begins to pose challenges in finding the right set or the right version. Different team members may work on different aspects of the same analysis, and merging spreadsheets without introducing errors can be extraordinarily difficult. Structured databases provide solutions to these challenges and have the effect of encouraging the team to work in a more structured way.
ETL
In my 3-part series on the ETL patterns in Data Migration, I examined the conditions in several real-world projects I have observed, and the choices the teams made to contend with those conditions. A couple of general lessons present themselves concerning investment in technology:
- Creating extraction scripts is essential, unless your source data is very simple. You might have a script for each source record type or data object, for instance. Taking an agile approach to your migration project plays really well here: a team I worked with did iterations across sprints to pull a growing list of fields from source systems.
- While Excel provides great tools to model transformations, creating transformation scripts that act on extracted data allows the team to transform data each time there is a pull.
- Connectivity to a variety of data sources, SQL and other, is an important area of investment and a good place to consider license over build. (Open source is a boon here.)
- A migration project of any complexity will certainly feature continuous experimentation, validation, correction, and improvement. Teams have made investments in large and sophisticated SQL databases to enable the kind of growth required over the course of such projects.
- Hosting your scripts in a system that allows automated execution, pause and resume, and error logging will ultimately save much time and frustration. One team I worked with devoted a sprint every increment to ‘hardening’ their automated executions to extract and transform data.
Experience with Teams
In the three large migration efforts in which I have served, there has been variation in the commitment each team has made.
- A large media company which was replacing a digital rights system made an enormous commitment from the start. Dedicating a team of engineers from early on, the migration team built a sophisticated SQL-based system from scratch that became the engine of their migration. The system they built allowed the team to pull data from a dozen source systems, to pull apart and create new data objects, to run scripts for hours on end unattended, and to be re-purposed again and again as the business identified new migrations to perform.
- A financial services company was replacing an obsolete system with a new vendor-maintained system. The vendor provided a transition environment, which initially did not meet the company’s migration requirements. The client and vendor each committed significant resources: the client: to build the extraction scripts; the vendor: to customize the transformation routines and improve loading performance to fit in the required migration window.
- Another media company replacing its in-house digital asset management system with a new cloud-based system did not initially commit that scale of funding. A single, heroic technical analyst built an app on Filemaker to facilitate the most challenging cleansing work. This made tracking and validation of cleansing dramatically easier than using spreadsheets.
We’ll start a new series in the next Data Migration post.
We at ArgonDigital are always happy to discuss data migration with you and explore ideas about making your project go smoothly.
