Populating an audit table
Overview: 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 the S3 Load component, 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.
Defining Variables
We will need to define a number of variables to temporarily store the results of the S3 Load. The variables are given default values; however, if these values ever appear in the audit table, it means they were not exported prior to being loaded, and therefore something is not configured correctly.
The variables must have the 'Copied' Scope. If you reuse the same variable names across multiple components that may be required to run concurrently, this Scope setting will ensure that each component uses its own copy of the variable.
On the S3 Load component, click the Export tab, and map the runtime values from the component into the audit variables.
Please 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 update tables, and orchestration components that interact with another service) can export this runtime information. Components used for flow (And, Or, Run Orchestration, Run Transformation) can not.
Setup a Reusable Transformation Job to Populate the Audit Table
In the below image is a Fixed Flow component connected to a Table Output component.
Fixed Flow defines a column for each of the audit variables, and populates them with the variable values:
Since the Status may be blank—and accounting for the component disallowing blank values—it is necessary to ensure that the Status has a value.
${audit_status ? audit_status : "None"}
Table Output maps this one row into an audit table. This could be created with a Create Table component, for example:
Running 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.
