Verify a Data Migration¶

Use this workflow to check that a copy of your data matches its source after a migration, a reload, or a move to another database or file data source. You generate tests on the source tables, transfer them to the destination, and run them there. Each test compares a property of the copy, such as a row count, a null count, or a column total, with the same property profiled on the source.

The workflow uses two table groups. The source table group holds the tables you migrated from. The destination table group holds the copy. They can be on the same connection or on different connections and types of data sources.

Prerequisites¶

A table group for the source and a table group for the destination. See Manage Table Groups.
The source data in the same state it was in when the copy was taken. If the source changes between the copy and the profiling run, tests fail on a correct copy.
Python 3.11 or later, to run the test transfer script.
An access token for a user who can view the source test suite and edit the destination test suite. See API Authentication.

Generate the comparison tests on the source¶

Set up the source table group for comparison.
1. Select Table Groups from the menu.
2. On the source table group, click the (edit icon).
3. Under Settings, select Profile for table comparison.
4. Expand Sampling Parameters, clear Use profile sampling, and click Save.
With sampling on, several comparison tests are not generated. Without Profile for table comparison, the hash tests and Match Epoch Sum are not generated. On SAP HANA and Salesforce Data 360 connections, the setting is not shown and these tests are not generated.
Run Profiling on the source table group.
Create a test suite for the source table group. Use this test suite only for comparison tests.
On the test suite, click Generate Tests. In Generation Sets, select Table Comparison only, and click Generate Tests.

You do not need to run this test suite on the source. Optionally, run it when the copy is taken. If every test passes, the source has not changed since it was profiled.

Prepare the destination¶

Create a test suite for the destination table group. To keep comparison results out of the destination table group's quality scores, select Exclude from quality scoring.

You do not need to profile the destination table group. The import checks the transferred tests against the tables and columns in its Data Catalog.

Create the mapping file¶

The test transfer script exports the tests from the source test suite, renames their tables and columns from a mapping file, and imports them into the destination test suite.

Download transfer_tests.py.

Create a mapping file in CSV format with four columns: source_table, source_column, target_table, and target_column. Enter names exactly as the Data Catalog shows them, with the same case and without quotes. When every table and column has the same name in the destination as in the source, the file holds only the header row.

source_table,source_column,target_table,target_column
orders,,sales_orders,
orders,cust_id,sales_orders,customer_id
orders,legacy_flag,,DROP
staging_tmp,,DROP_ALL,

To	Enter
Rename a table	`source_table` and `target_table`
Rename a column, or move it to another table	All four columns
Leave out a column the destination does not have	`source_table`, `source_column`, and `DROP` in `target_column`
Leave out a table that was not migrated	`source_table` and `DROP_ALL` in `target_table`
Leave out only a table's Match Row Count and Match Column Count tests	`source_table` and `DROP` in `target_table`
Keep a table or column name	No line

When a column is dropped, or moves to another table, the script also leaves out the Match Column Count test of each table involved. When the key column of a table is dropped, the script also leaves out every keyed hash test on that table.

Use DROP only for a column that was left out of the migration on purpose. A column the migration lost by mistake is no longer detected once it is mapped to DROP.

Transfer the tests to the destination¶

Copy the ID of each test suite. Select Test Suites from the menu, and click the (copy icon) on the source test suite and on the destination test suite.
Set the TESTGEN_API_TOKEN environment variable to the access token.
```
export TESTGEN_API_TOKEN=<access_token>
```

Run the script to preview the transfer.

python transfer_tests.py \
    --api-url <testgen-api-url> \
    --export-from <source_test_suite_id> \
    --import-to <destination_test_suite_id> \
    --mapping mapping.csv \
    --output mapped.json \
    --report transfer_report.txt

<testgen-api-url> is the API and MCP URL: http://<host>:8530 by default on a Docker or pip install, or https://<API domain> on Kubernetes. It is not the UI URL.

Review the report. Nothing is imported yet. In particular, check these sections.
- Mapping rows that matched no test — usually a misspelled source name.
- Tables kept as named — tables with no mapping row, which keep their source name.
- Import preview — the number of tests the import would create and update, and the tests it would skip, grouped by reason. A test skipped because TestGen has not found its table or column in the destination table group usually means a misspelled target_table or target_column name, or a column the migration lost.
Run the same command with --apply added. New tests are created and locked, and matching tests are updated, including locked ones.

When the mapping has a problem, such as two source names that map to the same target name, the script writes nothing, names the problem, and exits. Fix the mapping and run it again.

The import is all or nothing. If any test would be skipped, the script imports none of them, lists the skipped tests in the report, and exits with an error. Fix the mapping and run the command again. If tables or columns were added to the destination after its table group was created, first Run Profiling on the destination table group to refresh its tables and columns in the Data Catalog.

Do not unlock the imported tests. Generating tests in the destination test suite refreshes an unlocked test from the destination's own profile, and the test then always passes.

Run the tests and read the results¶

Run Tests in the destination test suite, and review them as described in Investigate Test Results. Each failed test shows the value measured on the destination next to the value expected from the source.

Read failures together. One defect usually fails several tests, and the combination points to the cause.

Failure pattern	Likely cause
Match Row Count, plus most counts and totals on the same table	Rows were lost or duplicated.
Match Null Count and Match Empty Strings on one column	Empty strings became nulls, or nulls became empty strings.
Match Null Count and Match Zero Count on one column	Nulls became zeros, or zeros became nulls.
Match Fractional Sum only	Decimal places were rounded or truncated.
Match Minimum Date and Match Maximum Date, both shifted by the same amount	A time zone conversion was applied.
Match Distinct Hash and Match Frequent Values	Values in a code column were remapped or changed case. The frequent values show which ones.
Match Total Length or Match Maximum Length	Values were truncated, padded, trimmed, or damaged by an encoding conversion.
Match Column Count or Match Column Type	The destination table has a different set of columns, or a column was loaded as a different kind of data.
A hash test, with the column's other tests passing	Values were changed in a way that keeps their counts and lengths, such as letters transposed or a date moved within its range.
A keyed hash test only, with the unkeyed hash test on the same column passing	Values were swapped between rows. When every keyed hash test on the table fails, the key itself changed. See Expected differences.

When Match Row Count fails, investigate it first. The other failures on the table follow from it.

For details on each test, see Consistency Tests.

Expected differences¶

Some tests fail on a correct copy when the source and the destination store values differently.

Situation	Tests that fail
CSV file on one side, database on the other, empty strings in a text column	Match Null Count, Match Empty Strings, Match Distinct Count, Match Minimum Length, Match Text Hash, and Match Keyed Text Hash on that column
Case-insensitive collation on one side only	Match Distinct Count on columns with values that differ only in case
SQL Server table on one side only	Match Distinct Count on columns with values that differ only in trailing spaces
Oracle `CHAR` column on one side only	Match Minimum Length, Match Maximum Length, Match Total Length, Match Text Hash, and Match Keyed Text Hash on that column
Decimal column with more than nine decimal places on one side, floating-point column on the other	Match Numeric Hash and Match Keyed Numeric Hash on that column
Key column regenerated by the reload, such as a surrogate key	Every keyed hash test on the table
SAP HANA or Salesforce Data 360 destination	Every hash test and Match Epoch Sum end with an error

A CSV file does not distinguish an empty value from a null, and TestGen reads an empty field as null. A case-insensitive collation, such as the SQL Server default, counts values that differ only in case as one distinct value. SQL Server counts values that differ only in trailing spaces as one distinct value under any collation. Oracle stores a CHAR value padded with spaces to the declared length. A floating-point value and a decimal value with more than nine decimal places can round differently.

A destination with a case-insensitive collation also hides some defects from Match Distinct Count, such as values changed to upper case. Match Upper Case still detects them.

To accept one of these differences, deactivate the test in the destination test suite. Do not change its tolerance, which is reset each time you refresh the baselines.

Refresh the baselines¶

When the source changes and you migrate it again, refresh the comparison tests from a new source profile.

Run Profiling on the source table group.
On the source test suite, click Regenerate Tests, and click Generate Tests.
Run the script with the same mapping file and --delete-absent added, as in Transfer the tests to the destination. If you added tests to the destination test suite yourself, leave out --delete-absent, which would delete them.
Review the report. The Import preview also shows the number of tests the import would delete.
Run the same command with --apply added.

--delete-absent deletes the destination tests that the source test suite no longer has, such as the tests on a column removed from the source. Without the flag, delete them from the destination test suite yourself.

A test you deactivate in the destination test suite stays inactive, and a severity you set there is kept unless the source test has a severity of its own.

What the comparison checks¶

The tests a column gets depend on its type in the source profile. The hash tests and Match Epoch Sum are generated only when the source was profiled with Profile for table comparison on.

Scope	Checked by
Every table	Row count, column count
Every column	Type, null count, distinct count
Numeric	Minimum, maximum, zero count, sum, standard deviation, and for decimal types, the sum of decimal parts
Date and timestamp	Minimum, maximum, number of distinct days, sum of epoch seconds
Boolean	True count
Text	Minimum, maximum, and total length, leading spaces, empty strings, upper case, non-printing characters, frequent patterns
Text with 2 to 70 distinct values, each up to 70 characters	Also the exact set of distinct values and the 10 most frequent values with their counts
Numeric, date, and text	Hash of the values and how often each occurs
Numeric, date, and text in a table with a key column	Hash of each value with its row's key

Without the setting, the comparison checks properties of each column, not individual values, so it can miss these changes.

Values swapped between rows.
A value in a text column with more than 70 distinct values changed to another value of the same length.
A date moved to another date inside the column's range.
A change in how often each value occurs, beyond the 10 most frequent values of text columns with 2 to 70 distinct values.
A numeric change smaller than the tolerance of Match Sum and Match Standard Deviation.

With the setting on, the hash tests detect these changes. They still miss values swapped between rows of a table with no key column, numeric changes that disappear when the value is rounded to six decimal places, and changes to text columns on SQL Server versions without UTF-8 collations.

The comparison also does not show which rows differ. To find them, query both tables by key.