How many times have you faced a situation where you performed countless tests to guarantee that your Matillion development was working as expected, but at some point, after you completed the tests, you needed to make some new changes in the workflows?
Due to the changes you made, you now need to redo all of the tests. This can sometimes be a bit frustrating and result in a significant amount of time spent on rework.
To help solve this problem, phData created a free tool called Automated Testing.
In this blog, you’ll learn all about our Automated Testing tool including how to leverage it to automatically rerun any number of SQL scripts you’ve written in Matillion to ensure your workflows are working properly.
Additionally, we’ll show you how to find the last results of your tests saved in a table at the end of the process. The result of that automation will be a table similar to this:
Before we dive into the tool, it’s important to define it and give you a little background on Matillion Shared Jobs.
What is The Automated Testing Tool From phData?
The Automated Testing tool gives you the ability to run as many SQL tests as you want in Matillion to ensure the quality of your data. It can be used more reactively, running ad-hoc or even scheduled but with no automated actions. You can also fit it into your current process, defining whether or not the subsequent jobs can run based on your test results.
What Are Matillion Shared jobs?
Matillion Shared Jobs are customized components that are developed by the Matillion community’s users but approved by the Matillion team. These jobs are designed to solve various scenarios that many Matillion users face, such as extracting data from a source system, transforming it to meet specific requirements, and loading it into a target system.
Shared Jobs can be easily accessed within the Matillion user interface once you have them imported to your instance and provide a starting point for building your own data integration pipelines and data transformations. They also allow users to learn best practices for using Matillion by sharing solutions for common data integration challenges.
Matillion provides a portal called “Matillion Exchange” where you can find all the Shared Jobs created by users and approved by the Matillion team.
How To Get The Automated Testing Shared Job?
The Automated Testing Shared Job is a component built by phData and approved by the Matillion team. It’s available in the Matillion Exchange portal, which you can download for free. Be sure to add the new Shared Job to your project in the Matillion instance after you’ve downloaded it.
To import it, download the “.melt” file, then click on “Project” and find the “Manage shared jobs” option. That will pop up a new window where you’ll have the option to import the Shared Job. Once you’ve completed these steps, you’re going to find the tool in the “Shared Jobs” section, right below the “Components” section.
How Do You Use That Shared Job?
Find the automation in the “Shared Jobs” section of any Orchestration job in the project where you imported it. That will be in the “phData Toolkit” group in the “User-defined” section, named “Automated Testing”.
To use it properly, click on the “Automated Testing” tool with the right button and select “unpack it”. This will add it to your project with the five following jobs:
Orchestration Jobs
- Master_Automated_Tests
- Automated_Tests_Caller
- Truncate Results Table
- DDL Results table
Transformation Jobs
- Run_Automated_Tests
How To Use The Automated Testing Tool
Now that you have the Automated Testing tool added to your project, let’s configure it so you can start using it. Feel free to organize the unpacked workflows as you prefer in the folders that are in your project.
The first step is to define the following environment variables with appropriate values for your project:
Note: Remember, the user role defined in the environment used by the Matillion instance needs to have the proper permissions to access the database and schemas defined in the variables, as well as permissions to create, truncate, and write tables.
Once the variables are defined, it is time to create the table to store the results of your automated tests. To do it, you should run the “DDL results table” job. This will use the environment variables you defined before to create the table. That job will run just once and it’s not necessary to run it again unless you want to make changes to the structure of that table later.
Now that you have everything set up to use the automation, it is time to define which tests you want to include in the test routine. To do so, open up the “Automated_Tests_Caller”. There, you will interact with the “Values Grid” configuration of the “Append_to_Grid” component called “Feed_Automated_Tests_Grid.”
This will pop up a window where you can add all the tests you want to do as well as the complementary information about the tests.
The queries you add in the “SQL Query” column of that grid will be the ones to run automatically. To run properly, here are the considerations you should follow when adding scripts:
- Remove the “;” at the end of the SQL script
- Have all your SQL script in a single line per test (no break lines)
- Do not include any comments in your script
- Add a “CASE WHEN” statement to test your script result to retrieve a “PASS” or “FAIL” response in a “STATUS” column, like in the SQL script example below:
Goal:
Test to see if there are any records added to the table.
SQL script:
SELECT
(CASE WHEN COUNT(*)>0 THEN ‘PASS’ ELSE ‘FAIL’ END) AS “STATUS”
FROM “training_airports”
Note: Another good way to feed it multiple tests at once is to have this same table structure in a spreadsheet, copy it from there, and paste it in here using the “Text Mode” option.
Once you’ve configured all of the tests in the grid, all that remains is to run the “Master_Automated_Tests”. When that job is triggered, the “Truncate Results Table” will run to clean up all the old test results in the results table, and then start the “Automated_Tests_Caller” job to run all your tests.
Would you Like to Change the Results Table?
No problem, that results table layout is just a recommended starting point for you, but you have all the flexibility to change it to the format that fits your needs.
Step 1
The first step to accomplishing the changes you want to make in the table layout is to go to the “DDL Results table” and change it to accommodate the same changes you desire. Once you change the DDL script for the new table layout, you should run that workflow to apply your changes.
Step 2
The second step is to open the “Automated_Tests_Caller” and edit the job variables so that they have one variable per column in the DDL script. The changes you make to the job variables for that job must also be applied to the “Run_Automated_Tests” job.
Step 3
The third step is to open the grid variables in the “Automated_Tests_Caller” and edit the values of the “VARIABLES_MAPPING” grid variable. In this part, you should have one line per column in your DDL script, adding the job variable name you created for the column in the “VARIABLES” column and adding it as a variable call in the “VALUE” column (i.e.: VARIABLES = new_variable | VALUE = ${new_variable} ).
Step 4
The next step is to open the “Run_Automated_Tests” job. In that job, you should open the calculator component called “Bring_Variable_Values” and have a field per column in the job variables previously defined. Each field has to be named with the respective column name of the DDL script, and as a value, put the job variable between double dollars (i.e.: $$${new_variable}$$). The double dollars prevent any problems reading the values of your variables if they contain any single quotes.
Step 5
Now the last step is still in the “Run_Automated_Tests” job. After revalidating the job, find the table output component and use the “column mapping” configuration to remap the columns in the output.
Once you finish all the steps, you’re good to run your automated tests with the new table results layout that fits your needs.
All the steps listed here will also be found in the jobs you get from the Shared Job you have downloaded. If you have any questions about how to use or adapt the job, please contact our team. We’re happy to help!
Conclusion
In this article, we covered how the phData Automated Testing tool can have your SQL test scripts automated to run at any point in your processes. Our Automated Testing tool is just one of several others that will be released soon. Stay tuned for more!
Looking for more Matillion assistance? phData excels at assisting organizations in achieving success with Matillion. Contact us today for help, advice, and actionable strategies.
