ETL Quality Assurance - is source-to-target mapping consider an input to the testing phase?

Question

At my place of work we have long debated whether source-to-target mapping should be an input to the testing phase perform by QA.

Developers insist that QAs should follow the requirements documents, while QAs insist that since requirements are usually written in business language and subject to interpretation by developers during the implementation, the need source-to-target mapping in addition to requirements to be able to perform testing accurately.

Is there a general industry consensus on whether source-to-target mapping is an input to the testing phase? I also have to clarify that our developers do produce mappings, sometimes during, sometimes after they complete ETL work, they are just often opposed to sharing those.

Answer 1

Yes and No. The developers mapping should not be a secret, but what if the developer misinterpreted the business requirement and their mapping is wrong? I would first validate that the mapping matches the requirements (and that nothing was overlooked) and then use the mapping as a basis for generating test cases.

Answer 2

The Source to Destination mappings is some kind of input for the Testing Phase but not exactly.

The Source data may be in the different format like DB, CSV, Excel, XML & JSON.

Each format file should have the header pieces of information like

Ex: Customer_ID is the header information.

The Source header pieces of information, we need to map to the destination fields.

After Mapping the source and destination need to perform the load for real data

Answer 3

Goal of whole organization (business, developers, QA, etc) is to deliver the best quality product to your customers for the best price.

If mapping contributes to this goal, it should be done. If contribution is not significant, it is waste of efforts.

Your QA manager need to talk to devs' manager and business manager how to accomplish that goal. Is the quality sufficient using current process? If not, where your process can be improved? Maybe it is mapping. maybe something else. You know your situation and we don't.

Answer 4

Source to target mapping must be a part of inputs shared with the testing team

I have worked as an ETL tester for about 4 years for one of our US clients. It must be kept up to date at a common repository accessible to testers and developers.

1. Why testers need mapping document?

The reason is; because it is the encyclopedia for each and every table that is part of ETL process from source to destination. We had a source database (call it Source) another Staging database and an operational data store (ODS) and final data warehouse. Sometimes, while working on a target table in data warehouse, we have to refer to around 4 to 5 tables from source to staging and ODS. A mapping document which includes the complete table details along with transformation logic is a neat way to organize data. At any point in time, you want to discuss any table in any of the database used in ETL process, you know that you will get it the mapping document (along with all the columns of the table, how they are derived, and which all target tables they affect).

We found many issues which existed because of incorrect mappings or because the mapping document was not regularly updated.

Isn't it a neat way? or is it convenient to open JIRA or other tool and get lost in 10 different stories? First, spend time in finding them and then end up realizing that...oh there are another 5 user stories that have information related to this table.

I hope that you got my point.

We had user stories but for table details and transformation logic, we use to refer to mapping document. It was must for each developer to update the mapping document if they have made any changes to the database while implementing a user story. We had strict instructions to reject a user story straight away if the mapping document is not updated by the developer.

I can imagine the difficulties that one might face because of the unavailability of mapping document. We knew that if in the mapping document, I am looking at table A then all the changes made to this table in last many years will be available on this page itself. Need not to go anywhere else.

See, standards are for the benefit of industry. Any information or process that is another step forward in improving the team ability to effectively deliver a software product should be adopted.

Thorough testing is not possible without the help of mapping document. Refer to

Why Source to Target Mapping documents matter

Answer 5

This probably is a topic of debate in every ETL Project. What I believe is a QA should use both Requirements as well as source- Target Mapping document for testing.

QA team (at least Test lead) should be involved in the project right from the requirement understanding phase and should actively participate in the design discussions as well. This involvement of the QA team from initial phases of the project will help in testing the design & end result from requirement perspective. For this it is important that QA should also have data modelling knowledge. Now once the design has been reviewed, they can use the source- Target Mapping document to verify the data model & data flow in the warehouse.

Additionally, as a part of End to End testing the requirements should be re validated after development. This will help to identify defects in the early phases of development life-cycle, reduce bug fixing cost & the ETL will be tested against both the requirements & design.

Hope this helps.