Logging logic

Matillion ETL
1

I’m looking for simple canned set of logging logic (job - pass/fail) to be implemented into every workflow? I’d like to
Save the daily job logs and possible send to S3, and maybe build a mechanism to consume later. Any ideas?

2

Hello @VVeda.

Thank you for your post, and welcome back to the Matillion Community!

To implement a reusable logging mechanism within Matillion ETL jobs for tracking job success/failure, storing logs daily, and optionally exporting them to S3 for analysis, you can follow these best practices:

Option 1: Logging job status in an audit table.

Create an audit (or job log) table in your data warehouse (e.g., Snowflake, Redshift, BigQuery). After each job or transformation, write a record to this table capturing key details such as job name, status (success/failure), start/end time, and row counts. This can be achieved by:

  • Defining variables in Matillion ETL to capture runtime information (e.g., status, start time, end time, row count). For more information, read Variables - Matillion Docs.
  • Using a Transformation Job with a Fixed Flow component to map these variables, and a Table Output component to insert them into your audit table. For more information, read Fixed Flow - Matillion Docs and Table Output - Matillion Docs .
  • Ensuring this logging step runs immediately after each job or component you wish to audit.

This method is reusable and can be applied to multiple jobs by calling the same audit logging transformation and passing the relevant variables each time. Here’s a simplified example using SQL:

– Example: Table structure for audit log
CREATE TABLE job_audit_log (
job_name VARCHAR,
status VARCHAR,
start_time TIMESTAMP,
end_time TIMESTAMP,
row_count INT,
log_date DATE DEFAULT CURRENT_DATE
);

Option 2: Scheduling and daily log storage.

To store logs daily, include a log date column (as shown above) and ensure each log entry is timestamped. You can schedule a Matillion ETL job to run daily, aggregating or exporting the previous day’s logs as needed.

Option 3: Exporting logs to S3.

If you want to export your audit logs to S3 for further analysis or archiving:

  • Use an Orchestration Job to extract the audit log data (e.g., with a Query or Extract component).

  • Use the S3 Load component to write the extracted data to an S3 bucket. For more information, read S3 Load - Matillion Docs.

  • Alternatively, you can automate file movement to S3 using external tools/scripts (e.g., incron and aws-cli) if you need to move files generated outside of Matillion ETL using incron to automatically copy data to S3, read Using incron to automatically copy data to S3 - Matillion Docs .

Option 4: Optional - event-driven or queue-based logging.

For more advanced or event-driven workflows, you can configure Matillion ETL to send job status messages (success/failure) to SQS, Pub/Sub, or Azure Queue Storage, which can then trigger further processing or logging jobs. For more information, read Manage SQS Configuration - Matillion Docs , Manage Pub-Sub configuration - Matillion Docs, and Azure Queue Message configuration - Matillion Docs.

So, to summarise:

  • Use an audit table and reusable transformation for logging.

  • Store logs with timestamps for daily tracking.

  • Export logs to S3 using Matillion components or external scripts.

  • Optionally, leverage queue-based triggers for advanced workflows.

I hope the above helps. Feel free to reach out if you need anymore assistance.

Kind regards,

Suzanne