3.3. LIN_GEN_LINEAGE Reference

3.3.1. Overview

LIN_GEN_LINEAGE is an Exasol LUA scalar script designed to dynamically generate complex SQL queries for tracing data lineage. It utilizes hierarchical querying (CONNECT BY NOCYCLE) to traverse data dependencies either upstream or downstream. The function adapts the generated SQL based on whether the user wants detailed column-level lineage, aggregated lineage grouped by specific hierarchy levels, or lineage across an entire system versus a specific starting node.

3.3.2. Parameters

The core logic resides in generate_sql_for_import_spec(import_spec), which expects an import_spec object containing a parameters table. Below are the supported parameters:

Parameter

Type

Default

Description

DIRECTION

String

“DOWN”

Determines the direction of the lineage traversal. DOWN: Traces downstream impact. UP: Traces upstream origins.

VERSION_ID

String/Int

(select max(version_id)…)

The specific snapshot version of the lineage to query. If omitted, the latest version in lin_base is used.

START_SCHEMA

String

None

The schema name of the starting point.

START_TABLE

String

None

The table name of the starting point.

START_COLUMN

String

None

The column name of the starting point.

START_ATTRIBUTE_TYPE

String

None

Filter for a specific attribute type name for the start node (e.g., matching types in lin_md_mstr_object_types).

START_MSTR_PROJECT

String

None

Specifically for MicroStrategy lineage. Overrides standard schema/table logic to start the trace from a specific project.

GROUP_BY

String

‘0’

Defines the level of aggregation. If ‘0’ or missing, detailed item-to-item lineage is returned. If provided, lineage is grouped by the specified container/level ID.

AGG_FUNC

String

None

An optional SQL aggregation function (e.g., SUM(x)) appended to the SELECT clause when GROUP_BY is active.

HIERARCHY

String

“STANDARD”

Defines which hierarchy relation mapping to use when grouping objects (used in conjunction with GROUP_BY).

SQL_DEBUG

Boolean/String

false

If “TRUE”, the function wraps the generated SQL query inside a SELECT '...' AS sql_statement, allowing the user to view the generated SQL without executing it.

3.3.3. Core Logic & Behavior

3.3.3.1. Single Node vs. Multi-Node (Full System) Lineage

  • Single Node: If a starting point is provided (schema, table, column, or MSTR project), the generated query uses a START WITH clause pointing to that specific item_id.

  • Multi-Node: If no specific starting point is provided, the script treats the request as a “multi” query. It retrieves lineage for all available items matching the version criteria, evaluating paths for the entire graph.

3.3.3.2. Detailed vs. Aggregated Lineage

  • Detailed (GROUP_BY is null or ‘0’): The script generates a detailed recursive query returning exact paths. Key columns returned in the SQL include:

  • Aggregated Lineage: The lineage is aggregated to configureable logical hierarchies.

3.3.3.3. Examples

 1# Lineage - Downstream with starting column
 2select * from (import from script redlin.lin_gen_lineage with direction='DOWN' start_schema='PSV_CORE_BA' start_table='CUSTOMER' start_column='SURENAME' )
 3select * from (import from script redlin.lin_gen_lineage with start_schema='PSV_CORE_BA' start_table='CUSTOMER' start_column='SURENAME' )
 4
 5# Lineage - Upstream with starting column
 6select * from (import from script redlin.lin_gen_lineage with direction='UP' start_schema='PSV_CORE_BA' start_table='CUSTOMER' start_column='SURENAME' )
 7
 8# Lineage - Downstream - aggregated by hierarchy level
 9select * from (import from script redlin.lin_gen_lineage with direction='DOWN' start_schema='PSV_CORE_DR' start_table='CUSTOMER' start_column='SURENAME' group_by='SX_STD_ARCHITECTURE' agg_func='count(1) as object_count' )
10
11# Lineage - Downstream - aggregated by Standard Architecture groups 
12select * from (import from script redlin.lin_gen_lineage with direction='DOWN' start_schema='PSV_CORE_DR' start_table='CUSTOMER' start_column='SURENAME' group_by='SX_STD_ARCHITECTURE' agg_func='count(1) as object_count' )
13
14# Lineage - Downstream - aggregated by hierarchy level - listing schemas and counting tables
15select * from (import from script redlin.lin_gen_lineage with direction='DOWN'  group_by='3' HIERARCHY='STANDARD' agg_func='listagg(distinct "SCHEMA",'','') as schema_list, count(distinct "TABLE") as object_count' )

3.3.3.3.1. 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.