what are Queries (SQL) used in ETL testing

Replies to This Discussion

Permalink Reply by Amit Nimje Infocepts on May 14, 2013 at 4:45pm

Hi ,

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

• ▶ Reply

Permalink Reply by Rohit Agrawal on June 7, 2013 at 12:21pm

Hi Kannan,

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

Minus(Or Except)

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-

Select count(*)

from Source_Table

where Integer_Column is not null

Target SQL would be-

Select count(*)

from Source_table a, Target_Table b

where a.Primary_Key=b.Primary_Key

and (a.Integer_Column/100)=b.Target_Column

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.

Thanks,

Rohit

ragrawal@infocepts.com

www.infocepts.com

• ▶ Reply

Permalink Reply by 0v8xiveaqap9q on December 16, 2013 at 1:21pm

Hi,

You follow below link for SQL Query Examples:

http://www.gcreddy.com/2013/04/sql-queries-and-sub-queries.html

• ▶ Reply