Data Validation in Data Warehouse has always been challenging. One of the most frequently way of data validation is by using MINUS/ EXCEPT operator. MINUS operator is used in ORACLE database while EXCEPT is used in SQL SERVER database. Before proceeding to testing part, first let us understand the functionality of MINUS operator.
MINUS operator gives the differences between the two table. Suppose we have two tables. Lets consider table A and B. When we will do MINUS operation between table A and table B (ie. A - B), it will return all those records which are present in table A but not in B. Similarly when we do MINUS operation between table B and table A (ie. B - A), it will return all those records which are present in table B but not in A.
One point to remember while doing MINUS operation between first table and second table is that every row in first table is searched in second table and if not found , that row is returned as a result set. Similarly, while doing MINUS operation between second table and first table is that every row in second table is searched in first table and if not found , that row is returned as a result set.
If all the rows present in first table is present in second table as well then output or result of first table MINUS second table will be empty result set or in other words nothing will be returned. Similarly, if all the rows present in second table is also present first table, then output or result of second table MINUS first table will be empty result set or in other words nothing will be returned.
Let us understand the usage of MINUS operator in data validation. Below SourceTable and TargetTable is used for illustration.
SourceTable TargetTable
ID
|
Name
|
ID
|
Name
|
|
1
|
ABC
|
1
|
ABC
|
|
2
|
DEF
|
2
|
DEF
|
|
3
|
GHI
|
3
|
GHI
|
|
4
|
JKL
|
4
|
JKL
|
|
5
|
LMN
|
5
|
LMN
|
|
6
|
OPQ
|
8
|
OPQ
|
