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

VERSION_ID

String/Timestamp

(select max(version_id)...)

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

DIRECTION

String

"DOWN"

Determines the traversal direction: DOWN (downstream impact) or UP (upstream origins).

START_SCHEMA

String

None

Lineage starting point schema (used together with START_TABLE and START_COLUMN).

START_TABLE

String

None

Lineage starting point table or view (used together with START_SCHEMA and START_COLUMN).

START_COLUMN

String

None

Lineage starting point column name (used together with START_SCHEMA and START_TABLE).

START_ATTRIBUTE_TYPE

String

None

Lineage starting point attribute type in case of BI (used together with START_BI_PROJECT and START_COLUMN).

START_BI_PROJECT

String

None

Lineage starting point BI project name in case of BI (used together with START_COLUMN).

SIMPLIFIED

Boolean/String

FALSE

Optional flag to contract intermediate steps and return direct architectural hops.

SKIP_MD

Boolean/String

TRUE

Optional flag to bypass MD (metadata) layer objects when SIMPLIFIED is TRUE.

SQL_DEBUG

Boolean/String

FALSE

If set to "TRUE", returns the generated SQL statement as a string for debugging rather than executing it.

LAYER_MAPPING

String

"RED_STD_ARCHITECTURE"

Optional flag / container to map objects to architectural layers.

HIERARCHY

String

"RED_STANDARD"

Optional hierarchy identifier used to resolve layer containers.

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 at column level.

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

3.3.4. 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' )

3.3.5. Returns

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

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

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

  • source_system_name: The name of the source system.

  • source_schema: The name of the source schema.

  • source_table: The name of the source object.

  • source_column: The name of the source column.

  • source_column_datatype: The data type of the source column.

  • source_object_type: The object type of the source object (e.g., TABLE, VIEW).

  • source_object_layer: The name of the source object layer.

  • source_bi_project: The name of the source BI project.

  • source_bi_type: The name of the source BI type.

  • source_bi_id: The name of the source BI id.

  • target_system_name: The name of the target system.

  • target_schema: The name of the target schema.

  • target_table: The name of the target object.

  • target_column: The name of the target column.

  • target_column_datatype: The data type of the target column.

  • target_object_type: The object type of the target object (e.g., TABLE, VIEW).

  • target_object_layer: The name of the target object layer.

  • target_bi_project: The name of the target BI project.

  • target_bi_type: The name of the target BI type.

  • target_bi_id: The name of the target BI id.

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

  • is_leaf: Boolean flag indicating if the node is a terminal leaf.

  • is_cycle: Boolean flag indicating if the node is part of a circular dependency.