3.6.0 Migration Testing Tool

Abbreviations, used in the manual:

EF - Execution Flow

Description

This feature was introduced to provide users with added value by leveraging existing capabilities already present in quilliup.

The Migration Tool enables users to quickly create a new Migration Project and run a package of Tests designed to assess the quality of the migration process.

The migration workflow is based on the "Tests Wizard" functionality and utilizes the Compare Test type.

Pre Step - Enable Data Source(s)

Currently, quilliup supports the following types of RDBMS Data Sources available for Migration Tool:

• Snowflake
• MSSQL
• MySQL
• Oracle
• PostgreSQL
• Redshift

To use a Data Source within the Migration Tool, you first need to enable it:

From the left-hand menu, navigate to Settings → Data Sources. Select one of the supported Data Sources for migration, open it in edit mode, check the Use for migration option, and save your changes:

Step 1 - Migration Settings

In the Quality Gates home screen click the "Migration Tool" tile or choose "Migration Tool" from the left side menu:

The Migration Settings screen will open:

Project Name - choose the name for the Project that will be created for migration Tests. Note that the Project name will always start with the "Migration-" prefix.

Role to use - choose a role (from the list of available Roles) that has all needed permissions for Tests/EFs. 

Source - choose the Data Source (from the list of Data Sources marked as "Use for migration")  that will be the source.

Target - choose the Data Source (from the list of Data Sources marked as "Use for migration")  that will be the target.

Emails - insert an email address(s) that will be used for EF's results sending. If you need to insert several email addresses => please separate them by comma sign:

Scheduler - define a Scheduler for the main EF (which will contain all the Tests and sub-EFs):

When finishing with all the Migration Settings, click "Create and Continue" and the list of all the tables from Source and Target Data Sources will be presented.

Step 2 - Table Mapping

Choose the Source and Target table pairs (by marking the checkbox from the left side) that should be checked:

When the relevant table pairs are chosen, click "Create". The Tests and EFs will be generated and you could be redirected to the new Migration created Project, to the EF's grid by clicking "Go to Execution Flows". 

Tests that are automatically created during the Migration Process

All the Tests are of  compare type (Source (master) is compared to Target) and can be divide sections: 

General Tests

1. Table Comparison 
   Purpose: validate the same table names in each schema related to the chosen Data Sources. 
   Compare type: Data Integrity.
   Query example (MySQL): "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'XXX'"
2. Total Rows 
   Purpose: validate the same number of rows in each table related to the chosen Data Sources. 
   Compare type: Raw Data.
   Query example (MySQL): "select TABLE_NAME, table_rows as TOTAL_ROWS from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'XXX'"

Specific Tests

* Each specific Test will be marked by a relevant Tag.

The following 3 Test types will be generated (a Test of each type for each chosen table's pair):

1. Sum Checks
   Purpose: Comparison of all the columns of Numeric data type. 
   Compare type: Aggregated Data.
    
2. String Checks
   Purpose: comparison of the length of each String value for all the columns of String data type.
   Compare type: Aggregated Data.
    
3. Table Structure
   Purpose: structure comparison of each column in the table. 
   Compare type: Raw Data.

Tags

An appropriate Tag is automatically added to each Specific Test. These Tags are designed to help the end-users to organize the Migration process, navigate through multiple perspectives and analyse data quality. 

Minimum 3 Tags will be created according to the Test's types:

- tableStructre

- sumChecks

- stringChecks

Note:

- In case there are no Numeric columns in table => sumChecks test for such table's pair won't be created. 

- In case there are no String columns in table => stringChecks test for such table's pair won't be created. 

Execution Flows Description

* Each specific EF will be marked by a relevant Tag.

The EFs will be created according to the Test's types. 
3 types of EFs will be created:

- "All_Sum_Tests" - will contain all the "Sum Checks" tests;

- "All_String_Tests" - will contain all the "String Checks" tests;

- "All_Structure_Tests" - will contain all the "Table Structure" tests:

Note: the number of EFs for each EF type depends on the parameter "migration.divider", defined in  the configuration file "../quilliup/webapps/ROOT/WEB-INF/classes/config.properties" file. By default, it's value is 10. This is the maximum number of Tests in each EF type. If the current EF of the specific type already contains a number of tests equal to "migration.divider" value => additional EF of the same type will be created. 

Main Execution Flow- "Migration_tests":

This is the most important EF in the Project. Main EF will contain the General Tests and all the specific tests (but inside EFs by test types). 

On the bottom line - the main EF will contain all created EFs (and all the Tests) and it's preferable to run it first. Then, the user could dive deeper into the General/Specific Tests.