Blog

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:Grid Variables in a Matillion ETL job to Apply Business Rules

 

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 Matillon, however, you can set up and apply the rules in a dynamic way using Grid Variables.

 

Grid Variables in a Matillion ETL job to Apply Business Rules - Grid Variable

 

Grid Variables in a Matillion ETL job to Apply Business Rules - create table

 

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:

 

Grid Variables in a Matillion ETL job to Apply Business Rules - Business Rule Logic

 

2. Build your Transformation Job to Apply Rules

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

 

Grid Variables in a Matillion ETL job to Apply Business Rules - transformation job

 

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:

 

Grid Variables in a Matillion ETL job to Apply Business Rules - Transformation output

 

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:

 

Grid Variables in a Matillion ETL job to Apply Business Rules - Manage Grid Variables

 

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

  • rule_name
  • amount

 

Grid Variables in a Matillion ETL job to Apply Business Rules - update grid variable

 

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.

 

Grid Variables in a Matillion ETL job to Apply Business Rules - iterate over python

 

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

 

Grid Variables in a Matillion ETL job to Apply Business Rules - final output

 

For further details, check out our Technical Articles (below in Useful Links) for a full step by step guide and to see the sample generated SQL.

 

Useful Links

Amazon Redshift – Grid Variables
BigQuery – Grid Variables
Snowflake – Grid Variables

 

Arrange a free demo of Matillion ETL to see how it works in real-time.