ETL testing is done before data is moved into a production data warehouse system. It is sometimes also called as table balancing or production reconciliation. It is different from database testing in terms of its scope and the steps to be taken to complete this.

The main objective of ETL testing is to identify and mitigate data defects and general errors that occur prior to processing of data for analytical reporting.

ETL Testing – Tasks to be Performed

Here is a list of the common tasks involved in ETL Testing −

Understand the data to be used for reporting
Review the Data Model
Source to target mapping
Data checks on source data
Packages and schema validation
Data verification in the target system
Verification of data transformation calculations and aggregation rules
Sample data comparison between the source and the target system
Data integrity and quality checks in the target system
Performance testing on data

ETL vs Database Testing

Both ETL testing and database testing involve data validation, but they are not the same. ETL testing is normally performed on data in a data warehouse system, whereas database testing is commonly performed on transactional systems where the data comes from different applications into the transactional database.

Here, we have highlighted the major differences between ETL testing and Database testing.

ETL Testing

ETL testing involves the following operations −

Validation of data movement from the source to the target system.
Verification of data count in the source and the target system.
Verifying data extraction, transformation as per requirement and expectation.
Verifying if table relations − joins and keys − are preserved during the transformation.

Common ETL testing tools include QuerySurge, Informatica, etc.

Database Testing

Database testing stresses more on data accuracy, correctness of data and valid values. It involves the following operations −

Verifying if primary and foreign keys are maintained.
Verifying if the columns in a table have valid data values.
Verifying data accuracy in columns. Example − Number of months column shouldn’t have a value greater than 12.
Verifying missing data in columns. Check if there are null columns which actually should have a valid value.

Common database testing tools include Selenium, QTP, etc.

The following table captures the key features of Database and ETL testing and their comparison −

Function	Database Testing	ETL Testing
Primary Goal	Data validation and Integration	Data Extraction, Transform and Loading for BI Reporting
Applicable System	Transactional system where business flow occurs	System containing historical data and not in business flow environment
Common tools	QTP, Selenium, etc.	QuerySurge, Informatica, etc.
Business Need	It is used to integrate data from multiple applications, Severe impact.	It is used for Analytical Reporting, information and forecasting.
Modeling	ER method	Multidimensional
Database Type	It is normally used in OLTP systems	It is applied to OLAP systems
Data Type	Normalized data with more joins	De-normalized data with less joins, more indexes, and aggregations.