How can you judge whether an ETL process has been successful? The mere fact that a process was not terminated with an exception does not necessarily mean that the process did what was expected of it. Readable and evaluable logging of an ETL process can help here. It enables a reliable assessment of the success of an ETL process.
This article presents a design pattern for logging an ETL process based on stored procedures that insert and update log records in log tables. The aim is to create a readable and evaluable protocol that provides answers to essential questions about the execution of an ETL process:
- How long does the ETL process take in total?
- How long does a stored procedure, SSIS package, or other component take to execute?
- How long does it take to execute a specific SQL statement?
- How many records were processed by an SQL statement?
Of course, we also want to know whether the process as a whole, a procedure or even a specific SQL statement was executed successfully.
The procedure is actually straightforward: At the beginning of each action, a log data record is written and after the action is completed, it is updated with either the status success or failure and, if necessary, other helpful information. That’s it! But it can still be a little more…