Types of ETL Testing

Production Validation Testing: Table balancing or production reconciliation - This type of ETL testing is performed on the data as it moves into the production system. Data in production systems must be in the correct order to support business decisions. Informatica's data validation option provides the ability to automate and manage this ETL tests to ensure that data does not compromise production systems.

Source to Target Testing (Validation Testing): This type of test is performed to verify that the converted data value is the expected data value.

Application Upgrades: This type of ETL tests can be generated automatically, saving a lot of time in test development. This type of test verifies that the data extracted from the legacy application or repository exactly matches the data in the repository or with other new application.

Metadata Testing: These tests include tests for data type checks, data length checks, and index/constraint checks.

Data Completeness Testing: Run data integrity tests to ensure that all expected data is loaded from the source to the target. Among the tests that can be performed are counts, aggregations, and comparisons and validations of actual data between source and target for columns with simple or no conversion.

Data Accuracy Testing: These tests are run to ensure that data is loaded and converted exactly as expected.

Data Transformation Testing: Data transformation testing is often performed because it cannot be achieved simply by constructing the source SQL query and comparing the output to the target. Validating transformation rules may require executing multiple SQL queries per row.

Data Quality Testing: Data quality tests include syntax tests and reference tests. Data quality tests are performed to avoid errors due to dates and order numbers in business processes.

Syntax Testing: Reports bad data based on invalid characters, character patterns, case differences, etc.

Reference test: data is checked against the data model. Example: Customer ID

Data quality tests include number checks, date checks, accuracy checks, data checks, null checks, etc.

Incremental ETL testing: This test is run to verify data integrity between old and new data when new data is added. Incremental testing verifies that inserts and updates are handled as expected during the incremental ETL process.

Graphical User Interface/Navigation Testing: These tests are run to validate navigation or GUI aspects of front-end reports.

ETL Test Scenarios with Test Cases

Mapping doc validation: Check the mapping document to see if the corresponding ETL information is provided. A change log should be kept in each mapping document.

Validation:

Validate the source and target table structure against the appropriate mapping document.

Source data type and target data type must be identical

Source and target data types must have the same length.

Four. Make sure the data field type and format are specified

The length of the source data type cannot be less than the length of the target data type

Validate the names of the columns in the table against the mapping document.

Constraint Validation: Check that the constraints are defined as expected for the table.

Data consistency issues: The data type and length of certain attributes may differ between files or tables, even though the semantic definitions are the same. Misapply Integrity Constraints

Completeness Issues:

Make sure all expected data is going to be loaded into the target table.

Comparison of the number of records in the source and destination table.

Find rejected records if any.

Audit data must not be truncated at target table columns.

Check limit value analysis

Compare unique values ​​of key fields between data loaded in Dataware House and source data.

Correctness Issues:

Misspelled or incorrectly recorded data.

Null, ambiguous, or out-of-range data.

Data Quality:

Number Verification: Numbers must be verified and verified.

Date check: Date format must be followed and must be the same for all records.

Accuracy check

Data review

Null check

Duplicate Check:

1. Unique key must be validated, primary key and all other columns must be unique according to business needs and contain duplicate rows.

2. Check for duplicate values ​​in columns extracted from multiple columns in the source and combined into one column

3. Need to ensure no duplicates in multiple column combinations only within the target as requested by the customer

Date Validation:

Date values ​​are used in many areas of ETL development

1. The creation date of a row

2. Identify active datasets according to ETL development perspective

3. Identify active records according to business needs

4. Updates and inserts may be generated based on date values.

Complete Data Validation:

1. Validate the full records of the source and target tables minus the query with the best solution

2. You must specify the source minus the target and the target minus the source.

3. If the minus query returns a value, these are considered non-matching rows.

4. Lines must be matched between the source and target using the intersect statement.

5. The counts returned by intersect should match the individual counts in the source and target tables.

6. If the minus query returns rows and the number of intersections is less than the source count or target table, then you can assume that there are duplicate rows.