1.3. REDLIN - Datamodel

REDLIN Data Model Overview The REDLIN data model is designed to track and represent data lineage across diverse environments. It utilizes a core-extension architecture to manage generic lineage metadata while allowing for technology-specific properties (e.g., Exasol, MicroStrategy) through 1-to-1 extension tables.

Structurally, the model represents a directed graph where LIN_ITEMS act as the nodes and LIN_BASE acts as the edges defining the data flow.

The data shouldn’t be accessed directly out of the tables. The function LIN_GEN_LINEAGE was written to cover the internals. Details see Gen Lineage.

            erDiagram

        LIN_ITEMS {
            string ITEM_ID PK
            string INSTANCE
            string SYSTEM_NAME
            string NAME
        }
        LIN_BASE {
            string ITEM_ID FK "Target"
            string REFERENCE_ITEM_ID FK "Source"
            string EXPRESSION_ID FK
        }
        LIN_BASE_EXPRESSIONS {
            string EXPRESSION_ID PK
            string EXPRESSION
        }
        LIN_ITEMS_EXASOL {
            string ITEM_ID PK,FK
            string SCHEMA
            string TABLE
            string COLUMN_TYPE
            string OBJECT_TYPE
        }
        LIN_ITEMS_MSTR {
            string ITEM_ID PK,FK
            string PROJECT_ID
            string PROJECT_NAME
            string ATTRIBUTE_TYPE
            string ATTRIBUTE_SUBTYPE
        }


        LIN_ITEMS ||--o{ LIN_BASE : "target (ITEM_ID)"
        LIN_ITEMS ||--o{ LIN_BASE : "source (REFERENCE_ITEM_ID)"
        LIN_BASE_EXPRESSIONS ||--o{ LIN_BASE : "applies to"
        LIN_ITEMS ||--o| LIN_ITEMS_EXASOL : "extends (1:1)"
        LIN_ITEMS ||--o| LIN_ITEMS_MSTR : "extends (1:1)"

  1. Core Entities (Nodes)

    LIN_ITEMS

    This is the central entity table representing any generic object within the lineage graph (e.g., a database column, a report attribute, or a table).

    • Fields: ITEM_ID (Primary Key), INSTANCE, SYSTEM_NAME, NAME.

    • Purpose: It holds the foundational metadata common to all objects, regardless of their underlying technology stack.

  2. Lineage Relationships (Edges)

    LIN_BASE

    This table resolves the many-to-many relationships between lineage items, effectively serving as the edges of the lineage graph.

    • Fields: ITEM_ID, REFERENCE_ITEM_ID, EXPRESSION_ID.

    • Purpose: It maps a target object (ITEM_ID) to its source object (REFERENCE_ITEM_ID). A single item from LIN_ITEMS can act as a source or target multiple times, allowing for complex, multi-layered data flows.

    LIN_BASE_EXPRESSIONS

    • Fields: EXPRESSION_ID (Primary Key), EXPRESSION.

    • Purpose: It stores the specific transformation logic or SQL expressions applied between a source and a target item. This is linked to the lineage edge via the EXPRESSION_ID in LIN_BASE. Normalizing this into a separate table allows identical transformation logic to be reused across multiple lineage paths.

  3. Technology-Specific Extensions (Subtypes)

    To accommodate the specific metadata requirements of different technologies without cluttering the core LIN_ITEMS table, the model uses extension tables connected via a 1-to-1 relationship to LIN_ITEMS.ITEM_ID.

    LIN_ITEMS_EXASOL

    • Fields: ITEM_ID (Foreign Key / Primary Key), SCHEMA, TABLE, COLUMN_TYPE, OBJECT_TYPE.

    • Purpose: Stores technical metadata specific to Exasol database objects, enabling detailed tracking of schema structures and data types within the DWH layer.

    LIN_ITEMS_MSTR

    • Fields: ITEM_ID (Foreign Key / Primary Key), PROJECT_ID, PROJECT_NAME, ATTRIBUTE_TYPE, ATTRIBUTE_SUBTYPE.

    • Purpose: Stores metadata specific to MicroStrategy (MSTR) reporting objects, allowing the lineage to extend seamlessly from the database backend into the BI frontend.