Populating an Audit Table

Populating an Audit Table

Auditing Runtime Data

Each Matillion ETL component can make runtime information available.

In this example, we are interested in how long an S3 load takes to complete and we want to demonstrate how to use this information to populate an audit table. Note, however, that this example is not unique to S3 Load, and the method is generally applicable to many components across all platforms.

For our given component, we want to capture the start time, end time, and row count every time the job runs. The orchestration flow adds a generic transformation job that populates a single record into an audit table. The setup of the AuditRecord job is shown later.

Set Up some Variables

We’ll need some variables defined to temporarily store the results of the S3 load. These variables are given some default values - but if these values ever appear in the audit table, it means they were not exported prior to being loaded and therefore something is not set up correctly.

It is important to ensure that these variables have the 'Copied' Scope. If you re-use the same variable names in multiple components that may run concurrently, this Scope setting ensures each component use its own copy of the variable.

On the S3 Load component, switch to the “Export” tab and map the runtime values from the component into the audit variables.

Note: Many, but not all, components can export runtime information into variables, as shown above. As a general rule, components that actually perform actions (Transformation components that load or updates tables, Orchestration components that interact with another service) can export this runtime information. Components used for flow (And, Or, Run Orchestration, Run Transformation) do not provide useful information.

Set-up a Reusable Transformation Job to Populate the Audit Table

This is a “Fixed Flow” followed by a “Table Output”. The fixed flow defines a column for each of the audit variables and populates them with the variable values:

Since the Status may be blank, but the component does not allow blank values, it is necessary to ensure that the Status has a value:

${audit_status ? audit_status : "None"}

The table output maps this one row into an audit table. This could be created with a Create Table component, for example:

Run the Transformation Immediately After the S3 Load

Since the same variables will be reused in multiple places, it is best to call the Transformation Job immediately after exporting the values.

Sample Jobs

The jobs used in this example are attached; however, the variables will need to exist in your project first.

The jobs used in this article can be imported from here.

A Shared Job for the processes in this article is also available here.