99 ) Data lineage project

 

What is Data Lineage?

Data lineage  means tracking the data from source to destination  and  show as a diagram

-

Data lineage is the lifecycle tracking of data that describes its origin, what happens to it, and where it moves over timeas it flows through systems, pipelines, and transformation processes.

      It provides a map of data movement from source systems (like transactional databases or APIs) through staging, data lakes, and transformations, all the way to final consumption points (like BI dashboards, machine learning models, or reports).

• Example:

  • Source: A customer's address is entered into an on-premises CRM database.

  • Transformation: An ETL pipeline extracts the raw address, cleans and standardizes the string formats using an AWS Glue or Spark job, and loads it into a data warehouse table (dim_customer).

  • Consumption: A Power BI executive dashboard uses dim_customer to display regional sales performance.

  • Lineage Insight: Data lineage maps this complete path (CRM -> ETL Pipeline -> dim_customer -> Power BI Dashboard), allowing an auditor or data engineer to trace exactly where a metric came from or assess the downstream impact if a source column changes.

Tools Used for Data Lineage

• Collibra: An enterprise-grade data intelligence and governance platform widely used for automated data lineage, business glossary management, and compliance mapping.

• Atlan: A modern data collaboration and governance platform with automated lineage parsing for cloud data stacks (Snowflake, dbt, Airflow, Tableau).

• Apache Atlas: An open-source metadata management and governance framework built for Hadoop ecosystem tools and big data architectures.

• Microsoft Purview: A unified data governance service native to Azure that automatically maps data lineage across Azure services, Power BI, and multi-cloud environments.

• dbt (data build tool): Popular developer-centric transformation tool that automatically generates column-level and model-level lineage graphs based on SQL transformation code.

Steps to Configure and Setup Data Lineage in Collibra

Configuring lineage in Collibra involves mapping technical metadata from source systems, ETL pipelines, and reporting tools into Collibra Data Governance Center (DGC) and connecting them via technical or manual integration methods.

Step 1: Define the Metamodel and Asset Types

• Set up or verify the asset types in Collibra (e.g., Systems, Database Tables, Columns, ETL Jobs, Reports, Dashboards).

• Establish the relationship types between them (e.g., Table contains Column, ETL Job consumes Table, Report reads from Table).

Step 2: Configure Ingestion Connectors (Collibra Data Lineage / Edge)

• Deploy Collibra Edge or configure native Collibra Connect integrations to securely connect with your data ecosystem (e.g., Snowflake, Databricks, AWS S3, SQL Server, Power BI, or dbt).

• Set up authentication credentials, service accounts, and network access rules for the target database engines and metadata sources.

Step 3: Harvest Technical Metadata

• Run metadata harvesters or API integrations to pull structural definitions (DDL, schemas, tables, and views) into Collibra.

• Execute specific harvesters for transformation tools (like dbt or SQL scripts) to capture how data transformations occur between source and target objects.

Step 4: Map Transformation Logic and Lineage Connections

• Configure lineage harvesters to automatically parse SQL queries or transformation code. This maps data flow at the column level or table level, establishing links between source database columns and target reporting objects.

• For custom pipelines not supported by native auto-harvesters, use the Collibra REST API or manual mapping templates to upload custom lineage relations.

Step 5: Validate and Publish the Lineage View

• Review the harvested metadata and generated lineage graphs inside the Collibra UI (under the Lineage tab of an asset).

• Validate that upstream sources and downstream dashboards link correctly without broken connections.

• Assign data stewards and publish the governance assets so business and technical users can trace data lineage and impact analysis across the enterprise.

Everyday Problems Solved by Data Lineage

1. The Missing Customer Problem

• What happens: A manager looks at the monthly report and sees that zero new customers signed up yesterday, even though the mobile app was busy with new sign-ups all day long.

• How data lineage helps: Without lineage, you have to guess if the app crashed, if the database is down, or if the report script failed. With data lineage, you click on the "New Customers" report. The lineage map shows you the exact path: Mobile App $\rightarrow$ Temporary Storage $\rightarrow$ Daily Report. You instantly see that the link between Temporary Storage and the Daily Report was accidentally deleted during a morning system update, causing the numbers to stop flowing into the report.

2. The Wrong Country Problem

• What happens: The insurance company's quarterly sales report shows that a tiny town in Texas sold more insurance policies than the entire state of New York, which is obviously impossible.

• How data lineage helps: You use data lineage to trace where the New York policy data went. The lineage map reveals that a data cleaning script had a typo, accidentally tagging all New York customer records with a Texas zip code. Because you can see every step the data took from the raw spreadsheet to the final report, you find the exact script that caused the mix-up in seconds.

3. The Duplicate Bill Problem

• What happens: A customer complains that they were billed twice for their monthly insurance payment, but the finance team cannot figure out why their internal accounting sheet is showing extra money coming in.

• How data lineage helps: You trace the billing data backward using the lineage tool. The map shows you that two different automated background files (one sent by the website team and one sent by the customer service team) contained overlapping records for the same set of customers. Because the lineage map clearly displays where both files merged into the billing database, you quickly realize the system didn't filter out the duplicates, allowing you to fix the filter rule immediately.

 ==================================================================== 

Complete Guide: Implementing Data Lineage with a Single Tool (Apache Atlas / Collibra)

Scenario: The Insurance Data Lineage Problem

Problem Statement & Why Data Lineage Is Required

• Problem Statement: An enterprise insurance firm cannot trace the exact path of claims data from legacy on-premises policy administration systems through transformation pipelines to final regulatory dashboards, resulting in prolonged compliance audit cycles and undetected data corruption.

• Why it needs data lineage and cannot be handled without it: In a complex insurance ecosystem where policy, claims, and billing data are constantly copied, filtered, and aggregated across multi-cloud and on-premises storage, finding the root cause of a corrupted risk calculation is impossible manually. Without automated data lineage, data engineers and auditors cannot answer impact analysis questions (e.g., "If I change this upstream column format in the claims database, which downstream executive dashboard will break?"). Data lineage provides a transparent, visual map of data movement, ensuring regulatory accountability and data trust.

------ 

Step-by-Step Resolution: Fixing the Missing Customer Problem Using Collibra Data Lineage

Step 1: Open the Data Lineage Portal and Locate the Broken Report

• What you do: Log into your enterprise data governance portal (Collibra) and search for the broken report asset.

• Navigation / Menus:

  1. Open your web browser and navigate to your company's Collibra Data Intelligence Platform URL.

  2. Click on the top search bar or go to Search $\rightarrow$ Assets.

  3. Type Daily Customer Report into the search field and select the matching dashboard asset from the results list.

Step 2: View the End-to-End Lineage Graph

• What you do: Open the visual lineage map to trace backward from the report to find where the data flow stopped.

• Navigation / Menus:

  1. Once inside the Daily Customer Report asset page, click on the "Relations" tab or click the "Show Lineage" button on the right-hand panel.

  2. Switch the view to "End-to-End Lineage" mode.

  3. Look at the visual graph showing the flow: Mobile App Database $\rightarrow$ Temporary Storage (S3 Bucket) $\rightarrow$ Daily Report Script.

Step 3: Identify the Broken Connection Point

• What you do: Inspect the visual status indicators on the lineage map to pinpoint where data stopped moving.

• Navigation / Menus:

  1. Hover your mouse over the connecting line (edge) between Temporary Storage (S3 Bucket) and Daily Report Script.

  2. Notice that the connection indicator is highlighted in red (showing a broken link or zero data throughput).

  3. Click on the connection line to open its metadata properties panel, which displays the technical pipeline job ID responsible for moving data between these two layers.

Step 4: Inspect the Transformation Script or Pipeline Configuration

• What you do: Use the lineage tool's code reference to find the exact script or configuration file that severed the connection during the morning update.

• Navigation / Menus:

  1. In the pipeline properties panel, click on the linked Source Code / Transformation Script reference (e.g., pointing to your data transformation repository or orchestration job).

  2. Review the script or configuration code to find where the table reference was deleted.

• Example of Broken Script Code (Found via Lineage):

• Plaintext

  +--------------------------------------------------------------------------------------------------+
  |                               COLLIBRA DATA INTELLIGENCE PLATFORM                                |
  |  [Search Assets...]                  [Catalog]    [Governance]    [Lineage]    [Reports]         |
  +--------------------------------------------------------------------------------------------------+
  
    Asset: Daily Customer Report > Visual Lineage Graph
    Mode: End-to-End Lineage | Status: [!] 1 Broken Connection Detected
    ------------------------------------------------------------------------------------------------  
  
        [ Mobile App Database ]
                   │
                   │ (Status: GREEN - Data Flowing Normal)
                   ▼
        [ Temporary Storage (S3 Bucket) ]
                   │
                   │ ❌ [!] BROKEN CONNECTION (Zero Data Throughput / Invalid Path)
                   │     Hover info: Connection severed during morning system update (Job ID: #4092)
                   ▼
        [ Daily Report Script ]
                   │
                   │ (Status: GRAY - Ingestion Failed / Zero Records)
                   ▼
        [ Daily Customer Report (Dashboard) ]
  
    ------------------------------------------------------------------------------------------------  
    [!] ALERT DETAILS:
    - Source Node: temp_staging_bucket.old_backup_folder
    - Target Node: Daily_Customer_Report_Table
    - Issue Description: Pipeline points to an unpopulated directory. Click here to view source code script.
  +--------------------------------------------------------------------------------------------------+
  

SQL

-- BROKEN SCRIPT: The select query was accidentally modified or pointed to an empty path
SELECT 
    customer_id, 
    signup_timestamp 
FROM temp_staging_bucket.old_backup_folder; -- ERROR: Points to wrong folder, missing active data!

Step 5: Fix the Code and Restore the Data Flow

• What you do: Correct the script to restore the link between Temporary Storage and the Daily Report.

• Technical Fix:

  Update the pipeline script or orchestration config so it points back to the correct active staging path:

SQL

-- FIXED SCRIPT: Pointing back to the correct active temporary storage path
SELECT 
    customer_id, 
    signup_timestamp 
FROM temp_staging_bucket.active_customer_signups; -- FIXED: Restores data flow to the daily report

Step 6: Validate the Fix in the Lineage Portal

• What you do: Re-run the data pipeline and confirm that the lineage link turns green and customer numbers reappear in the report.

• Navigation / Menus:

1. Trigger your pipeline orchestrator to execute the updated script.

2. Return to Collibra and click the "Refresh Lineage" button on the asset page.

3. Verify that the connection line between Temporary Storage and the Daily Report turns green, confirming that customer data is flowing successfully again.

================================================================ 

 ==================================================================== 

DBT tool for data lineage

 ==================================================================== 

Step-by-Step Resolution: Fixing the Missing Customer Problem Using dbt

Step 1: Open the dbt Documentation Lineage Graph

• What you do: Launch your local or hosted dbt documentation site to visually inspect the project's dependency graph.

• Navigation / Commands:

  1. Open your terminal in your dbt project directory.

  2. Run the command: dbt docs generate followed by dbt docs serve.

  3. Open the automatically generated local web link in your browser and click on the "Lineage Graph" icon (the network node icon in the bottom right corner of the dbt UI).

Step 2: Locate the Broken Model on the Visual Graph

• What you do: Search for the impacted daily report model and trace its upstream dependencies visually.

• Navigation / Visuals:

  1. Type fct_daily_customer_report into the dbt docs search bar.

  2. The interactive node-and-edge visual graph centers on the report model.

  3. Visually trace the upstream path: stg_mobile_app $\rightarrow$ stg_temp_storage $\rightarrow$ fct_daily_customer_report.

Step 3: Identify the Broken Connection or Compilation Error

• What you do: Inspect the visual status indicators and node colors in the dbt graph to see where the data flow or compilation failed.

• Visual Representation in dbt:

  • Blue Nodes: Base/Staging sources (Healthy).

  • Orange Nodes: Transformations.

  • Red / Warning Node: The compilation or run error indicator on the model referencing the missing table path.

Step 4: Inspect the dbt SQL Model Code

• What you do: Click on the affected node in the visual graph to open its definition panel and review the underlying SQL code causing the broken path.

• Example of Broken dbt SQL Code (Found via Model Inspection):

SQL

-- BROKEN dbt MODEL: models/fct_daily_customer_report.sql
SELECT 
    customer_id, 
    signup_timestamp 
FROM {{ source('temp_staging_bucket', 'old_backup_folder') }} -- ERROR: References unpopulated folder!

Step 5: Fix the Code Using the dbt ref() or source() Function

• What you do: Edit your dbt SQL model file in your code editor (like VS Code) to point back to the correct active source or staging table.

• Technical Fix:

  Update the SQL file to reference the correct active path:

SQL

-- FIXED dbt MODEL: models/fct_daily_customer_report.sql
SELECT 
    customer_id, 
    signup_timestamp 
FROM {{ source('temp_staging_bucket', 'active_customer_signups') }} -- FIXED: Restores data flow

Step 6: Validate and Refresh the Lineage Graph

• What you do: Run the model via the terminal to confirm the fix, then refresh the dbt documentation UI.

• Commands & Visuals:

  1. Run dbt run --select fct_daily_customer_report in your terminal to successfully execute the updated model....

  2. Re-run dbt docs generate and refresh your browser window.

  3. Verify that the node status turns healthy/blue and the customer data flows successfully into the report.

Graphical Representation of dbt Lineage Graph with Error

Plaintext

+------------------------------------------------------------------------------------------------------------------+
| dbt DOCUMENTATION & LINEAGE UI                                                                [Search Models...] |
+------------------------------------------------------------------------------------------------------------------+
| Project: insurance_analytics_project                                                   [Graph] [Docs] [Manifest] |
+------------------------------------------------------------------------------------------------------------------+
|                                                                                                                  |
|                                         +-----------------------+                                                |
|                                         |  source: mobile_app   |                                                |
|                                         |     [ Base Table ]    |                                                |
|                                         +-----------------------+                                                |
|                                                     |                                                            |
|                                                     v                                                            |
|                                         +-----------------------+                                                |
|                                         | stg_temp_storage      |                                                |
|                                         |   [ Staging Model ]   |                                                |
|                                         +-----------------------+                                                |
|                                                     |                                                            |
|                                                     |  ❌ [!] COMPILATION / RUN ERROR                            |
|                                                     |  Hover: "Source relation not found: old_backup_folder"     |
|                                                     v                                                            |
|                                         +-----------------------+                                                |
|                                         | fct_daily_customer_rep|                                                |
|                                         |    [ Fact Model ]     |                                                |
|                                         +-----------------------+                                                |
|                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------+
| MODEL DETAILS PANEL (Right Sidebar / Bottom Pop-up)                                                              |
| File Path: models/fct_daily_customer_report.sql                                                                  |
| Error: Failed to resolve relation {{ source('temp_staging_bucket', 'old_backup_folder') }}                      |
+------------------------------------------------------------------------------------------------------------------+