Blog

How to use Matillion ETL Grid Variables to Incrementally Load Insurance Claim Data

This blog describes how to build a single, generic method for data transformation (the T of ELT). You would use it to apply the newly-loaded changed data into its permanent home in a replicated table in the data warehouse.

One of the first things you’ll want to do with Matillion is copy data out from source systems into the target data warehouse. Every source system usually contains multiple tables, each one having a different set of columns.

Every source system has its own mechanism for data extraction and load (the E and L of ELT). It might be JDBC, or API-based, or maybe it’s a SaaS system with a specific Matillion connector. You would use this mechanism periodically to bring changed data into the data warehouse staging area, by asking for data updated after the current high water mark. This is shown as steps 1-5 in the below diagram.

 

Matillion ETL Grid Variables to Incrementally Load - Diagram

 

The Insurance source system

We’ll use an insurance claims management system as the example.

The data model is fairly simple, with 12 related tables. The diagram below shows 7 of them as the data flows from source system to target data warehouse.

  • Steps 1-5 perform the Change Data Capture Extract and Load.
  • Step 6 performs the Transform step which merges the changed data into the permanent schema containing a replicated copy of every source table.

 

Matillion ETL Grid Variables to Incrementally Load - Source System

 

Change Data Capture

The main driver for Matillion’s generic incremental loading is a loop over the tables to replicate.

 

Matillion ETL Grid Variables to Incrementally Load - Change data capture

 

You could use a Fixed Iterator for this, as shown above. Alternatively, you could make it data-driven by using a Table Iterator to loop through a list of table names. The iterator passes the table name into a Job Variable owned by the Generic Incremental job.

The Generic Incremental job looks like this:

 

Matillion ETL Grid Variables to Incrementally Load - Generic Incremental Load

 

We won’t dive into the details of the Delta Load in this blog, because this transformation technique applies to any source system and any method of Extraction and Load. Instead, we’ll pick up the trail after steps 1-5 have been completed, and every STG_ table has been loaded with just the records that have been changed at the source.

The first step in making the transformations dynamic is to capture the runtime column names. To store the list of names, the job declares a Grid Variable.

 

Matillion ETL Grid Variables to Incrementally Load - Manage Grid Variable

 

This grid variable is given a name: g_columns, and it contains one column called (confusingly!) name.

 

Matillion ETL Grid Variables to Incrementally Load - Name Grid Variable

 

Matillion’s Table Metadata To Grid component is used to populate this grid variable with the column names of a table.

 

Matillion ETL Grid Variables to Incrementally Load - Table Metadata to Grid

 

Now at runtime, the g_columns grid variable will have a name column containing a list of all the column names in whichever STG_ table it’s currently dealing with.

This grid variable is passed as a parameter into the generic Transformation job named Merge to Target.

 

Matillion ETL Grid Variables to Incrementally Load - Merge to target transformation job

 

Generic Transformation

This is quite a simple Transformation Job, containing only a Table Input and a Table Update.

 

Matillion ETL Grid Variables to Incrementally Load - generic transformation

 

Its purpose is to apply the changed records in every source table to their respective target table in the Data Warehouse.

To make the Table Input component dynamic:

  • It selects from a variable-driven Table Name
  • The Column Names are not hardcoded, rather it uses the grid variable. This is another Grid Variable, declared at job level, identical to the one in the Generic Incremental job

 

Matillion ETL Grid Variables to Incrementally Load - Table Input

 

Similarly, to make the Table Update component dynamic:

  • The Target Table Name is a variable
  • The Column Mapping uses g_columns grid variable again, to map all the same-named columns together

 

Matillion ETL Grid Variables to Incrementally Load - Table Update

 

In the Column Mapping editor, the name column is used both as the Input Column Name and the Output Column Name.

 

Matillion ETL Grid Variables to Incrementally Load - Edit properties

 

Note that the first time you create the Column Mapping you’ll need at least one valid column name as a default value in the grid variable, otherwise Matillion will reject the mapping. After configuring the component you can remove this default from the grid variable.

In this example, every table has a primary key column named “id” which is used as the Unique Keys property. Other options are possible, including making the Unique Keys another grid variable.

Advanced options

We’ve looked at the simple case where the source and target tables have an identical schema. Matillion’s Extract and Load components typically drop and recreate the staging tables every time they run, so as to match the source schema. This means you may need to manipulate grid variables at runtime, perhaps to remove a column that does not exist in the Data Warehouse target system.

Grid variable manipulation is done in Matillion’s Python Script component, running in Jython mode.

To acquire a grid variable you use context.getGridVariable:

gv = context.getGridVariable('g_columns')

If you wanted to remove a value named ‘tbc’ then you could do something like this:

try:
gv.remove([u'tbc'])
except:
print "Could not remove column"

You do then need to update the real grid variable from the modified Python value:

context.updateGridVariable('g_columns', gv)

You could add value in a very similar way.

Summary

Shown you how to use Matillion’s parameter subsystem to manipulate and use column lists at runtime.

This blog article has explored how to take advantage of Matillion’s Grid Variable subsystem to create a generic data transformation job. You can schedule this job as part of a generic change data capture process, as shown in this insurance schema example.

One single parameterized transformation job is able to take care of incrementally loading 12 different tables.

Matillion ETL Grid Variables to Incrementally Load - result

 

 

 

Want to see Matillion ETL in action? Request a free 1hr demo today!