ETL Testing

Usually in data warehousing/business intelligence projects, ETL (Extract, Transform, Load) tools such as Informatica, Datastage, Talend, etc., are used to design ETL Jobs. Some companies still use scripting to develop ETL jobs.

ETL testing can be as simple as

Testing an ETL job manually by running it from a GUI verifying that the job runs, and validating the data loaded against expected data.

or can be complex like

Automating test data creation using scripting, SQL and tools like SOAP UI, using tools like Jenkins or ETL tools itself to trigger automated test data creation scripts and then automatically run the actual ETL job and then automated comparison of the loaded data against baselined data and highlight anomalies (if any) and auto check data against expected results for every single test case that was created earlier. Integrate the test scripts and test data creation scripts with the full set of ETL jobs so that it can be run during regression testing of the ETL Jobs when one of the existing jobs changes. Also includes performance testing (volumes, run times, impact on source systems).

In my view, the job market for an ETL tester is very small compared to a Job market for an ETL developer. Many projects do not have specific role of ETL tester. These projects usually have a end to end (e.g. reports, portal, DB and ETL) tester rather than specific to ETL, or the developers themselves review and test each others' code. So the number of openings specific to ETL testing will be less and it could reduce further with increasing test automation. I also think that it's not good to limit a profile to only ETL testing. Manual ETL testing is usually carried out by offshore based IT companies that take advantage of cheap labour. The Manual testing of ETL jobs and reports has to stop. Those into writing scripts for automation of ETL and report testing and automated test data setup will be valued more and have better future compared to manual testers.

Capabilities of the ETL tool itself could be used to create separate set of jobs (test project) to carry out data comparisons, validations and write results to file or DB. If you use a reporting tool you could analyze the data with that.

Comments

Post a Comment

Thanks for your comment. It will be posted after checks.

Popular posts from this blog

ETL developer vs Data engineer

Image
Rephrased question : ETL developer vs Data engineer Answer : Unfortunately there are no strict industry standards on these job titles. That is just one part of it. Before ETL tools such as DataStage, Informatica, Ab Initio, etc., became popular, developers were hand coding every ETL flow. These ETL tools shortened the ETL flow development time to a great extent and allowed ETL developers to focus on business rule/logic/requirement (what to implement) than how to code it or optimize the code. There are many other benefits of using a tool but I won’t go into that in this answer. So an ETL developer with experience in these tools without any programming (coding) experience was/is able to design and develop end to end data flows. Whenever new types of source/target data format comes up, these tools catch up but it takes time, i.e., the ETL tool provider (e.g. Microsoft, IBM, etc.,) adds new components/connectors within the ETL tool to be able to work with new data format. For example, let’
Read more

3 years of IBI

This week (on 19th Feb 2020) I completed 3 years of IBI. To celebrate it I have created a Dashboard in Google Data Studio. So from now on instead of using the charts in the Google Sheets I will use the Dashboard. Google Data Studio is more user friendly and mobile friendly too, also it comes with easy to use filters and drill down/up capabilities. PDF version of the first 2 pages of the shareable dashboard is attached below. I am happy to share the first 2 pages of the IBI Dashboard, reach out to me if you would like to get the Google Data Studio IBI dashboard. Once you get the dashboard (without my data of course) you can make a copy of it and point it to your IBI data (assuming you have started), add more charts, pages, etc., and use it for your benefit. Like with every year, this IBI year too I have learnt a lot about myself, made new mistakes, changed several things about me. The benefits of IBI is much more than we can think of. Use it to know it. And what are some of the
Read more