3.4.1 Compare tests

Compare tests validate the consistency and completeness of two or more Data Sets at any stage of the data flow. They allow you to choose different comparison levels: Data Integrity, Aggregated Data, or Raw Data.

Note: The test cannot be performed with a connection to only one Data Set!

 

STEP 1:

Choose/create two or more Data Sets for the Test in the "Data Sets" area:

STEP 2:

In "Test Type" area select "Compare" option - "Compare" Test tile will be added to the "Test Flow" area:

Access the compare types by clicking the three-line icon at the top right of the Compare tile. Then, click the pencil icon:

 

STEP 3 - choose a compare test type

There are 3 compare types available:

1. Data Integrity - validate keys consistency and relationships between Data Sets.

2. Aggregated Data - validate measures consistency between Data Sets and find missing grouping dimensions values.

3. Raw Data - validate measures consistency between Data Sets raw data values.

Choose suitable compare type and proceed to "Compare Mappings".

Compare Mappings for Data Integrity:

Define the key(s) for comparison and the comparison type:

Auto Map button - sets a default compare between columns.

Clear button - deletes all default columns that were chosen.   

Set As Master button - select the current Data Set to be master.

Full Comparison - validate record values for all Data Set records (i.e. Outer Join).

Compare to master - validate record values for master Data Set records (i.e. Left Join).

Compare Mappings for Aggregated Data:

Define the key(s) and the field(s) for comparison and the comparison type:

Compare common records -The test compares only records common to all data sets, similar to an Inner Join.

Total Rows - When selecting the Aggregated Data comparison type, aggregation functions available for the chosen data type will be shown. One function must be selected for each participating column that isn't a key:

Note: The user can specify an alias to replace the aggregate function in the result table by entering the desired value, which will appear in both the result email and the exported Excel file. 
For example, changing the value "Sum(price)" to the "Friendly Name":

From:

To:

Compare Mappings for Raw Data:

Define the key(s) and the field(s) for comparison and the comparison type:

Select a column in one Data Set to compare against each column in the other Data Set (Row-level comparison).

STEP 4:

When set up the Compare test is finished - user can defined the results Threshold and Log Settings. Please see related manual: Test Results - Thresholds & Log Results

Now the Test is ready and you can run it by clicking "Sample Run" (Test will be implemented partially, for 25 rows from each Data Set) or "Run Test" (Test will be fully implemented) buttons.

Note: Data Sets of type Snowflake could be compared regardless of case-sensitive column names.

Please see a video demonstration: Compare Data sources - Sales Data Chain