3.4. LIN_LIVE_LINEAGE Reference

3.4.1. Overview

LIN_LIVE_LINEAGE is a Lua scalar script deployed within the Exasol environment. It acts as a dynamic SQL generator designed to be used in conjunction with Exasol’s IMPORT FROM SCRIPT functionality.

The script dynamically constructs a complex hierarchical query (using CONNECT BY) that calculates the live, column-level data lineage for a given SQL statement. It bridges on-the-fly SQL parsing (via lin_live_lineage_udf) with the persisted historical lineage data stored in the lin_base and lin_items_all tables.

3.4.2. Parameters

The function generate_sql_for_import_spec(import_spec) extracts its arguments from the import_spec.parameters object.

Parameter	Type	Required	Description
TARGET_SQL	String	Yes	The raw SQL query string for which the live lineage should be generated. If missing, the script throws an error.
TARGET_COL	String	No	A specific target column to isolate in the lineage trace. Defaults to null if not provided, tracing all columns.
SQL_DEBUG	Boolean/String	No	If set to "TRUE", the script bypasses execution and instead emits the generated SQL statement as a string. Useful for debugging the hierarchical query construction.

3.4.3. Generated SQL Workflow

When invoked, the script generates an extensive SQL statement that performs the following logical steps:

1. Snapshot Resolution (CTE: max_versionid): Identifies the most recent metadata snapshot by fetching the maximum version_id from the lin_base table.

2. Live Lineage Generation (CTE: live_lineage): Invokes the internal lin_live_lineage_udf to parse the provided TARGET_SQL against the system’s catalog (exa_all_columns). It joins these real-time results with the latest snapshot of lin_items_all to resolve exact item identifiers.

3. Node & Edge Resolution: Combines the persisted dependency edges (from lin_base) with the newly generated live edges. It formats the node names, resolving physical schema/table combinations or mapping them to friendly system/project names via lin_md_system_mapping.

4. Hierarchical Traversal (CONNECT BY): Executes an Oracle-style recursive graph traversal:

   START WITH a.is_start_node = 1
   CONNECT BY NOCYCLE prior a.referenced_item_id = a.item_id
   

5. Output Formatting: Calculates the exact path depth (path_level) and generates a human-readable trace string (PATH) showing the step-by-step transformation from the source literal/column to the final target.

3.4.4. Returns

The script emits the structured lineage graph with the following core attributes:

• version_id: The timestamp or ID of the lineage snapshot.

• lineage: The direct edge relationship (e.g., [Source] Table.ColA -> [Target] Table.ColB).

• expression: The SQL transformation logic applied at this step (if available).

• path_level: The depth of the node in the directed acyclic graph (DAG).

• PATH: The full hierarchical breadcrumb trail from the origin node.

• isleaf / iscycle: Boolean flags indicating if the node is a terminal leaf or part of a circular dependency.

• source_system_name: The name of the source system.

• source_schema_name: The name of the source schema.

• source_object_name: The name of the source object.

• source_column_name: The name of the source column.

• target_system_name: The name of the target system.

• target_schema_name: The name of the target schema.

• target_object_name: The name of the target object.

• target_column_name: The name of the target column.

• root_schema: The schema name of the root (starting) object.

• root_table: The table or view name of the root (starting) object.

• root_element: The column or attribute name of the root (starting) element.

3.4.5. Usage Example

To execute this script and retrieve the live lineage within Exasol, use the IMPORT FROM SCRIPT syntax:

Example 1: CTE Expression

SELECT * FROM (
    IMPORT FROM SCRIPT "REDLIN"."LIN_LIVE_LINEAGE" WITH
    TARGET_SQL = 'with x as (SELECT 3 + 2 AS col_c), y as (select 5 as col_a) select col_c*col_a as col_b from x join y on col_c=col_a'
);

Example 2: Simple Select Statement

SELECT * FROM (
    IMPORT FROM SCRIPT "REDLIN"."LIN_LIVE_LINEAGE" WITH
    TARGET_SQL = 'SELECT col_a + col_b AS col_c FROM my_schema.my_table'
);

Example 3: Debugging the generated SQL

SELECT * FROM (
    IMPORT FROM SCRIPT "REDLIN"."LIN_LIVE_LINEAGE" WITH
    TARGET_SQL = 'SELECT col_a + col_b AS col_c FROM my_schema.my_table',
    SQL_DEBUG = 'TRUE'
);