Transformation testing can be tackled using the following two approaches 


White Box testing : Implement the transformation in the test case and verify that the output matches the data in the target table. While there are several types of transformations, I will try to address the most common ones. 
  • Expression Transformation: We discussed a classic example of this scenario when we met :  When the name is like  'Divya%' in the source data, the target should have 'D'. This can be implemented using the Query Compare test case using the 'Case When' in the SQL Query. 

    Source Query: select case when first_name like 'Divya%' then 'D' when first_name like 'John%' then 'J' else '' end as Transformed_name from customers

    We used this type of example of compare the source and target data when we met. 

  • Lookup Transformation: Generally there are three common types of lookups -
    • Name - Value Pairs lookup : Example is Country code to country name lookup. Usually these can implemented in the query itself by joining with the table having the lookup data but it can be tedious. So one of the feature planned in the upcoming release of ETL Validator is to provide an easy to use function for these types of lookups. ETL Validator already provides an option to capture these name value pairs using 'lookups'.
    • Foreign Key lookup : Apart from using Source to Target queries, ETL Validator provides a Foreign Key test plan to validate the joins in the target data. This test plan helps identify missing foreign keys and orphan child records (or fact table records).
    • Multivalue Lookup : These lookups generally return multiple values. ETL Validator's Component test case can be used in this scenario. You can have a Source Query Component, Lookup Component and a Join Component to combine the lookup data with the source data. 

  • Joiner Transformation: This transformation is generally used when there are more than one source data for the ETL. ETL Validator's Component test case can be scenario. Screenshot of a sample test case in shown below: 



  • Aggregate Transformation : Again, this can be easily achieved using ETL Validator's Component Test case




    Black Box testing : Given a set of input data does the ETL produce the expected output. Setting up proper test data in the source system become important in this case. ETL Validator has the 'Benchmark and Compare' feature for this type of test. User can take a snapshot of the target data that has the expected data for a given source data. The benchmark and compare feature allows the user to compare the latest data in the target table with the snapshot data every time the test is run. This feature is also part of the Component test case.