Assert Unit Test Components: Validating Objects in Matillion ETL

Based on Matillion ETL user feedback, as part of version 1.46 we have released some new unit testing components. These components can be used to validate objects created in your target data warehouse before continuing your Matillion ETL jobs. By utilizing the Assert unit testing components, Matillion ETL users can develop and verify jobs, reducing the development life cycle timeframe. Here we will look at the new components available and how they can be used to simplify your Orchestration and Transformation Jobs.

Assert View

Assert View is a new Transformation component that can be used to verify that the transformation is as you would expect at a particular point of the job. The component can verify:

• The metadata
• The row count, either an exact value or a range
• All or some values in the transformation

Here is an example of the Assert View component in use. It is applied part way through a Transformation Job to ensure that the results of the join are as expected. Here it is verifying the metadata as a result of the transformation and that the row count is correct:

Now changing the metadata and rerunning gives an example of the sort of error you can receive, which helps identify exactly what’s different in the transformation from what you were expecting:

Multiple Assert Views could even be used on the same Transformation Job to validate different parts of the transformation.

Since Matillion ETL exists within your cloud environment, it can check the data before you even run the job, which can save you time and money. 

Assert Table and Assert External Table

These are two new Orchestration components that validate Tables and External Tables.

These two components allow users to check:

• That the table exists in a given db and schema
• What the metadata for that table looks like
• The row count, either an exact value or a range

One example where you would use these components when loading data from an external source using one of Matillion ETL’s many load connecters. With an Assert Table component,  you can validate the table created from the source by checking that the table exists, the metadata is correct, and data is available in the table as expected. This can be done before you run Transformation Job that  may fail, or, worse, succeed with the wrong results, if any of the above information is not available or correct.

The error messages from these components will detail exactly why the component has failed, making debugging and fixes really simple to identify and remedy. 

In this example, there is staging data from Google Analytics.  The Assert Table component is verifying that the metadata is as expected and there is data in the table, that is, the row count is greater than 1:

Here is an example of the Assert External Table trying to Assert on an external table that  doesn’t exist:

Assert Scalar Variables

Assert Scalar Variables can be used to verify that a variable value is as expected at a particular point in an Orchestration Job. The component can identify whether the variable is wrong and indicate a failure. Additionally, using the Assert Scalar Variable component can determine whether the job should continue or not. For example, you can use this component to validate that the variables have been correctly passed down from a parent job before running the child processes.

Here’s an example of the Assert Scalar Variables component validating three variable values, two of which are correct and one is incorrect. 

Below is an example of when all values are correct:

Validate objects and spot errors early 

The Assert Unit Test components are a useful additional tool to verify jobs as you are building them and to provide a way to highlight errors earlier in the job run.

To learn more about other new features and functionalities in Matillion ETL version 1.46, check out our latest release blog.   

The post Assert Unit Test Components: Validating Objects in Matillion ETL appeared first on Matillion.