You can use parameters to perform incremental validation. Incremental validation is when you validate a subset of the records that are loaded into a target.
Every day, you run a PowerCenter session that loads approximately 50,000 records into a target. The target currently has 300 million records. You want to create a table pair and tests to validate that all records are loaded into the target every day. However, you do not want to validate the source records against the 300 million target records.
To increase Data Validation Option performance, you perform incremental validation. Each time you run the PowerCenter session, you validate the source records with the latest records that are loaded into the target. You use a $$batchID parameter in the WHERE clause of the table pair to dynamically filter the target records based on the batch ID of the latest load. The PowerCenter session generates a unique batch ID for all records in each load and writes the batch ID of the latest load to the Data Validation Option parameter file.
You perform the following tasks to implement incremental validation:
Create the PowerCenter session that loads the source records into the target and populates the BATCH_ID target column with the batch ID of each record.
Add a post-session command to the PowerCenter session to write the current batch ID to the parameter file.
Create a PowerCenter workflow and add the PowerCenter session to the workflow.
Add a Command task to the PowerCenter workflow to run the table pair tests from the DVOCmd command line after the load completes.
Add a Decision task and additional logic to the PowerCenter workflow to determine what to do based on the results of the table pair tests. If all tests pass, you configure the workflow to run the s_PASS_AND_LOAD session. Otherwise, the workflow runs the s_FAIL_AND_RECOVER task, and then sends an email notification.
Place the parameter file in a location that is accessible by the Data Validation Client that runs the tests.
Specify the location of the parameter file in the table pair definition in the Data Validation Client.
Add the following WHERE clause in the table pair definition in the Data Validation Client:
is the column in the target table that stores the batch ID of each record.
The following figure shows the PowerCenter workflow: