Data Lineage Concepts

Why this matters

Data lineage shows where data comes from, how it changes, and where it goes. As a Data Engineer, you use lineage to:

• Debug broken dashboards by tracing the exact upstream table/column that changed.
• Do impact analysis for schema changes before you deploy.
• Prove compliance: show sources and transformations for sensitive fields.
• Reduce cost and risk by removing duplicate pipelines and orphaned tables.
• Speed up onboarding: new teammates see clear paths from sources to products.

Concept explained simply

Data lineage is the map of your data’s journey. It connects sources, transformations, and outputs as a directed graph.

Mental model

Think of a subway map:

• Stations = datasets/tables/views/files.
• Lines = transformations (SQL, code, ETL) that move and reshape data.
• Trains = rows/columns flowing through. Colors can represent columns or data classes (like PII).

Levels of lineage

• Asset-level: table/view/job relationships.
• Column-level: how each column is derived or mapped.
• Run-level: which job run created which version of a dataset.
• Business-level: semantic meaning ("Customer" = people who made a purchase in last 12 months).

Directions and types

• Backward (upstream) lineage: from a report back to original sources.
• Forward (downstream) lineage: from a source out to all consumers.
• Technical lineage: extracted from code/logs (SQL, jobs, orchestrators).
• Business lineage: described in plain language for stakeholders.

Minimal metadata to capture

• Source asset, operation (e.g., SELECT/JOIN/AGGREGATE), target asset.
• Timestamp and job run ID/version.
• Column mappings (when feasible).
• Owner/team and tool that performed the change.
• Hash or reference to code/config used.

Worked examples

CREATE TABLE mart.daily_sales AS
SELECT o.order_date::date AS order_day,
       SUM(o.order_total) AS gross_sales
FROM staging.orders o
GROUP BY 1;

CREATE VIEW mart.customer_region_sales AS
SELECT c.customer_id,
       r.region_name,
       SUM(o.order_total) AS total
FROM dim.customers c
JOIN dim.regions r ON c.region_id = r.id
JOIN fact.orders o ON o.customer_id = c.customer_id
GROUP BY 1,2;

How to capture lineage (practical steps)

1. Inventory assets: list sources, staging, core models, marts, and BI datasets.
2. Collect edges automatically when possible: parse SQL, job configs, and execution logs.
3. Normalize names: use consistent qualifiers (catalog.schema.table) and stable IDs.
4. Record minimal metadata per edge: source, operation, target, timestamp, run ID, tool, code hash.
5. Capture column mappings for key models: especially for regulated or high-value tables.
6. Validate: spot-check with sample queries; compare expected vs observed read/write sets.
7. Visualize: render a graph view filtered by system, domain, or sensitivity.
8. Automate updates: run lineage extraction on each deploy or scheduled interval.

Who this is for

• Data Engineers who ship and maintain pipelines.
• Analytics Engineers who own models and BI datasets.
• Platform Engineers enabling governance and metadata.

Prerequisites

• Comfort with SQL (SELECT, JOIN, GROUP BY, CTEs).
• Basic understanding of your data platform (warehouse/lake, orchestrator).
• Familiarity with dataset naming conventions and ownership.

Learning path

1. Access and data classification basics (sensitivity, ownership).
2. Data lineage concepts (this lesson): assets, edges, directions, granularity.
3. Provenance and audit trails (runs, versions, who changed what).
4. Controls and monitoring (policy checks, freshness, incident playbooks).
5. Lifecycle and cleanup (deprecation with impact analysis).

Exercises

Try these practical tasks. Compare your results with the solutions (hidden below). Use this checklist as you work:

• I identified assets and edges clearly.
• I captured column-level mappings where needed.
• I noted assumptions and transformation types.

1. Exercise 1: Trace lineage for a daily sales table.
   Given a transform that creates mart.daily_sales from staging.orders, describe asset-level and column-level lineage, including operation types and any casts.
2. Exercise 2: Write a lineage statement for a join.
   Given a view joining dim.customers, dim.regions, and fact.orders, document source assets, key joins, and column mappings.
3. Exercise 3: Impact analysis (backward vs forward).
   If dim.regions adds a new column region_code and renames region_name to name, identify backward and forward lineage paths and list impacted downstream assets and columns.

Common mistakes and how to self-check

Practical projects

• Build a lineage map for one domain (e.g., Sales): sources -> staging -> core -> marts -> dashboards. Include at least 3 column-level mappings.
• Create an impact analysis playbook: steps to check before renaming/dropping a column, plus communication template to affected teams.
• Add lineage requirements to code reviews: any SQL change includes a brief lineage diff (assets and key column mappings).

Mini challenge

Pick a KPI dashboard and, in 10 minutes, sketch its upstream lineage to raw sources and the top three transformations. Mark where data types change or where PII enters/leaves. What’s the riskiest edge? How would you validate it?

Next steps

• Document lineage for one critical job today; automate extraction this week.
• Tag sensitive columns and verify their downstream lineage.
• Take the quick test below to check your understanding. Note: anyone can take the test; sign in to save your progress.

Quick Test

Everyone can take the Quick Test below. Only logged-in users will have results saved to their progress.