Quality is delighting customers
In ETL testing you need to compare source to target data comparison.
So sample queries on Source Data is -
1) select count(*) from SourceTable where id is not null
And Target table query will be-
2) select count(*) from SourceTable a, TargetTable b where a.ID=b.ID and b.ID is not null
So the count returned from query 1 and query 2 should be matched. Then you can specify in your test case, that the data of ID column is successfully transferred from source to destination.
Similar kind of queries can be written on another columns of the tables as per the transformation rules.
Thanks and regards
Amit Nimje : Infocepts Pvt Ltd
SQL queries depends on different types of test activities that you need to perform in ETL testing.
For example you are testing the Referential Integrity of the two Dimension tables, you may use following query-
Select Primary_key from Child_Dimension
Select Primary_Key from Parent_Dimension
If there is SCD logic implemented in dimension loading then these primary key would be surrogate key.
Another example is when you are testing facts or metrices that involves transormation such as datatype converion from integer to percentage.
Source SQl would be-
where Integer_Column is not null
Target SQL would be-
from Source_table a, Target_Table b
and a.Integer_Column is not null
If Source query count matches if Target count, it is a pass case.
Similarly, modify the source query to take counts where column is null and avoid the column join in target query as null values would undergo joins.
You follow below link for SQL Query Examples: