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.

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:

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

Download METL-sf-1.68.3-automated-testing-toolkit.melt

• Target : Snowflake
• Version : 1.68.3 or higher

Installation instructions

Author: phData