Matillion ETL Shared Job

Author: phData
Date Posted: Dec 4, 2023
Last Modified: Dec 13, 2023

Automated Testing Toolkit

Automated Snowflake testing from a Matillion ETL orchestration job.

This shared job, by phData, can be unpacked to set up automated testing from within Matillion ETL. The jobs will automate the execution of SQL tests against specific objects in your data warehouse and store the results in a table. Testing can be scheduled or implemented as part of your workflows.

Automated Testing Toolkit

Please reach out to the phData team for help or if you have any questions.

Parameters

This shared job has no parameters. Unpack it and set environment variables to proceed.

Prerequisites

After unpacking the shared job, the first step is to define the following environment variables with appropriate values for your project:

Environment VariableDescription
env_AT_databaseThe database where your test results table will be stored
env_AT_schemaThe schema where your test results table will be stored
env_AT_table_nameThe table name of your test results table (you don’t need to have it created yet at this point)

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. That will use the environment variables you defined before to create the table. That job will run just this time, 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 that, 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. That 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 ; of the end of the SQL script
  • Have all your SQL script in a single line per test (no line breaks)
  • 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

Example

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"

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.

You can find the last results of your tests saved in a table at the end of the process.


Downloads

Licensed under: Matillion Free Subscription License

Installation Instructions

How to Install a Matillion ETL Shared Job