A table comparison compares tables in two different folders. Run a table comparison to generate table pairs and tests for each table pair. Use the test results to determine if the data is the same in both tables or to determine what the differences are.
For example, run a table comparison after you upgrade PowerCenter or migrate from a PowerCenter development environment to a PowerCenter production environment. To verify that an upgrade is successful, take a specified set of input data and run it through the pre-upgrade mappings/workflows and then the post upgrade mappings/workflows. Use Data Validation Option to run a full regression test by comparing the pre-upgrade target tables to the post-upgrade target tables to identify any potential errors in the data. If the upgrade process completes successfully, the two data sets should be identical. Similarly, to verify a migration is successful, use Data Validation Option to compare the pre-migration data against the post migration data, incorporating any expected or required migration data changes into tests in Table Pairs.
You can run a table comparison on all data source types. If you compare database tables, you must select a default connection for the tables. If you compare flat files, you must enter a default directory for the files. If a table or file requires a different connection, update the connection for the applicable table pair after Data Validation Option generates the table pairs and tests.
When you run a table comparison, by default, Data Validation Option generates table pairs for tables that have matching names. To generate table pairs for tables that have different names, define the table pairs in a spreadsheet and import the table pairs from the spreadsheet.
You might also use spreadsheets to define table pairs for other reasons. If you defined PowerCenter ETL rules in spreadsheets, you can reuse the ETL rules that map PowerCenter sources to targets. You might also use spreadsheets to define the table pair definitions when you work offline or are not a Data Validation Option user. A Data Validation Option user can import the table pairs from the spreadsheet.
Data Validation Option generates value and outer value tests for a table pair based on the primary keys of the tables. You must specify the primary keys for both tables in the table pair definition. By default, you can select one column as the primary key for a table. If a relational table has a composite primary key or a flat file does not have a column header, you can create a primary key file that specifies the primary key for the relational table or flat file.
When Data Validation Option generates table pairs, it also generates tests for each generated table pair.