Quality Testing

Quality is delighting customers

sample sql query pls 

Views: 34930

Reply to This

Replies to This Discussion

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

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.






You follow below link for SQL Query Examples:



TTWT Magazine





© 2021   Created by Quality Testing.   Powered by

Badges  |  Report an Issue  |  Terms of Service