Using grid variables to apply business rules in a transformation job

Overview

When Transforming data, it is often the case that business rules need to be applied to data to do different calculations on different rows of data depending on certain criteria. Traditionally these business rules would be hard coded as part of the ETL and any changes made to the business logic would mean the ETL needs to be changed. However Grid Variables can be used in Matillion to apply the rules dynamically.

Taking some example product transactions where the product code and region sold can affect the tax paid:

In this example the first character of the product code is used to indicate whether the product sold is subject to a “treat tax” and each region has a separate tax rate. In addition, a discount of 10% is given on any products with a price of over 10.
 

Traditionally these rules would be applied in a case statement which are hard coded into a transformation job. However Matillon can use Grid Variables to set up and apply the rules in a dynamic way.

 

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

1. Dummy Transactions to test the rules against - see first screenshot

2. Business Rules with the logic behind the rules:

These rules are stored in a table in the database so can be modified by external users if required.

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

The main logic in this is in the join statement which is determining which rule to apply to which Transaction:

This logic will map the correct region, product, price to the correct rule and if there’s no rule for the applicable region, product or price by mapping the “null” to “true” we will keep the record in the table.

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

Back in the Main Orchestration job:

Next set up the grid variables to use to store these rules in. Right click on the canvas and select Manage Grid Variables:

  

Create a new variable called business_rule_list with behaviour Copied and 2 text columns:

• rule_name

• amount

Give some dummy default values:

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:

 

An example of this SQL generated is:

 

     CASE when rule_name like '%price_over_10_discount_RULE%'
     then -10.00
     else 0
     end as price_over_10_discount_RULE
,
     CASE when rule_name like '%standard_tax_E_RULE%'
     then 10.00
     else 0
     end as standard_tax_E_RULE
,
     CASE when rule_name like '%treat_tax_E_RULE%'
     then 20.00
     else 0
     end as treat_tax_E_RULE
,
     CASE when rule_name like '%all_tax_S_RULE%'
     then 20.00
     else 0
     end as all_tax_S_RULE
,
     CASE when rule_name like '%standard_tax_N_RULE%'
     then 15.00
     else 0
     end as standard_tax_N_RULE
,
     CASE when rule_name like '%treat_tax_N_RULE%'
     then 50.00
     else 0
     end as treat_tax_N_RULE

This CASE statement is used in the final Transformation job to apply to the Transactions table:

The variable containing the python generated SQL is then referenced in the SQL component to apply the required logic: 

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