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.
This shared job has no parameters. Unpack it and set environment variables to proceed.
After unpacking the shared job, the first step is to define the following environment variables with appropriate values for your project:
|The database where your test results table will be stored
|The schema where your test results table will be stored
|The 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 WHENstatement to test your script result to retrieve a
FAILresponse in a
Goal: Test to see if there are any records added to the table.
(CASE WHEN COUNT(*)>0 THEN 'PASS' ELSE 'FAIL' END) AS "STATUS"
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.
- Target : Snowflake
- Version : 1.68.3 or higher
Installation instructionsHow to Install a Matillion ETL Shared Job