Reconciliation Activity (2.13)
Introduction
A reconciliation activity compares the most recent data loaded into two tables. The key map describes how to match a record in one table (the primary table) with a record in the other table (the secondary table). Creating the key map consists of two steps:
-
Identifying the primary key of each table. Then for a given record in the primary table, the system knows which record of the secondary table to use as comparison.
-
Matching up the other fields as appropriate. Then for a given field in a given record in the primary table, the system knows which field of the other record to use as comparison.
For each field-to-field comparison, a reconciliation activity specifies a comparison function, a tolerance (if applicable), and how to handle a reconciliation failure.
The results of the reconciliation checks are written to a reconciliation report file.
Options
For each field to be reconciled, the following options apply:
-
Function Type —Specifies the comparison function to use.
-
Rule Name —Specifies a unique rule name.
-
Result Field —Specifies a unique field name. The reconciliation activity will write a 1 or a 0 to this field, to indicate whether reconciliation was successful.
-
Tolerance —Specifies the numeric tolerance. This option is required for numeric comparisons.
If the tolerance ends with a percent sign ( %), then the system treats it as a percentage.
-
Fatal —If selected, that means that failing this reconciliation check should be a fatal error (halting the recipe).
Reconciliation Report
When the reconciliation activity completes, the system writes the results to the Recipe Report. This report summarizes discrepancies between the primary and secondary data sources.
Reconciliation can identify records that exist in only one data source as well as differences in corresponding field values, depending on the options configured.
The reconciliation report includes samples of the following types of findings:
-
Primary keys of records that exist in the primary data source but not in the secondary data source
-
Optionally, primary keys of records that exist in the secondary data source but not in the primary data source (if the Report Missing Records from Primary Source option is enabled)
-
Optionally, primary keys of records that failed one or more field-level validation checks, such as mismatched values
For each type of finding, the report includes a sample of rows rather than the full result set. The number of rows included in each sample is controlled in the system setting.
To access the complete set of reconciliation results, query the following table via the SQL Editor:
SELECT *
FROM SDS_DataLoader_Reconciliation.ReconciliationActivityResultsDetail
WHERE %BatchId = '<BatchId>'
You can obtain the BatchId from the corresponding run record in the business scheduler.
