ETL Data Transformation
ETL Testing - Data Transformations
Performing data transformations is a bit complex, as it cannot be achieved by writing a single SQL query and then comparing the output with the target to make the purpose meet from all the ends for this. In real projects for ETL Testing Data Transformation, you may have to write multiple SQL queries for each row to verify the transformation rules. To start with, make sure the source data is sufficient to test all the transformation rules and I wish you get this explanation.
The key steps for ETL Testing Data Transformation are listed below
· The first step is to create a list of scenarios of input data and the expected results and validate these with the business customer to give you the best of the result in assertion of progression. Since for a purpose getting in to details of this this is a good approach for requirements gathering during design and could also be used as a part of testing.
· The next step is to create the test data that contains all the scenarios and I wish you get this explanation. I really find this interesting utilize an ETL developer to automate the entire process of populating the datasets with the scenario spreadsheet to permit versatility and mobility for the reason that the scenarios are likely to change.
· Next, utilize data profiling results to compare the range and submission of values in each field between the target and source data.
· Validate the accurate processing of ETL generated fields, e.g.,, surrogate keys.
· Validating the data types within the warehouse are the same as was specified in the data model or design.
· Create data scenarios between tables that test referential integrity.
· Validate the parent-to-child relationships in the data.
· The final step is to perform lookup transformation for your project information. I think for next understanding your lookup query should be straight without any aggregation and expected to return only one value per the source table and likely the act of utmost plausible task. I think for next understanding you can directly join the lookup table in the source qualifier as in the previous test to make the purpose meet from all the ends for this. Depending on necessity if this is not the case, write a query joining the lookup table with the main table in the source and compare the data in the corresponding columns in the target.