Author: Rashi RKG & Vijaya Sharma
Introduction:
An Audit log displays all the information about a job in a Matillion instance. Any changes made by a user in a job are recorded in the log. Information about runtime, success/failure, and message can be made available by each Matillion component.
In our use case, we are interested in how long a job takes to complete, whether it's successful or failed, if failed, what's the error message, et cetera; and we want to demonstrate how to use this information to populate an audit table in the Snowflake data warehouse.
For our given job, we want to capture the start time, end time, duration, status, message, job id, job name, and row count every time the job runs. The orchestration flow adds a generic Orchestration Job that populates a single record into an audit table. The setting up of the AuditRecord job is shown later.
Assumptions :
Snowflake's connection with Matillion is already established.
Approach 1:
Create a generic Orchestration Job for Auditing
1. Right-click on the Project and select Add Orchestration Job from the drop-down menu.
2. Defining Job Variables: We will need to define a number of variables to temporarily store the results of the job. The variables are given default values; however, if these values never appear in the audit table, it means they were not exported prior to being loaded, and therefore something is not configured correctly.
3. Create an Audit table in Snowflake like the one below.
4. Use the job variables in an insert script to AUDIT table:
Insert into core.audit_log (job_name, start_time, end_time, duration ,row_count,
status,message,job_id)
Values ( ' ${task_name}',' ${start_time}',' ${end_time}','${duration}',' ${row_count}','
${status}',' ${message}','${task_id}')
5. Running the Orchestration Immediately After the S3 Load in this job.
To Implement Audit logging in other orchestration jobs:
1. Click on Manage Job variables in the Job you want to implement audit logging for.
2. Export Matillion Component metadata and map them to the job variables.
Component Export :
Every orchestration component possesses an export tab that stores some component metadata into variables. Some of these components are standard attributes that are common across all components; these are:
Component
Started At
Completed At
Duration
Message
Status
Row Count
And some components have metadata that is very specific. For example, there is an attribute called “Table Recreated” in Create Table Component. Similarly, “Time Taken To Load” and “Time Taken To Stage” attributes of Data Stager Components.
We need to store those values in some variables for later use. So that there is no need to create a set of variables for every job, environment variables that will be accessible across all jobs can be used, and since we need to access the values in a different branch (line originating at source component), we have to select the shared behavior.
3. Set Scalar variables for the highlighted components
4. Run the job and validate in Snowflake
Approach 2:
Setting up a Reusable Transformation Job to Populate the Audit Table
The below image is of a Fixed Flow component that is connected to a Table Output component.
Fixed Flow defines a column for every audit variable and populates them with the variable values:
Since the Status may be blank—but accounting for the component not allowing blank values—it is necessary to confirm that the Status is not empty.
${audit_status ? audit_status : "None"}
Running the Transformation Immediately After the S3 Load
Since the same variables will be used again in multiple places, it is beneficial to call the Transformation Job immediately after the values are exported.
Conclusion
Audit logging is a very useful concept provided by Matillion to keep track of user activities for any Orchestration Job. Using this feature, we can identify the errors in our job and rectify them easily. These logs help with security as they provide information on all activities related to a job. Using any of the above two approaches, one can implement Audit logging in their Orchestration Jobs.
Reference
https://documentation.matillion.com/docs/2852125#:~:text=Overview,query%20components%20or%20integrations%2C%20interchangeably.