Data Migration and ETL Testing
ETL (Extract, Transform, Load) is a process in which data is extracted following Extraction, Transformation (Conversion), and Loading (Mapping) rules.
An ETL process can be applied in a number of ways:
- To “Legacy” data when transitioning from an old system to a new system
- When updating a database schema
- When moving to new hardware
- To nightly batch jobs that copy data from one system and loads it into another
…Just to name a few. (Note: The data can be stored in Relational and Access databases, Excel files, or anywhere data hides these days.)
There is also the step of determining the quality of the source data, and if needed creating a plan to improve the quality of this data, which is referred to as data cleansing or data scrubbing. Legacy systems often have issues with data quality.
At a high level, there are four steps in the ETL Process:
- Determine the quality of the source data, and if needed create a plan to improve data quality
- Create Extraction requirements, get approved and create test cases
- Create Transformation requirements, get approved and create test cases
- Create Load requirements, get approved and create test cases
Each of these are challenging due to the fact that you are working with data, data attributes, and working on the backend of the system(s) involved. The other challenging piece of this is the more data that is involved, the more of a challenge it is to test and verify.
Olenick’s Test Management Services partners with you and works through the challenges. Business and Data analysts will work directly with your team to determine the quality of the data and all the needed requirements of the ETL process.
Our Test Management Services team will review the data quality plan and requirements to ensure they are complete and testable, then will create the needed test cases to cover all requirements and execute these test cases to ensure the ETL processes are working as expected.
Olenick’s Test Management Services will partner with business and data analysts, technical, and business resources to first determine which master and transactional data needs to be run through the ETL process. This includes how much historical data must be extracted.
Working together, they will analyze the data to determine if there are issues with the data. They will analyze the data for patterns of issues that can be corrected programmatically and those that must be resolved manually.
Test Management Services will create a data cleansing/scrubbing plan to correct the issues; the resolutions will improve the quality of the data and get the data to a known state which will be used to help determine the final transformation and load rules of the ETL processes.
A test plan/test strategy will be created to explain the Who, What, When, and Where for testing the ETL processes.
Test cases will be based on the approved requirements and ensure complete coverage, and will be reviewed with you and any necessary changes will be included.
Additional tools will be incorporated as necessary to help with verification of the data processed through the ETL processes, and will get to as close as possible of verifying every column, record, and table. If files are involved, these will be verified also.
Once the ETL processes are proven to be working according to the requirements, the results will be reviewed and approved by your team. Then, we will support the Production Go-Live in any way we can.
Olenick’s Test Management Services expertise includes all needed roles for ETL projects, including Business Analysts, Data Analysts, Test Managers, Test Engineers and Project Managers.
Our experienced team has been involved in the testing of countless ETL processes, big and small – from ETL processes that are part of a small to medium size project, to large Acquisitions where entire eco-systems along with its corresponding data need to be consolidated as well via ETL processes.
Olenick’s Test Management Services will deliver to you an efficient and effective ETL process plan for defining and testing the data. Our test approach works to find all issues in the ETL processes, and get them fixed and retested to ensure your processes work as expected and are ready to be executed in Production.
Olenick has developed and honed an extensive set of tools that improve productivity and engagement impact for our clients.
ETL, Data Conversion, and Migration: The Conclusion
Read the conclusion of Ken Stawarz’s 6 part ETL series: whether it is ETL, Data Conversion, or Data Migration, it is all about the data.
ETL, Data Conversion, and Migration: The “L” in ETL
Read the fifth in Associate Ken Stawarz’s educational series on Data Migration, Conversion, and ETL: The “L” in ETL.
ETL, Data Conversion, and Migration: The “E” in ETL
Read the third in Associate Ken Stawarz’s educational series on Data Migration, Conversion, and ETL: The “E” in ETL.
ETL, Data Conversion, and Migration: All About the Data
This second article in Ken Stawarz’s series on Data Migration and ETL testing focuses on the importance of data quality to ETL processes.
ETL (Extract, Transform, Load), Data Conversion, Data Migration: What’s the Difference?
Read the first article in a series by Senior Associate Ken Stawarz on ETL, Data Conversion, and Data Migration.