99 ) Data lineage project

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.

What is Data Lineage?

Data lineage is the lifecycle documentation that describes the origin of data, what happens to it as it moves through various pipelines, and where it ultimately moves over time. It provides a visual map showing the complete path of data—from its source systems, through every transformation, filter, and join, down to the final consumption reports or dashboards—enabling teams to trace errors, audit compliance, and understand data dependencies.

 

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') }}                      |
+------------------------------------------------------------------------------------------------------------------+