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
Why Source to Target Mapping documents matter