How Automated SQL Lineage works on scripts and routines

This section will showcase the features supporting System Routines within the SQL Lineage module. For additional details on System Routines, please refer to the Data Catalog documentation.

A system routine can embody a database stored procedure/routine or a SQL script. The Automated SQL Lineage module meticulously analyzes the routine to extract the SQL statements. A System Routine can exist in one of three statuses:

• PENDING: Not analyzed yet.
• ANALYZED: Successfully analyzed.
• ERROR: Analysis encountered an error.

When configuring the system routine analysis, two key aspects must be taken into consideration: the strategy for extracting statements from the routine and whether this operation should be performed under user supervision or automatically upon the receipt of a new routine or its modification. Routine states can assist in supervising these kinds of operations.

Visualize statements from a system routine

All the Statements extracted from a System Routine can be consulted in its detail page, under the “SQL LINEAGE” section. By pressing the bin button all the Statements associated with the System Routine will be deleted.

Manual statement extraction

To manually analyze a System Routine, extract the statements via the graphical interface. Simply highlight the text of the statement in the “Routine Body Definition” section of the System Routine detail page, right-click, and select the “ADD” key.

Within the modal, it becomes possible to provide additional information related to the statement, such as specifying the destination Bucket.

By using the “SAVE” button, the statement is saved. After manually extracting various statements, you can change the status of the System Routine to “ANALYZED” by pressing the “CONFIRM” button.

Automatic statements extraction

The automatic extraction of Statements it’s possible thanks to a parser that recognizes their position inside a System Routine.

Automatic extraction has two different strategies:

• MARKUP: The Statements of interest are chosen by the user, who signals them by adding a line of code within the System Routine. For example:

--SQL_LINEAGE (name: InsertStatementName, description: StatementDescription, schema: default_schema_name)
INSERT INTO store
		SELECT store_id, city, country 
		FROM profit_raw.address_tmp, profit_raw.store_tmp
		WHERE address_tmp.address_id = store_tmp.address_id;

• AUTOMATIC: all statements within the System Routine are taken into consideration for the analysis.

Automatic extraction from interface

To automatically analyze a System Routine from the interface, simply click the “ANALYZE ALL” button in the “DEFINITION” section on the detail page.

Following this, you need to choose the destination Bucket for the extracted statements and specify the strategy for analyzing the System Routine.

Automatic extraction without manual intervention

The System Routines analysis process can be automated by linking a System to a Bucket. To achieve this, navigate to the Bucket creation or modification mode. Select the desired System to associate with the Bucket and specify the analysis strategy for System Routines. Consequently, whenever a System Routine within the associated System is inserted or modified, automated analysis takes place. The extracted statements are then automatically inserted into the designated Bucket.