How To Perform Testing Jobs In Matillion?

Author: Mayank Tiwari


This blog provides you with in-depth information on how to perform testing in Matillion and what prerequisites you need to have to perform the same.


Introduction

What is Testing?


Testing is finding out whether desired objectives are being met or not. It measures the overall quality, correctness, completeness, usability, and other attributes.


Prerequisites

  • You must be aware of the Matillion components. You can learn more about Matillion at the documentation of Matillion.

  • An understanding of Environment Variables before starting doing testing.

  • How to create an SNS topic in AWS?


Testing in Matillion

We can perform various types of testing based on our data and needs. We will discuss some here

  • Testing For Null Values

  • Testing For Negative Or Zero Values

  • Testing For Day,Month,Year Data

  • Testing For For Email Data

  • Testing For For Phone Data

  • Testing For For Strings Data

  • Testing For For Invalid Journey Data

  • Testing For Boolean Data


We shall discuss all the mentioned tests in detail.


Testing For Null Values

As we know, there can sometimes be when null data gets ingested, so that can be of no use. To avoid this, we are doing testing on null values. In this testing, we are checking for null values in our data while doing a CDI.


Step 1: Go to Components on the left side -> then go to Variables, in which there is a component called Query Result to Scalar.


See documentation for complete reference Query Result to Scalar Component



Drag that component on your worksheet. We are going to write a query and save the result in an environment variable that you have to create.


Step 2: Now click on the query result to scalar component and put the SQL query which selects all those records where Date is Current_Date() and any of the weather factors, such as maxtemp, mintemp, etc., is null. So for this component, we have the following parameters:

  • Name: Any name that makes sense. What you are doing here with that, we are taking NULL_DETECTOR.

  • SQL QUERY: SELECT listagg(DATE_TIME || ', ' || coalesce(City, ' '), '; ') AS NULL_FACTORS_LIST FROM (SELECT DATE_TIME,CITY FROM WEATHER_TABLE where DATE=CURRENT_DATE() AND (FACTORS IS NULL))

  • Scalar variable mapping: Now you have to map the result from the query above that is stored in NULL_FACTORS_LIST to an environment variable.



Step 3: Now drag and drop the if the component that is under the flow folder of components. In the component, we will check for NULL_CITY_DATE_LIST is blank or not and take necessary action.

If it has the following parameters:

  • Name: Any suitable name that you want and makes sense.

  • Condition: Check if the environment variable is blank or not.

  • Combine Conditions: AND


See documentation for complete reference. If Component



Step 4: Look for the SNS component and drag and drop it on the worksheet; we will notify you with whole information regarding null values. It has the following parameters:

  • Name: Any suitable name like a message component or send email

  • Aws region: Select an AWS region in which you have created an SNS topic.

  • Topic Name: give a suitable name for the topic.

  • Subject: Write a subject about your mail.

  • Message: Write a message you want to send and send the information.


See documentation for reference. SNS Component


Testing For Negative Or Zero Values

Here we are testing for negative or zero values that are invalid for our data.


Step 1: Go to components on the left side and drag and drop Query Result to Scalar; if component and SNS component

  • 1 - SNS Component

  • 1 - IF Component

  • 1 - Query Result to Scalar Component



Step 2: Now click on the query result to the scalar component and put the SQL query, which selects all those records where the value is less than negative or zero. So for this component, we have the following parameters:

  • Name : Any name that makes sense. What you are doing here with that, so here we are taking PHONE_NZ.

  • SQL QUERY : SELECT LISTAGG(CUSTOMER_ID, ', ') AS LIST_CURRENT_LOCATION_NZ_VALUE_CUSTOMER


FROM

(SELECT CUSTOMER_ID, CURRENT_LOCATION, SIGN(CURRENT_LOCATION)

AS

CURRENT_LOCATION_NEGATIVE_OR_ZERO_VALUE_CHECK FROM CUSTOMER

WHERE CURRENT_LOCATION_NEGATIVE_OR_ZERO_VALUE_CHECK = -1

OR

CURRENT_LOCATION_NEGATIVE_OR_ZERO_VALUE_CHECK = 0)


  • Scalar variable mapping : Now you have to map the result from the query above that is stored in LIST_PHONE_NZ_HOTEL_DATA to an environment variable.



Step 3: Now drag and drop the if the component that is under the flow folder of components. In the component, we will check for LIST_PHONE_NZ_VALUE_CUSTOMERis blank or not, and take necessary action. If it has the following parameters:

  • Name: Any suitable name that you want and makes sense.

  • Condition: Check if the environment variable is blank or not.

  • Combine Conditions: AND



Step 4: We will notify you with whole information regarding null values. If it has following parameters:

  • Name: Any suitable name like a message component or send email

  • Aws region: Select an AWS region in which you have created an SNS topic.

  • Topic Name: give a suitable name for the topic.

  • Subject: Write a subject about your mail.

  • Message: Write a message you want to send and send the information.




Testing For Day,Month,Year Data

Step 1: Go to components on the left side and drag and drop Query Result to Scalar; if component and SNS component

  • 1 - SNS Component

  • 1 - IF Component

  • 1 - Query Result to Scalar Component



Step 2: Now click on the query result to the scalar component and put the SQL query, which selects all those records where the calendar value is wrong. So for this component, we have the following parameters:

  • Name: Any name that makes sense. What you are doing here with that, so here we are taking TEST_YEAR_CITY_WEATHER.

  • Scalar variable mapping: Now you have to map the result from the query above that is stored in TEST_YEAR_CITY_WEATHER_DATA to an environment variable.


Step 3: Now drag and drop the if the component that is under the flow folder of components. In the component, we will check for TEST_YEAR_CITY_WEATHER_DATA is blank or not and take necessary action. If it has the following parameters:

  • Name: Any suitable name that you want and makes sense.

  • Condition: Check if the environment variable is blank or not.

  • Combine Conditions: AND



Step 4: We will notify you with whole information regarding null values. It has the following parameters:

  • Name: Any suitable name like a message component or send email

  • Aws region: Select an AWS region in which you have created an SNS topic.

  • Topic Name: give a suitable name for the topic.

  • Subject: Write a subject about your mail.

  • Message: Write a message you want to send and send the information.



Testing For For Email & Phone Data

Step 1: Go to components on the left side and drag and drop Query Result to Scalar; if component and SNS component

  • 1 - SNS Component

  • 1 - IF Component

  • 1 - Query Result to Scalar Component



Step 2: Now click on the query result to the scalar component and put the SQL query, which selects all those records where the calendar value is wrong. For this component, we have the following parameters:


  • Name: Any name that makes sense. What you are doing here with that, so here we are taking EMAIL_CHECK.

  • SQL QUERY:

SELECT LISTAGG(CUSTOMER_ID, ', ')

AS

LIST_CUST_ID_CHECK_RATE_SIGN, LISTAGG(EMAIL_ID, ', ')

AS

LIST_EMAIL_CHECK_RATE_SIGN FROM CUSTOMER

WHERE EMAIL_ID NOT LIKE '%_@_%._%'

  • Scalar variable mapping: Now you have to map the result from the query above that is stored in LIST_EMAIL_CHECKTYPE_DATAto an environment variable.



Step 3: Now drag and drop the if the component that is under the flow folder of components. If the component is, we will check for LIST_EMAIL_CHECKTYPE_DATA is blank or not and take necessary action.


If it has the following parameters:

  • Name: Any suitable name that you want and makes sense.

  • Condition: Check if the environment variable is blank or not.

  • Combine Conditions: AND



Step 4: We will notify with whole information regarding null values. It has the following parameters:

  • Name: Any suitable name like a message component or send email

  • AWS region: Select an AWS region in which you have created an SNS topic.

  • Topic Name: give a suitable name for the topic.

  • Subject: Write a subject about your mail.

  • Message: Write a message you want to send and send the information.



Testing For For Strings Data

Step 1: Go to components on the left side and drag and drop Query Result to Scalar; if the component and SNS component

  • 1 - SNS Component

  • 1 - IF Component

  • 1 - Query Result to Scalar Component



Step 2: Now click on the query result to the scalar component and put the SQL query, which selects all those records where the calendar value is wrong. So for this component, we have following parameters : -


  • Name: Any name that makes sense what you are doing here with that, so here we are taking FIRSTNAME_CHECKTYPE.

  • SQL QUERY:

SELECT LISTAGG(CUSTOMER_ID, ', ') AS LIST_FIRSTNAME_CHECKTYPE FROM

(SELECT CUSTOMER_ID, FIRST_NAME FROM CUSTOMER

WHERE FIRST_NAME LIKE '%1%' OR FIRST_NAME LIKE '%2%' OR FIRST_NAME LIKE '%3%' OR FIRST_NAME LIKE '%4%' OR FIRST_NAME LIKE '%5%' OR FIRST_NAME LIKE '%6%' OR FIRST_NAME LIKE '%7%' OR FIRST_NAME LIKE '%8%' OR FIRST_NAME LIKE '%9%' OR FIRST_NAME LIKE '%0%')


  • Scalar variable mapping: Now you have to map the result from the query above that is stored in LIST_FIRSTNAME_CHECKTYPE_DATA to an environment variable.



Step 3: Now drag and drop the if the component that is under the flow folder of components. In if the component, we will check for LIST_FIRSTNAME_CHECKTYPE_DATA is blank or not and take necessary action. If it has the following parameters:

  • Name : Any suitable name that you want and makes sense.

  • Condition : Check if the environment variable is blank or not.

  • Combine Conditions : AND



Step 4 : We will notify with the whole information regarding null values.It has following parameters:

  • Name: Any suitable name like a message component or send email

  • Aws region: Select an aws region in which you have created an SNS topic.

  • Topic Name: give a suitable name for the topic.

  • Subject: Write a subject about your mail.

  • Message: Write a message you want to send and send the information.



Testing For For Invalid Journey Data

Step 1 : Go to components on the left side and drag and drop Query Result to Scalar, if component and SNS component

  • 1 - SNS Component

  • 1 - IF Component

  • 1 - Query Result to Scalar Component



Step 2: Now click on the query result to the scalar component and put the SQL query, which selects all those records where the calendar value is wrong. So for this component, we have following parameters:

  • Name: Any name that makes sense. What you are doing here with that, so here we are taking CUSTOMER_LOCATION_INVALID.

  • SQL QUERY:

SELECT LISTAGG(BOOKING_ID, ', ')

AS BOOKING_JOURNEY_INVALID_BUS

FROM

(SELECT BOOKING_ID,FROM_DATE,TO_DATE FROM DIM_BOOKING WHERE FROM_DATE > TO_DATE AND FROM_DATE >=CURRENT_DATE()

AND

TYPE_OF_BOOKING='BUS'

AND

TO_DATE(RECORD_CREATED_TIMESTAMP)=CURRENT_DATE())

  • Scalar variable mapping : Now you have to map the result from the query above that is stored in CURRENT_LOCATION_INVALID_DATA to an environment variable.



Step 3: Now drag and drop the if the component that is under the flow folder of components.In if component, we will check for CURRENT_LOCATION_INVALID_DATA is blank or not and take necessary action.

If has following parameters:

  • Name: Any suitable name that you want and makes sense.

  • Condition: Check if the environment variable is blank or not.

  • Combine Conditions: AND



Step 4: We will notify with whole information regarding null values.It has following parameters : -

  • Name: Any suitable name like a message component or send email

  • Aws region: Select an aws region in which you have created an SNS topic.

  • Topic Name: give a suitable name for the topic.

  • Subject: Write a subject about your mail.



Testing For Boolean Data

Step 1: Go to components on the left side and drag and drop Query Result to Scalar, if component and SNS component

  • 1 - SNS Component

  • 1 - IF Component

  • 1 - Query Result to Scalar Component



Step 2: Now click on the query result to the scalar component and put the SQL query, which selects all those records where the calendar value is wrong. So for this component, we have following parameters:

  • Name: Any name that makes sense what you are doing here with that,so here we are taking Check Boolean Datatype.

  • SQL QUERY:

SELECT LISTAGG(CUSTOMER_ID, ', ') AS LIST_BOOLEAN_CHECKTYPE FROM

(SELECT CUSTOMER_ID FROM CUSTOMER WHERE SOLO_TRAVELLER NOT IN (1, 0))

  • Scalar variable mapping: Now you have to map the result from the query above that is stored in LIST_BOOLEAN_CHECKTYPE_DATA to an environment variable.



Step 3: Now drag and drop the if the component that is under the flow folder of components. In the component we will check for LIST_BOOLEAN_CHECKTYPE_DATA is blank or not and take necessary action. If it has the following parameters:

  • Name: Any suitable name that you want and makes sense.

  • Condition: Check if the environment variable is blank or not.

  • Combine Conditions: AND



Step 4 : We will notify with the whole information regarding null values. It has the following parameters:

  • Name: Any suitable name like a message component or send email

  • Aws region: Select an aws region in which you have created a SNS topic.

  • Topic Name: give a suitable name for the topic.

  • Subject: Write a subject about your mail.



Step 5: Drag and attach the Job success component in last.



Conclusion

This gives you a basic idea of how to perform testing in Matillion and how it helps in maintaining clean data by performing required testing.


References
  1. Matillion Documentation

  2. Perform Auditing Jobs in Matillion

  3. Creating SNS topic in AWS


31 views0 comments

Recent Posts

See All