How to do Data Lineage in Data Governance: step by step

João Barros 28 de July de 2026 4 min read

This tutorial shows how to implement Data Lineage in Data Governance to track the origin, transformations and consumption of a dataset. Knowing the lineage helps diagnose errors, assess the impact of changes and meet audit and compliance requirements.

Prerequisites

Access to an environment with ETL/ELT (e.g.: Azure Data Factory, Synapse or other) and a database (e.g.: Azure SQL or SQL Server).
Permissions to read pipeline metadata and create metadata tables.
Basic knowledge of SQL and how to export pipeline logs in JSON.

Step 1: Define the metadata model for Lineage

Before recording anything, define a simple model that describes sources, transformations and targets. A minimal model has the tables: entity (files/tables), process (ETL/transformation) and lineage (links entities to processes).

-- Exemplo de esquema mínimo em Azure SQL/SQL Server
CREATE TABLE entity (
  entity_id INT IDENTITY PRIMARY KEY,
  name NVARCHAR(255),
  type NVARCHAR(50), -- e.g. 'table','file','view'
  location NVARCHAR(1000)
);

CREATE TABLE process (
  process_id INT IDENTITY PRIMARY KEY,
  name NVARCHAR(255),
  tool NVARCHAR(100), -- e.g. 'ADF','Spark'
  run_id NVARCHAR(100),
  started_at DATETIME2,
  finished_at DATETIME2
);

CREATE TABLE lineage (
  lineage_id INT IDENTITY PRIMARY KEY,
  process_id INT FOREIGN KEY REFERENCES process(process_id),
  source_entity_id INT FOREIGN KEY REFERENCES entity(entity_id),
  target_entity_id INT FOREIGN KEY REFERENCES entity(entity_id),
  details NVARCHAR(2000)
);

Step 2: Capture pipeline/ETL metadata

Configure your ETL (e.g.: Azure Data Factory) to export execution logs in JSON or similar. The goal is to extract: run_id, dataset names, timestamps and operations (copy, transform).

// Exemplo conceptual de payload JSON que o pipeline deve exportar
{
  "run_id": "abc-123",
  "pipeline": "Load_Sales",
  "started_at": "2026-07-01T10:00:00Z",
  "finished_at": "2026-07-01T10:05:00Z",
  "sources": [{"name":"stg.sales.csv","type":"file","location":"/lake/sales/2026/07"}],
  "targets": [{"name":"dw.sales","type":"table","location":"server.database.schema.dw.sales"}],
  "operations": ["copy","cleanse"]
}

Step 3: Load metadata into the Lineage tables

Create a process that converts the pipeline JSON into rows in the entity, process and lineage tables. It can be a T-SQL script, a Spark notebook, or an Azure Function.

-- Pseudocódigo T-SQL para inserir metadados (simplificado)
DECLARE @run_id NVARCHAR(100) = 'abc-123';
-- Inserir process
INSERT INTO process (name, tool, run_id, started_at, finished_at)
VALUES ('Load_Sales','ADF',@run_id,'2026-07-01T10:00:00','2026-07-01T10:05:00');
DECLARE @process_id INT = SCOPE_IDENTITY();
-- Inserir entidades (ex.: fonte)
INSERT INTO entity (name,type,location)
VALUES ('stg.sales.csv','file','/lake/sales/2026/07');
DECLARE @source_id INT = SCOPE_IDENTITY();
-- inserir destino
INSERT INTO entity (name,type,location)
VALUES ('dw.sales','table','server.database.schema.dw.sales');
DECLARE @target_id INT = SCOPE_IDENTITY();
-- ligar
INSERT INTO lineage (process_id, source_entity_id, target_entity_id, details)
VALUES (@process_id,@source_id,@target_id,'copy,cleanse');

Step 4: Automate ingestion and handle duplicates

Automate loading these metadata after each run: use pipeline triggers, webhooks or a scheduled job. Avoid duplicates by checking run_id and entity location before inserting.

-- Verificação simples antes de inserir (exemplo)
IF NOT EXISTS (SELECT 1 FROM process WHERE run_id = @run_id)
BEGIN
  -- inserir process e lineage como acima
END

Step 5: Visualize Lineage and manage impact

Create queries or a dashboard (Power BI) that show the path of a column/table from source to target and that allow calculating the impact of a change on an entity.

-- Exemplo de query recursiva para seguir lineage de uma entidade
WITH RecLineage AS (
  SELECT l.source_entity_id, l.target_entity_id, 1 AS depth
  FROM lineage l
  WHERE l.source_entity_id = @start_entity_id
  UNION ALL
  SELECT l2.source_entity_id, l2.target_entity_id, rl.depth + 1
  FROM lineage l2
  JOIN RecLineage rl ON l2.source_entity_id = rl.target_entity_id
)
SELECT * FROM RecLineage ORDER BY depth;

Verify the result

Confirm that each pipeline run creates a record in process and that there are corresponding entries in entity and lineage. Validate with simple queries: search for run_id and follow the lineage with the recursive query. In Power BI, verify that the graph shows correct sources and targets.

Conclusion

Implementing Data Lineage in Data Governance starts with a simple metadata model and automatic capture of ETL logs. Next steps: enrich metadata (columns, SQL transformations), integrate with Microsoft Purview or create richer visualizations in Power BI. Tip: start small (one critical pipeline) and expand when the process is solid — what is the first pipeline you will track?