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, and 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 the 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 max temp, min temp, 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 the documentation for a 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 the 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 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 Day, Month, and 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 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 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 the 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. If the component is, 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 you 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 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 the 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. If the component is, we will check for CURRENT_LOCATION_INVALID_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.
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 the 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 you 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 an 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 the 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.