Source To Target Mapping Testing

Can someone point me in the direction on how to test source target mapping of data?   I need a template or direction on how to create a test plan that tests source to target mapping of a web application.  The Web application source is from a Data Warehouse with SQL tables on the back end.  We are mapping to the application with Oracle tables.

I am asking testers of the source system to check that the data source is mapped correctly to tables and validation of the data.  I need suggestions or instructions on how to develop a test plan to provide to them.  

alpha100Application Systems Analyst IIAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Evan CutlerConnect With a Mentor Volunteer Chief Information OfficerCommented:
have you built a Source-to-Target Mapping document? SourcetoTarget-Mapping-Template.xlsx
It is the responsibility of your DB Modeler to define the document, while your ETL architect to design the movement (ETL Jobs).  If they are the same person, it's easier, but most of the time it isn't.  

In your case, it sounds like you are creating a ODS database which is a copy of the raw data coming in.  A user story for ODS would be: 'As a data consumer, I want a copy of all incoming data to work with so I may process them into my data warehouse from inside my database'.

You can use a Source to Target Mapping document like the one attached to visualize your transformations.  To answer your initial question, a date field is a date field.  As long as it's inserted, you can confirm that source.datefield = target.datefield in a Unit test.  Unless your ETL is changing the format of the Date, then your transformation is "COPY".  Otherwise, if the date is in a string format in your source, and you use ETL to capture the string into a date you can set transformation in your document to something like below:

Business Rule: transform String to Date when given 'YYYY/MM/DD' format <--You can chance that
Technical Rule: TO_DATE('2003/07/09', 'yyyy/mm/dd')

Hope this helps.
Evan CutlerVolunteer Chief Information OfficerCommented:
In most cases, the mapping itself is just documentation.

What you need to do is compare source data to target data.
What most people do is write the SQL statement that shows source data in the target data's format, then compare that output to target rows completed by your ETL/SQL.  To assist, make a fake dataset of a small dataset.
alpha100Application Systems Analyst IIAuthor Commented:
Can you provide me with an example of a statement that shows source date in the target data's format to make a comparison?  Thanks.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Evan CutlerVolunteer Chief Information OfficerCommented:
can you please be more specific?  Are you converting date formats?
Is this from a file to a db? db to db? or db to file?  if file, what kind?

alpha100Application Systems Analyst IIAuthor Commented:

What I am trying to do is send the data owners use case scenarios.  We are receiving an extract file  from our customer; the source being a data warehouse.  We are doing a 1 to 1 mapping of their data to our oracle warehouse tables used to display information on our GUI.  We do take the data through and ETL process but there is there is no transformation of the data.  No file format changes.   We just need  to verify if the information captured is true.  Since I am not familiar with the data, I just need to know how to format scenarios.  What should I provide to the data owner.  That is where the confusion is for me.  Thanks.
alpha100Application Systems Analyst IIAuthor Commented:
Are these the instructions that I provide to the testers.  The testers are not use to source to target mapping.  I have my test plan developed.  I just need what wording to provide to them for test plan instructions?  What do they need to do?
alpha100Application Systems Analyst IIAuthor Commented:
What exactly do the testers need to be checking for.  I just need wording on instructing them on what to check?  Thanks.
Evan CutlerVolunteer Chief Information OfficerCommented:
use this as your reference:

however, the Source To Target Matrix describes the expectation of data from Source to Target.  The Testers need to perform a series of tests (below is examples):

1. RowCount  (Expected Target Table rowcount = actual Target Table rowcount)
2. Proper Lookups (Expected Target field value = Actual Target field value)
3. Null Value handling (NO Null errors; Expected NULL to NON-NULL Hanlding)
4. Negative value test (Reject handling)
5. No Operational Failures (ALL ETL RUN)

You're testers should be trained enough to read your STM and develop a data sets and sql queries to create true/false reporting on expected results.  

So, to answer your question, give your testers the STM, and they should develop a series of data sets for your tables, and be able to run the ETL code without failures.  From there, their SQL statements will validate the values you are supposed to have.

Do you need a consultation?  I do provide full time consultation and assistance in ETL development and testing.
alpha100Application Systems Analyst IIAuthor Commented:
Yes I need consultation.  How much do you charge.  I know in the future, I will be working on this again.  And we don't have individuals in our group who have ever had to do this type of document or testing.  So we have no templates, no instructions no nothing to assist with this type of task.
Evan CutlerVolunteer Chief Information OfficerCommented:
No problem.  I will message you directly with contact and other information.
alpha100Application Systems Analyst IIAuthor Commented:
Do I need to send the tester the ETL Code?
alpha100Application Systems Analyst IIAuthor Commented:
What if I don't have access to the code?
Evan CutlerVolunteer Chief Information OfficerCommented:
no. The tester shouldn't need to access the code.  just the ability to run it when developed.  please understand, I'm typing this in the blind with no knowledge of your business, so I'm just using normal standards.

The Source-to-Target Mapping document has all the information needed for the developers to create ETL code.  That same guidance is given to a tester to create a SQL-style test.


if I had a table of 5 columns, and they were going to just be copied from source to target, with no transformation, then the ETL will be designed with high-speed movement.  however a tester would do the following:

1. create a series of test rows (could be 10 or 100, depending on your preference).  
2. Insert the series of rows into an empty Source table.
3. Run the ETL or ask the Developers to run it.
4. Compare the following:
- Number of rows that made it into Target
- Values of a randomly selected row are identical to source
- ETL ran successfully with no errors.

To do this, i would write a sql statement like this in the target:
1. select count(1) from target table <- gets number of rows
2. select * from target table where table_primary_key = x <-- where x is a randomly selected row to compare with target

if you start running into more complicated issues, such as lookups and null handling, then I would pick a random record, and create this:
Example 1: select nonnullablefield from target_table where table_primary_key = x <--where x is a randomly selected row in source that has a null entry
Example 2: select lookup_field_value from target_table where table_primary_key = x <--where x is a randomly selected row in source that has a value that matches a lookup table's key value.

And further tests could be done, such as reject handling.  The number of tests are usually equaled by the patience of the product owner.  The Source-to-Target mapping informs the testers how the transformations are being done, and they should be creating these statements.  WHen they are run against the target table, the results can assist in proving positive, and sometimes negative, tests.

Hope this gives you an idea.
alpha100Application Systems Analyst IIAuthor Commented:
Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.