Jira Audit Log Parsing Summary

Overview

This project utilizes the Snowflake Snowpark API to parse Jira audit logs, focusing specifically on process step change events. The goal is to build a structured view that tracks the transitions and statuses of Jira Epics across various process steps over time. As this project was done at a previous employer I'm not permitted to show the actual code itself.

Key Tasks Performed

• Temporary Storage: A temporary Snowflake stage is created to store and register user-defined functions (UDFs).
• Extract Process Steps: UDFs extract "From" and "To" process steps using regular expressions on text fields.
• Data Filtering: Loads Jira logs and filters relevant entries, such as process step changes or Epic creation events.
• Data Enrichment: Adds event types such as CREATED and UPDATED to categorize changes.
• Window Functions: Utilizes window functions to manage duplicates, calculate start and end times, and identify the latest step.
• Aggregation: Groups consecutive process steps and aggregates time intervals.
• View Creation: Stores the final transformed data in Snowflake views for analysis and monitoring.

Purpose

The code helps track the progress of Jira Epics through different stages and identify process bottlenecks. By analyzing how long each step takes and whether it is complete, teams can monitor workflow efficiency and improve processes.

Output

The output is a structured view containing:

• Epic ID
• Process Step
• Start and End Times
• FULL_PERIOD flag indicating if the step is complete
• LATEST_PROCESS_STEP to mark the most recent step

Conclusion

This project showcases the use of Snowflake's Snowpark API to efficiently parse and transform data from Jira audit logs, providing valuable insights into process management and workflow optimization.