Module Execution Stats (ufnModuleStats)

Purpose

To provide a clear and ordered overview of the latest execution steps performed by a specific module, aiding operational insight and debugging efforts.

Motivation

During module execution, various steps are logged in the (data logistics) control framework. This often leads to fragmented visibility over what actually happened during an execution. The need for a consolidated, ordered view of execution steps — including log messages, semaphore activity, and data operations — becomes essential to quickly understand, analyze, and debug module behavior. This pattern captures that consolidated view.

Applicability

This pattern is applicable in scenarios such as:

• Operational troubleshooting of a failed or unexpected module execution
• Auditing and analysis of module performance and execution behavior
• Providing transparency during data processing for quality assurance
• Enabling efficient root cause analysis in complex data pipelines

Structure

The core of this pattern is a function that, when provided with a Module Id, retrieves and returns all execution-related events from the most recent execution of that module. The result is structured as a list of steps, each capturing:

• Module Id and Batch Id
• Description of the step (e.g., “Semaphore written”, “Module execution start”)
• Timestamps and record identifiers for chronological ordering
• Optional log messages and record counts
• The source data object that logged the information

This dataset can be sorted by:

• [Timestamp - Step] (Inscription Timestamp)
• [Record Id - Step] (Inscription Record Id)

to visualize the execution as a timeline.

Implementation Guidelines

• Develop a SQL-based user-defined function (UDF), e.g., ufnModuleStats(@ModuleId INT)
• Aggregate data from key sources:
  • ModuleSemaphoreArchive for semaphore state changes
  • ModuleLogMessage for logging during the module lifecycle
  • ProcessStepVapourTrail for critical process steps
• Ensure data is retrieved only from the latest execution batch for the specified module
• Join and normalize all records into a unified format/schema
• Allow ordering via timestamp and record ID to simulate a timeline
• Handle NULL values gracefully to ensure readability

Considerations and Consequences

N/A

Related Patterns

N/A

Sample

SQL Example

SELECT *
 FROM MetadataZoneCore.ufnModuleStats(1679)
ORDER BY [Timestamp - Step] ASC, [Record Id - Step] ASC;

Sample Output

The displayed output is a simplified excerpt intended to illustrate the structure and format of the full result set.

ModuleId	BatchId	Data Logistics Process - Step	Timestamp - Step	Record Id - Step	No of Records	Log Message	Source Table
1679	8	Semaphore written	2024-11-07 12:53:49.546	1	NULL	NULL	ModuleSemaphoreArchive
1679	8	Log message	2024-11-07 12:53:49.582	122	NULL	Current module was not locked by semaphore, semaphore written	ModuleLogMessage
1679	8	Module execution start	2024-11-07 12:53:53.248	1	0	SUCCESSFUL: load process with AuditTrailId 61282 finished	ProcessStepVapourTrail
1679	8	Module execution done	2024-11-07 12:53:53.639	1	NULL	SUCCESSFUL: load process with AuditTrailId 61282 finished	ProcessStepVapourTrail
1679	8	Semaphore released	2024-11-07 12:53:53.700	1	NULL	NULL	ModuleSemaphoreArchive