Database verifications for ETL Testing

What is ETL Testing?

ETL testing ensures that the transfer of data from heterogeneous sources to the central data warehouse occurs with strict adherence to transformation rules and is in compliance with all validity checks.

• Involves validating large quantities of data across various forms of data sources.
• Source and Destination data sources are usually heterogeneous.
• Typically require thousands of verification points in test cases.

ACCELQ supports ETL test automation with various capabilities including bulk file-level validations, database comparisons, complex transformation logic, etc. This article describes the various capabilities available for database comparisons and validations.

ACCELQ supports the comparison of data across multiple databases like Postgres, MySQL, Snowflake, etc. You can find information on general database-related automation along with a full listing of the supported databases in this article.

Table Metadata Verification

Using this command, you can compare the metadata of two different tables or views (either on the same DB connection or different ones).

Compare metadata of two database tables or views

This command can be used to compare the metadata between two different tables or views belonging to the source and destination data sources.  You can compare primary keys, column names, data types, and indexes separately or all the metadata together in one statement.

Connect to the source and destination databases using the DB connect command, and provide the session names for the metadata comparison command. 

Verify metadata of database table or view

This command verifies the metadata of a table or view against expected values. You can specify the expected metadata as a JSON array, and includes the column names and data types, primary keys, or indexes. Each option expects a specific format of JSON input array as shown below.

• • • For column names and data types

• For primary keys

• For Indexes

Here is a sample statement for metadata verification.

Data Verification

These commands are used for verifying the data between different data sources.

Compare database records

You can compare two sets of database records from the same or separate databases with this command. You can also provide a SQL query as the source/destination, and compare its results against the result of another query or database table.

You can control how many records get compared between the two sources:

• count from the first source
• count from the second source
• minimum of the first or second source
• given row count

Also, when comparing different fields, you can provide a mapping of column names between the first and second sources. It is not necessary that the column names of both sources are the same.

Get row count of database table or view

Gets the row count of a table or a view. Useful for performing validations in a loop.

Get aggregate column value

Execute aggregate functions like AVG, SUM, COUNT, MIN, and MAX on a table or a view. Users can also provide existing custom aggregate functions to be executed.

Result Reporting

ETL test results are presented in a comprehensive and user-friendly format in the centrally accessible reports.

Error data is highlighted and a facility to navigate across the errors is also provided. The detailed error information is displayed when you click on a cell.

Note: By default, the first 1000 errors are presented in the report to manage the navigation and loading time.