What is Automation Testing ?

Automation Testing is the test process performed using tools, scripts, and software to perform test cases by repeating pre-defined actions. Test Automation focuses on reducing more time used to perform a repeated work or test activity.

Because Automated testing is done through an automation tool, it consumes less time in exploratory tests and more time in maintaining test scripts while increasing overall test coverage.

Results are compared runtime and test reports can be generated on spot which saves more time compared to traditional testing process.

An organization can apply automated tests to a broad range of test cases such as Unit testing, API, End to end Functionality testing and Regression testing. There are different tools available in market which supports Web Application, ETL, Load testing etc.

Case Study

What is Automation Needed ?

A software needs to be fully tested with many test cases using positive and negative test data before its release. And the application needs to be executed multiple times as part of Unit, Functional and Regression testing which is repeating.

Automation will reduce the execution time by automating the repeated test execution. Tester will write down the script with necessary input data required. We can set up the batches to execute the script at a desired time of a day.

Automation script can be executed ‘n’ no of times per day and it has no limits. Below are the benefits of Automation testing :

Report Preparation

Automation script uses well-designed test cases for the execution which help us to generate the report runtime. Some of the tool like Protractor and Playwright help the user to generate report and take screenshot runtime which saves lot of time compared to traditional manual testing method.

Finding Defects

We can cover wider range of test scenarios with different test data in short time. It results to test the application completely and finding the defect and fixing it. Retesting the defect is also very easy by running the same script.

Speeds Up the Testing Process 

Machines and automated technology work faster than humans. Along with improved accuracy, therefore we use them. In turn, this shortens your software development cycles.

Reduces Human Intervention

It is possible to execute tests at any time of day or night without human supervision.

Saves Time

The testing process often takes a lot of time. While there is an up-front expense associated with automating a process, doing so can result in cost savings in the long term. Team members use their time in other areas and are no longer required to carry out manual testing in many situations. This improves their workflow.

Test Tool Selection

There are many types of tools available in market and each one comes with their own advantages and dis-advantages as mentioned below. A Test manager should consider the requirement and challenges when selecting a test tool.

  • Open-Source tools
  • Commercial tools
  • Custom tools

Open – Source Tools

Open source tools are the program wherein the source code is openly published for use and it’s free of charge.

Almost every step of the testing process, from managing Test Cases to keeping track of defects, has an open-source application that may be used. In case of any support is required there may be a delay in getting response from the support team, which may affect the delivery of the Product.

Commercial Tools

A test manager can go with the commercial test tools when the project has more advanced area to be tested. For example – Banking application which needs more advanced report generation with Pie charts, Bar graphs and Auditing activities.

Compared to Open Source, support for the commercial tool is good and it is a paid support.

Custom Tools

There are unique aspects to the testing environment and testing procedure in particular Testing projects. No open-source or commercial tool can meet the requirement. Therefore, the Test Manager must consider the development of the custom tool.

Different Stages of ETL Testing

  • Identifying data sources and requirements

Data sources, the destination system, and the needed degree of transformation between them must all be defined in order to meet business needs. Check the current data model and update it if it needs to be.

  • Data Acquisition

Perform a count of records of the primary source table so that you can later confirm that all the data was moved.

  • Create Test Cases

Consider evaluating the data for accuracy and reliability on the target system. There should also be some kind of ETL performance testing.

  • Data Extraction

Confirm that the data has been transformed to the appropriate format for the target system and that it complies with all the mapping rules or business rules.

  • Load Data into Target

Check the record count to confirm that all the data has been moved. Check if any records were rejected by the target system and not loaded to make sure the data is correct.

  • Build Reports 

Document any bugs or issues that you’ve identified during the test process. Capturing all the challenges faced during the testing will help to avoid time loss in the future executions.

Types of ETL Testing

The ETL process incorporates numerous types of testing. Below are the main types of testing involved at ETL projects:

  • Source to Target Testing ( Mapping Validation Testing )

It is carried out to validate whether the data values transformed based on the Mapping rules are the expected data values.

  • Data Completeness Testing

Data completeness testing ensures that all of the source data is successfully transferred to the target environment. Counts, aggregates, and actual data can be compared and validated between the source and target for columns with minimal or no transformation in order to determine whether or not the transformation was successful.

  • Data Accuracy Testing

Data loading and transformation are tested to guarantee they go off without a hassle. Data will be validated in the target system using the mapping conditions.

  • Data Transformation Testing

Testing data transformation is done because, in many cases, it can’t be done by writing one source SQL query and comparing the output to the target. For each row, you may need to run more than one SQL query to check the transformation rules.

  • Data Quality Testing

Data Quality Tests includes syntax and reference tests. Data quality testing is carried out to ensure that there are no mistakes in the business process as a result of a mismatched date or order number.

No rejection will usually happen in the Data Quality layer. All the data will be verified against the business rules and notified.

  • Data Cleansing Testing

Data cleansing tests includes the rejection of the identified invalid data in the Data Quality layer. Rejection will be happened based on the report generated in the DQ layer.

For Example: FirstName field should not allow Numeric. DOB should be in ‘dd/mm/yyyy ‘ after the standard data transformation rule.

  • Meta Data Testing

Metadata testing includes testing for data type, data length, and index/constraint.

Benefits of ETL Testing

  • Quality of Data

It is essential for making the decisions as it helps to assure that only standard quality and accurate data is saved in the production servers.

  • Avoid the Risk of Data Loss 

ELT testing helps to avoid the risk factor of data loss.

  • Provides Timely Access

Check the data and provide the access to the user at any time.

Roles of an ETL Tester

  • Design and develop UNIX and SQL commands as part of the ETL process, automate the process of pulling the data.
  • Work extensively with SQL and PL/SQL queries to test database functionality.
  • Involve in all the phases of SDLC and STLC and gather the requirements from BRD’s.
  • Validate mainframe datasets migrating to DB2.
  • Experience in working with DB2, Teradata.
  • Used ALM to track and report on defects.
  • Test the database schema with help of data architects.
  • Work with leadership team to analyse current SDLC process and recommend process improvements.
  • Used ALM for storing, maintaining the test repository, bug tracking and reporting.
  • Execute SQL queries to compare the data in database with Microsoft SQL GUI and web services.
  • Generate test reports, test closures and Deployment notes as part of the testing process.
  • Promote the code into higher regions like UAT/BAT/PAT.
  • Closely monitor the executions and rejections of higher regions and try to recreate the same in test environment to check the behaviour of the code.

Challenges in ETL Testing

ETL is quite different with regular testing. There are lot of challenges are facing while performing ETL or Data warehouse testing. 

The main challenges of ETL or Data warehouse testing includes:

  • Data duplication:

An ETL tester involves in the test data preparation as part of the test design process. Test data are prepared in a way which satisfies all the logics and transformation rules as per the mapping document.

Data duplication is handled in ETL testing using the Deduplication component. Duplicate data will be found in the staging layer and rejected.  All the duplicate records are captured in the rejection ports of the join components for the validation process.

  • Data Loss:

 During the extract and transformation process, there will be a loss of data due to many reasons like:

  1. Test data has not acquired under the Business SQL selection criteria
  2. Test data is not updated properly during the batch execution.
  3. Due to frequent change of requirement over the releases, human error may occur in test data preparation which leads to data loss.
  • Test Bed

Tough to generate and build test cases, as data volume is too high and complex.

  • Requirement Gaps

ETL testers normally do not have an idea of end-user report requirements and business flow of the information.

  • Region Unavailability 

Unstable testing environment delay the development and testing of a process.

  • Data Unavailability 

Production region may have the latest data wherein Test and UAT has the replication of PROD data. Sometimes there will be a delay in getting the data which leads to a big challenge in testing the latest requirements.

  • Third Party Team

In ETL process, sometimes 3rd party teams will be participated, and they have their own role to perform. Due to Communication gap or Understanding gap there is a high chance for the defect leakage or delay in code delivery.

  • Short Release

In Agile projects, many requirements may come at the end which has to be tested and promoted to higher regions. Due to short timeline and Resource restrictions, there is a high chance for a defect leakage. This can be avoided through well planned test executions.

Key points to be considered when selecting a Test tool:

  • Understand the Project requirement fully.
  • Select the test tool where less trainings are required for the team members.
  • Test tool should have the feature of reporting and evidence tracking.
  • Ease of Developing the scripts and maintaining it.
  • Ease of test execution
  • Batch execution
  • Support to Web, Desktop and Mobile application testing.
  • Cross browser testing
  • Freedom of executing selected test cases or modules
  • Support to Data Driven testing
  • Technical support and assistance.
  • Programming language support
  • Pricing

Scope of Automation Testing

We cannot automate all types test scenarios using Automation testing. So, it is very important to analyze the scope of testing at first place. Here are the things to consider while identifying the scope of automation testing:

  1. What are the modules that can be automated?
  2. What are the total effective costs and the team size?
  3. What are the tests to be automated, and which is the approach to be taken?
  4. Initial region set up based on the modules going to automate.

Types of Automation Testing

  • Functional Testing

Functional testing is about what the product does and verifies each feature of the application. It is based on customers’ requirements. Testing the functionality of a programme involves verifying its responses to specific user input. It can be done manually as well as automated.

  • Non-Functional Testing

Non-functional testing is about how the product acts and checks the non-functional characteristics, such as performance, reliability, usability, etc., of the product. It is based on customers’ expectations. With non-functional testing, you validate the performance of the software. It is hard to do manually.


  • Unit Testing

This is mostly performed by the Developers to make sure all the loops and individual part of the code is working as expected.

  • API Testing

API works as a communicator/messenger between the Front-end and database. API testing checks the end-to-end functionality of the application. The software is made to send API calls to get the output, and the testers note the system/application response to check the functionality. This type of testing is more effective and useful in API, Web application and mobile application.

  • UI Testing

Tester involves in validating the appearance of the application and the functionalities of each component like check box, text field, Data field, attachment icon, Hyperlink, Image box, drop down, upload button, download button etc.,

UI testing checks for the functionality of the application in handling user actions, which are done via their keyboard, mouse, and other input devices. Its main intent is to provide a friendly user interface (UI) and experience.

  • Performance Testing

Tester involves in validating the application’s responsiveness, speed, stability, reliability, and robustness under a certain workload.

It is very important for an application to work in different condition like huge workload, accessing large user, taking huge volume of data and network traffic.

  • Integration Testing

Entire application is properly integrated and tested in this layer to make sure there is no gaps in the flow/connections. All the modules are integrated and tested with different types of input.

  • Regression Testing

Whether the developed and tested software works the same way after changes have been made, then you perform regression testing. The changes can be bug fixes, configuration changes, or the enhancement of the software. You achieve this by re-running functional and non-functional tests on the application.

Leave a Reply

Subscribe to Our Newsletter

Recent Articles

Knowledge Center Article

Get in Touch