Using Grid Variables in a Matillion ETL job to Apply Business Rules

In this blog, we will show you how to use grid variable to apply business rules in a Transformation job within Matillion ETL. It is often the case that business rules are hard-coded into an ETL meaning any change to the business rules requires an ETL developer to update the logic. Here we look at an alternative approach.

Using Grid Variables Apply Business Rules with Matillion ETL

In this example we look at product transactions where the product code and region it is sold will affect the amount of tax paid:

We have several variable rules at play.

1. The first character of the product code indicates whether the product sold is subject to a specific tax we will call “treat tax”.
2. Each region is subject to a different tax rate.
3. A discount of 10% is given on any products with a price exceeding 10.

Traditionally you would apply these rules in a case statement by hard coding them into a Transformation job.

In Matillion, however, you can set up and apply the rules in a dynamic way using Grid Variables.

1. Set up your Tables

The first step in this process is to set up two tables:

First, a Dummy Transactions to test the rules against
Second, a Business Rules with the logic behind the rules:

2. Build your Transformation Job to Apply Rules

The next Transformation job is used to apply the rules to the transactions:

The main logic in this is in the JOIN statement determining what rules apply to each product Transaction.

The output of the Transformation job is a list of all transactions with the associated rule or rules to apply:

3. Set up Grid Variables

Next, switch to your Orchestration job and set up the grid variables to store the rules in. Right click on the canvas and select Manage Grid Variables:

Create a new variable called business_rule_list. Set the ‘Behaviour’ to ‘Copied’ and add two text columns:

• rule_name
• amount

The Query Result to Grid Component can be used to write the results of the stg_business rules from the first Transformation Job into the Grid Variable.

This Grid Variable can then be iterated over in a Python Script to generate a CASE statement for each rule which can split the rules into separate columns with the discount or tax amount to be applied.

The resulting output gives all percentages to apply against every transaction which can then be used to calculate the total: