Data Migration Consultancy

Data Migration Consultancy Services

Moving data from one system to another brings with it huge risk and challenges with regards to potential data loss and data inaccuracy. PTR is a Microsoft Solutions Partner AI & Azure Data and we have been working with all things data since the 1980s. Working with data means many things - creating it, storing it, moving it, analysing it. Over the last 3 decades we have been helping clients across all these areas, and across all industry sectors.

Our consultants have a passion for solving problems and enjoy working with clients to figure out how they can get many different sets of data from one place or system to another, in the right format, with the right relationships, with the right business measures and calculations, and with the efficiency and performance needed by business users today. As well as a passion for puzzles, a good eye, and attention to detail, and a methodical and organised approach to design and implementation are critical for data migration work. Our team are expert in their field and have the qualities required to assist you with a successful data migration project.

Data Migration Challenges

Data Migration can be time consuming and technically challenging and whereas in some environments if migrated data is not a 100% match from source to destination it may not be critical, there are many business scenarios where 100% accuracy is essential. Guaranteeing that data is moved from one place to another without any unwanted or unplanned changes requires rigorous testing, data validation and exception reporting. The ETL or ELT process itself may be the most straight forward element. If data needs to change shape (but not meaning or business value) to accommodate differences between a source database system and a destination database system, then identifying all the busness rules to be applied, all the decodes and mappings that may be required, and ensuring that all data scenarios such as NULL values, missing values, free text values, missing records, mismatched data types (to name but a few things) have been identified and handled is far from straight forward. Simple record counts at source and destination are not enough, and often simply not possible because of the structural differences between a source and destination system.

As well as physically moving the data from one place to another it is extremely important that you can reconcile the new data with the old, and explain and present any legitimate differences that there might be between the two systems. It is also vital that the authenticity and provenance can be tracked so that we can always verify exactly where a piece of data originated from, whether it has been transformed or modified, and whether it has been validated or quality checked. This means being able to track all transformations applied tom data during ELT processes throughout the data migration process.

And let's not forget the huge challenge of managing security and access to confidential and sensitive data. How do you enable data to be successfully moved from one place to another if your developers are not allowed to view sensitive or classified data?

Why Do a Data Migration?

System or Platform Upgrades:

Upgrading to newer, more advanced systems or platforms often requires migrating existing data to be compatible with the new environment.

Consolidating Data:

Bringing together data spread across multiple systems or platforms can streamline access and analysis, improving decision-making and operational efficiency.

Improving Data Quality:

Migrating data can help in cleaning, organising, and consolidating data, thereby improving its quality and usefulness.

Increasing Efficiency:

By moving to more efficient systems or platforms, businesses can enhance their operational efficiency.

Enhancing Security:

Migrating to platforms with better security features can protect data against breaches and loss.

Cost Reduction:

Migrating to more cost-effective storage solutions can reduce operational expenses.

Regulatory Compliance:

Adapting to new legal and regulatory requirements may necessitate data migration to ensure compliance.

Business Process Migration:

Aligning data with evolving business processes or models often requires migration to support these changes.

Data Migration Life Cycle

The data migration process has many stages to it but the general life cycle of a data migration project might look something like this:

Planning
Data Assessment
Designing the Migration Process
Data Extraction
Data Cleansing
Data Transformation
Build Mapping Document
Testing and Validation
Reconciliation Reporting
Corrective Actions
Data Loading
Go-Live and Monitoring
Post-Migration Support

Data Migration Process Design

There are many considerations to be taken into account when embarking on a data migration journey.

The solution needs the ability to:

Ingest source data of many different types and schemas
Transform data into target schema
Re-use solution for multiple migrations of different sources
Use global ‘decode’ values that can be mapped differently for each source.
Run multiple cuts of data and compare.
Run multiple migrations simultaneously.
Run data cleansing and validation checks before loading output.
Anonymise personal data for testing
Extract subsets of data for loading tests as well as split output into multiple files (by scheme but also by file size).
Reconcile data, including complex transformations
Provide a comprehensive reporting suite, including control totals and exception reports
Share code across migrations to reduce development work
Update the toolset without compromising in-progress migrations

A successful data migration will require collabroation at all points of the journey to combine the technical skills driving a data migration with the understanding of business data and processes to ensure transformations are executed in the the desired manner.

Data Migration Data Extraction

The initial extraction of data from a source system will require the following considerations:

Understanding where the source data is coming from
Ability to handle multiple different sources
How to access to the source data - API vs download vs File share vs Database backup
Security restriction on source data access ans storage
Source system changing schema between data cuts

Data Migration Data Cleansing

There will most likely be data that is not required to be transported to the destination system so filters may be implemented to prevent unwanted data being migrated, and the data that is migrated will need to be in the correct format for the new destination repository involving the following:

Ensuring data typing of ingested data is correct
Ensuring consistency with blanks/nulls etc.
Clearing out data that is not required
Checking for bloated data
Checking special charcaters ar encoded correctly
Checking for any mappings implemented to derive missing source data

Data Migration Data Transformation

This step holds the bulk of the logic to change data from the source schema to the target schema and often the schemas can be significantly different. We need to be mindful of the following:

Must be able to join to decode values used in the output.
Transformations must be able to merge data, unpivot data and other complex transformations that may take multiple steps.
Being able to debug this process easily will allow for simpler reconciliation.
There may be a requirement to be able to run transformations in a provided order.
Transformations should be saved so that they can be audited in the future for authenticity and provenance, or re-used for similar migrations.
May need to be able to handle large datasets
Performance might need to be considered
Exception reports should be created to support validation and acceptance of differences or missing data

Data Migration Testing and Validation

Testing and validation is a very important step in a migration process.

Run a test set of data through the migration process
Run initial validation tests on the data - record counts, key dimension counts, key metric sums
Check control reports
Run reconciliation reports
Ensure data has not been dropped during migration
Ensure data has not been changed in transit
Validate accepted differences between source and destination

Data Migration Data Loading

Once the migration process has been developed and tested it will be time to move data all the way through form source to new destination. The final stage of the migration will be to extract the transformed and validated data and load it into the target system. If the development and testing was carried out with anonimised data then it is possible additional issues will be thrown from using actual data. It may also be that relatively small data sets were used for development and testing so at this data loading stage we may need to consider the following:

Large source data files may prove to be problematic from a performance perspective
Real data may identify new data anomolies not poicked up in anonomised data sets
Target system may still be in development so there could be new schema changes by the time we load data for the first time
Reconciliation reporting on target system v extracted transformed data will be required

A Few More Iterations

It may take several iterations of the develop, test, load cycle before all tests pass and all data successfully loads into the target system.

How can we help?

We have a wealth of experience delivering robust data migration solutions utlising a variety of data platforms and toolsets. If you would like to arrange a free consultation with one of our lead consultants to see how we might be able to assist you with your data migrations then give us a call on 0118 979 4000 or email us at info@ptr.co.uk.

Data Migration Consultancy

You are here