Automatic Workflow Generation from SQL Code Dependencies
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Manual creation of workflows from SQL files is time-consuming and dependent on developer experience, leading to inconsistent quality and difficulty in standardization, especially in large entities with thousands of ETL jobs.
Innovation Solution
An automatic workflow generation system that parses code statements from files, determines dependencies, builds a dependency graph, partitions it to address unclear dependencies, schedules jobs, annotates code statements, and generates workflows without requiring manual intervention or access to table schemas.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If manual workflow creation from SQL files is used, then workflow quality can be adjusted by developer experience, but the process is extremely time-consuming and cannot be standardized
Solution Approach 1:
The system performs automatic workflow generation where the code statements themselves contain the workflow definition information. The parser extracts workflow definitions directly from the code without requiring manual intervention or external metadata files, making the system self-sufficient and eliminating time-consuming manual creation processes while maintaining consistent quality through standardized parsing rules
Solution Approach 2:
The manual mechanical process of developers creating workflows from SQL files is replaced by an automated computational system. The parser automatically analyzes code statements, extracts workflow definitions, and generates workflow objects programmatically, substituting human manual effort with automated code processing that is both faster and more consistent
2Adaptability or versatility
If manual workflow creation is used, then workflows can be customized according to developer judgment, but standardization across thousands of ETL jobs is extremely difficult
Solution Approach 1:
The system changes the fundamental parameter of workflow creation from manual customization to automated extraction based on code structure. By parsing the inherent workflow definitions embedded in code statements, the system maintains adaptability to different workflow scenarios while enforcing standardization through consistent parsing rules and automated generation processes across all ETL jobs
Solution Approach 2:
The automatic workflow generation system serves multiple functions: it parses various code statement formats, extracts different types of workflow definitions, handles dependencies between jobs, and generates standardized workflow objects. This universal approach works across thousands of different ETL jobs while maintaining consistent quality and standardization
3Productivity
If automatic workflow generation from code statements is implemented, then time consumption is significantly reduced, but the system must accurately parse and determine dependencies between code statements
Solution Approach 1:
The parser acts as an intermediary that bridges the gap between raw code statements and workflow definitions. It systematically analyzes code statements, identifies workflow-relevant information, determines dependencies between statements, and transforms them into structured workflow objects. This intermediary processing layer enables accurate dependency detection while maintaining high automation speed
Solution Approach 2:
The system performs preliminary parsing and dependency analysis directly on the code statements before workflow execution. By extracting and analyzing workflow definitions and dependencies in advance during the generation phase, the system prepares the complete workflow structure upfront, enabling accurate dependency determination while maintaining fast automated generation
Data Source
AI summary
Automatic workflow generation is described. One or more files containing code statements for accessing and modifying information in a destination database is received. The code statements are parsed from the one or more files and dependencies between the code statements are determined. A dependency graph is built by arranging the code statements according to the dependencies between the code statements. The dependency graph is partitioned by identifying at least one barrier code statement having an unclear dependency and dividing the dependency graph between code statements occurring prior to the at least one barrier code statement and code statements occurring after the at least one barrier code statement. Jobs are scheduled based on the partitioned dependency graph, and the code statements are annotated according to the scheduled jobs. A workflow is then automatically generated based on the annotated code statements.