Difference between Database Testing and ETL Testing | SQL queries for ETL/Data Base Testing
- The main Objective of Database testing is check if the data is following the rules/standards defined in the Data Model.
- In Database testing , Data is organised in the database in the form of tables.
- DB testing includes verifying the components of database under test like schema, Functions, Stored Procedures, triggers, tables, underlying data, Performance etc.
- The main Objective of ETL testing is to check if the data moved properly as expected.
- ETL Testing covers testing of all these three steps involved in the ETL as well as testing the target database where in the data is loaded.
We will learn below topics in this article
Difference between Database testing and ETL Testing
|Objective||Extraction, Transform and Loading for BI Reporting||Data validation and Integration|
|Systems applicable||Systems with historical data||Systems with more of transactional data|
|Database type||OLAP database for historical data||OLTP database for transactional data.|
|Data type||Denormalized data with a lot of indexes and aggregation||Normalized data consisting of a lot of joins.|
|Examples of tools||QuerySurge, Informatica||Selenium, QTP|
|Modeling||Multidimensional model||Entity-Relationship model|
|Operation||read-only option is used||create, read, update, and delete operations are performed|
|Data Size||Voluminous data||Data size used is small.|
|Volume||smaller scale.||Large scale.|
SQL Queries for Database Testing/ETL Testing
Below are the top SQL queries/Concepts used in ETL testing.
Not Null Constraint
Primary Key Constraint
Foreign Key Constraint
SQL Sorting (Order By)
SQL Group By
SQL Aggregate Function
Left Outer Join
RIGHT Outer Join
Independent Sub Queries
Correlated Sub Queries