ETL Process (Testing) / Data Ware House Testing Process
This article will present you with a complete idea about ETL process (testing) and ETL testes roles and responsibilities
ETL is a process in Data Warehousing and ETL process encompasses data extraction, transformation and loading.
We will learn below topics in this article
ETL Testing Process
ETL testing detects problems with the source data early-before it is loaded to the data repository — as well as inconsistencies or ambiguities in business rules intended to guide data transformation and integration. Below are the stages of ETL Testing Process
Identifying business Requirements:
Business Analyst goes to the client’s place and understands the requirement and prepares the CRS document as per the client need.
Test Planning:
Team works on documents which describe testing activities like test selection tool, cost estimation, resource planning etc.
Designing test cases and test data:
Testing team writes the detailed test cases for the various scenarios, which include positive and negative test cases.
Test execution and bug reporting:
Testing team perform testing and prepare the bug report and report it back to the development team for fixing the bug and again, testers re-test the defect fixes and report the status.
Summarizing reports:
This report lets decision-makers/stakeholders know details and results of the testing process.
Test closure:
Document, which contains the closure report having the detailed analysis of project and bugs, fixes and defect found.
ETL Tester Roles and Responsibilities
1) Writing ETL test cases – Designing SQL queries that can simulate important parts of the ETL process. Create, design and execute test cases, test plans and test harness
2) Applying transformation logic—ETL testers are qualified ETL engineers who can run and design transformation scripts.
3) Verifying source system tables—displaying expertise in data sources, their meaning, and how they contribute to the final data in the data warehouse.
4) Loading data—loading data into QA environments to enable testing.
5) ETL tool testing—checking and troubleshooting the underlying ETL system.
6) Perform smoke testing when migration requests to move the changes from development to QA / UAT / production environment.
7) Involve in all phases of SDLC and give support to UAT team to perform their validations.
8)Perform data quality analysis using advance SQL skills
9) Testing end user reports and dashboards—ensuring that business analysts and data scientists have a complete and accurate view of the data they need.
10) Develop XML test data for web services testing and mimic the same using XML spy.
ETL Testing Challenges
There are many challenges testers faced while performing ETL testing.
Few ETL testing challenges are listed below
-
-
- Inaccurate and duplicate data
- Data Loss during ETL process
- Testers are not facilitated with the source-to-target mapping data.
- Volume and complexity of data is very huge
- Missing business flow information
- Fault in business process and procedures.
- Unable to get access to see Job Schedules
- Complex SQL concepts for data validation
- Huge amount of historical data that makes the data volume vast and extremely complex to perform ETL testing in the target system
- Missing business flow information
- Testers have no privileges to execute ETL jobs by their own.
-
Recommended Articles:
Exemplary knowledge
I really liked the content in all aspects of software testing , testing tools etc