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 |
|---|---|---|---|
|
String/Timestamp |
|
The specific snapshot version or alias of the lineage to query. If omitted, the latest version in |
|
String |
|
Determines the traversal direction: |
|
String |
None |
Lineage starting point schema (used together with |
|
String |
None |
Lineage starting point table or view (used together with |
|
String |
None |
Lineage starting point column name (used together with |
|
String |
None |
Lineage starting point attribute type in case of BI (used together with |
|
String |
None |
Lineage starting point BI project name in case of BI (used together with |
|
Boolean/String |
|
Optional flag to contract intermediate steps and return direct architectural hops. |
|
Boolean/String |
|
Optional flag to bypass MD (metadata) layer objects when |
|
Boolean/String |
|
If set to |
|
String |
|
Optional flag / container to map objects to architectural layers. |
|
String |
|
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 WITHclause pointing to that specificitem_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.