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.