ETL testing- checklist for testing

Question

Can anyone tell me the basic test scenarios and checklist for ETL process testing for beginner level with any example ?

Answer 1

ETL Testing is one of the scenarios where the testing is straightforward but the coding is complicated. As an overview, you should be looking to test each of the elements: Extract, Transform and Load individually and then all of them again as an integrated process.

In the systems I've tested in the past, failures would be flagged and stored, allowing a manual operator to intervene and do what ever needed doing to make the data right. Your system may not need this fallback option but I would suggest that there's some thought given to it.

Extract

From a testing perspective, this appears to be the simplest step. You need to prove that the target data can be extracted and stored. You need to know what data is available and then prove that, after the extraction process, you have all the data you expect.

Things to consider:

• Do you need all the data or just a subset of the fields?
• Do you need all the data or just a subset of the records?
• What format should the extracted data be in? (Windows, Unix... that sort of thing)
• Will the new repository have enough storage capacity?
• Will the new repository be accessible to the Transformation process?
• What happens if data doesn't extract correctly?
• If the extraction is automated and runs daily, what happens over a weekend or a long bank holiday if there's a problem? Does it queue up, is it time stamped?
• How is the environment kept clean? What happens to stale extracted data?
• Does the extraction process handle unexpected / invalid data?

Transformation

This is the bit that everyone focuses on but is usually the bit that's the simplest (as in most traditional) in that it involves a tester proving that some code does what it should. As such, you can expect the defect cycle (finding and fixing) to be the most predictable.

Testing Transformation is all about getting the right data. Understand what each of the transformation rules are and what data boundaries will affect it. I usually spend a great deal of time planning and refining my data requirements and thrashing through the transformation rules with the Developers and Business Owner.

I would isolate each transformation rule and test it in isolation, then identify any transformation rules that overlap and test each overlapping area in isolation.

Things to consider:

• What does each transformation rule do?
• Is each rule doing the right thing?
• What data does it affect?
• What format of input data is expected?
• What format of output data should be generated?
• What are the expected real life data boundaries?
• How much data is the transformation process expected to handle in a given time period?
• What happens if one or more transformation rule fails?
• What happens if the data extract contains invalid data?

Load

Much like the Extraction phase, this seems simple on paper. All you're doing is loading data into a new system. You need to know what data you have and then how it looks in the final respository after the upload process.

To do this, of course, you need to be able to interrogate the final respository directly, avoiding any additional "Extract" stages that could muddy the water.

Things to consider:

• When you query the data, are you seeing what's -actually- there or something that's been packaged by another process? (which would be bad)
• What format of data does the upload process require?
• How quickly does it need to upload the data?
• What happens if the upload fails?
• What happens if the same data is uploaded multiple times?
• What happens if invalid data is uploaded?
• Bearing in mind that the data should be in its permanent home, what is the performance of the system like?
• Will it be able to expand to handle future data loads?

Integration

By this point, you're confident that all the individual -technical- elements are working but now you have to test the business processes that make it all work.

Things to consider:

• How often does the end to end process happen?
• Do the right tasks happen in the right order?
• Is there enough time available for each process to complete before the next one kicks off?
• What happens if one or more of the phases fails?
• How is the automated process monitored to ensure that everything is working?
• Over time, the acceptable input data criteria may change. What contingencies are in place to ensure that today's system will still be working in 5 years time?

---

That's a starter for 10. I hope it gives you some ideas.

Answer 2

Please find below the list of ETL testing scenarios for beginners:

Validation of Mapping Document

A mapping document serves as a requirement specification document for the ETL testers. Testers should verify that the mapping document has all the required information which is as follows:- • Source Database information • Transformation rules • Target Database information • Change log

Validation of database Schema

Schema validation includes comparison of database schema against the mapping document. This test ensures that there is low probability of ETL process failures because of data type mismatch between the processed data and the tables that are designed to hold this data. Schema check includes the following:- • Name of tables • Number of columns • Name of columns • Data types • Lengths of data types

Validation of constraints

Mapping document contains the details related to the database constraints. Testing is performed to ensure that the constraints are defined for the tables as mentioned in the mapping document. For example, is the column “Nullable” or “Not Nullable”

Record count validation

This is one of the very basic but important tests while validating data. This test ensures that records in source and target tables are as expected. Usually the number of records remains the same, but there are cases when the number of records can increase or decrease depending on the requirement. When the number of records remain same, it is known as a “Passive Transformation”. On the other hand, when the number of records change, it is known as an “Active Transformation”.

Validation of correctness of data This is a very important test to ensure the integrity of data. There are different business rules that impact the data as it moves from source to data warehouse. So, it is important to ensure that no data is lost during processing. This can be done by designing test cases around this requirement and analyzing some individual records manually.

Validation of transformation logic

From BI stand point, transformation logic plays a vital role in converting the data into meaning information for the end users to analyze. For every table, all the business rules are stated in the mapping document. And testers need to be very cautious while validating the transformation logic. Incorrect implementation of transformation logic can result in incorrect data after processing; ultimately providing misleading information to the end users. So, it is of utmost importance for testers to ensure that the business transformations have taken place correctly from source to target as per the mapping document. And no transformation rule should be left unchecked. For instance, if the source does not have data to test some specific scenario then data must be simulated in testing environment in order to test such scenario.

Validation of data for duplication

On many occasions, it has been observed that there are some common transformation rules that process the data in one particular way even for different inputs from different sources. In some other cases, source data comprises of data from multiple columns and is processed and populated in a single column in the target; after processing. In such cases, there are high chances of duplication of data in the data warehouse tables. However, these are just examples as there can be multiple reasons that can contribute to duplication of data. Duplicate data is redundant in nature and does not bring any value. Moreover, it can adversely impact the performance of data operations being performed on tables (that contain too many duplicates). So, ETL testers should always include tests that can uncover duplicate data in data warehouse tables as it moves from source to target. Here, testers should check for the unique keys, primary keys.

Answer 3

I got some information in web for ETL testing as below points, Is it useful for me ..Awaiting for your suggestion and feedback

Verify data is mapped correctly from source to target system Verify all tables and their fields are copied from source to target Verify keys configured to be auto-generated are created properly in target system Verify that null fields are not populated Verify data is neither garbled nor truncated Verify data type and format in target system is as expected Verify there is no duplicity of data in the target system Verify transformations are applied correctly Verify that the precision of data in numeric fields is accurate Verify exception handling is robust

Reconciliation check- record count between the STG (staging) tables and target tables are same after applying filter rules Insert a record which is not loaded into target table for given key combination Copy records, sending same records that are already loaded into target tables-should not be loaded Update a record for a key when value columns changed on day_02 loads Delete the records logically in the target tables Values loaded by process tables Values loaded by reference tables

Check if the target and source data base are connected well and there are no access issues. For a full load, check the truncate option and ensure its working fine. While loading the data, check for the performance of the session Check for non-fatal errors. Verify you can fail the calling parent task if the child task fails. Verify that the logs are updated Verify mapping and workflow parameters are configured accurately Verify the number of tables in source and target systems is the same Compare the attributes from stage tables to that of the target tables. They should be matched.

Display date and time Decimal precision for key figures In a given page display the number of rows and columns Free characteristics in the report How are blank values/data displayed for both characteristics and key figures in the report Whether search for characteristics is based on key or key&text as applicable Does search option on text is case sensitive- Upper, Lower or both