Extract transformation and load testing (ETL)

              HAPPY TESTING...!

What is ETL Testing?

ETL testing is done to ensure that the data that has been loaded from a source to the destination after business transformation is accurate. It also involves the verification of data at various middle stages that are being used between source and destination. ETL stands for Extract-Transform-Load.

ETL Testing Process

Similar to other Testing Process, ETL also go through different phases. The different phases of ETL testing process is as follows

ETL testing is performed in five stages

1.  Identifying data sources and requirements
2. Data acquisition
3. Implement business logics and dimensional Modelling
4. Build and populate data
5. Build Reports

Types of ETL Testing

Types Of Testing	Testing Process
Production Validation Testing	“Table balancing” or “production reconciliation” this type of ETL  testing is done on data as it is being moved into production systems.  To support your business decision, the data in your production systems has to be in the correct order.  Data Validation Option provides the ETL testing automation and management capabilities to ensure that production systems are not compromised by the data.
Source to Target Testing (Validation Testing)	Such type of testing is carried out to validate whether the data values transformed are the expected data values.
Application Upgrades	Such type of ETL testing can be automatically generated, saving substantial test development time. This type of testing checks whether the data extracted from an older application or repository are exactly same as the data in a repository or new application.
Metadata Testing	Metadata testing includes testing of data type check, data length check and index/constraint check.
Data Completeness Testing	To verify that all the expected data is loaded in target from the source, data completeness testing is done. Some of the tests that can be run are compare and validate counts, aggregates and actual data between the source and target for columns with simple transformation or no transformation.
Data Accuracy Testing	This testing is done to ensure that the data is accurately loaded and transformed as expected.
Data Transformation Testing	Testing data transformation is done as in many cases it cannot be achieved by writing one source query and comparing the output with the target.  Multiple queries may need to be run for each row to verify the transformation rules.
Data Quality Testing	Data Quality Tests includes syntax and reference tests.  In order to avoid any error due to date or order number during business process Data Quality testing is done. Syntax Tests: It will report dirty data,  based on invalid characters, character pattern, incorrect upper or lower case order etc. Reference Tests: It will check the data according to the data model.  For example: Customer ID Data quality testing includes number check, date check, precision check, data check , null check etc.
Incremental ETL testing	This testing is done to check the data integrity of old and new data with the addition of new data.  Incremental testing verifies that the inserts and updates are getting processed as expected during incremental ETL process.
GUI/Navigation Testing	This testing is done to check the navigation or GUI aspects of the front end reports.

How to create ETL Test Case

ETL testing is a concept which can be applied to different tools and databases in information management industry.  The objective of ETL testing is to assure that the data that has been loaded from a source to destination after business transformation is accurate.  It also involves the verification of data at various middle stages that are being used between source and destination.
 


While performing ETL testing, two documents that will always be used by an ETL tester are

1.  ETL mapping sheets :An ETL mapping sheets contain all the information of source and destination tables including each and every column and their look-up in reference tables. An ETL testers need to be comfortable with  queries as ETL testing may involve writing big queries with multiple joins to validate data at any stage of ETL. ETL mapping sheets provide a significant help while writing queries for data verification.
2. DB Schema of Source, Target: It should be kept handy to verify any detail in mapping sheets.

 

ETL Test Scenarios and Test Cases

 

Test Scenario	Test Cases
Mapping doc validation	Verify mapping doc whether corresponding ETL information is provided or not.  Change log should maintain in every mapping doc.
Validation	Validate the source and target table structure against corresponding mapping doc. Source data type and target data type should be same Length of data types in both source and target should be equal Verify that data field types and formats are specified   Source data type length should not less than the target data type length Validate the name of columns in the table against mapping doc.
Constraint Validation	Ensure the constraints are defined for specific table as expected
Data consistency issues	The data type and length for a particular attribute may vary in files or tables though the semantic definition is the same. Misuse of integrity constraints
Completeness Issues	Ensure that all expected data is loaded into target table. Compare record counts between source and target. Check for any rejected records Check data should not be truncated in the column of target tables Check boundary value analysis Compares unique values of key fields between data loaded to WH and source data
Correctness Issues	Data that is misspelled or inaccurately recorded Null, non-unique or out of range data
Transformation	Transformation
Data Quality	Number check: Need to number check and validate it Date Check: They have to follow date format and it should be same across all records Precision Check Data check Null check
Null Validate	Verify the null values, where “Not Null” specified for a specific column.
Duplicate Check	Needs to validate the unique key, primary key and any other column should be unique as per the business requirements are having any duplicate rows Check if any duplicate values exist in any column which is extracting from multiple columns in source and combining into one column As per the client requirements, needs to be ensure that no duplicates in combination of multiple columns within target only
Date Validation	Date values are using many areas in ETL development for To know the row creation date Identify active records as per the ETL development perspective Identify active records as per the business requirements perspective Sometimes based on the date values the updates and inserts are generated.
Complete Data Validation	To validate the complete data set in source and target table minus a query in a best solution We need to source minus target and target minus source If minus query returns any value those should be considered as mismatching rows Needs to matching rows among source and target using intersect statement The count returned by intersect should match with individual counts of source and target tables If minus query returns of rows and count intersect is less than source count or target table then we can consider as duplicate rows are existed.
Data Cleanness	Unnecessary columns should be deleted before loading into the staging area.

Types of ETL Bugs

Type of Bugs	Description
User interface bugs/cosmetic bugs	Related to GUI of application  Font style, font size, colors, alignment, spelling mistakes, navigation and so on
Boundary Value Analysis (BVA) related bug	Minimum and maximum values
Equivalence Class Partitioning (ECP) related bug	Valid and invalid type
Input/Output bugs	Valid values not accepted  Invalid values accepted
Calculation bugs	Mathematical errors Final output is wrong
Load Condition bugs	Does not allows multiple users Does not allows customer expected load
Race Condition bugs	System crash & hang System cannot run client platforms
Version control bugs	No logo matching  No version information available This occurs usually in regression testing
H/W bugs	Device is not responding to the application
Help Source bugs	Mistakes in help documents

 

Difference between Database testing and ETL testing

 

ETL Testing	Data Base Testing
Verifies whether data is moved as expected	The primary goal is to check if the data is following the rules/ standards defined in the Data Model
Verifies whether counts in the source and target are matching Verifies whether the data transformed is as per expectation	Verify that there are no orphan records and foreign-primary key relations are maintained
Verifies that the foreign primary key relations are preserved during the ETL	Verifies that there are no redundant tables and database is optimally normalized
Verifies for duplication in loaded data	Verify if data is missing in columns where required

 

Responsibilities of an ETL tester

Key responsibilities of an ETL tester are segregated into three categories

• Stage table/ SFS or MFS
• Business transformation logic applied
• Target table loading from stage file or table after applying a transformation. 

Some of the responsibilities of an ETL tester are 

• Test ETL software
• Test components of  ETL datawarehouse
• Execute backend data-driven test
• Create, design and execute test cases, test plans and test harness
• Identify the problem and provide solutions for potential issues
• Approve requirements and design specifications
• Data transfers and Test flat file
• Writing  queries3 for various scenarios like count test

ETL Performance Testing and Tuning

ETL performance testing is a confirmation test to ensure that an ETL system can handle the load of multiple users and transactions.  The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks. To tune or improve the performance of the session, you have to identify performance bottlenecks and eliminate it. Performance bottlenecks can be found in source and target databases, the mapping, the session and the system. One of the best tools used for performance testing is Informatica.

Automation of ETL Testing

The general methodology of ETL testing is to use scripting or do “eyeballing” of data.. These approaches to ETL testing are time-consuming, error-prone and seldom provide complete test coverage. To accelerate, improve coverage, reduce costs, improve defect detection ration of ETL testing in production and development environments, automation is the need of the hour. One such tool is Informatica.

Best Practices for ETL Testing

1. Make sure data is transformed correctly
2.  Without any data loss and truncation projected data should be loaded into the data warehouse
3.  Ensure that ETL application appropriately rejects and replaces with default values and reports invalid data
4.  Need to ensure that the data loaded in data warehouse within prescribed and expected time frames to confirm scalability and performance
5.  All methods should have appropriate unit tests regardless of visibility
6. To measure their effectiveness all unit tests should use appropriate coverage techniques
7. Strive for one assertion per test case
8.  Create unit tests that target exceptions

      HAPPY TESTING ...!