An ETL tester is basically responsible for invalidating the data sources, in the extraction of data from sources, applying the transformation logic, and loading the data in the target tables.
Important ETL Tester Job Responsibilities are:
• Test ETL Datawarehouse components.
• Executing backend data-driven test.
• Test flat file data transfers.
• Writing SQL queries for various scenarios like count test,
• Primary key test,
• Duplicate test,
• Attribute test,
• Technical data quality,
• Business data quality.
The main responsibilities of an ETL tester are stated below.
• Verify the Tables in the Source System
• Verification of tables involves the below-listed operations −
• In Count check
• Reconcile records with the source data
• In Data type check
• Ensure no spam data loaded
• In removing duplicate data
• To check all the keys are in place
Apply Transformation Logic
• Transformation logic is applied before loading the required data. This involves the below-listed operations −
• Data threshold validation check, for suppose, age value shouldn’t be greater than 100.
• Record count check should be done, i.e before and after the transformation logic is applied.
• Data flow validation from the staging area to the intermediate tables.
• Surrogate key check.
• Data is loaded from the staging area to the target system. This involves the below-listed operations −
• To record the count check from the intermediate table to the target system.
• To ensure the key field data is not missing or Null.
• To check if the aggregate values and calculated measures are loaded in the fact tables.
• To check modeling views based on the target tables.
• To check if CDC has been applied on the incremental load table.
• Data check-in dimension table and history table check.
• To check the BI reports w.r.t the loaded facts and the dimension tables as per the expected outputs.
ETL Tester’s responsibilities don’t differ much from any other software tester or Quality Analyst .here are some of their responsibilities
• Create Test plans which ensure that ETL batch performs the required data transformations and load as specified in the required documentation.
• most of the requirements are derived based on source to target mappings which is nothing but a document that specifies any business logic to perform while performing ETL operation. an example may be source have First Name and Last Name as two separate columns but the Target database would need a single column as Name and that should be specified as First Name and Last name together.
• create some data records which test the boundary conditions of the logic defined for example if ETL requirement says to filter any transaction amount greater than $10000 .ETL tester should run the ETL batch with input amount value as 10000 or 9999 etc to insure logic works for exact values too.
• Create some failures in the record format to check if the ETL batches fail with a correct error or log the error as expected per the design
• Check the resulted data to ensure correct was of the data as per requirement.